rentbuildingsinfodaoimp.java

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

package imis_hous.com.impl;

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

import imis_hous.com.bean.RentBuildingsInfo;
import imis_hous.com.dao.RentBuildingsInfoDAO;
import cmis.database.DBPoolManager;
import imis_hous.com.pub.interfaces.PageCount;
import imis_hous.com.db.DBClose;

public class RentBuildingsInfoDAOImp implements RentBuildingsInfoDAO, PageCount {

	public RentBuildingsInfoDAOImp() {
		// TODO Auto-generated constructor stub
	}

	public int totalRecord = 0;

	public int record_per_page = 15;

	public boolean add(RentBuildingsInfo rbi) {
		boolean flag = false;
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement stmt = null;
		DBClose dbclose = new DBClose();

		String sql = "insert into Rent_Buildings_info(RentId, BuildingName, BuildingArea, "
				+ "BuildingLayers, BuildDate, Own, Addr, ReMark, Memo, RentBd, RentEd, "
				+ "Rent, RentFor, PerDate, Perm) values(RentId.nextVal,'"
				+ rbi.getBuildingName()
				+ "', "
				+ rbi.getBuildingArea()
				+ ","
				+ rbi.getBuildingLayers()
				+ ", to_date('"
				+ rbi.getBuildDate()
				+ "','yyyy-mm-dd'), '"
				+ rbi.getOwn()
				+ "', '"
				+ rbi.getAddr()
				+ "', "
				+ rbi.getReMark()
				+ ", '"
				+ rbi.getMemo()
				+ "', to_date('"
				+ rbi.getRentBd()
				+ "','yyyy-mm-dd'), to_date('"
				+ rbi.getRentEd()
				+ "','yyyy-mm-dd'), "
				+ rbi.getRent()
				+ ", '"
				+ rbi.getRentFor()
				+ "', to_date('"
				+ rbi.getPerDate()
				+ "','yyyy-mm-dd'), '" + rbi.getPerm() + "')";
		
		dbpool.getConnection();
		try {
			stmt = dbpool.conn.createStatement();

			int i = stmt.executeUpdate(sql);

			flag = i > 0 ? true : false;

			dbpool.commit();
		} catch (SQLException e) {
			

			dbpool.rollback();
			
		} finally {
			dbclose.CloseStmt(stmt);
			dbpool.freeConnection();
		}
		// TODO Auto-generated method stub
		return flag;
	}

	public boolean del(int id) {
		boolean flag = false;
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement stmt = null;
		DBClose dbclose = new DBClose();
		String sql = "delete from Rent_Buildings_info where RentId="+ id +"";
	
		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

			dbpool.rollback();
			
		} finally {
			dbclose.CloseStmt(stmt);
			dbpool.freeConnection();
		}
		// TODO Auto-generated method stub
		return flag;
	}

	public List getSelect(String buildingName, String RentBd, String RentEd) {
		// TODO Auto-generated method stub
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		RentBuildingsInfo rbi = null;
		DBClose dbclose = new DBClose();
		List L_RentBuildingsInfo = new ArrayList();
		String sql = "select * from Rent_Buildings_info  where 1=1";
		if (buildingName != null && !buildingName.equals("")) {
			sql += " and buildingName='" + buildingName + "'";
		}
		if ((RentBd != null) && (!RentBd.equals(""))) {
			sql += " and RentBd=to_date('" + RentBd + "','yyyy-mm-dd') ";
		}
		if ((RentEd != null) && (!RentEd.equals(""))) {
			sql += " and RentEd=to_date('" + RentEd + "','yyyy-mm-dd') ";
		}
		dbpool.getConnection();
		try {
			stmt = dbpool.conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				rbi = new RentBuildingsInfo();

				rbi.setBuildingName(rs.getString("buildingName"));
				rbi.setBuildingArea(rs.getFloat("buildingArea"));
				rbi.setBuildingLayers(rs.getInt("buildingLayers"));
				rbi.setBuildDate(rs.getDate("buildDate"));
				rbi.setOwn(rs.getString("own"));
				rbi.setAddr(rs.getString("addr"));
				rbi.setReMark(rs.getInt("reMark"));
				rbi.setMemo(rs.getString("memo"));
				rbi.setRentBd(rs.getDate("rentBd"));
				rbi.setRentEd(rs.getDate("rentEd"));
				rbi.setRent(rs.getFloat("rent"));
				rbi.setRentFor(rs.getString("rentFor"));
				rbi.setPerDate(rs.getDate("perDate"));
				rbi.setPerm(rs.getString("perm"));

				L_RentBuildingsInfo.add(rbi);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			
		} finally {
			dbclose.CloseResultSet(rs);
			dbclose.CloseStmt(stmt);
			dbpool.freeConnection();
		}

		return L_RentBuildingsInfo;
	}

	public List getSelect(String BuildingName) {
		// TODO Auto-generated method stub
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		RentBuildingsInfo rbi = null;
		DBClose dbclose = new DBClose();
		List L_RentBuildingsInfo = new ArrayList();
		String sql = "select * from Rent_Buildings_info  where 1=1";
		if (BuildingName.length() != 0) {
			sql += " and BuildingName='" + BuildingName + "'";
		}
		dbpool.getConnection();
		try {
			stmt = dbpool.conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				rbi = new RentBuildingsInfo();

				rbi.setBuildingName(rs.getString("buildingName"));
				rbi.setBuildingArea(rs.getFloat("buildingArea"));
				rbi.setBuildingLayers(rs.getInt("buildingLayers"));
				rbi.setBuildDate(rs.getDate("buildDate"));
				rbi.setOwn(rs.getString("own"));
				rbi.setAddr(rs.getString("addr"));
				rbi.setReMark(rs.getInt("reMark"));
				rbi.setMemo(rs.getString("memo"));
				rbi.setRentBd(rs.getDate("rentBd"));
				rbi.setRentEd(rs.getDate("rentEd"));
				rbi.setRent(rs.getFloat("rent"));
				rbi.setRentFor(rs.getString("rentFor"));
				rbi.setPerDate(rs.getDate("perDate"));
				rbi.setPerm(rs.getString("perm"));

				L_RentBuildingsInfo.add(rbi);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			
		} finally {
			dbclose.CloseResultSet(rs);
			dbclose.CloseStmt(stmt);
			dbpool.freeConnection();
		}

		return L_RentBuildingsInfo;
	}

	public boolean update(int id) {

		boolean flag = false;
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement stmt = null;
		DBClose dbclose = new DBClose();
		RentBuildingsInfo rbi = new RentBuildingsInfo();
		String sql = "update rent_Buildings_info set BuildingName='"
				+ rbi.getBuildingName() + "', BuildingArea="
				+ rbi.getBuildingArea() + ", " + "BuildingLayers="
				+ rbi.getBuildingLayers() + ", BuildDate='"
				+ rbi.getBuildDate() + "', Own='" + rbi.getOwn() + "', Addr='"
				+ rbi.getAddr() + "', ReMark=" + rbi.getReMark() + ", Memo='"
				+ rbi.getMemo() + "', RentBd='" + rbi.getRentBd()
				+ "', RentEd='" + rbi.getRentEd() + "', " + "Rent="
				+ rbi.getRent() + ", RentFor='" + rbi.getRentFor()
				+ "', PerDate='" + rbi.getPerDate() + "', Perm='"
				+ rbi.getPerm() + "' where RentId=" + id + " ";
		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

			dbpool.commit();
		
		} finally {
			dbclose.CloseStmt(stmt);
			dbpool.freeConnection();
		}
		// TODO Auto-generated method stub
		return flag;
	}

	public List pageCount(int page) {
		List list = new ArrayList();
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		RentBuildingsInfo rbi = null;
		DBClose dbclose = new DBClose();
		String sql = "select * from rent_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 (i < record_per_page && !rs.isAfterLast()) {
				rbi = new RentBuildingsInfo();

				rbi.setRentId(rs.getInt("rentId"));
				rbi.setBuildingName(rs.getString("buildingName"));
				rbi.setBuildingArea(rs.getFloat("buildingArea"));
				rbi.setBuildingLayers(rs.getInt("buildingLayers"));
				rbi.setBuildDate(rs.getDate("buildDate"));
				rbi.setOwn(rs.getString("own"));
				rbi.setAddr(rs.getString("addr"));
				rbi.setReMark(rs.getInt("reMark"));
				rbi.setMemo(rs.getString("memo"));
				rbi.setRentBd(rs.getDate("rentBd"));
				rbi.setRentEd(rs.getDate("rentEd"));
				rbi.setRent(rs.getFloat("rent"));
				rbi.setRentFor(rs.getString("rentFor"));
				rbi.setPerDate(rs.getDate("perDate"));
				rbi.setPerm(rs.getString("perm"));

				list.add(rbi);

				i++;

				rs.next();
			}

		} 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 BuildingName,String RentBd, String RentEd) {
		List list = new ArrayList();
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		RentBuildingsInfo rbi = null;
		DBClose dbclose = new DBClose();
		String sql = "select * from rent_Buildings_info where 1=1 ";
//		if(!RentBd.equals("")){	
//			if(!RentEd.equals(""))
//			{
//				sql+=" and RentBd between to_date('"+RentBd+"', 'yyyy-mm-dd') and to_date('"+RentEd+"','yyyy-mm-dd')";
//			}
//			if(RentEd.equals(""))
//			{
//				sql+=" and RentBd=to_date('"+RentBd+"', 'yyyy-mm-dd')";
//			}
//		}
//		if(!RentEd.equals(""))
//		{
//			if(!RentBd.equals(""))
//			{
//				sql+=" and RentBd between to_date('"+RentBd+"', 'yyyy-mm-dd') and to_date('"+RentEd+"','yyyy-mm-dd')";
//			}
//			if(RentBd.equals(""))
//			{
//				sql+=" and RentEd=to_date('"+RentEd+"', 'yyyy-mm-dd')";
//			}
//		}
		if(!RentBd.equals(""))
		{
			sql += "and (RentBd > to_date('"+RentBd+"', 'yyyy-mm-dd') or RentBd = to_date('"+RentBd+"', 'yyyy-mm-dd'))";
		}
		if(!RentEd.equals(""))
		{
			sql += "and (RentEd < to_date('"+RentEd+"', 'yyyy-mm-dd') or RentEd = to_date('"+RentEd+"', 'yyyy-mm-dd'))";
		}
		sql+=" order by RentBd";
		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()) {
				rbi = new RentBuildingsInfo();

				rbi.setRentId(rs.getInt("rentId"));
				rbi.setBuildingName(rs.getString("buildingName"));
				rbi.setBuildingArea(rs.getFloat("buildingArea"));
				rbi.setBuildingLayers(rs.getInt("buildingLayers"));
				rbi.setBuildDate(rs.getDate("buildDate"));
				rbi.setOwn(rs.getString("own"));
				rbi.setAddr(rs.getString("addr"));
				rbi.setReMark(rs.getInt("reMark"));
				rbi.setMemo(rs.getString("memo"));
				rbi.setRentBd(rs.getDate("rentBd"));
				rbi.setRentEd(rs.getDate("rentEd"));
				rbi.setRent(rs.getFloat("rent"));
				rbi.setRentFor(rs.getString("rentFor"));
				rbi.setPerDate(rs.getDate("perDate"));
				rbi.setPerm(rs.getString("perm"));

				list.add(rbi);
	
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			
		} 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;
	}

}