sqlconfigimpl.java

sso呵呵

package com.mdcl.mocha.jlcmcc.conferenceManager.sqlConfig.sqlImpl;

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

import org.apache.log4j.Logger;

import com.mdcl.mocha.jlcmcc.DBconnection.DBConnectionManager;
import com.mdcl.mocha.jlcmcc.conferenceManager.bean.Dept;
import com.mdcl.mocha.jlcmcc.conferenceManager.bean.MeetingInfo;
import com.mdcl.mocha.jlcmcc.conferenceManager.bean.MeetingUseInfoBean;
import com.mdcl.mocha.jlcmcc.conferenceManager.bean.UserInfo;
import com.mdcl.mocha.jlcmcc.conferenceManager.sqlConfig.ISqlConfig;
/**
 * @author 杜永彬
 *
 */
public class SqlConfigImpl implements ISqlConfig {

	//连接名
	private String conName="idb";


	/**
	 * Logger for this class
	 */
	private static final Logger S_LOGGER = Logger
			.getLogger(SqlConfigImpl.class);


	/**查询所有会议室信息
	 * @param sql sql语句
	 * @return List
	 */

	public List queryAllMeetingInfo(StringBuffer sql,List prmList) {

		if (S_LOGGER.isDebugEnabled()) {
			S_LOGGER.debug("SqlConfigImpl.queryAllMeetingInfo()>>>>>>");
		}
		DBConnectionManager db = DBConnectionManager.getInstance();
		Connection con = null;

		PreparedStatement pstmt = null;
		ResultSet rs = 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));
						}
					}
					rs = pstmt.executeQuery();//获得rs对象
					List re_list = new ArrayList();
					while(rs.next()) {
						MeetingInfo meetingInfo = new MeetingInfo();//得到会议室信息实体
						meetingInfo.setId(rs.getString(1));
						meetingInfo.setName(rs.getString(2));
						meetingInfo.setIs_medium(rs.getString(3));
						meetingInfo.setHold_peoples(rs.getString(4));
						meetingInfo.setEquipment(rs.getString(5));
						meetingInfo.setNote(rs.getString(6));
						re_list.add(meetingInfo);
					}
					return re_list;
				}else{
					S_LOGGER.error("!!!!!未获得PreparedStatement对象!!!!!!");
				}
			}else{
				S_LOGGER.error("!!!!!未获得connection连接!!!!!!");
			}
		} catch (SQLException e) {
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				if(rs != null){
					rs.close();
					rs = null;
				}
				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.queryAllMeetingInfo()<<<<<<<<<<<<<<<<<<");
			}
		}
		return null;
	}
	/**查询单条会议室信息
	 * @param sql sql语句
	 * @return List
	 */
	public MeetingInfo queryAllMeetingInfoSingle(StringBuffer sql,List prmList) {

		if (S_LOGGER.isDebugEnabled()) {
			S_LOGGER.debug("SqlConfigImpl.queryAllMeetingInfoSingle()>>>>>>");
		}
		DBConnectionManager db = DBConnectionManager.getInstance();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = 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));
						}
					}
					MeetingInfo meetingInfo = new MeetingInfo();//得到会议室信息实体
					rs = pstmt.executeQuery();//获得rs对象
					while(rs.next()) {
						meetingInfo.setId(rs.getString(1));
						meetingInfo.setName(rs.getString(2));
						meetingInfo.setIs_medium(rs.getString(3));
						meetingInfo.setHold_peoples(rs.getString(4));
						meetingInfo.setEquipment(rs.getString(5));
						meetingInfo.setNote(rs.getString(6));
					}
					return meetingInfo;
				}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(rs != null){
					rs.close();
					rs = null;
				}
				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.queryAllMeetingInfoSingle()<<<<<<<<<<<<<<<<<<");
			}
		}
		return null;
	}
	/**查询单条会议室预定信息
	 * @param sql sql语句
	 * @return List
	 */
	public MeetingUseInfoBean queryAllMeetingUseInfoSingle(StringBuffer sql,List prmList) {

		if (S_LOGGER.isDebugEnabled()) {
			S_LOGGER.debug("SqlConfigImpl.queryAllMeetingUseInfoSingle()>>>>>>");
		}
		DBConnectionManager db = DBConnectionManager.getInstance();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = 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));
						}
					}
					MeetingUseInfoBean meetingUseInfo = new MeetingUseInfoBean();//得到会议室使用信息实体
					rs = pstmt.executeQuery();//获得rs对象
					while(rs.next()) {
						meetingUseInfo.setId(rs.getString(1));
						meetingUseInfo.setUserInfo(new UserInfo((rs.getString(2))));
						meetingUseInfo.getUserInfo().setUser_name(rs.getString(3));
						meetingUseInfo.setDept(new Dept(rs.getString(4)));
						meetingUseInfo.getDept().setDept_name(rs.getString(5));
						meetingUseInfo.setDraft_time(rs.getString(6));
						meetingUseInfo.setContact_phone(rs.getString(7));
						meetingUseInfo.setExigency(rs.getString(8));
						meetingUseInfo.setStart_time(rs.getString(9));
						meetingUseInfo.setEnd_time(rs.getString(10));
						meetingUseInfo.setPresent_num(rs.getString(11));
						meetingUseInfo.setMeetinginfo(new MeetingInfo(rs.getString(12)));
						meetingUseInfo.getMeetinginfo().setName(rs.getString(13));
						meetingUseInfo.getMeetinginfo().setIs_medium(rs.getString(14));
						meetingUseInfo.getMeetinginfo().setHold_peoples(rs.getString(15));
						meetingUseInfo.setMeeting_content(rs.getString(16));
						meetingUseInfo.setNote(rs.getString(17));
						meetingUseInfo.setFlow_id(rs.getString(18));
					}
					return meetingUseInfo;
				}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(rs != null){
					rs.close();
					rs = null;
				}
				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.queryAllMeetingUseInfoSingle()<<<<<<<<<<<<<<<<<<");
			}
		}
		return null;
	}
	/**查询是否存在某项信息
	 * @param sql sql语句
	 * @return boolean
	 */
	public boolean isExist(StringBuffer sql,List prmList) {

		if (S_LOGGER.isDebugEnabled()) {
			S_LOGGER.debug("SqlConfigImpl.isExist()>>>>>>");
		}

		DBConnectionManager db = DBConnectionManager.getInstance();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			boolean flag = false;
			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));
						}
					}
					rs = pstmt.executeQuery();//获得rs对象
					while(rs.next()) {
						if(rs.getInt(1)>0){
							flag = true;
						}
					}
					return flag;
				}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(rs != null){
					rs.close();
					rs = null;
				}
				if(pstmt != null){
					pstmt.close();
					pstmt = null;
				}
				if(con != null){
					db.freeConnection("idb", con);
				}
			} catch (SQLException e) {
				e.printStackTrace();