newsdao.java

企业宣传网站

package s2jsp.bysj.dao;

import s2jsp.bysj.entity.*;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.*;

public class NewsDao extends BaseDao {
	private Connection conn = null; // 保存数据库连接

	private PreparedStatement pstmt = null; // 用于执行SQL语句

	private ResultSet rs = null; // 用户保存查询结果集

	private final int TOP = 7; // 定义首页上显示新闻的记录数

	public static final int PAGE_NUM = 10;// 定义新闻列表页面显示新闻行数

	/**
	 * 查找所有的新闻
	 * 
	 * @return 新闻的集合
	 */
	public List findAllNews() {
		List list = new ArrayList();
		String sql = "select * from news order by newsID desc ";

		try {
			conn = this.getConn(); // 取得数据库连接
			pstmt = conn.prepareStatement(sql); // 取得PreparedStatement对象

			rs = pstmt.executeQuery(); // 执行SQL取得结果集
			while (rs.next()) {
				News news = new News();
				news.setNewsID(rs.getInt("newsID"));
				news.setTitle(rs.getString("title"));
				news.setContent(rs.getString("content"));
				news.setWriterDate(rs.getString("writerDate"));

				list.add(news);
			}
		} catch (Exception e) {
			e.printStackTrace(); // 处理异常
		} finally {
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}

	/**
	 * 查找最新的7条新闻
	 * 
	 * @return 5条新闻的集合
	 */
	public List findNews() {
		List list = new ArrayList();
		String sql = "select top " + TOP + " * from news order by newsID desc ";

		try {
			conn = this.getConn(); // 取得数据库连接
			pstmt = conn.prepareStatement(sql); // 取得PreparedStatement对象

			rs = pstmt.executeQuery(); // 执行SQL取得结果集
			while (rs.next()) {
				News news = new News();
				news.setNewsID(rs.getInt("newsID"));
				news.setTitle(rs.getString("title"));
				news.setContent(rs.getString("content"));
				news.setWriterDate(rs.getString("writerDate"));

				list.add(news);
			}
		} catch (Exception e) {
			e.printStackTrace(); // 处理异常
		} finally {
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}

	/**
	 * 根据新闻的ID查找新闻信息
	 * 
	 * @param newsID
	 * @return 与newsID相关的新闻信息对象
	 */
	public News findNewsByID(String newsID) {
		String sql = "select * from news where newsID= ? ";
		News news = null;
		try {
			conn = this.getConn(); // 取得数据库连接
			pstmt = conn.prepareStatement(sql); // 取得PreparedStatement对象
			pstmt.setString(1, newsID);
			rs = pstmt.executeQuery(); // 执行SQL取得结果集
			while (rs.next()) {
				news = new News();
				news.setNewsID(rs.getInt("newsID"));
				news.setTitle(rs.getString("title"));
				news.setContent(rs.getString("content"));
				news.setWriterDate(rs.getString("writerDate"));
			}
		} catch (Exception e) {
			e.printStackTrace(); // 处理异常
		} finally {
			this.closeAll(conn, pstmt, rs);
		}
		return news;
	}

	/**
	 * 实现新闻分页显示
	 * 
	 * @param page
	 *            当前页数
	 * @return 显示新闻的列表
	 */
	public List showNewsForPage(int page) {
		List list = new ArrayList();

		int number = 0;
		if (page > 1) {
			number = PAGE_NUM * (page - 1);
		}
		String sql = "select top "
				+ PAGE_NUM
				+ " * from news where  newsID not in ( select top "
				+ number
				+ " newsID from news order by newsID desc ) order by newsID desc";
		try {
			conn = this.getConn();
			pstmt = conn.prepareStatement(sql);

			rs = pstmt.executeQuery();
			while (rs.next()) {
				News news = new News();
				news.setNewsID(rs.getInt("newsID"));
				news.setTitle(rs.getString("title"));
				news.setContent(rs.getString("content"));
				news.setWriterDate(rs.getString("writerDate"));

				list.add(news);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			this.closeAll(conn, pstmt, rs);
		}

		return list;
	}

	/**
	 * 根据新闻ID删除相关新闻信息
	 * 
	 * @param newsID
	 *            新闻ID
	 * @return 执行SQL语句影响数据库的行数
	 */
	public int deleteNewsByID(String newsID) {
		String sql = "delete from news where newsID = ? ";
		String[] param=new String[]{ newsID };
		return this.executeSQL(sql, param);
	}

	/**
	 *添加新闻信息 
	 * @param news 封装了信息的新闻对象
	 * @return 执行SQL语句所影响数据库的行数
	 */
	public int insertNews(News news) {
		String sql = "insert into news values(?,?,?)";
		String[] param = new String[] { news.getTitle(), news.getContent(),
				new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()) };
		return this.executeSQL(sql, param);

	}

}