知识库系统源代码——持久层（JDBC）

package com.cr.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import com.cr.model.News;
import com.cr.model.PageBean;
import com.cr.util.StringUtil;

/**
 * @author 汤如义
 * @项目名称：Rbps   
 * @类名称：NewsDao   
 * @类描述：  信息dao层    
 * @创建时间：2016年7月28日 上午9:39:44      
 */
public class NewsDao {
	
	public ResultSet newsList(Connection con,PageBean pageBean,News news) throws Exception {
		StringBuffer sb=new StringBuffer("select * from t_news t  where 1=1 ");
		if(StringUtil.isNotEmpty(news.getName())){
			sb.append(" and t.name like '%"+news.getName()+"%' ");
		}
		sb.append(" order by t.createtime desc ");
		if(pageBean!=null){
			sb.append(" limit "+pageBean.getStart()+","+pageBean.getRows());
		}
		PreparedStatement pstmt=con.prepareStatement(sb.toString());
		return pstmt.executeQuery();
	}
	
	public int newsCount(Connection con,News news) throws Exception{
		StringBuffer sb=new StringBuffer("select count(*) as total from t_news t  where 1=1 ");
		if(StringUtil.isNotEmpty(news.getName())){
			sb.append(" and t.name like '%"+news.getName()+"%' ");
		}
		PreparedStatement pstmt=con.prepareStatement(sb.toString());
		ResultSet rs=pstmt.executeQuery();
		if(rs.next()){
			return rs.getInt("total");
		}else{
			return 0;
		}
	}
	
	public int newsAdd(Connection con,News news) throws Exception{
		
		String sql="insert into t_news values(null,?,?,?)";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1, news.getName());
		pstmt.setString(2, news.getText());
		pstmt.setDate(3,new java.sql.Date(news.getCreatetime().getTime()));
		int executeUpdate = pstmt.executeUpdate();
		return executeUpdate;
	}
	
	public int newDelete(Connection con,String ids)throws Exception{
		String sql="delete from t_news where id in ("+ids+")";
		PreparedStatement pstmt=con.prepareStatement(sql);
		return pstmt.executeUpdate();
	}
	
	public News showNew(Connection con,String ids)throws Exception{
		String sql="select * from t_news where id = ("+ids+")";
		PreparedStatement pstmt=con.prepareStatement(sql);
		ResultSet resultSet = pstmt.executeQuery();
		News news=new News();
		while(resultSet.next()){
			String text = resultSet.getString("text");
			int id = resultSet.getInt("id");
			String name = resultSet.getString("name");
			Date date = resultSet.getDate("createtime");
			news.setCreatetime(date);
			news.setName(name);
			news.setText(text);
			news.setId(id);
		}
		return news;
	}
	public int newsEdit(Connection con,News news) throws Exception{
		String sql="update t_news set name=?,text=? where id=?";
		PreparedStatement pstmt=con.prepareStatement(sql);
		pstmt.setString(1, news.getName());
		pstmt.setString(2, news.getText());
		pstmt.setInt(3, news.getId());
		return pstmt.executeUpdate();
	}
}