roominfodaoimp.java

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

package imis_hous.com.impl;

import imis_hous.com.bean.RoomInfo;
import imis_hous.com.dao.RoomInfoDAO;
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.util.ArrayList;
import java.util.List;

import cmis.database.DBPoolManager;

public class RoomInfoDAOImp implements RoomInfoDAO, PageCount {

	public RoomInfoDAOImp() {
		// 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(RoomInfo ri) {
		boolean flag = false;
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement stmt = null;
		DBClose dbclose = new DBClose();
		// if (new ExistSql().exist("select * from Room_Info where BuildingNu='"
		// + ri.getBuildingNu() + "'")) {

		String sql = "insert into Room_Info(RoomNu,BuildingNu,LayerNu,RoomArea,"
				+ "RoomDirect,RoomStatus,RoomUseFlag,UseDept,ReMark,Memo) values('"
				+ ri.getRoomNu()
				+ "','"
				+ ri.getBuildingNu()
				+ "', '"
				+ ri.getLayerNu()
				+ "',"
				+ ri.getRoomArea()
				+ ",'"
				+ ri.getRoomDirect()
				+ "',"
				+ ri.getRoomStatus()
				+ ","
				+ ri.getRoomUseFlag()
				+ ","
				+ ri.getUseDept()
				+ ","
				+ ri.getReMark() + ",'" + ri.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(String roomNu,String bNu) {
		boolean flag = false;
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement stmt = null;
		DBClose dbclose = new DBClose();
		String sql = "delete from Room_Info  where RoomNu='" + roomNu + "' and buildingnu ='"+bNu+"'";
		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(int UseDept) {
		// TODO Auto-generated method stub
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		RoomInfo ri = null;
		DBClose dbclose = new DBClose();
		List L_RoomInfo = new ArrayList();
//		String sql = "select * from Room_Info where 1=1";
		String sql = "select r.ROOMNU,r.remark,r.memo,r.buildingNu,r.layernu,r.roomarea,r.roomstatus,r.roomdirect,r.roomuseflag,r.usedept,b.buildingname from Room_Info r,buildings_info b where  r.buildingnu=b.buildingnu";
		if (UseDept != -1) {
			sql += " and UseDept='" + UseDept;
		}
		sql += " order by r.BuildingNu,r.roomNu";

		dbpool.getConnection();
		try {
			
			stmt = dbpool.conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				ri = new RoomInfo();
				ri.setRoomNu(rs.getString("roomNu"));
				ri.setBuildingNu(rs.getString("buildingNu"));
				ri.setBuildingname(rs.getString("buildingname"));	
				ri.setLayerNu(rs.getString("layerNu"));
				ri.setRoomArea(rs.getFloat("roomArea"));
				ri.setRoomDirect(rs.getString("roomDirect"));
				ri.setRoomStatus(rs.getInt("roomStatus"));
				ri.setRoomUseFlag(rs.getInt("roomUseFlag"));
				ri.setUseDept(rs.getInt("useDept"));
				ri.setReMark(rs.getInt("reMark"));
				ri.setMemo(rs.getString("memo"));
				L_RoomInfo.add(ri);
			}

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

	public List getSelect(String UseDept) {
		// TODO Auto-generated method stub
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		RoomInfo ri = null;
		DBClose dbclose = new DBClose();
		List L_RoomInfo = new ArrayList();
		String sql = "select r.ROOMNU,r.remark,r.memo,r.buildingNu,r.layernu,r.roomarea,r.roomstatus,r.roomdirect,r.roomuseflag,r.usedept,b.buildingname from Room_Info r,buildings_info b where  r.buildingnu=b.buildingnu";
		if(!UseDept.equals("-1"))
		{
			int ud = Integer.valueOf(UseDept);
			sql += " and r.UseDept=" + UseDept;
		}	
		sql += " order by r.BuildingNu,r.roomNu";
		dbpool.getConnection();
		try {
			stmt = dbpool.conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				ri = new RoomInfo();
				ri.setRoomNu(rs.getString("roomNu"));
				ri.setBuildingNu(rs.getString("buildingNu"));
				ri.setLayerNu(rs.getString("layerNu"));
				ri.setRoomArea(rs.getFloat("roomArea"));
				ri.setBuildingname(rs.getString("buildingname"));
				ri.setRoomDirect(rs.getString("roomDirect"));
				ri.setRoomStatus(rs.getInt("roomStatus"));
				ri.setRoomUseFlag(rs.getInt("roomUseFlag"));
				ri.setUseDept(rs.getInt("useDept"));
				ri.setReMark(rs.getInt("reMark"));
				ri.setMemo(rs.getString("memo"));
				L_RoomInfo.add(ri);
			}

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

	public boolean update(RoomInfo ri) {
		boolean flag = false;
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement stmt = null;
		DBClose dbclose = new DBClose();
		
		String sql = "update Room_Info set LayerNu='" + ri.getLayerNu()
				 + "',RoomArea="
				+ ri.getRoomArea() + ",RoomDirect='" + ri.getRoomDirect()
				+ "',RoomStatus=" + ri.getRoomStatus() + ",RoomUseFlag="
				+ ri.getRoomUseFlag() + ",UseDept=" + ri.getUseDept()
				+ ",ReMark=" + ri.getReMark() + ",Memo='" + ri.getMemo()
				+ "' where RoomNu='" + ri.getRoomNu()+ "'";
		
		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;
		RoomInfo ri = null;
		DBClose dbclose = new DBClose();
		String sql = "select * from room_Info order by BuildingNu,roomNu";
		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()) {
				ri = new RoomInfo();

				ri.setRoomNu(rs.getString("roomNu"));
				ri.setBuildingNu(rs.getString("buildingNu"));
				ri.setLayerNu(rs.getString("layerNu"));
				ri.setRoomArea(rs.getFloat("roomArea"));
				ri.setRoomDirect(rs.getString("roomDirect"));
				ri.setRoomStatus(rs.getInt("roomStatus"));
				ri.setRoomUseFlag(rs.getInt("roomUseFlag"));
				ri.setUseDept(rs.getInt("useDept"));
				ri.setReMark(rs.getInt("reMark"));
				ri.setMemo(rs.getString("memo"));

				list.add(ri);

				i++;

				rs.next();
			}

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

	public List pageCount(int page, int useDept) {
		List list = new ArrayList();
		DBPoolManager dbpool = new DBPoolManager();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		RoomInfo ri = null;
		DBClose dbclose = new DBClose();
		String sql = "select * from room_Info where 1=1 ";
		if (useDept != -1) {
			sql += " and useDept=" + useDept + "";
		}
		sql+=" order by BuildingNu,roomNu";
		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()) {
				ri = new RoomInfo();

				ri.setRoomNu(rs.getString("roomNu"));
				ri.setBuildingNu(rs.getString("buildingNu"));
				ri.setLayerNu(rs.getString("layerNu"));
				ri.setRoomArea(rs.getFloat("roomArea"));
				ri.setRoomDirect(rs.getString("roomDirect"));
				ri.setRoomStatus(rs.getInt("roomStatus"));
				ri.setRoomUseFlag(rs.getInt("roomUseFlag"));
				ri.setUseDept(rs.getInt("useDept"));
				ri.setReMark(rs.getInt("reMark"));
				ri.setMemo(rs.getString("memo"));

				list.add(ri);


				
			}

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

	public RoomInfo getSelectByRoomNu(String roomNu,String bname) {		
		
		String sql = "select * from room_Info where 1=1";
		if (roomNu != null && !roomNu.equals("")) {
			sql += " and roomNu='" + roomNu + "' ";
		}
		if(bname != null && !bname.equals(""))
		{
			sql +="and buildingnu='"+bname+"'";
		}		
		sql+=" order by BuildingNu,roomNu";
		RoomInfo ri = 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()) {
				ri = new RoomInfo();
				ri.setRoomNu(rs.getString("roomNu"));
				ri.setBuildingNu(rs.getString("buildingNu"));
				ri.setLayerNu(rs.getString("layerNu"));
				ri.setRoomArea(rs.getFloat("roomArea"));
				ri.setRoomDirect(rs.getString("roomDirect"));
				ri.setRoomStatus(rs.getInt("roomStatus"));
				ri.setRoomUseFlag(rs.getInt("roomUseFlag"));
				ri.setUseDept(rs.getInt("useDept"));
				ri.setReMark(rs.getInt("reMark"));
				ri.setMemo(rs.getString("memo"));
			}
		} catch (SQLException e) {
			
		} finally {
			dbclose.CloseResultSet(rs);
			dbclose.CloseStmt(stmt);
			dbpool.freeConnection();
		}

		return ri;
	}

	public RoomInfo getSelectByRoomNu(String roomNu) {
		// TODO Auto-generated method stub
		return null;
	}

}