sqlconfigimpl.java

sso呵呵

			}
			if (S_LOGGER.isDebugEnabled()) {
				S_LOGGER.debug("SqlConfigImpl.isExist()<<<<<<<<<<<<<<<<<<");
			}
		}
		return false;
	}
	/**根据 sql语句新增信息或修改
	 * @param sql sql语句
	 * @param meetinfo MeetingUseInfoBean
	 * @return int 返回 增加的记录条数 synchronized
	 */
	public void changeInfo(StringBuffer sql,List prmList) {

		if (S_LOGGER.isDebugEnabled()) {
			S_LOGGER.debug("SqlConfigImpl.changeInfo()>>>>>>");
		}
		DBConnectionManager db = DBConnectionManager.getInstance();
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = db.getConnection("idb");//通过连接池 获得 connection 连接
			if(con != null){
				pstmt = con.prepareStatement(sql.toString());//创建PreparedStatement对象
				if(pstmt != null){//设置参数信息
					if(prmList != null && !prmList.isEmpty()){
						for (int i = 0; i < prmList.size(); i++) {
							pstmt.setString(i+1, (String)prmList.get(i));
						}
					}
					pstmt.executeUpdate();
				}else{
					S_LOGGER.error("!!!!!未获得PreparedStatement对象!!!!!!");
				}
			}else{
				S_LOGGER.error("!!!!!未获得PreparedStatement对象!!!!!!");
			}
		}catch (SQLException e){
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally{
			try {
				if(pstmt != null){
					pstmt.close();
					pstmt = null;
				}
				if(con != null){
					db.freeConnection("idb", con);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			if (S_LOGGER.isDebugEnabled()) {
				S_LOGGER.debug("SqlConfigImpl.changeInfo()<<<<<<<<<<<<<<<<<<");
			}
		}
	}
/*********************************************************************************************************************/

	/**
	 * 方法描述 查询所有会议室信息
	 * @return List(item: MeetingInfo)
	 */
	public List selectMeetingAll(){
//		ln(">>>> SqlConfigImpl.selectMeetingAll()");
		// 声明用到的变量
		List meets = null;
		String sql = "select * from meeting_info";
		Connection con = null;
		Statement stmt = null;
		ResultSet result = null;

		try {
			//1 得到结果集
			con = this.getConnection();
//			ln(">>>> con:" + con);
			if(con != null){
				stmt = con.createStatement();
				result = stmt.executeQuery(sql);
//				ln(">>>> sql:" + sql);
				int cont = 0;

				while (result.next()) {
					cont++;
					if(cont == 1){
						meets = new ArrayList();
					}
					//2 将数据转化为对象 并添加到集合当中去
					MeetingInfo meet = new MeetingInfo();
					meet.setId(result.getString("id"));
					meet.setName(result.getString("name"));
					meet.setIs_medium(result.getString("is_medium"));
					meet.setHold_peoples(result.getString("hold_peoples"));
					meet.setEquipment(result.getString("equipment"));
					meet.setNote(result.getString("note"));

					meets.add(meet);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			//3 释放系统资源
			try {
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			stmt = null;
			result = null;
			this.freeConnection(con);
		}
//		ln("<<<< SqlConfigImpl.selectMeetingAll()");
		// 4 反回结果
		return meets;
	}

	/**
	 * 方法描述 根据会议室ID查询详细的会议室信息
	 * @param meetID
	 * @return
	 */
	public MeetingInfo selectMeetingByID(String meetID){
		// 声明用到的变量
		MeetingInfo meetInfo = null;
		String sql = "select * from meeting_info where id = '" + meetID + "'";
		Connection con = null;
		Statement stmt = null;
		ResultSet result = null;

		try {
			//1 得到结果集
			con = this.getConnection();
			stmt = con.createStatement();
			result = stmt.executeQuery(sql);

			if(result.next()) {
				meetInfo = new MeetingInfo();
				meetInfo.setId(result.getString("id"));
				meetInfo.setName(result.getString("name"));
				meetInfo.setIs_medium(result.getString("is_medium"));
				meetInfo.setHold_peoples(result.getString("hold_peoples"));
				meetInfo.setEquipment(result.getString("equipment"));
				meetInfo.setNote(result.getString("note"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			//3 释放系统资源
			try {
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			stmt = null;
			result = null;
			this.freeConnection(con);
		}
		// 4 反回结果
		return meetInfo;
	}

	/**
	 * 方法描述 查询当前会议室是否被使用,根据会议室和时间
	 * @param meetID 会议室ID
	 * @param Date 时间
	 * @return
	 */
	public boolean selectMeetingIsUse(String meetID, String startDate ,String endDate){
		// 声明用到的变量
		boolean flag = false;
		String sql = "select * from MEETING_USEINFO where MEETINGROOM_ID ='" + meetID + "' " +
//					"or (START_TIME like '" + startDate + "%' " +
					"and to_date('" + startDate + " 23:59:59', 'yyyy-MM-dd HH24:mi:ss') >= to_date(nvl(start_time, '1000-01-01 00:00:00'), 'yyyy-MM-dd HH24:mi:ss') " +
					"and to_date('" + startDate +"', 'yyyy-MM-dd HH24:mi:ss') <= to_date(nvl(end_time, '1000-01-01 00:00:00'), 'yyyy-MM-dd HH24:mi:ss')";
		Connection con = null;
		Statement stmt = null;
		ResultSet result = null;

		try {
			//1 得到结果集
			con = this.getConnection();
			if(con != null){
				stmt = con.createStatement();
//				ln(">><< SQL:" + sql);
				result = stmt.executeQuery(sql);

				if(result.next()) {
					flag = true;
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			//3 释放系统资源
			try {
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			stmt = null;
			result = null;
			this.freeConnection(con);
		}
		// 4 反回结果
		return flag;
	}

	/**
	 * 方法描述 查找会议室使用的详细信息列表
	 * @param meetID
	 * @param Date
	 * @return
	 */
	public List selectMeetingUseInfo(String meetID, String date){
		// 声明用到的变量
		List meetInfos = null;
		String sql = "select * from MEETING_USEINFO where MEETINGROOM_ID ='" + meetID + "' " +
		"and to_date('" + date + " 23:59:59', 'yyyy-MM-dd HH24:mi:ss') >= to_date(nvl(start_time, '1000-01-01 00:00:00'), 'yyyy-MM-dd HH24:mi:ss') " +
		"and to_date('" + date +"', 'yyyy-MM-dd HH24:mi:ss') <= to_date(nvl(end_time, '1000-01-01 00:00:00'), 'yyyy-MM-dd HH24:mi:ss')";

		Connection con = null;
		Statement stmt = null;
		ResultSet result = null;

		try {
			//1 得到结果集
			con = this.getConnection();
			stmt = con.createStatement();
			result = stmt.executeQuery(sql);
			int cont = 0;

			while (result.next()) {
				cont++;
				if(cont == 1){
					meetInfos = new ArrayList();
				}
				//2 将数据转化为对象 并添加到集合当中去
				MeetingUseInfoBean mtuInfo = new MeetingUseInfoBean();
				//封装用户信息(数据不完整只是用户的部分信息)
				UserInfo user = new UserInfo();
				user.setUser_id(result.getString("USER_ID"));
				user.setUser_name(result.getString("USER_NAME"));
				//封装部门信息(数据不完整只是部门的部分信息)
				Dept dept = new Dept();
				dept.setDept_id(result.getString("DEPT_ID"));
				dept.setDept_name(result.getString("DEPT_NAME"));
				//封装 会议室信息 只有ID
				MeetingInfo meet = new MeetingInfo();
				meet.setId(result.getString("MEETINGROOM_ID"));

				mtuInfo.setId(result.getString("ID"));
				mtuInfo.setUserInfo(user);
				mtuInfo.setDept(dept);
				mtuInfo.setMeetinginfo(meet);
				mtuInfo.setDraft_time(result.getString("DRAFT_TIME"));
				mtuInfo.setContact_phone(result.getString("CONTACT_PHONE"));
				mtuInfo.setExigency(result.getString("EXIGENCY"));
				mtuInfo.setStart_time(result.getString("START_TIME"));
				mtuInfo.setEnd_time(result.getString("END_TIME"));
				mtuInfo.setPresent_num(result.getString("PRESENT_NUM"));
				mtuInfo.setMeeting_content(result.getString("MEETING_CONTENT"));
				mtuInfo.setNote(result.getString("NOTE"));
				mtuInfo.setFlow_id(result.getString("FLOW_ID"));

				meetInfos.add(mtuInfo);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			//3 释放系统资源
			try {
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			stmt = null;
			result = null;
			this.freeConnection(con);
		}
		// 4 反回结果
		return meetInfos;
	}


	/**
	 *
	 * 方法描述 得到一个连接对象
	 * @return Connection
	 */
	private Connection getConnection(){
		return (Connection)DBConnectionManager
									.getInstance()
									.getConnection(conName);
	}

	/**
	 *
	 * 方法描述 释放连接
	 * @return
	 */
	private void freeConnection(Connection con){
		DBConnectionManager.getInstance().freeConnection(conName, con);
	}
}