mailaddrbookdetaildao.java

功能是公安和学校的管理系统,用struts+hibernate+spring等匡架组成,在eclepse就能编译

package com.comingnet.bean;

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

import com.db.KeyGenException;
import com.db.KeyGenerator;
import com.db.ProxoolAction;

public class MailAddrBookDetailDAO {
	private Connection conn ;
	private PreparedStatement pstmt ;
	private ResultSet rs ;
	
	public int getCount(int lbid){//获得每收藏夹下文件的数量
		String sqlStr ="SELECT COUNT(*) FROM txl_xx WHERE lb_id =? " ;
		int count = 0 ;
		try {
			conn = ProxoolAction.getConnection();
			pstmt = conn.prepareStatement(sqlStr);
			pstmt.setInt(1, lbid) ;
			rs = pstmt.executeQuery() ;
			while(rs.next()){
				count = rs.getInt(1) ;
			}
		} catch (Exception e) {
			// TODO: handle exception
			System.out.println("MailAddrBookDetailDAO.exception------------->"+e) ;
		}finally{
			ProxoolAction.closeResultSet(rs) ;
			ProxoolAction.closeStatement(pstmt) ;
			ProxoolAction.closeConnection(conn) ;
		}
		return count ;
	}
	public ArrayList<MailAddrBookDetail> queryByFzlbId(int lbid,int  pageno, int recordPerPage){//根据分组类别id的查询
		String sqlStr = "SELECT TOP "+recordPerPage+"* FROM txl_xx WHERE lb_id = ? and id NOT IN (SELECT TOP "+(pageno-1)*recordPerPage+" id FROM txl_xx  WHERE lb_id = ? ORDER BY id )ORDER BY id" ;
		MailAddrBookDetail addrBookDetail = null ;
		ArrayList<MailAddrBookDetail> listResult = new ArrayList<MailAddrBookDetail>() ;
		
		try {
			conn = ProxoolAction.getConnection();
			pstmt = conn.prepareStatement(sqlStr);
			pstmt.setInt(1, lbid) ;
			pstmt.setInt(2, lbid) ;
			rs = pstmt.executeQuery() ;
			while(rs.next()){
				addrBookDetail = new MailAddrBookDetail() ;
				
				addrBookDetail.setId(rs.getInt("id")) ;
				addrBookDetail.setLb_id(rs.getInt("lb_id")) ;
				addrBookDetail.setYjlxr(rs.getString("yjlxr")) ;
				addrBookDetail.setYjzh(rs.getString("yjzh")) ;
				
				listResult.add(addrBookDetail) ;
			}
			
		} catch (Exception e) {
			// TODO: handle exception
			System.out.println("MailAddrBookDetailDAO.exception------------->"+e) ;
		}finally{
			ProxoolAction.closeResultSet(rs) ;
			ProxoolAction.closeStatement(pstmt) ;
			ProxoolAction.closeConnection(conn) ;
		}
		return listResult ;
	}
	
	public MailAddrBookDetail queryById(int id){
		String sqlStr = "SELECT * FROM txl_xx WHERE id = ?" ;
		MailAddrBookDetail addrBookDetail = null ;
		
		try {
			conn = ProxoolAction.getConnection();
			pstmt = conn.prepareStatement(sqlStr);
			pstmt.setInt(1, id) ;
			rs = pstmt.executeQuery() ;
			while(rs.next()){
				addrBookDetail = new MailAddrBookDetail() ;
				
				addrBookDetail.setId(rs.getInt("id")) ;
				addrBookDetail.setLb_id(rs.getInt("lb_id")) ;
				addrBookDetail.setYjlxr(rs.getString("yjlxr")) ;
				addrBookDetail.setYjzh(rs.getString("yjzh")) ;
			}
			System.out.println("addrbid"+addrBookDetail.getYjlxr()) ;
			
		} catch (Exception e) {
			// TODO: handle exception
			System.out.println("MailAddrBookDetailDAO.load.exception------------->"+e) ;
		}finally{
			ProxoolAction.closeResultSet(rs) ;
			ProxoolAction.closeStatement(pstmt) ;
			ProxoolAction.closeConnection(conn) ;
		}
		return addrBookDetail ;
	}
	
	public boolean add(MailAddrBookDetail addrBookDetail){
		String sqlStr ="INSERT INTO txl_xx(id,lb_id,yjzh,yjlxr)VALUES(?,?,?,?)" ;
		boolean flag = false ;
		
		try {
			int id = KeyGenerator.getInstance().getNextKey("txl_xx") ;
			conn = ProxoolAction.getConnection();
			pstmt = conn.prepareStatement(sqlStr);
			pstmt.setInt(1, id) ;
			pstmt.setInt(2, addrBookDetail.getLb_id()) ;
			pstmt.setString(3, addrBookDetail.getYjzh()) ;
			pstmt.setString(4, addrBookDetail.getYjlxr()) ;
			pstmt.executeUpdate() ;
			flag = true ;
		} catch (SQLException se) {
			// TODO: handle exception
			System.out.println("MailAddrBookDetailDAO.add.SQLException-------------->"+se) ;
			
		} catch(KeyGenException ke){
			System.out.println("MailAddrBookDetailDAO.KeyGenException-------------->"+ke) ;
		} finally{
			ProxoolAction.closeStatement(pstmt) ;
			ProxoolAction.closeConnection(conn) ;
		}
		return flag ;		
	}
	
	public boolean delete(int id){
		String sqlStr = "DELETE FROM txl_xx WHERE ID = ?" ;
		boolean flag = false ;
		
		try {
			conn = ProxoolAction.getConnection();
			pstmt = conn.prepareStatement(sqlStr);
			pstmt.setInt(1, id) ;
			pstmt.executeUpdate() ;
			flag = true ;
		} catch (SQLException se) {
			// TODO: handle exception
			System.out.println("MailAddrBookDetailDAO.SQLException-------------->"+se) ;
		}finally{
			ProxoolAction.closeStatement(pstmt) ;
			ProxoolAction.closeConnection(conn) ;
		}
		return flag ;		
	}
	
	public boolean edit(MailAddrBookDetail addrBookDetail){
		boolean flag = false ;
		String sqlStr = "UPDATE txl_xx SET yjlxr=?,yjzh=? WHERE id=?" ;
		
		try {
			conn = ProxoolAction.getConnection() ;
			pstmt = conn.prepareStatement(sqlStr) ;
			pstmt.setString(1, addrBookDetail.getYjlxr()) ;
			pstmt.setString(2, addrBookDetail.getYjzh()) ;
			pstmt.setInt(3, addrBookDetail.getId()) ;
			pstmt.executeUpdate() ;
			flag = true ;
				} catch (Exception e) {
			System.out.println("MailAddrBookDetailDAO.edit.exception------------->"+e) ;
			
		}finally{
			ProxoolAction.closeStatement(pstmt) ;
			ProxoolAction.closeConnection(conn) ;
		}	
		return flag ;	}
	
}