newsinfodao.java

基于j2ee的物流软件

package com.wuliu.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.wuliu.DBConnection.DBConnection;
import com.wuliu.entity.NewsInfo;

public class NewsInfoDAO {

	/**
	 * @袁子鹏 新闻的所有方法
	 */
	//2.15崔斌添加新闻的发布时间
	/*查找全部新闻*/
	public List<NewsInfo> selectNews(){
		List<NewsInfo> list = new ArrayList<NewsInfo>();
		DBConnection db = new DBConnection();
		Connection cn = db.getConnection();
		try
		{
			Statement st = cn.createStatement();
			ResultSet rs = st.executeQuery("select * from NewsTable");
			while(rs.next()){
				int newsId = rs.getInt("NewsId");
				String newsTitle =rs.getString("NewsTitle");
				String newsContent = rs.getString("NewsContent");
				String newsAuthor = rs.getString("NewsAuthor");
				String newsPic = rs.getString("NewsPic");
				String newsRemark = rs.getString("NewsRemark");
				String newsTime = rs.getString("NewsTime");
				NewsInfo ni = new NewsInfo(newsId,newsTitle,newsContent,newsAuthor,newsPic,newsRemark,newsTime.substring(0,10));
				list.add(ni);
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally{
			db.closeConnection(cn);
		}
		return list;
	}
	
	/*查找一条新闻*/
	public NewsInfo selectNewsById(int NewsId){
		NewsInfo news = null;
		DBConnection db = new DBConnection();
		Connection cn = db.getConnection();
		try
		{
			Statement st =  cn.createStatement();
			ResultSet rs = st.executeQuery("select * from NewsTable where NewsId='"+NewsId+"'");
			if(rs.next()){
				int newsId = rs.getInt("NewsId");
				String newsTitle =rs.getString("NewsTitle");
				String newsContent = rs.getString("NewsContent");
				String newsAuthor = rs.getString("NewsAuthor");
				String newsPic = rs.getString("NewsPic");
				String newsRemark = rs.getString("NewsRemark");
				String newsTime = rs.getString("NewsTime");
				news = new NewsInfo(newsId,newsTitle,newsContent,newsAuthor,newsPic,newsRemark,newsTime);
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		return news;
	}
	
	/*新增一条新闻*/
	public void insertNewsInfo(NewsInfo news){
		DBConnection db = new DBConnection();
		Connection cn = db.getConnection();
		try
		{
			PreparedStatement ps = cn.prepareStatement("insert into " +
					"NewsTable(NewsTitle,NewsContent,NewsAuthor,NewsPic,NewsRemark) " +
					"values(?,?,?,?,?)");
			ps.setString(1,news.getNewsTitle());
			ps.setString(2,news.getNewsContent());
			ps.setString(3, news.getNewsAuthor());
			ps.setString(4, news.getNewsPic());
			ps.setString(5, news.getNewsRemark());
			ps.addBatch();
			ps.executeUpdate();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally{
			db.closeConnection(cn);
		}
	}
	
	/*修改一条新闻*/
//	public void updateNewsInfo(int NewsId,String NewsTitle,String NewsContent,String NewsAuthor,String NewsPic,String NewsRemark){
//		DBConnection db = new DBConnection();
//		Connection cn = db.getConnection();
//		try
//		{
//			PreparedStatement ps = cn.prepareStatement("update NewsTable set NewsTitle = ?,NewsContent=?,NewsAuthor=?,NewsPic=?,NewsRemark=? where NewsId=?");
//			ps.setString(1,NewsTitle);
//			ps.setString(2,NewsContent);
//			ps.setString(3,NewsAuthor);
//			ps.setString(4, NewsPic);
//			ps.setString(5, NewsRemark);
//			ps.setInt(6, NewsId);
//			ps.addBatch();
//			ps.executeUpdate();
//		} catch (SQLException e)
//		{
//			e.printStackTrace();
//		}finally{
//			db.closeConnection(cn);
//		}
//	}
	
	/*修改一条新闻*/
	public void updateNewsInfo(int NewsId,String NewsTitle,String NewsContent,String NewsPic,String NewsRemark){
		DBConnection db = new DBConnection();
		Connection cn = db.getConnection();
		try
		{
			PreparedStatement ps = cn.prepareStatement("update NewsTable set NewsTitle = ?,NewsContent=?,NewsPic=?,NewsRemark=? where NewsId=?");
			ps.setString(1,NewsTitle);
			ps.setString(2,NewsContent);
			ps.setString(3, NewsPic);
			ps.setString(4, NewsRemark);
			ps.setInt(5, NewsId);
			ps.addBatch();
			ps.executeUpdate();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally{
			db.closeConnection(cn);
		}
	}
	
	/*删除一条新闻*/
	public void deleteNewsInfo(int NewsId){
		DBConnection db = new DBConnection();
		Connection cn = db.getConnection();
		try
		{
			Statement st = cn.createStatement();
			st.executeUpdate("delete from NewsTable where NewsId='"+NewsId+"'");
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			db.closeConnection(cn);
		}
	}
	
	//通过页面大小获取总页数
	public int getPageNum(int pageSize) {
		DBConnection db = new DBConnection();
		Connection cn = db.getConnection();
		int num = 0;
		try {
			PreparedStatement ps = cn.prepareStatement("select count(*) from NewsTable");
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				num = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeConnection(cn);
		}
		if (num % pageSize == 0) {
			return num / pageSize;
		} else {
			return num / pageSize + 1;
		}
	}
	
	// 分页查询新闻信息
	public List<NewsInfo> selectNewsInfoPage(int pageSize, int pageNum) {
		DBConnection db = new DBConnection();
		Connection cn = db.getConnection();
		List<NewsInfo> list = new ArrayList<NewsInfo>();
		try {
			PreparedStatement ps = cn.prepareStatement("select top " + pageSize
							+ "* from NewsTable where NewsId not in(select top "
							+ pageSize * pageNum
							+ " NewsId from NewsTable order by NewsId)order by NewsId");
			ResultSet rs =ps.executeQuery();
			while (rs.next()) {
				int newsId = rs.getInt("NewsId");
				String newsTitle =rs.getString("NewsTitle");
				String newsContent = rs.getString("NewsContent");
				String newsAuthor = rs.getString("NewsAuthor");
				String newsPic = rs.getString("NewsPic");
				String newsRemark = rs.getString("NewsRemark");
				String newsTime = rs.getString("NewsTime");
				NewsInfo ni = new NewsInfo(newsId,newsTitle,newsContent,newsAuthor,newsPic,newsRemark,newsTime.substring(0,10));
				list.add(ni);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeConnection(cn);
		}
		return list;
	}
	
	public static void main(String[] args){
		//测试查找
//		NewsInfoDAO nidao = new NewsInfoDAO();
//		List<NewsInfo> list = nidao.selectNewsInfoPage(4, 0);
//		Iterator<NewsInfo> it = list.iterator();
//		while(it.hasNext()){
//			NewsInfo ni = it.next();
//			System.out.println(ni.getNewsId()+" "+ni.getNewsAuthor()+" "+ni.getNewsContent()+" "+ni.getNewsPic()+" "+ni.getNewsRemark()+" "+ni.getNewsTitle());
//		}
//	    NewsInfo ni = new  NewsInfo("都发生","大幅度","峨峨","二二五","法国风格");
//	    ni = nidao.selectNewsById(1);
//		System.out.print(ni.getNewsContent()+" "+ni.getNewsAuthor()+" "+ni.getNewsTitle());
//		nidao.insertNewsInfo(ni);
//	    nidao.updateNewsInfo(5, "的", "的", "的", "的", "的");
//		nidao.deleteNewsInfo(5);
	}
	
	
}