newsdao.java

简单的企业宣传的网站

package dao;
import entity.*;
import java.util.*;
import java.text.*;
import java.sql.*;
import java.util.Date;
public class NewsDao extends BaseDao{
	private Connection conn=null;
	private PreparedStatement pstmt=null;
	private ResultSet rs=null;
	private final int top = 7;
	public static final int page_num = 10;
	public List findAllNews()
	{
		List list=new ArrayList();
		String sql="select * from news 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 (ClassNotFoundException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}finally
		{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	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);
			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 (ClassNotFoundException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}finally
		{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	public News findNewsByID(String newsID)
	{
		String sql="select * from news where newsID=?";
		News news=null;
		try {
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, newsID);
			rs=pstmt.executeQuery();
			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 (ClassNotFoundException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}finally
		{
			this.closeAll(conn, pstmt, rs);
		}
		return news;
	}
	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 (ClassNotFoundException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}finally
		{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
	public int deleteNewsByID(String newsID)
	{
		String sql="delete from news where newsID = ?";
		String[] param=new String[]{ newsID };
		return this.excuteSQL(sql, param);
	}
	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.excuteSQL(sql, param);
	}
}