assetstypedao.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.DBUtils;
import com.db.KeyGenerator;

public class AssetsTypeDAO {
	private Connection conn ;
	private PreparedStatement pstmt ;
	private ResultSet rs ;
	
	public ArrayList<AssetsType> queryAll(int pageno, int recordPerPage){
		ArrayList<AssetsType> resultList = new ArrayList<AssetsType>() ;
		String sqlStr = "select top "+recordPerPage+" * from assets_lb where asset_lbid not in (select top "+(pageno-1)*recordPerPage+" asset_lbid from assets_lb order by asset_lbid) order by asset_lbid";
		AssetsType at = null ; 
		try {
			conn = DBUtils.getConnection() ;
			pstmt = conn.prepareStatement(sqlStr) ;
			rs = pstmt.executeQuery() ;
			while (rs.next()){
				at = new AssetsType() ;
				at.setId(rs.getInt("asset_lbid")) ;
				at.setLbbh(rs.getString("asset_lbbh")) ;
				at.setLbbz(rs.getString("asset_lbbz")) ;
				at.setLbmc(rs.getString("asset_lbmc")) ;
				at.setLbms(rs.getString("asset_lbms"));
				resultList.add(at);
				}
			
			DBUtils.closeResultSet(rs);
			DBUtils.closeStatement(pstmt);
		} catch (SQLException e) {
			System.out.println("AssetsTypeDAO.qureyAll--------->"+e) ;
		}finally{
			DBUtils.closeResultSet(rs);
			DBUtils.closeStatement(pstmt);
			DBUtils.closeConnection(conn);
		}
		
		return resultList ;
	}
	public AssetsType queryById(int id ){
		String sqlStr = "select * from assets_lb where asset_lbid = ? " ;
		AssetsType at = null ; 
		try {
			conn = DBUtils.getConnection() ;
			pstmt = conn.prepareStatement(sqlStr) ;
			pstmt.setInt(1, id) ;
			rs = pstmt.executeQuery() ;
			while (rs.next()){
				at = new AssetsType() ;
				at.setId(rs.getInt("asset_lbid")) ;
				at.setLbbh(rs.getString("asset_lbbh")) ;
				at.setLbbz(rs.getString("asset_lbbz")) ;
				at.setLbmc(rs.getString("asset_lbmc")) ;
				at.setLbms(rs.getString("asset_lbms"));
				}
			DBUtils.closeResultSet(rs);
			DBUtils.closeStatement(pstmt);
		} catch (SQLException e) {
			System.out.println("AssetsTypeDAO.qureyById--------->"+e) ;
		}finally{
			DBUtils.closeResultSet(rs);
			DBUtils.closeStatement(pstmt);
			DBUtils.closeConnection(conn);
		}
		return at ;
	}
	
	public boolean delete(int id){
		boolean flag = false ;
		String sqlStr = "delete assets_lb where asset_lbid = ? " ;
		try {
			conn = DBUtils.getConnection() ;
			pstmt = conn.prepareStatement(sqlStr) ;
			pstmt.setInt(1, id) ;
			pstmt.executeUpdate() ;
			flag = true ;
			DBUtils.closeStatement(pstmt) ;
		} catch (SQLException e) {
			System.out.println("AssetsTypeDAO.delete--------->"+e) ;
			return flag ;
		}finally{
			DBUtils.closeStatement(pstmt) ;
			DBUtils.closeConnection(conn) ;
		}
		return flag ;
	}
	
	public boolean add(AssetsType at){
		int id ;
		boolean flag = false ;
		String sqlStr = "insert into assets_lb(asset_lbid,asset_lbbh,asset_lbbz,asset_lbmc,asset_lbms)values(?,?,?,?,?) " ;
		try {
			id =  KeyGenerator.getInstance().getNextKey("assets_lb") ;
			conn = DBUtils.getConnection() ;
			pstmt = conn.prepareStatement(sqlStr) ;
			pstmt.setInt(1, id) ;
			pstmt.setString(2, at.getLbbh()) ;
			pstmt.setString(3,at.getLbmc()) ;
			pstmt.setString(4,at.getLbmc()) ;
			pstmt.setString(5,at.getLbms() ) ;
			pstmt.executeUpdate() ;
			flag = true ;
			DBUtils.closeStatement(pstmt) ;
		} catch (Exception e) {
			System.out.println("AssetsTypeDAO.delete--------->"+e) ;
			return flag ;
		}finally{
			DBUtils.closeStatement(pstmt) ;
			DBUtils.closeConnection(conn) ;
		}
		return flag ;
	}
	
	public boolean edit(AssetsType at){
		boolean flag = false ;
		String sqlStr = "update assets_lb set asset_lbbh=?,asset_lbbz=?,asset_lbmc=?,asset_lbms=? where asset_lbid = ?" ;
		try {
			conn = DBUtils.getConnection() ;
			pstmt = conn.prepareStatement(sqlStr) ;
			
			pstmt.setString(1, at.getLbbh()) ;
			pstmt.setString(2,at.getLbmc()) ;
			pstmt.setString(3,at.getLbmc()) ;
			pstmt.setString(4,at.getLbms() ) ;
			pstmt.setInt(5, at.getId()) ;
			pstmt.executeUpdate() ;
			flag = true ;
			DBUtils.closeStatement(pstmt) ;
		} catch (Exception e) {
			System.out.println("AssetsTypeDAO.edit--------->"+e) ;
			return flag ;
		}finally{
			DBUtils.closeStatement(pstmt) ;
			DBUtils.closeConnection(conn) ;
		}
		return flag ;
	}
		
	public int countAll(){
		int count = 0 ;
		String sqlStr = "select count(*) from assets_lb " ;
		try {
			conn = DBUtils.getConnection() ;
			pstmt = conn.prepareStatement(sqlStr) ;
			rs = pstmt.executeQuery() ;
			while(rs.next()){
				count = rs.getInt(1) ;
			}
			DBUtils.closeResultSet(rs) ;
			DBUtils.closeStatement(pstmt) ;
		} catch (Exception e) {
			System.out.println("AssetsTypeDAO.countAll--------->"+e) ;
		}finally{
			DBUtils.closeResultSet(rs) ;
			DBUtils.closeStatement(pstmt) ;
			DBUtils.closeConnection(conn) ;
		}
		return count  ;
	}
	//查询是否有编号重复的
	public boolean queryByBH(String bh){
		boolean flag = false ;
		int count = 0 ;
		String sqlStr = "select count(*) from assets_lb where asset_lbbh = ? " ;
		try {
			conn = DBUtils.getConnection() ;
			pstmt = conn.prepareStatement(sqlStr) ;
			pstmt.setString(1, bh) ;
			rs = pstmt.executeQuery() ;
			while(rs.next()){
				count = rs.getInt(1) ;
			}
			DBUtils.closeResultSet(rs) ;
			DBUtils.closeStatement(pstmt) ;
		} catch (Exception e) {
			System.out.println("AssetsTypeDAO.countByBH--------->"+e) ;
		}finally{
			DBUtils.closeResultSet(rs) ;
			DBUtils.closeStatement(pstmt) ;
			DBUtils.closeConnection(conn) ;
		}
		if(count>0){
			flag = true ;
		}
		return flag  ;
	}
	//查询是否有名称重复的
	public boolean queryByMC(String mc){
		boolean flag = false ;
		int count = 0 ;
		String sqlStr = "select count(*) from assets_lb where asset_lbmc = ? " ;
		try {
			conn = DBUtils.getConnection() ;
			pstmt = conn.prepareStatement(sqlStr) ;
			pstmt.setString(1, mc) ;
			rs = pstmt.executeQuery() ;
			while(rs.next()){
				count = rs.getInt(1) ;
			}
			DBUtils.closeResultSet(rs) ;
			DBUtils.closeStatement(pstmt) ;
		} catch (Exception e) {
			System.out.println("AssetsTypeDAO.countByMC--------->"+e) ;
		}finally{
			DBUtils.closeResultSet(rs) ;
			DBUtils.closeStatement(pstmt) ;
			DBUtils.closeConnection(conn) ;
		}
		if(count>0){
			flag = true ;
		}
		return flag  ;
	}
}