newsmgr.java

《JSP通用模块及典型系统开发实例导航》源代码

/**
 *  Title  新闻管理系统
 *  @author: 王夕宁
 *  @version 1.0
 * 对news表的一些增，删，改，查询操作
 */
package com.wxpn.tutorial.control.news;

import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collection;

import com.wxpn.tutorial.bean.news.News;
import com.wxpn.tutorial.db.DBConnect;

public class NewsMgr {

	/*
	 * 添加新闻
	 */
	public int add(News news) {
		DBConnect dbc = null;
		ResultSet rs = null;
		int newsid = 1;
		try {
			dbc = new DBConnect();
			dbc
					.prepareStatement("INSERT INTO news (topic,content,hits,categoryid,picture,publishtime,publishuser) VALUES ( ?,?,?,?,?,?,?)");
			dbc.setString(1, new String(news.getTopic().getBytes("ISO8859_1"),"UTF-8"));
			dbc.setString(2, new String(news.getContent().getBytes("ISO8859_1"),"UTF-8"));
			dbc.setInt(3, 1);
			dbc.setInt(4, news.getCategoryid());
			dbc.setString(5, new String(news.getPicture().getBytes("ISO8859_1"),"UTF-8"));
			dbc.setDate(6, new java.sql.Date(new java.util.Date().getTime()));
			dbc.setString(7, new String(news.getPublishuser().getBytes("ISO8859_1"),"UTF-8"));
			dbc.executeUpdate();

			dbc.prepareStatement("SELECT max(id) FROM news");
			rs = dbc.executeQuery();
			if (rs.next())
				newsid = rs.getInt(1);

		} catch (Exception e) {
			System.err.println(e);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				dbc.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return newsid;
	}

	/*
	 * 修改新闻
	 */
	public void modify(News news) {
		DBConnect dbc = null;
		try {
			dbc = new DBConnect();
			dbc
					.prepareStatement("UPDATE news SET topic=?,content=?,categoryid=?,picture=? WHERE id=?");
			dbc.setString(1, new String(news.getTopic().getBytes("ISO8859_1"),"UTF-8"));
			dbc.setString(2, new String(news.getContent().getBytes("ISO8859_1"),"UTF-8"));
			dbc.setInt(3, news.getCategoryid());
			dbc.setString(4, new String(news.getPicture().getBytes("ISO8859_1"),"UTF-8"));
			dbc.setInt(5, news.getId());
			dbc.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				dbc.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	/*
	 * 删除新闻
	 */
	public void delete(int id) {
		try {
			DBConnect dbc = new DBConnect();
			dbc.prepareStatement("delete from news WHERE id=?");
			dbc.setInt(1, id);
			dbc.executeUpdate();
			dbc.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/*
	 * 新闻浏览次数加一
	 */
	public void addhits(int id) {
		DBConnect dbc = null;
		try {
			dbc = new DBConnect();
			dbc.prepareStatement("UPDATE news SET hits = hits +1 WHERE id = ?");
			dbc.setInt(1, id);
			dbc.executeUpdate();
		} catch (Exception e) {
			System.err.println(e);
		} finally {
			try {
				dbc.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	/*
	 * 根据CategoryId得到某栏目所有的新闻
	 */
	public Collection getAllByCategory(int categoryId) {
		DBConnect dbc = null;
		Collection c = new ArrayList();
		ResultSet rs = null;
		try {
			dbc = new DBConnect();
			dbc
					.prepareStatement("SELECT * FROM news WHERE categoryId = ? order by id desc");
			dbc.setInt(1, categoryId);
			News news = null;
			rs = dbc.executeQuery();
			while (rs.next()) {
				news = new News();
				news.setId(rs.getInt("id"));
				news.setTopic(rs.getString("topic"));
				news.setContent(rs.getString("content"));
				news.setHits(rs.getInt("hits"));
				news.setPublishtime(rs.getDate("publishtime"));
				news.setPublishuser(rs.getString("publishuser"));
				news.setCategoryid(rs.getInt("categoryid"));
				news.setPicture(rs.getString("picture"));
				c.add(news);
				news = null;
			}
		} catch (Exception e) {
			System.err.println(e);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				dbc.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return c;
	}

	/*
	 * 根据ID得到新闻
	 */
	public News getById(int id) {
		DBConnect dbc = null;
		ResultSet rs = null;
		try {
			dbc = new DBConnect();
			dbc.prepareStatement("SELECT * FROM news WHERE id = ?");
			dbc.setInt(1, id);
			rs = dbc.executeQuery();
			if (rs.next()) {
				News news = new News();
				news.setId(rs.getInt("id"));
				news.setTopic(rs.getString("topic"));
				news.setContent(rs.getString("content"));
				news.setHits(rs.getInt("hits"));
				news.setStrPublishtime(rs.getString("publishtime"));
				news.setPublishuser(rs.getString("publishuser"));
				news.setCategoryid(rs.getInt("categoryid"));
				news.setPicture(rs.getString("picture"));
				return news;
			}
		} catch (Exception e) {
			System.err.println(e);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				dbc.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	/*
	 * 查询所有的新闻(按点击数排序)
	 */
	public Collection getAllByHitsDesc() {
		DBConnect dbc = null;
		Collection c = new ArrayList();
		ResultSet rs = null;
		try {
			dbc = new DBConnect();
			News news = null;
			rs = dbc.executeQuery("SELECT * FROM news order by hits desc");
			while (rs.next()) {
				news = new News();
				news.setId(rs.getInt("id"));
				news.setTopic(rs.getString("topic"));
				news.setContent(rs.getString("content"));
				news.setHits(rs.getInt("hits"));
				news.setStrPublishtime(rs.getString("publishtime"));
				news.setPublishuser(rs.getString("publishuser"));
				news.setCategoryid(rs.getInt("categoryid"));
				news.setPicture(rs.getString("picture"));
				c.add(news);
				news = null;
			}
		} catch (Exception e) {
			System.err.println(e);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				dbc.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return c;
	}

	/*
	 * 计算某类别新闻的总数
	 */
	public int getTotalByCategoryId(int categoryId) {
		DBConnect dbc = null;
		int newsCount = 0;
		ResultSet rs = null;
		try {
			dbc = new DBConnect();
			dbc
					.prepareStatement("SELECT count(*) FROM news WHERE categoryid = ?");
			dbc.setInt(1, categoryId);
			rs = dbc.executeQuery();
			if (rs.next())
				newsCount = rs.getInt(1);
		} catch (Exception e) {
			System.err.println(e);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				dbc.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return newsCount;
	}

	/*
	 * 计算某新闻的最大点击数
	 */
	public int maxHit() {
		DBConnect dbc = null;
		int maxhit = 0;
		ResultSet rs = null;
		try {
			dbc = new DBConnect();
			dbc.prepareStatement("SELECT max(hits) FROM news");
			rs = dbc.executeQuery();
			if (rs.next())
				maxhit = rs.getInt(1);
		} catch (Exception e) {
			System.err.println(e);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				dbc.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return maxhit;
	}

	/*
	 * 根据条件在所有的新闻中查询
	 */
	public Collection searchNews(String search) {
		DBConnect dbc = null;
		Collection c = new ArrayList();
		ResultSet rs = null;
		try {
			dbc = new DBConnect();
			dbc
					.prepareStatement("SELECT * FROM news WHERE topic like ? or content like ? order by id desc");
			dbc.setBytes(1, ("%" + search + "%").getBytes("UTF-8"));
			dbc.setBytes(2, ("%" + search + "%").getBytes("UTF-8"));
			rs = dbc.executeQuery();
			News news = null;
			while (rs.next()) {
				news = new News();
				news.setId(rs.getInt("id"));
				news.setTopic(rs.getString("topic"));
				news.setContent(rs.getString("content"));
				news.setHits(rs.getInt("hits"));
				news.setStrPublishtime(rs.getString("publishtime"));
				news.setPublishuser(rs.getString("publishuser"));
				news.setCategoryid(rs.getInt("categoryid"));
				news.setPicture(rs.getString("picture"));
				c.add(news);
				news = null;
			}
		} catch (Exception e) {
			System.err.println(e);
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
				dbc.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return c;
	}
}