trainingnoticedao.java

sso呵呵

package com.mdcl.mocha.jlcmcc.trainingNotice.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.*;
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.dbhelper.DBHelper;
import com.mdcl.mocha.jlcmcc.trainingNotice.bean.TrainingNoticeBean;

/**
 * <strong>Title : TrainingNoticeDAO<br>
 * </strong> <strong>Description : </strong><br>
 * <strong>Create on : 2007-9-26<br>
 * </strong>
 * <p>
 * <strong>Copyright (C) Mocha Software Co.,Ltd.<br>
 * </strong>
 * <p>
 *
 * @author linda [e-mail]<br>
 * @version <strong>吉林移动BPM一期</strong><br>
 *          <br>
 *          <strong>修改历史:</strong><br>
 *          修改人 修改日期 修改描述<br>
 *          -------------------------------------------<br>
 *          <br>
 *          <br>
 */
public class TrainingNoticeDAO extends DBHelper {
	/**
	 * <code>tableName</code>-数据库中培训人员信息表的表名
	 */
	private static final String tableName = "training_userinfo";

	/**
	 * <code>idSequence</code>-数据库中培训人员信息表的id字段序列名
	 */
	private static final String idSequence = "training_userinfo_seq";

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

	/**
	 * 方法描述
	 *
	 * 查询操作
	 *
	 * @param tnb
	 *            封装查询条件的TrainingNoticeBean
	 * @return 查询结果List，里面是封装人员信息的TrainingNoticeBean对象
	 */
	public List search(TrainingNoticeBean tnb, String orderBy) {
		if (S_LOGGER.isDebugEnabled()) {
			S_LOGGER.debug("TrainingNoticeDAO.search()>>>");
		}
		String sql = "SELECT * FROM " + tableName;
		if (tnb != null) {
			sql += " WHERE 0=0 ";
			if (!checkNone(tnb.getId()))
				sql += " AND id=" + tnb.getId();
			if (!checkNone(tnb.getUser_name()))
				sql += " AND user_name like '%" + tnb.getUser_name() + "%' ";
			if (!checkNone(tnb.getUser_id()))
				sql += " AND user_id=" + checkString(tnb.getUser_id());
			if (!checkNone(tnb.getDept_name()))
				sql += " AND dept_name like '%" + tnb.getDept_name() + "%' ";
			if (!checkNone(tnb.getDept_id()))
				sql += " AND dept_id=" + tnb.getDept_id();
			if (!checkNone(tnb.getCompany_name()))
				sql += " AND company_name like '%" + tnb.getCompany_name()
						+ "%' ";
			if (!checkNone(tnb.getCompany_id()))
				sql += " AND company_id= " + tnb.getCompany_id();
			if (!checkNone(tnb.getPhone()))
				sql += " AND phone=" + checkString(tnb.getPhone());
			if (!checkNone(tnb.getEmail()))
				sql += " AND email=" + checkString(tnb.getEmail());
			if (!checkNone(tnb.getUser_type()))
				sql += " AND user_type=" + checkString(tnb.getUser_type());
			if (!checkNone(tnb.getRemark()))
				sql += " AND note=" + checkString(tnb.getRemark());
			if (!checkNone(tnb.getAdd_user()))
				sql += " AND add_user=" + checkString(tnb.getAdd_user());
			if (!checkNone(tnb.getBoInsId()))
				sql += " AND boinsid=" + checkString(tnb.getBoInsId());
		}
		if (orderBy == null || orderBy.trim().equalsIgnoreCase("id"))
			orderBy = " id";

		sql += " ORDER BY " + orderBy;

		DBConnectionManager db = DBConnectionManager.getInstance();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		List list = new ArrayList();
		try {
			conn = db.getConnection("idb");
			if (conn == null)
				return null;
			stmt = conn.createStatement();
			if (stmt == null)
				return null;
			rs = stmt.executeQuery(sql);
			while (rs != null && rs.next()) {
				TrainingNoticeBean tn = new TrainingNoticeBean();
				tn.setId(convertNone(rs.getString("id")));
				tn.setUser_name(convertNone(rs.getString("user_name")));
				tn.setUser_id(convertNone(rs.getString("user_id")));
				tn.setDept_name(convertNone(rs.getString("dept_name")));
				tn.setDept_id(convertNone(rs.getString("dept_id")));
				tn.setCompany_name(convertNone(rs.getString("company_name")));
				tn.setCompany_id(convertNone(rs.getString("company_id")));
				tn.setPhone(convertNone(rs.getString("phone")));
				tn.setEmail(convertNone(rs.getString("email")));
				tn.setUser_type(convertNone(rs.getString("user_type")));
				tn.setRemark(convertNone(rs.getString("note")));
				tn.setAdd_user(convertNone(rs.getString("add_user")));
				tn.setBoInsId(convertNone(rs.getString("boinsid")));
				list.add(tn);
			}
		} catch (SQLException e) {
			return null;
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					return null;
				}
			}
			db.freeConnection("idb", conn);
		}
		if (S_LOGGER.isDebugEnabled()) {
			S_LOGGER.debug("TrainingNoticeDAO.search()<<<");
		}
		return list;
	}

	/**
	 * 方法描述
	 *
	 * 插入操作
	 *
	 * @param tnb
	 *            封装需添加的信息的TrainingNoticeBean
	 * @return 操作状态：true——操作成功；false——操作失败
	 */
	public boolean insert(TrainingNoticeBean tnb) {
		if (S_LOGGER.isDebugEnabled()) {
			S_LOGGER.debug("TrainingNoticeDAO.insert()>>>");
		}
		if (tnb == null)
			return false;

		String sql = "INSERT INTO " + tableName + " VALUES( " + idSequence
				+ ".NEXTVAL, " + checkString(convertNone(tnb.getUser_name()))
				+ ", " + checkString(tnb.getUser_id()) + ", "
				+ checkString(tnb.getDept_name()) + ", "
				+ checkString(tnb.getDept_id()) + ", "
				+ checkString(tnb.getCompany_name()) + ", "
				+ checkString(tnb.getCompany_id()) + ", "
				+ checkString(tnb.getPhone()) + ", "
				+ checkString(tnb.getEmail()) + ", "
				+ checkString(tnb.getUser_type()) + ", "
				+ checkString(tnb.getRemark()) + ", "
				+ checkString(tnb.getAdd_user()) + ", "
				+ checkString(tnb.getBoInsId()) + " )";
		return dataManipulation(sql);
	}

	public boolean insert(List list) {
		if (S_LOGGER.isDebugEnabled()) {
			S_LOGGER.debug("TrainingNoticeDAO.delete()>>>");
		}

		if (list == null || list.isEmpty())
			return true;
		DBConnectionManager db = null;
		Connection conn = null;
		PreparedStatement pstm = null;
		String sql = "INSERT INTO " + tableName + " VALUES( " + idSequence
				+ ".NEXTVAL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		try {
			db = DBConnectionManager.getInstance();
			conn = db.getConnection("idb");
			if (conn == null)
				return false;
			conn.setAutoCommit(false);
			pstm = conn.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				TrainingNoticeBean tnb = (TrainingNoticeBean) list.get(i);
				pstm.setString(1, convertNone(tnb.getUser_name()));
				pstm.setString(2, convertNone(tnb.getUser_id()));
				pstm.setString(3, convertNone(tnb.getDept_name()));
				pstm.setString(4, convertNone(tnb.getDept_id()));
				pstm.setString(5, convertNone(tnb.getCompany_name()));
				pstm.setString(6, convertNone(tnb.getCompany_id()));
				pstm.setString(7, convertNone(tnb.getPhone()));
				pstm.setString(8, convertNone(tnb.getEmail()));
				pstm.setString(9, convertNone(tnb.getUser_type()));
				pstm.setString(10, convertNone(tnb.getRemark()));
				pstm.setString(11, convertNone(tnb.getAdd_user()));
				pstm.setString(12, convertNone(tnb.getBoInsId()));
				pstm.addBatch();
			}
			pstm.executeBatch();
			conn.commit();
		} catch (SQLException e) {
			return false;
		} finally {
			if (pstm != null) {
				try {
					pstm.close();
					conn.setAutoCommit(true);
				} catch (SQLException e) {
					return false;
				}
			}
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				return false;
			}
			db.freeConnection("idb", conn);
		}
		return true;
	}

	/**
	 * 方法描述
	 *
	 * 删除操作
	 *
	 * @param id
	 *            需刪除的id
	 * @return 操作状态：true——操作成功；false——操作失败
	 */
	public boolean delete(String id) {
		if (S_LOGGER.isDebugEnabled()) {
			S_LOGGER.debug("TrainingNoticeDAO.delete()>>>");
		}
		String sql = "DELETE FROM " + tableName + " WHERE id=" + id;
		return dataManipulation(sql);
	}

	public boolean delete(List list) {
		if (S_LOGGER.isDebugEnabled()) {
			S_LOGGER.debug("TrainingNoticeDAO.delete()>>>");
		}

		if (list == null || list.isEmpty())
			return true;
		DBConnectionManager db = null;
		Connection conn = null;
		PreparedStatement pstm = null;
		String sql = "DELETE FROM " + tableName + " WHERE id=?";
		try {
			db = DBConnectionManager.getInstance();
			conn = db.getConnection("idb");
			if (conn == null)
				return false;
			conn.setAutoCommit(false);
			pstm = conn.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				TrainingNoticeBean tnb = (TrainingNoticeBean) list.get(i);
				pstm.setInt(1, Integer.parseInt((tnb.getId())));
				pstm.addBatch();
			}
			pstm.executeBatch();
			conn.commit();
		} catch (SQLException e) {
			return false;
		} finally {
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					return false;
				}
			}
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				return false;
			}
			db.freeConnection("idb", conn);
		}
		return true;
	}

	/**
	 * 方法描述
	 *
	 * 更新操作
	 *
	 * @param tnb
	 *            封装需更新的信息的TrainingNoticeBean
	 * @return 操作状态：true——操作成功；false——操作失败
	 */
	public boolean update(TrainingNoticeBean tnb) {
		if (S_LOGGER.isDebugEnabled()) {
			S_LOGGER.debug("TrainingNoticeDAO.update()>>>");
		}
		if (tnb == null)
			return false;
		String sql = "UPDATE " + tableName + " SET  user_name="
				+ checkString(tnb.getUser_name()) + ", user_id="
				+ checkString(tnb.getUser_id()) + ", dept_name="
				+ checkString(tnb.getDept_name()) + ", dept_id="
				+ checkString(tnb.getDept_id()) + ", company_name="
				+ checkString(tnb.getCompany_name()) + ", company_id="
				+ checkString(tnb.getCompany_id()) + ", phone="
				+ checkString(tnb.getPhone()) + ", email="
				+ checkString(tnb.getEmail()) + ", user_type="
				+ checkString(tnb.getUser_type()) + ", note="
				+ checkString(tnb.getRemark()) + ", add_user="
				+ checkString(tnb.getAdd_user()) + ", boinsid="
				+ checkString(tnb.getBoInsId()) + " WHERE id=" + tnb.getId();
		return dataManipulation(sql);
	}

	public boolean update(List list) {
		if (S_LOGGER.isDebugEnabled()) {
			S_LOGGER.debug("TrainingNoticeDAO.delete()>>>");
		}

		if (list == null || list.isEmpty())
			return true;
		DBConnectionManager db = null;
		Connection conn = null;
		PreparedStatement pstm = null;
		String sql = "UPDATE " + tableName
				+ " SET user_name=?, user_id=?, dept_name=?, dept_id=?"
				+ ", company_name=?, company_id=?, phone=?, email=?"
				+ ", user_type=?, note=?, add_user=?, boinsid=? WHERE id=? ";
		try {
			db = DBConnectionManager.getInstance();
			conn = db.getConnection("idb");
			if (conn == null)
				return false;
			conn.setAutoCommit(false);
			pstm = conn.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				TrainingNoticeBean tnb = (TrainingNoticeBean) list.get(i);
				pstm.setString(1, convertNone(tnb.getUser_name()));
				pstm.setString(2, convertNone(tnb.getUser_id()));
				pstm.setString(3, convertNone(tnb.getDept_name()));
				pstm.setString(4, convertNone(tnb.getDept_id()));
				pstm.setString(5, convertNone(tnb.getCompany_name()));
				pstm.setString(6, convertNone(tnb.getCompany_id()));
				pstm.setString(7, convertNone(tnb.getPhone()));
				pstm.setString(8, convertNone(tnb.getEmail()));
				pstm.setString(9, convertNone(tnb.getUser_type()));
				pstm.setString(10, convertNone(tnb.getRemark()));
				pstm.setString(11, convertNone(tnb.getAdd_user()));
				pstm.setString(12, convertNone(tnb.getBoInsId()));
				pstm.setString(13, convertNone(tnb.getId()));
				pstm.addBatch();
			}
			pstm.executeBatch();
			conn.commit();
		} catch (SQLException e) {
			return false;
		} finally {
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					return false;
				}
			}
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				return false;
			}
			db.freeConnection("idb", conn);
		}
		return true;
	}
}