buildingsinfodaoimp.java

JSP移动商品管理平台源代码.........

package imis_hous.com.impl;

import imis_hous.com.bean.BuildingsInfo;
import imis_hous.com.dao.BuildingsInfoDAO;
import imis_hous.com.db.DBClose;
import imis_hous.com.pub.interfaces.PageCount;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.sql.Date;
import java.util.List;

import cmis.database.DBPoolManager;

public class BuildingsInfoDAOImp implements BuildingsInfoDAO, PageCount {
	public BuildingsInfoDAOImp() {

	}

	public int totalRecord = 0;

	public int record_per_page = 15;

	public boolean add(BuildingsInfo bi) {
		boolean flag = false;
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement stmt = null;
		DBClose dbclose = new DBClose();
		if(new ExistSql().exist("select * from area_info where AreaNu='"+bi.getAreaNu()+"'")){
				String sql = "insert into Buildings_info(BuildingNu, AreaNu, BuildingName,"
				+ " BuildingArea, BuildingLayers, BuildDate, UseFor, ReMark, BuildIdNo, IdTime, Memo) values('"
				+ bi.getBuildingNu()
				+ "', '"
				+ bi.getAreaNu()
				+ "', '"
				+ bi.getBuildingName()  //to_date('"+bi.getO_BuildDate()+"','yyyy-mm-dd')
				+ "', "
				+ bi.getBuildingArea()  //2008-2-2  checkdate
				+ ", "
				+ bi.getBuildingLayers()
				+ ", to_date('"
				+ bi.getBuildDate()
				+ "','yyyy-mm-dd'), "
				+ bi.getUseFor()+ ", "
				+ bi.getReMark()
				+ ", '"
				+ bi.getBuildIdNo()  
				+ "', to_date('"
				+ bi.getIdTime()
				+ "','yyyy-mm-dd'), '"				
				+ bi.getMemo() + "')";
				dbpool.getConnection();
				try {
					
					stmt = dbpool.conn.createStatement();
					int i = stmt.executeUpdate(sql);
					flag = i > 0 ? true : false;
					dbpool.commit();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					flag=false;
					dbpool.rollback();
					
				} finally {
					dbclose.CloseStmt(stmt);
					dbpool.freeConnection();
			}
		}
		// TODO Auto-generated method stub
		return flag;
	}

	public boolean del(String buildingNu) {
		boolean flag = false;
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement stmt = null;
		DBClose dbclose = new DBClose();
		String sql = "delete from Buildings_info where BuildingNu='" + buildingNu + "'";
		dbpool.getConnection();
		try {
			stmt = dbpool.conn.createStatement();

			int i = stmt.executeUpdate(sql);

			flag = i > 0 ? true : false;
			dbpool.commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			flag=false;
			dbpool.rollback();
		
		} finally {
			dbclose.CloseStmt(stmt);
			dbpool.freeConnection();
		}
		// TODO Auto-generated method stub
		return flag;
	}

	public List getSelect(String BuildingNu) {
		// TODO Auto-generated method stub
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		BuildingsInfo bi = null;
		List L_BuildingsInfo = new ArrayList();
		DBClose dbclose = new DBClose();

		String sql = "select * from Buildings_info where 1=1";
		if (BuildingNu.length()!=0) {
			sql += " and buildingNu='" + BuildingNu + "'";
		}
		dbpool.getConnection();
		try {
			
			stmt = dbpool.conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				bi = new BuildingsInfo();
				bi.setBuildingNu(rs.getString("buildingNu"));
				bi.setAreaNu(rs.getString("areaNu"));
				bi.setBuildingName(rs.getString("buildingName"));
				bi.setBuildingArea(rs.getFloat("buildingArea"));
				bi.setBuildingLayers(rs.getInt("buildingLayers"));
				bi.setBuildDate(rs.getDate("buildDate"));
				bi.setUseFor(rs.getInt("useFor"));
				bi.setReMark(rs.getInt("reMark"));
				bi.setMemo(rs.getString("memo"));
				bi.setBuildIdNo(rs.getString("buildIdNo"));
				bi.setIdTime(rs.getDate("idTime"));
				
				
				
				L_BuildingsInfo.add(bi);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			
		} finally {
			dbclose.CloseResultSet(rs);
			dbclose.CloseStmt(stmt);
			dbpool.freeConnection();
		}
		return L_BuildingsInfo;
	}

	public boolean update(BuildingsInfo bi) {
		boolean flag = false;
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement stmt = null;
		DBClose dbclose = new DBClose();
		
		String sql = "update Buildings_info set BuildingNu='"
				+ bi.getBuildingNu() + "', AreaNu='" + bi.getAreaNu()
				+ "', BuildingName='" + bi.getBuildingName()
				+ "', BuildIdNo='" + bi.getBuildIdNo()
				+ "',BuildingArea=" + bi.getBuildingArea()
				+ ", BuildingLayers=" + bi.getBuildingLayers()
				+ ", idTime=to_date('" + bi.getIdTime() + "','yyyy-mm-dd')"
				+ ", BuildDate=to_date('" + bi.getBuildDate() + "','yyyy-mm-dd'),UseFor="
				+ bi.getUseFor() + ", ReMark=" + bi.getReMark()
				+ ", Memo='" + bi.getMemo() + "' where BuildingNu='" + bi.getBuildingNu()
				+ "' ";
		

		
		dbpool.getConnection();
		try {
			stmt = dbpool.conn.createStatement();
			int i = stmt.executeUpdate(sql);
			
			flag = i > 0 ? true : false;
			dbpool.commit();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			flag=false;
			dbpool.rollback();
			
		} finally {
			dbclose.CloseStmt(stmt);
			dbpool.freeConnection();
		}
		// TODO Auto-generated method stub
		return flag;
	}

	public List pageCount(int page) {
		// TODO Auto-generated method stub
		List list = new ArrayList();
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		BuildingsInfo bi = null;
		DBClose dbclose = new DBClose();
		String sql = "select * from  buildings_info ";
		dbpool.getConnection();
		try {
			stmt = dbpool.conn.createStatement(
					ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(sql);
			
//			rs.last();
//			totalRecord = rs.getRow();
//
//			int startRow = (page - 1) * record_per_page + 1;
//
//			rs.absolute(startRow);
//
//			int i = 0;

			while (rs.next()) {
				bi = new BuildingsInfo();

				bi.setBuildingNu(rs.getString("buildingNu"));
				bi.setAreaNu(rs.getString("areaNu"));
				bi.setBuildingName(rs.getString("buildingName"));
				bi.setBuildingArea(rs.getFloat("buildingArea"));
				bi.setBuildingLayers(rs.getInt("buildingLayers"));
				bi.setBuildDate(rs.getDate("buildDate"));
				bi.setUseFor(rs.getInt("useFor"));
				bi.setReMark(rs.getInt("reMark"));
				bi.setMemo(rs.getString("memo"));
				bi.setBuildIdNo(rs.getString("buildIdNo"));
				bi.setIdTime(rs.getDate("idTime"));

				list.add(bi);


				
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			
		} finally {
			dbclose.CloseResultSet(rs);
			dbclose.CloseStmt(stmt);
			dbpool.freeConnection();
		}
	
		return list;
	}
		
		public List pageCount(int page, String buildDate,String buildingName,String buildDateed) {
			// TODO Auto-generated method stub
			List list = new ArrayList();
			
			DBPoolManager dbpool = new DBPoolManager();
			Connection conn = null;
			Statement stmt = null;
			ResultSet rs = null;
			BuildingsInfo bi = null;
			DBClose dbclose = new DBClose();
			String sql = "select * from  buildings_info where 1=1";
			if(!buildDate.equals("")){			
					sql+=" and  buildDate between to_date('"+buildDate+"','yyyy-mm-dd') and sysdate";							
			}
			
			if(!buildingName.equals("")){
				sql+=" and BuildingNu='"+buildingName+"' ";
			}
			if (null != buildDate)
				if (!buildDate.equals("")) {
					sql += " and  BuildDate between to_date('" + buildDate
							+ "','yyyy-mm-dd') and to_date('"+buildDateed+"','yyyy-mm-dd')";
				}
			sql+=" order by buildingName";
			dbpool.getConnection();
			try {
				stmt = dbpool.conn.createStatement(
						ResultSet.TYPE_SCROLL_SENSITIVE,
						ResultSet.CONCUR_READ_ONLY);
				rs = stmt.executeQuery(sql);
				
//				rs.last();
//				totalRecord = rs.getRow();
//
//				int startRow = (page - 1) * record_per_page + 1;
//
//				rs.absolute(startRow);
//
//				int i = 0;

				while (rs.next()) {
					bi = new BuildingsInfo();
										
					bi.setBuildingNu(rs.getString("buildingNu"));
					bi.setAreaNu(rs.getString("areaNu"));
					bi.setBuildingName(rs.getString("buildingName"));
					bi.setBuildingArea(rs.getFloat("buildingArea"));
					bi.setBuildingLayers(rs.getInt("buildingLayers"));
					bi.setBuildDate(rs.getDate("buildDate"));
					bi.setUseFor(rs.getInt("useFor"));
					bi.setReMark(rs.getInt("reMark"));
					bi.setMemo(rs.getString("memo"));
					bi.setBuildIdNo(rs.getString("buildIdNo"));
					bi.setIdTime(rs.getDate("idTime"));

					list.add(bi);

					
				}

			} catch (SQLException e) {
				
			} finally {
				dbclose.CloseResultSet(rs);
				dbclose.CloseStmt(stmt);
				dbpool.freeConnection();
			}
			return list;
			
	}

	public int getAllRecords() {
		return totalRecord;
	}

	public int getAllPages() {
		return (totalRecord + record_per_page - 1) / record_per_page;
	}

	public BuildingsInfo getSelectByBuildingNu(String BuildingNu) {
		// TODO Auto-generated method stub
		String sql = "select * from Buildings_info where buildingNu ='"+BuildingNu+"'";
		BuildingsInfo bi = null;
		Statement stmt = null;
		ResultSet rs = null;
		DBPoolManager dbpool = new DBPoolManager();
		DBClose dbclose = new DBClose();
		dbpool.getConnection();
		try {
			stmt = dbpool.conn.createStatement();
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				bi = new BuildingsInfo();
				bi.setBuildingNu(rs.getString("buildingNu"));
				bi.setAreaNu(rs.getString("areaNu"));
				bi.setBuildingName(rs.getString("buildingName"));
				bi.setBuildingArea(rs.getFloat("buildingArea"));
				bi.setBuildingLayers(rs.getInt("buildingLayers"));
				bi.setBuildDate(rs.getDate("buildDate"));
				bi.setUseFor(rs.getInt("useFor"));
				bi.setReMark(rs.getInt("reMark"));
				bi.setMemo(rs.getString("memo"));
				bi.setBuildIdNo(rs.getString("buildIdNo"));
				bi.setIdTime(rs.getDate("idTime"));
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			
		}finally{
			dbclose.CloseResultSet(rs);
			dbclose.CloseStmt(stmt);
			dbpool.freeConnection();
		}
		return bi;
	}

}