roombuyinfodaoimp.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.RoomBuyInfo;
import imis_hous.com.dao.RoomBuyInfoDAO;
import cmis.database.DBPoolManager;
import imis_hous.com.pub.interfaces.PageCount;
import imis_hous.com.db.DBClose;

public class RoomBuyInfoDAOImp implements RoomBuyInfoDAO, PageCount {

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

	public int totalRecord = 0;

	public int record_per_page = 15;

	public int getAllRecords() {
		return totalRecord;
	}

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

	public boolean add(RoomBuyInfo rbi) {
		boolean flag = false;
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement stmt = null;
		DBClose dbclose = new DBClose();
		String sql = "insert into RoomBuyInfo(RoomBuyId, RoomBuyUser,"
				+ "RoomBuyReasion, BuyAppDate, RoomBuyLeader, "
				+ "RoomBuyExcuter, RoomBuyDate, RoomBuyAgreenFlag,"
				+ "CheckDes,BuyIs, BuyNo, RoomInfo, ReMark, Memo) values(RoomBuyId.nextVal,'"
				+ rbi.getRoomBuyUser() + "', '" + rbi.getRoomBuyReasion()
				+ "',to_date('" + rbi.getRoomBuyDate() + "','yyyy-mm-dd'),'"
				+ rbi.getRoomBuyLeader() + "','" + rbi.getRoomBuyExcuter()
				+ "',to_date('" + rbi.getRoomBuyDate() + "','yyyy-mm-dd'),"
				+ rbi.getRoomBuyAgreenFlag() + ",'" + rbi.getCheckDes() + "',"
				+ rbi.getBuyIs() + ",'" + rbi.getBuyNo() + "','"
				+ rbi.getRoomInfo() + "'," + rbi.getReMark() + ", '"
				+ rbi.getMemo() + "')";

		dbpool.getConnection();
		try {
			stmt = dbpool.conn.createStatement();

			int i = stmt.executeUpdate(sql);

			flag = i > 0 ? true : false;
			dbpool.commit();
		} catch (SQLException e) {
			
		} 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 RoomBuyInfo where RoomBuyId=" + id + " ";
		dbpool.getConnection();
		try {
			stmt = dbpool.conn.createStatement();
			int i = stmt.executeUpdate(sql);

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

	public List getSelect(String buyAppDate, String roomInfo) {
		// TODO Auto-generated method stub
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		RoomBuyInfo rbi = null;
		DBClose dbclose = new DBClose();
		List L_RoomBuyInfo = new ArrayList();
		String sql = "select * from RoomBuyInfo where 1=1 ";


		if (buyAppDate.length() != 0) {
			sql += " and buyAppDate=to_date('" + buyAppDate + "','yyyy-mm-dd')";
		}
		if (roomInfo != null && !roomInfo.equals("")) {
			sql += " and roomInfo='" + roomInfo + "' ";
		}
		
		dbpool.getConnection();
		try {
			stmt = dbpool.conn.createStatement();
			rs = stmt.executeQuery(sql);

			while (rs.next()) {
				rbi = new RoomBuyInfo();
				rbi.setRoomBuyId(rs.getInt("roomBuyId"));
				rbi.setRoomBuyUser(rs.getString("roomBuyUser"));
				rbi.setRoomBuyReasion(rs.getString("roomBuyReasion"));
				rbi.setBuyAppDate(rs.getDate("buyAppDate"));
				rbi.setRoomBuyLeader(rs.getString("roomBuyLeader"));
				rbi.setRoomBuyExcuter(rs.getString("roomBuyExcuter"));
				rbi.setRoomBuyDate(rs.getDate("roomBuyDate"));
				rbi.setRoomBuyAgreenFlag(rs.getInt("roomBuyAgreenFlag"));
				rbi.setCheckDes(rs.getString("checkDes"));
				rbi.setBuyIs(rs.getInt("buyIs"));
				rbi.setBuyNo(rs.getString("buyNo"));
				rbi.setRoomInfo(rs.getString("roomInfo"));
				rbi.setReMark(rs.getInt("reMark"));
				rbi.setMemo(rs.getString("memo"));
				L_RoomBuyInfo.add(rbi);
			}
		} catch (SQLException e) {
			
		} finally {
			dbclose.CloseResultSet(rs);
			dbclose.CloseStmt(stmt);
			dbpool.freeConnection();
		}

		return L_RoomBuyInfo;
	}

	public boolean update(int id) {
		boolean flag = false;
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement stmt = null;
		DBClose dbclose = new DBClose();
		RoomBuyInfo rbi = new RoomBuyInfo();
		String sql = "update RoomBuyInfo set RoomBuyUser='"
				+ rbi.getRoomBuyUser() + "'," + "RoomBuyReasion='"
				+ rbi.getRoomBuyReasion() + "', BuyAppDate='"
				+ rbi.getBuyAppDate() + "', RoomBuyLeader='"
				+ rbi.getRoomBuyLeader() + "', " + "RoomBuyExcuter='"
				+ rbi.getRoomBuyExcuter() + "', RoomBuyDate='"
				+ rbi.getRoomBuyDate() + "', RoomBuyAgreenFlag="
				+ rbi.getRoomBuyAgreenFlag() + "," + "CheckDes='"
				+ rbi.getCheckDes() + "',BuyIs=" + rbi.getBuyIs() + ", BuyNo='"
				+ rbi.getBuyNo() + "', RoomInfo='" + rbi.getRoomInfo()
				+ "', ReMark=" + rbi.getReMark() + ", Memo='" + rbi.getMemo()
				+ "' where RoomBuyId=" + id + "";
		dbpool.getConnection();
		try {
			stmt = dbpool.conn.createStatement();

			int i = stmt.executeUpdate(sql);

			flag = i > 0 ? true : false;
			dbpool.commit();
		} catch (SQLException e) {
			
		} 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;
		RoomBuyInfo rbi = null;
		DBClose dbclose = new DBClose();
		String sql = "select * from roomBuyInfo";
		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 RoomBuyInfo();

				rbi.setRoomBuyUser(rs.getString("roomBuyUser"));
				rbi.setRoomBuyReasion(rs.getString("roomBuyReasion"));
				rbi.setBuyAppDate(rs.getDate("buyAppDate"));
				rbi.setRoomBuyLeader(rs.getString("roomBuyLeader"));
				rbi.setRoomBuyExcuter(rs.getString("roomBuyExcuter"));
				rbi.setRoomBuyDate(rs.getDate("roomBuyDate"));
				rbi.setRoomBuyAgreenFlag(rs.getInt("roomBuyAgreenFlag"));
				rbi.setCheckDes(rs.getString("checkDes"));
				rbi.setBuyIs(rs.getInt("buyIs"));
				rbi.setBuyNo(rs.getString("buyNo"));
				rbi.setRoomInfo(rs.getString("roomInfo"));
				rbi.setReMark(rs.getInt("reMark"));
				rbi.setMemo(rs.getString("memo"));

				list.add(rbi);

				i++;

				rs.next();
			}

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

	public List pageCount(int page, String roomInfo, String roomBuyDate,String buyAppDateed) {
		List list = new ArrayList();
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		RoomBuyInfo rbi = null;
		DBClose dbclose = new DBClose();

		String sql = "select * from roomBuyInfo where 1=1 ";	
		if (null != roomInfo)
			if (!roomInfo.equals("0")) {
				sql += " and  RoomInfo='" + roomInfo + "'";
			}
		if (null != roomBuyDate)
			if (!roomBuyDate.equals("")) {
				sql += " and  roomBuyDate between to_date('" + roomBuyDate
						+ "','yyyy-mm-dd') and to_date('"+buyAppDateed+"','yyyy-mm-dd')";
			}
		sql+=" order by RoomBuyDate";
		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 RoomBuyInfo();
				rbi.setRoomBuyId(rs.getInt("roomBuyId"));
				rbi.setRoomBuyUser(rs.getString("roomBuyUser"));
				rbi.setRoomBuyReasion(rs.getString("roomBuyReasion"));
				rbi.setBuyAppDate(rs.getDate("buyAppDate"));
				rbi.setRoomBuyLeader(rs.getString("roomBuyLeader"));
				rbi.setRoomBuyExcuter(rs.getString("roomBuyExcuter"));
				rbi.setRoomBuyDate(rs.getDate("roomBuyDate"));
				rbi.setRoomBuyAgreenFlag(rs.getInt("roomBuyAgreenFlag"));
				rbi.setCheckDes(rs.getString("checkDes"));
				rbi.setBuyIs(rs.getInt("buyIs"));
				rbi.setBuyNo(rs.getString("buyNo"));
				rbi.setRoomInfo(rs.getString("roomInfo"));
				rbi.setReMark(rs.getInt("reMark"));
				rbi.setMemo(rs.getString("memo"));

				list.add(rbi);

				

				
			}

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

}