jianliuxinxidaoimpl1.java

来自「功能是公安和学校的管理系统,用struts+hibernate+spring等匡」· Java 代码 · 共 156 行

package com.comingnet.bean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
//import java.sql.Statement;
import java.util.ArrayList;

import com.db.DBUtils;

public class JianLiuXinXiDAOImpl1 implements JianLiuXinXiDAO1 {

	public ArrayList<JianLiuXinXi1> getAll(int pageNum,int recordPerPage,String xs) {
		//String sql="select no.n_id,no.n_bt,no.n_fbrzh,no.n_fbsj from m_notice no where no.n_xszt='1' order by no.n_id desc";
		String sql="select top "+recordPerPage+" no.ar_id,no.ar_bt,no.ar_fbrzh,no.ar_fbsj,no.ar_fbrxm,lm1.lmmc from w_article no,w_lmb lm1 where " +
				" no.ar_lm1=lm1.lmid and ar_id not in (select top "+((pageNum-1)*recordPerPage)+
				" ar_id  from  w_article where "+xs+"  order by  ar_id DESC) and "+xs+" order by  ar_id DESC";
//		System.out.println("pageCountSql--->"+sql) ;
		Connection conn=null;
		//Statement stmt=null;
		PreparedStatement stmt=null;
		ResultSet rs=null;
		ArrayList<JianLiuXinXi1> hsJlxx=new ArrayList<JianLiuXinXi1>();
		try{
			conn=DBUtils.getConnection();
			stmt=conn.prepareStatement(sql);
			/*stmt=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			if(pageNum!=0){
				stmt.setFetchSize(recordPerPage);
			}
			rs=stmt.executeQuery(sql);
			if (pageNum>1){//第一页不需要重定位，只需对大于一页的才进行
				rs.absolute(recordPerPage*(pageNum-1));//将记录移到指定的行编号
			}
			while(rs.next()&&((rowCount++)<recordPerPage)){
				JianLiuXinXi jlxx=new JianLiuXinXi();
				jlxx.setId(rs.getInt("n_id"));
				jlxx.setBt(rs.getString("n_bt"));
				jlxx.setFbrzh(rs.getString("n_fbrzh"));
				jlxx.setFbsj(rs.getDate("n_fbsj"));
				hsJlxx.add(jlxx);
			}*/
			rs=stmt.executeQuery();
			while(rs.next()){
				JianLiuXinXi1 jlxx=new JianLiuXinXi1();
				jlxx.setId(rs.getInt("ar_id"));
				jlxx.setBt(rs.getString("ar_bt"));
				jlxx.setFbrzh(rs.getString("ar_fbrzh"));
				jlxx.setFbsj(formatDate(rs.getTimestamp("ar_fbsj")));
				jlxx.setFbrxm(rs.getString("ar_fbrxm")) ;
				jlxx.setLmmc(rs.getString("lmmc"));
				hsJlxx.add(jlxx);
			}
			DBUtils.closeResultSet(rs);
			DBUtils.closeStatement(stmt);
		}catch(SQLException e){
			System.out.println("getAll"+e) ;
		}finally{
			DBUtils.closeResultSet(rs);
			DBUtils.closeStatement(stmt);
			DBUtils.closeConnection(conn);
		}
		
		return hsJlxx;
	}

	public JianLiuXinXi1 getById(int id) {
		String sql="select no.ar_id,no.ar_bt,no.ar_fbrzh,no.ar_fbsj,no.ar_fbrxm from w_article no where no.ar_id=? and no.ar_xszt='1'";
		Connection conn=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		JianLiuXinXi1 jlxx=null;
		try{
			conn=DBUtils.getConnection();
			pstmt=conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			rs=pstmt.executeQuery();
			if(rs.next()){
				jlxx=new JianLiuXinXi1();
				jlxx.setId(rs.getInt("ar_id"));
				jlxx.setBt(rs.getString("ar_bt"));
				jlxx.setFbrzh(rs.getString("ar_fbrzh"));
				jlxx.setFbsj(formatDate(rs.getTimestamp("ar_fbsj")));
				jlxx.setFbrxm(rs.getString("ar_fbrxm"));
			}
			DBUtils.closeResultSet(rs);
			DBUtils.closeStatement(pstmt);
		}catch(SQLException e){
			
			e.printStackTrace();
			System.out.println("getbyid"+e) ;
		}finally{
			DBUtils.closeResultSet(rs);
			DBUtils.closeStatement(pstmt);
			DBUtils.closeConnection(conn);
		}
		
		return jlxx;
	}
	
	
	public int getPageCount(String cond) {
		String sql="select count(*) from w_article no where "+cond;
		System.out.println("pageCountSql--->"+sql) ;
		Connection conn=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		int pageCount=0;
		try{
			conn=DBUtils.getConnection();
			pstmt=conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			if(rs.next()){
				pageCount=rs.getInt(1);
			}
			DBUtils.closeResultSet(rs);
			DBUtils.closeStatement(pstmt);
		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("pageCount--->"+e) ;
		}finally{
			DBUtils.closeResultSet(rs);
			DBUtils.closeStatement(pstmt);
			DBUtils.closeConnection(conn);
		}
		
		return pageCount;
	}

	public static void main(String[] args){
		JianLiuXinXiDAO1 jlxxDao=new JianLiuXinXiDAOImpl1();
		JianLiuXinXi1 jlxx=jlxxDao.getById(1);
		if(jlxx!=null){
			System.out.println(jlxx.getId()+" "+jlxx.getBt()+" "+jlxx.getFbrzh()+" "+jlxx.getFbsj());
		}else{
			System.out.println("数据库中没有相应的信息.....");
		}
		//HashSet<JianLiuXinXi> hs=jlxxDao.getAll();
		//System.out.println(hs.size());
		
//		System.out.println("PageCount:"+jlxxDao.getPageCount());
	}
	
	private String formatDate(Timestamp time){
		String formattedDate = "";
		if(time != null){
		    SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");   
		    formattedDate = formatter.format(time);
		}
	    return formattedDate;
	}
}