userdao.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.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.ILogDAO;
import com.tb.log.model.dao.idao.IProDAO;
import com.tb.log.model.dao.idao.IUserDAO;
import com.tb.log.model.po.Dept;
import com.tb.log.model.po.Log;
import com.tb.log.model.po.User;
import com.tb.log.system.SystemException;
import com.tb.log.util.pages.PageBean;


public class UserDAO implements IUserDAO{

	private int totalResults = 0;
	private static final int pageSize = 5;
	
	private static final String FIND_ALL = "select * from t_user";
	private static final String FIND_USER_BY_LOGINAME_PASSWORD = "select * from t_user where loginname=? and psw=?";
	private static final String REMOVE_USER = "delete from t_user where user_id=?";	
	private static final String FIND_BY_ID = "select * from t_user where user_id=?";
	private static final String UPDATE = "update t_user set user_name=?,stu_id=?,sex=?,birthday=to_date(?,'YYYY-MM-DD'),"
		+"telephone=?,address=?,email=?,qq=?,msn=?,deptid=?,grade=?,loginname=?,psw=?,question=?,answer=?,"
		+"startdate=to_date(?,'YYYY-MM-DD'),intodate=to_date(?,'YYYY-MM-DD'),regdate=to_date(?,'YYYY-MM-DD'),user_state=? where user_id=?";
	//用项目ID查找用户列表
	private static final String FIND_BY_PRO_ID = "select a.user_id,a.user_name from t_user a,t_userproject b where a.user_id=b.user_id and b.pro_id=?";
	
	
	public User findByNamePsw(String LoginName, String password)
			throws SystemException {
		User user = null;
		Connection coon = OracleDAOFactory.getConnection();
		try {
			PreparedStatement psm = coon.prepareStatement(FIND_USER_BY_LOGINAME_PASSWORD);
			psm.setString(1, LoginName);
			psm.setString(2, password);
			ResultSet rs = psm.executeQuery();
			if(rs.next()){
				user = new User(rs.getInt(1),rs.getString(2), rs.getString(3), rs
						.getInt(4), rs.getString(5), rs.getString(6), rs
						.getString(7), rs.getString(8), rs.getString(9), rs
						.getString(10), rs.getString(11), rs.getString(12), rs
						.getString(13), rs.getString(14), rs.getString(15), rs
						.getString(16), rs.getString(17), rs.getString(18), rs
						.getString(19), rs.getString(20));
			}
			
			
		} catch (Exception e) {
			String err = "SQLException in UserDAO:findByNamePsw()--"+e;
			throw new SystemException(err);
		}
		return user;
	}
	
	public List findAll() throws SystemException {
		List userList = new ArrayList();
		User user = null;
		try {
			Connection coon = OracleDAOFactory.getConnection();
			PreparedStatement psm = coon.prepareStatement(FIND_ALL);
			ResultSet rs = psm.executeQuery();
			while(rs.next()){
				user = new User(rs.getInt(1),rs.getString(2), rs.getString(3), rs
						.getInt(4), rs.getString(5), rs.getString(6), rs
						.getString(7), rs.getString(8), rs.getString(9), rs
						.getString(10), rs.getString(11), rs.getString(12), rs
						.getString(13), rs.getString(14), rs.getString(15), rs
						.getString(16), rs.getString(17), rs.getString(18), rs
						.getString(19), rs.getString(20));
				userList.add(user);
			}
			
		} catch (Exception e) {
			String err = "SQLException in UserDAO:findAll()--"+e;
			throw new SystemException(err);
		}
		return userList;
	}

	public List findAll(int currentPage) throws SystemException {
		List userList = new ArrayList();
		User user = null;
		try {
			Connection coon = OracleDAOFactory.getConnection();
			PreparedStatement psm = coon.prepareStatement(FIND_USER_BY_LOGINAME_PASSWORD);
			psm.setInt(1, pageSize * currentPage);
			psm.setInt(2, pageSize * (currentPage - 1));
			ResultSet rs = psm.executeQuery();
			while(rs.next()){
				user = new User(rs.getInt(1),rs.getString(2), rs.getString(3), rs
						.getInt(4), rs.getString(5), rs.getString(6), rs
						.getString(7), rs.getString(8), rs.getString(9), rs
						.getString(10), rs.getString(11), rs.getString(12), rs
						.getString(13), rs.getString(14), rs.getString(15), rs
						.getString(16), rs.getString(17), rs.getString(18), rs
						.getString(19), rs.getString(20));
				userList.add(user);
			}
			
		} catch (Exception e) {
			String err = "SQLException in UserDAO:findAll(string currentPage)--"+e;
			throw new SystemException(err);
		}
		return userList;
	}

	public List findByCondition(String consql, int currentPage)
			throws SystemException {
		// TODO Auto-generated method stub
		return null;
	}

	public User findById(String id) throws SystemException {
		User user = null;
		try {
			Connection conn = OracleDAOFactory.getConnection();
			PreparedStatement psm = conn.prepareStatement(FIND_BY_ID);
			psm.setString(1, id);
			ResultSet rs = psm.executeQuery();

			while (rs.next()) {
				user = new User(rs.getInt(1),rs.getString(2), rs.getString(3), rs
						.getInt(4), rs.getString(5), rs.getString(6), rs
						.getString(7), rs.getString(8), rs.getString(9), rs
						.getString(10), rs.getString(11), rs.getString(12), rs
						.getString(13), rs.getString(14), rs.getString(15), rs
						.getString(16), rs.getString(17), rs.getString(18), rs
						.getString(19), rs.getString(20));
//System.out.println(rs.getString(4));				
			}
			
		} catch (SQLException e) {
			String err = "SQLException in UserDAO:findById(String id)--"+e;
			throw new SystemException(err);
		}
		return user;
	}

	public void remove(Object obj) throws SystemException {
		User user = (User)obj;
		Connection conn = OracleDAOFactory.getConnection();
		try {
			PreparedStatement psm = conn.prepareStatement(REMOVE_USER);
			psm.setInt(1, user.getUser_id());
			psm.executeUpdate();
			
		} catch (SQLException e) {
			String err = "SQLException in UserDAO:remove(Object obj)--"+e;
			throw new SystemException(err);
		}
		
	}

	public void save(Object obj) throws SystemException {
		User user = (User)obj;
		boolean b = false;
		//List userList = new ArrayList();
		String sql = "insert into t_user values(scq_user_id.nextval,?,?,?,to_date(?,'YYYY-MM-DD'),?,?,?,?,?,?,?,?,?,?,?,to_date(?,'YYYY-MM-DD'),to_date(?,'YYYY-MM-DD'),to_date(?,'YYYY-MM-DD'),?)";
		Connection conn = OracleDAOFactory.getConnection();
		try {
			PreparedStatement psm = conn.prepareStatement(sql);
//System.out.println(user.getUser_name());
//System.out.println(user.getStu_id());
//System.out.println(user.getSex());
//System.out.println(user.getDeptid());
			psm.setString(1, user.getUser_name());
			psm.setString(2, user.getStu_id());
			psm.setInt(3, user.getSex());
			psm.setString(4, user.getBirthday());
			psm.setString(5, user.getTelephone());
			psm.setString(6, user.getAddress());
			psm.setString(7, user.getEmail());
			psm.setString(8, user.getQq());
			psm.setString(9, user.getMsn());
			psm.setString(10, user.getDeptid());
			psm.setString(11, user.getGrade());
			psm.setString(12, user.getLoginname());
			psm.setString(13, user.getPsw());
			psm.setString(14, user.getQuestion());
			psm.setString(15, user.getAnswer());
			psm.setString(16, user.getStartdate());
			psm.setString(17, user.getIntodate());
			psm.setString(18, user.getRegdate());
			psm.setString(19, user.getUser_state());
//System.out.println("////"+user.getBirthday());
//System.out.println(user.getTelephone());
			psm.executeUpdate();
		} catch (SQLException e) {
			String err = "SQLException in UserDAO:save(Object obj)--"+e;
			throw new SystemException(err);
		}
	}

	public void update(Object obj) throws SystemException {
		User user = (User)obj;
		Connection conn = OracleDAOFactory.getConnection();
		try {
			PreparedStatement psm = conn.prepareStatement(UPDATE);
			psm.setString(1, user.getUser_name());
			psm.setString(2, user.getStu_id());
			psm.setInt(3, user.getSex());
			psm.setString(4, user.getBirthday());
			psm.setString(5, user.getTelephone());
			psm.setString(6, user.getAddress());
			psm.setString(7, user.getEmail());
			psm.setString(8, user.getQq());
			psm.setString(9, user.getMsn());
			psm.setString(10, user.getDeptid());
			psm.setString(11, user.getGrade());
			psm.setString(12, user.getLoginname());
			psm.setString(13, user.getPsw());
			psm.setString(14, user.getQuestion());
			psm.setString(15, user.getAnswer());
			psm.setString(16, user.getStartdate());
			psm.setString(17, user.getIntodate());
			psm.setString(18, user.getRegdate());
			psm.setString(19, user.getUser_state());
			psm.setInt(20, user.getUser_id());
//System.out.println("////"+user.getUser_id());
//System.out.println(user.getTelephone());
			psm.executeUpdate();
		} catch (SQLException e) {
			String err = "SQLException in UserDAO:update(Object obj)--"+e;
			throw new SystemException(err);
		}
		
	}
	
	public List findById(List ids) throws SystemException{
		IProDAO ipro = DAOFactory.getDAOFactory(DAOFactory.ORACLE).getProDAO();
		IUserDAO iuser = DAOFactory.getDAOFactory(DAOFactory.ORACLE).getUserDAO();
		User user = null;
		List userList = new ArrayList();
		for (Iterator iterator = ids.iterator(); iterator.hasNext();) {
			String user_id = (iterator.next()).toString();
			user = (User)iuser.findById(user_id);
			userList.add(user);
		}
		return userList;
	}
	
	
	public List findByProId(String id) throws SystemException{
		User user = null;
		List userList = new ArrayList();
		try {
			Connection conn = OracleDAOFactory.getConnection();
			PreparedStatement psm = conn.prepareStatement(FIND_BY_PRO_ID);
			psm.setString(1, id);
			ResultSet rs = psm.executeQuery();

			while (rs.next()) {
				user = new User(rs.getInt(1),rs.getString(2), rs.getString(3), rs
						.getInt(4), rs.getString(5), rs.getString(6), rs
						.getString(7), rs.getString(8), rs.getString(9), rs
						.getString(10), rs.getString(11), rs.getString(12), rs
						.getString(13), rs.getString(14), rs.getString(15), rs
						.getString(16), rs.getString(17), rs.getString(18), rs
						.getString(19), rs.getString(20));
				userList.add(user);
//System.out.println(rs.getString(4));				
			}
		} catch (SQLException e) {
			String err = "SQLException in UserDAO:findByProid(String id)--"+e;
			throw new SystemException(err);
		}
		return userList;
	}
	
	
	public PageBean getTotalResults(String getCountSql) throws SystemException{
		Connection coon = OracleDAOFactory.getConnection();
		try {
			PreparedStatement psm = coon.prepareStatement(getCountSql);
			ResultSet rs = psm.executeQuery();
			if(rs.next()){
				totalResults = rs.getInt(1);
			}			
		} catch (SQLException e) {
			String err = "SQLException in UserDAO:getTotalResults(String id)--"+e;
			throw new SystemException(err);
		}
		return new PageBean(totalResults,pageSize);
	}
	
	
	/**条件查询用户信息
	 * @param currentPage （当前页数）
	 * @return List （用户信息列表）
	 */
	public List findUserInfoList(int currentPage,String getPageSql) throws SystemException{
		IProDAO ipro = DAOFactory.getDAOFactory(DAOFactory.ORACLE).getProDAO();
		List userInfo = new ArrayList();
	//	Set userProSet = new HashSet();
				//String findProInfo = "select c.pro_name,c.pro_state from t_project c ,t_userproject d where c.pro_id=d.pro_id  and a.user_id=?";
		try {
			Connection coon = OracleDAOFactory.getConnection();
			PreparedStatement psm = coon.prepareStatement(getPageSql);
			psm.setInt(1, pageSize*currentPage);
			psm.setInt(2, pageSize*(currentPage-1));
			ResultSet rs = psm.executeQuery();
			User user = null;
			Dept dept = null;
			ProDAO userPro = null;
			while(rs.next()){
				userPro = new ProDAO();
				dept = new Dept();
				user = new User();
				user.setUser_id(rs.getInt(1));
				user.setStu_id(rs.getString(2));
				user.setUser_name(rs.getString(3));
				user.setDeptid(rs.getString(4));
				user.setGrade(rs.getString(5));
				user.setUser_state(rs.getString(6));
				dept.setDept_name(rs.getString(7));
				dept.setDept_state(rs.getString(8));
			//	userProSet = userPro.findProidByUserid(Integer.toString(rs.getInt(1)));
				List proidList= ipro.findById("pro_id", "user_id", rs.getInt(1));
				List userProList = ipro.findById(proidList);
				userInfo.add(user);
				userInfo.add(dept);
				userInfo.add(userProList);
			}
		} catch (Exception e) {
			String err = "SQLExecption in UserDAO():findUserInfoList()-----"+e;
			throw new SystemException(err);
		}
		return userInfo;
	}
	
	
	public void editUserInfo(User user)throws SystemException{
		boolean b = false;
		String update_userInfo = "update t_user set grade=? ,deptid=? where user_id=?";
		Connection conn = OracleDAOFactory.getConnection();
		try {
			PreparedStatement psm = conn.prepareStatement(update_userInfo);
			psm.setString(1, user.getGrade());
			psm.setString(2, user.getDeptid());
			psm.setInt(3, user.getUser_id());
			psm.executeUpdate();
		} catch (SQLException e) {
			String err = "SQLException in UserDAO:save(Object obj)--"+e;
			throw new SystemException(err);
		}
	}
	
	public void removeAll(User user) throws SystemException{
		Connection conn = OracleDAOFactory.getConnection();
		IProDAO ipro = DAOFactory.getDAOFactory(DAOFactory.ORACLE).getProDAO();
		ILogDAO ilog = DAOFactory.getDAOFactory(DAOFactory.ORACLE).getLogDAO();
		IUserDAO iuser = DAOFactory.getDAOFactory(DAOFactory.ORACLE).getUserDAO();
		List logList = ilog.findbyUserid(Integer.toString(user.getUser_id()));
		if(!logList.isEmpty()){
			for (Iterator iterator = logList.iterator(); iterator.hasNext();) {
				Log log = (Log) iterator.next();
				ilog.remove(log);
			}
		}
		ipro.removeUserPro("user_id", user.getUser_id());
		iuser.remove(user);
	}
	
	public static void main(String[] args) {
		List list = new ArrayList();
		int ia = 1,ib = 2,ic = 22;
		list.add(ia);
		list.add(2);
		list.add(22);
		User user = new User();
		user.setUser_id(2);
		Connection conn = OracleDAOFactory.getConnection();
		try {
			//System.out.println(new UserDAO().findById(list));
			//System.out.println(((User)new UserDAO().findById("1")).getBirthday());
			new UserDAO().remove(user);
		} catch (SystemException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
//			try {
//				conn.rollback();
//			} catch (SQLException e) {
//				// TODO Auto-generated catch block
//				e.printStackTrace();
//			}
		}
	}
}