jsp+javabean +mysql开发的新闻发布系统

/** * Title 新闻管理系统 * @author: 陈钢 * Company: http://www.pcccp.com * Copyright: Copyright (c) 2003 * @version 1.0 * 新闻表中的一些查询操作 */ package view; import java.sql.*; import java.util.*; import javax.servlet.http.HttpServletRequest; import controller.PageQuery; import bean.News; import bean.Page; import database.DBCon; public class ViewNews extends News { public ResultSet rs; public ViewNews() { } /* * 根据rootid得到某栏目所有的新闻 */ public ArrayList<News> rootidToNews(int rootID) { DBCon db = null; ArrayList<News> newsInfoVector = new ArrayList<News>(); try { db = new DBCon(); String sql = "SELECT * FROM news WHERE rootid =" + rootID; rs = db.doQuery(sql); while (rs.next()) { News news = new News(); news.setID(rs.getInt("id")); news.setTopic(rs.getString("topic")); news.setBody(rs.getString("body")); news.setHits(rs.getInt("hits")); news.setAdddate(rs.getString("adddate")); news.setAdduser(rs.getString("adduser")); news.setRootID(rs.getInt("rootid")); news.setPic(rs.getString("pic")); newsInfoVector.add(news); } } catch (Exception e) { System.err.println(e); } finally { try { db.closeConnection(); } catch (Exception e) { e.printStackTrace(); } } return newsInfoVector; } /* * 根据adduser得到某栏目所有的新闻 */ public ArrayList<News> adduserToNews() { DBCon db = null; ArrayList<News> newsInfoVector = new ArrayList<News>(); try { db = new DBCon(); String sql = "SELECT * from news WHERE adduser =" + adduser; rs = db.doQuery(sql); while (rs.next()) { News news = new News(); news.setID(rs.getInt("id")); news.setTopic(rs.getString("topic")); news.setBody(rs.getString("body")); news.setHits(rs.getInt("hits")); news.setAdddate(rs.getString("adddate")); news.setAdduser(rs.getString("adduser")); news.setRootID(rs.getInt("rootid")); news.setPic(rs.getString("pic")); newsInfoVector.add(news); } } catch (Exception e) { System.err.println(e); } finally { try { db.closeConnection(); } catch (Exception e) { e.printStackTrace(); } } return newsInfoVector; } /* * 根据ID得到新闻 */ public News idToNews() { DBCon db = null; News news = new News(); try { db = new DBCon(); String sql = "SELECT * FROM news WHERE id =" + ID; rs = db.doQuery(sql); if (rs.next()) { news.setID(rs.getInt("id")); news.setTopic(rs.getString("topic")); news.setBody(rs.getString("body")); news.setHits(rs.getInt("hits")); news.setAdddate(rs.getString("adddate")); news.setAdduser(rs.getString("adduser")); news.setRootID(rs.getInt("rootid")); news.setPic(rs.getString("pic")); } } catch (Exception e) { System.err.println(e); } finally { try { db.closeConnection(); } catch (Exception e) { e.printStackTrace(); } } return news; } /* * 查询所有的新闻(按点击数排序) */ public ArrayList<News> allNews() { DBCon db = null; ArrayList<News> allNewsVector = new ArrayList<News>(); try { db = new DBCon(); rs = db.doQuery("SELECT * FROM news"); while (rs.next()) { News news = new News(); news.setID(rs.getInt("id")); news.setTopic(rs.getString("topic")); news.setBody(rs.getString("body")); news.setHits(rs.getInt("hits")); news.setAdddate(rs.getString("adddate")); news.setAdduser(rs.getString("adduser")); news.setRootID(rs.getInt("rootid")); news.setPic(rs.getString("pic")); allNewsVector.add(news); } } catch (Exception e) { System.err.println("error:" + e); } finally { try { db.closeConnection(); } catch (Exception e) { e.printStackTrace(); } } return allNewsVector; } /* * 查询所有的新闻(按发布时间排序) */ public ArrayList<News> allNewsNum() { DBCon db = null; ArrayList<News> allNewsVector = new ArrayList<News>(); try { db = new DBCon(); rs = db.doQuery("SELECT * FROM news order by id desc limit 10"); while (rs.next()) { News news = new News(); news.setID(rs.getInt("id")); news.setTopic(rs.getString("topic")); news.setBody(rs.getString("body")); news.setHits(rs.getInt("hits")); news.setAdddate(rs.getString("adddate")); news.setAdduser(rs.getString("adduser")); news.setRootID(rs.getInt("rootid")); news.setPic(rs.getString("pic")); allNewsVector.add(news); } } catch (Exception e) { System.err.println("error:" + e); } finally { try { db.closeConnection(); } catch (Exception e) { e.printStackTrace(); } } return allNewsVector; } /* * 计算某类别新闻的总数 */ public int newsNum() { DBCon db = null; int newsCount = 0; try { db = new DBCon(); String sql = "SELECT count(*) FROM news WHERE rootid =" + rootID; rs = db.doQuery(sql); if (rs.next()) newsCount = rs.getInt(1); } catch (Exception e) { System.err.println(e); } finally { try { db.closeConnection(); } catch (Exception e) { e.printStackTrace(); } } return newsCount; } /* * 计算某新闻的最大点击数 */ public int maxHit() { DBCon db = null; int maxhit = 0; try { db = new DBCon(); rs = db.doQuery("SELECT max(hits) FROM news"); if (rs.next()) maxhit = rs.getInt(1); } catch (Exception e) { System.err.println(e); } finally { try { db.closeConnection(); } catch (Exception e) { e.printStackTrace(); } } return maxhit; } public String search; public String getSearch() { return search; } public void setSearch(String s) { this.search = s; } /* * 根据条件在所有的新闻中查询 */ public ArrayList<News> searchNews() { DBCon db = null; ArrayList<News> searchNewsInfoVector = new ArrayList<News>(); try { db = new DBCon(); String sql = "SELECT * FROM news WHERE topic like '%" + search + "%'"; rs = db.doQuery(sql); while (rs.next()) { News news = new News(); news.setID(rs.getInt("id")); news.setTopic(rs.getString("topic")); news.setBody(rs.getString("body")); news.setHits(rs.getInt("hits")); news.setAdddate(rs.getString("adddate")); news.setAdduser(rs.getString("adduser")); news.setRootID(rs.getInt("rootid")); news.setPic(rs.getString("pic")); searchNewsInfoVector.add(news); } } catch (Exception e) { System.err.println(e); } finally { try { db.closeConnection(); } catch (Exception e) { e.printStackTrace(); } } return searchNewsInfoVector; } public ArrayList<Page> page(HttpServletRequest request){ ArrayList<Page> pageList=new ArrayList<Page>(); try { String sql = "SELECT * FROM news"; PageQuery pg=new PageQuery(); pg.setMaxLine(10); ResultSet rs=pg.myQuery(sql, request); String bar=pg.PageLegend(); while(rs.next()) { Page p1=new Page(); News news=new News(); news.setID(rs.getInt("id")); news.setTopic(rs.getString("topic")); news.setBody(rs.getString("body")); news.setAdddate(rs.getString("adddate")); news.setRootID(rs.getInt("rootid")); news.setHits(rs.getInt("hits")); news.setAdduser(rs.getString("adduser")); p1.setObject(news); p1.setPageBar(bar); pageList.add(p1); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } return pageList; } public ArrayList<Page> pageById(HttpServletRequest request,int pid){ ArrayList<Page> pageList=new ArrayList<Page>(); try { String sql = "SELECT * FROM news where rootid="+pid ; PageQuery pg=new PageQuery(); pg.setMaxLine(10); ResultSet rs=pg.myQuery(sql, request); Strin