prodao.java

实现统一的人员日志管理系统管理后台

package com.tb.log.model.dao.impl;


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.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Set;

import com.tb.log.factory.OracleDAOFactory;
import com.tb.log.model.dao.DAOFactory;
import com.tb.log.model.dao.idao.IDeptDAO;
import com.tb.log.model.dao.idao.ILogDAO;
import com.tb.log.model.dao.idao.IProDAO;
import com.tb.log.model.dao.idao.IUserDAO;
import com.tb.log.model.po.Pro;
import com.tb.log.model.po.User;
import com.tb.log.system.SystemException;
import com.tb.log.util.pages.PageBean;
import com.tb.log.util.tools.ToolKit;

public class ProDAO implements IProDAO{
	private int totalResults = 0;
	private static final int pageSize = 5;
	
	private static final String ADD_PRO= "insert into t_project values(scq_project_proid.nextval,?,?,?,?,?,?,?,?)";
	private static final String UPDATE_PRO = "update t_project set pro_category=?,pro_name=?,plan_start_date=?,"
		+"plan_stop_date=?,pro_desc=?,pro_state=? where pro_id=?";
	private static final String REMOVE_BY_PRO = "delete from t_project where pro_id=?";
	private static final String FIND_BY_ID = "select * from t_project where pro_id=?";
	private static final String FIND_ALL = "select * from t_project";
	private static final String FIND_ALL_CATEGORY = "select distinct pro_category from t_project";
	private static final String FIND_PROID_BY_USERID = "select pro_id from t_userproject where user_id=?";
	private static final String FIND_ALL_PRONAME = "select pro_name,pro_id from t_project"; 
	
	public List findAll(int currentPage) throws SystemException {
		List proList = new ArrayList();
		Connection conn = OracleDAOFactory.getConnection();
		Pro pro = null;
		try {
			PreparedStatement psm = conn.prepareStatement(FIND_ALL);
			psm.setInt(1, pageSize*currentPage);
			psm.setInt(2, pageSize*(currentPage-1));
			ResultSet rs = psm.executeQuery();
		   while(rs.next()){
				pro = new Pro(rs.getInt("pro_id"),rs.getString("pro_category"),
						rs.getString("pro_name"),rs.getString("plan_start_date"),
					rs.getString("plan_stop_date").substring(0, 10),rs.getString("pro_desc"),
					rs.getString("pro_state"),rs.getString("start_date"),
					rs.getString("stop_date"));
				proList.add(pro);
	//System.out.println(rs.getString("pro_category"));
				}
		} catch (SQLException e) {
			String err = "SQLException in ProDAO:findAll()--"+e;
			throw new SystemException(err);
		}
		return proList;
	}

	public List findByCondition(String consql, int currentPage)
			throws SystemException {
		List proList = new ArrayList();
		Connection conn = OracleDAOFactory.getConnection();
		Pro pro = null;
		try {
			PreparedStatement psm = conn.prepareStatement(consql);
			psm.setInt(1, pageSize*currentPage);
			psm.setInt(2, pageSize*(currentPage-1));
		   ResultSet rs = psm.executeQuery();
		   while(rs.next()){
				pro = new Pro(rs.getInt("pro_id"),rs.getString("pro_category"),
						rs.getString("pro_name"),rs.getString("plan_start_date"),
					rs.getString("plan_stop_date").substring(0, 10),rs.getString("pro_desc"),
					rs.getString("pro_state"),rs.getString("start_date"),
					rs.getString("stop_date"));
				proList.add(pro);
			//System.out.println(rs.getString("pro_category"));
				}
		} catch (SQLException e) {
			String err = "SQLException in ProDAO:findByCondition()--"+e;
			throw new SystemException(err);
		}
		return proList;
	}
	
	public PageBean getCurrentPageByCondition(String sql) throws SystemException{
		Connection conn = OracleDAOFactory.getConnection();
		try {
			PreparedStatement psm = conn.prepareStatement(sql);
			ResultSet rs = psm.executeQuery();
			if (rs.next()) {
				totalResults = rs.getInt(1);
			}
		} catch (SQLException e) {
			String errStr = "SQLException in DeptDAO:findAll()--" + e;
			throw new SystemException(errStr);
		}
		return new PageBean(totalResults, pageSize);
	}

	public Object findById(String id) throws SystemException {
		Connection conn = OracleDAOFactory.getConnection();
		Pro pro = null;
		try {
			PreparedStatement psm = conn.prepareStatement(FIND_BY_ID);
			psm.setString(1, id);
			ResultSet rs = psm.executeQuery();
			while(rs.next()){
				//将查询结果封装到ProBean 中，单条记录所以不用List
				pro = new Pro(rs.getInt("pro_id"),rs.getString("pro_category"),
						rs.getString("pro_name"),rs.getString("plan_start_date"),
					rs.getString("plan_stop_date"),rs.getString("pro_desc"),
					rs.getString("pro_state"),rs.getString("start_date"),
					rs.getString("stop_date"));
				}
		} catch (SQLException e) {
			String err = "SQLException in ProDAO:findById()--"+e;
			throw new SystemException(err);
		}
		return pro;
	}

	public void remove(Object obj) throws SystemException {
		Pro pro = (Pro)obj;
		Connection conn = OracleDAOFactory.getConnection();
		try {
			PreparedStatement psm = conn.prepareStatement(REMOVE_BY_PRO);
			psm.setInt(1, pro.getPro_id());
			psm.executeUpdate();
		} catch (SQLException e) {
			String err = "SQLException in ProDAO:save()--"+e;
			throw new SystemException(err);
		}
	}
	
	public void removeAll(Pro pro) throws SystemException {
		IProDAO ipro = DAOFactory.getDAOFactory(DAOFactory.ORACLE).getProDAO();
		ILogDAO ilog = DAOFactory.getDAOFactory(DAOFactory.ORACLE).getLogDAO();
		if(!ipro.findById("user_id", "pro_id", pro.getPro_id()).isEmpty()){
			ipro.removeUserPro("pro_id", pro.getPro_id());
		}
		ilog.removeByProid(pro.getPro_id());
		ipro.remove(pro);
	}

	public void save(Object obj) throws SystemException {
		Pro pro = (Pro)obj;
		//List userList = new ArrayList();
		Connection conn = OracleDAOFactory.getConnection();
		try {
			PreparedStatement psm = conn.prepareStatement(ADD_PRO);
		
			psm.setString(1, pro.getPro_category());
			psm.setString(2,pro.getPro_name());
			psm.setString(3, pro.getPlan_start_date()) ;
			psm.setString(4,pro.getPlan_stop_date());
			psm.setString(5, pro.getPro_desc());
			psm.setString(6,pro.getPro_state());
			psm.setString(7, pro.getStart_date());
			psm.setString(8, pro.getStop_date());

			psm.executeUpdate();
		} catch (SQLException e) {
			String err = "SQLException in ProDAO:save()--"+e;
			throw new SystemException(err);
		}
	}

	public void update(Object obj) throws SystemException {
		Pro pro = (Pro)obj;
		List userList = new ArrayList();
		Connection conn = OracleDAOFactory.getConnection();
		try {
			PreparedStatement psm = conn.prepareStatement(UPDATE_PRO);
		
			psm.setString(1, pro.getPro_category());
			psm.setString(2,pro.getPro_name());
			psm.setString(3, pro.getPlan_start_date()) ;
			psm.setString(4,pro.getPlan_stop_date());
			psm.setString(5, pro.getPro_desc());
			psm.setString(6,pro.getPro_state());
			psm.setInt(7, pro.getPro_id());

			psm.executeUpdate();
		} catch (SQLException e) {
			String err = "SQLException in ProDAO:update()--"+e;
			throw new SystemException(err);
		}
	}
	
	
	public List findProCategory() throws SystemException{
		List proList = new ArrayList();
		Connection conn = OracleDAOFactory.getConnection();
		try {
			PreparedStatement psm = conn.prepareStatement(FIND_ALL_CATEGORY);
			ResultSet rs = psm.executeQuery();
			while(rs.next()){
				proList.add(rs.getString(1));
			}
		} catch (SQLException e) {
			String err = "SQLException in ProDao():findProCategory()---"+e;
			throw new SystemException(err);
		}
		return proList;
	}
	
	public void changeProStateBySql(String sql) throws SystemException{
		Connection conn = OracleDAOFactory.getConnection();
		try {
			Statement st = conn.createStatement();
			st.executeUpdate(sql);
		} catch (SQLException e) {
			String err = "SQLException in ProDAO():changeProStateBySql()----"+e;
			throw new SystemException(err);
		}
	}
	
	
	/* (non-Javadoc)
	 * @see com.tb.log.model.dao.idao.IProDAO#findProidByUserid(java.lang.String)
	 */
	public Set findProidByUserid(String user_id) throws SystemException{
		Set proidSet = new HashSet();
		Connection conn = OracleDAOFactory.getConnection();
		PreparedStatement psm;
		try {
			psm = conn.prepareStatement(FIND_PROID_BY_USERID);
			psm.setString(1, user_id);
			ResultSet rs = psm.executeQuery();
			while(rs.next()){
				proidSet.add(rs.getString(1));
			}
		} catch (SQLException e) {
			String err = "SQLException in ProDAO():findProByUserid()---"+e;
			throw new SystemException(err);
		}
		return proidSet;
	}
	
	
	/* (non-Javadoc)
	 * @see com.tb.log.model.dao.idao.IProDAO#findById(java.util.Set)
	 */
	public List findById(List proidList) throws SystemException{
		List proList = new ArrayList();
		IProDAO ipro = DAOFactory.getDAOFactory(DAOFactory.ORACLE).getProDAO();
		
		Iterator it = proidList.iterator();
		Pro pro = null;
		while(it.hasNext()){
			String id = (String)it.next();
			pro = new Pro();
			pro =(Pro) ipro.findById(id);
			proList.add(pro);
		}
	return proList;
	}
	
	public List findProName() throws SystemException{
		List userProList = new ArrayList();
		Connection conn = OracleDAOFactory.getConnection();
		Pro pro = null;
		try {
			PreparedStatement psm = conn.prepareStatement(FIND_ALL_PRONAME);
			ResultSet rs = psm.executeQuery();
			while(rs.next()){
				pro = new Pro();
				pro.setPro_name(rs.getString(1));
				pro.setPro_id(rs.getInt(2));
				userProList.add(pro);
				}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return userProList;
	}	
	
	
	public void removeUserPro(String delName,int delid) throws SystemException{
		String remove = "delete from t_userproject where "+delName+"=?";
		Connection coon = OracleDAOFactory.getConnection();
		try {
			coon.setAutoCommit(false);
			PreparedStatement psm = coon.prepareStatement(remove);
			psm.setInt(1, delid);
			psm.executeUpdate();
			
			coon.commit();
			coon.setAutoCommit(true);
		} catch (SQLException e) {
			String err = "SQLException in UserRoleDAO():remove()---------"+e;
			throw new SystemException(err);
		}
		//return true;
	}

	public void addUserPro(int user_id, int pro_id) throws SystemException{
		String ADD = "insert into t_userproject values (?,?)";
		Connection coon = OracleDAOFactory.getConnection();
		try {
			PreparedStatement psm = coon.prepareStatement(ADD);
			psm.setInt(1, user_id);
			psm.setInt(2, pro_id);
			psm.executeUpdate();
		} catch (SQLException e) {
			String err = "SQLException in UserRoleDAO():add()---------"+e;
			throw new SystemException(err);
		}
	}
	
	
	public List findById(String findId,String conName,int id) throws SystemException{
		String find_by_id = "select "+findId+" from t_userproject where "+conName+"=?";
		//System.out.println(find_by_id);
		List list = new ArrayList();
		try{
		Connection conn = OracleDAOFactory.getConnection();
		PreparedStatement psm = conn.prepareStatement(find_by_id) ;
		psm.setInt(1, id);
		ResultSet rs = psm.executeQuery();
		while(rs.next()){
			list.add(rs.getString(1));
		}
		}catch (SQLException e) {
			String err = "SQLException in UserRoleDAO():add()---------"+e;
			throw new SystemException(err);
		}
		return list;
	}
	
	
	public void addUserProByUserList(int pro_id,String[] usersid)throws SystemException{
		IProDAO ipro = DAOFactory.getDAOFactory(DAOFactory.ORACLE).getProDAO();
		Connection coon = OracleDAOFactory.getConnection();
		try {
			if(!ipro.findById("user_id","pro_id",pro_id).isEmpty() ){
				ipro.removeUserPro("pro_id",pro_id);
			//	System.out.println("ok");
			}
			for(int i=0;i<usersid.length;i++){
				ipro.addUserPro(Integer.parseInt(usersid[i]), pro_id);
			}		
		} catch (Exception e) {
			String err = "SQLException in UserRoleDAO():addUserProByUserList()---------"+e;
			throw new SystemException(err);
		}
	}
	
	public void addUserProByProList(int user_id,String[] prosid)throws SystemException{
		IProDAO ipro = DAOFactory.getDAOFactory(DAOFactory.ORACLE).getProDAO();
		Connection coon = OracleDAOFactory.getConnection();
		try {
			if(ipro.findById("pro_id","user_id",user_id)!=null ){
				ipro.removeUserPro("user_id",user_id);
			}
			for(int i=0;i<prosid.length;i++){
				ipro.addUserPro(user_id,Integer.parseInt(prosid[i]));
			}		
		} catch (Exception e) {
			String err = "SQLException in UserRoleDAO():addUserProByUserList()---------"+e;
			throw new SystemException(err);
		}
	}
	
	public static void main(String[] args) {
		Set set = new HashSet();
//		try {
//			if(new ProDAO().findById("user_id", "pro_id", 2).isEmpty()){
//			System.out.println("OK");}
//		} catch (SystemException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//		set.add("1");
//		set.add("2");
//		try {
//			set = new ProDAO().findProidByUserid("1");
//			System.out.println(new ProDAO().findById(set));
//		} catch (SystemException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}

	String str= "27,2";
	Pro pro = new Pro();
	pro.setPro_id(63);
	Connection conn = OracleDAOFactory.getConnection();
	try {
		try {
			conn.setAutoCommit(false);
			new ProDAO().removeAll(pro);
			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		} catch (SystemException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
}