changerecorddao.java

J2ee开发的 人事管理系统 使用oracle数据库 myeclips平台开发

/**
 * @(#)Prmis , Created on 2008-10-19
 *
 * Copyright (c) galaxy Teamwork
 *
 * MODIFY MEMO:
 * 
 */
package com.galaxy.dao;


import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import com.galaxy.util.*;

import com.galaxy.base.DaoInterface;
import com.galaxy.db.ConnectDB;
import com.galaxy.vo.ChangeRecordVO;
import com.galaxy.vo.DeptInfoVO;
import com.galaxy.vo.RoleInfoVO;
import com.galaxy.vo.UserInfoVO;

public class ChangeRecordDAO extends ConnectDB implements DaoInterface{
	/**
	 * Serialization...
	 *
	 * @param  向方法传入一个封装好的VO对象，用于放到SQL中
	 * @return int i 表示插入是否成功
	 * @throws SQLException Problem performing DB exception
	 */
	public int addObject(Object ob) {
		int i=0;
		Long pcr_id=0l;
		ChangeRecordVO changevo=(ChangeRecordVO)ob;
		super.openDBConnection();	
		String psql="insert into galaxy.change_record values" +
				"(seq.nextval,?,?,?,?,?,?,?,?,?,?)";
		try {
			PreparedStatement pst=super.dbConnection.prepareStatement(psql);
			pst.setLong(1, changevo.getUserInfo().getUiId());
			pst.setLong(2, changevo.getDeptInfo().getDiId());
			pst.setString(3, changevo.getPcrClass());
			pst.setString(4, changevo.getPcrCause());
			pst.setString(5, changevo.getPcrExdept());
			pst.setString(6, changevo.getPcrExstudy());
			pst.setString(7, changevo.getPcrStudy());
			pst.setString(8, changevo.getPcrRange());
			/**从VO中取出的Date是util类型的*/
			java.util.Date Date=changevo.getPcrTime();
			/**实现util类型和sql类型转换*/
			java.sql.Date sqlDate = new java.sql.Date(Date.getTime()); 
			/**设置输入数据库的格式yyyy-mm-dd*/
			SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-mm-dd");
			dateFormat.format(sqlDate);
			/**把date装入预编译中*/
			pst.setDate(9, sqlDate);
			pst.setString(10, changevo.getPcrExtend());
			i=pst.executeUpdate();
			super.closeDBConnection();			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return i;
	}
	/**
	 * Serialization...
	 *
	 * @param  向方法传入一个Object对象cond，用于放到SQL中
	 * @return int result 表示删除是否成功
	 * @throws SQLException Problem performing DB exception
	 */
	public int deleteObject(Object cond) {
		int result = 0;		
		super.openDBConnection();
		String sql="delete from CHANGE_RECORD where pcr_id in("+cond+")";
		try {
			result=super.dbStatement.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		super.closeDBConnection();
		return result;
	}
	/**
	 * Serialization...
	 *
	 * @param  向方法传入一个Object对象cond，用于放到SQL中
	 * @return List transferlist返回从结果集中取出的多个VO对象
	 * @throws SQLException Problem performing DB exception
	 */
	public List queryByCondition(Object cond) {
		List transferlist=new ArrayList();
		super.openDBConnection();
		String sql=(String)cond;
		try {
			super.dbResultSet =super.dbStatement.executeQuery(sql);
			while(dbResultSet.next()){
				ChangeRecordVO changevo=new ChangeRecordVO();
				Long di_id=super.dbResultSet.getLong("di_id");
				Long ui_id=super.dbResultSet.getLong("ui_id");
				DeptInfoDAO depdao=new DeptInfoDAO();
				UserInfoDAO userdao=new UserInfoDAO();
				DeptInfoVO depvo=new DeptInfoVO();
				UserInfoVO uservo=new UserInfoVO();
				/**将外键数据封装到各自的VO中*/
				depvo.setDiId(di_id);
				uservo.setUiId(ui_id);
				/**将外键数据通过各自的dao读出所需数据后又装入各自VO中*/
				depvo=(DeptInfoVO)depdao.readObject(depvo);
				uservo=(UserInfoVO)userdao.readObject(uservo);
				/**将全部数据都放进人事调动VO中*/
				changevo.setUserInfo(uservo);
				changevo.setDeptInfo(depvo);
				changevo.setPcrCause(dbResultSet.getString("pcr_cause"));
				changevo.setPcrClass(dbResultSet.getString("pcr_class"));
				changevo.setPcrExdept(dbResultSet.getString("pcr_exdept"));
				changevo.setPcrExstudy(dbResultSet.getString("pcr_exstudy"));
				changevo.setPcrId(dbResultSet.getLong("pcr_id"));
				changevo.setPcrRange(dbResultSet.getString("pcr_range"));
				changevo.setPcrStudy(dbResultSet.getString("pcr_study"));
				changevo.setPcrTime(dbResultSet.getDate("pcr_time"));
				/**把VO中的数据放入List中*/
				transferlist.add(changevo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		super.closeDBConnection();
		return transferlist;
	}
	/**
	 * Serialization...
	 *
	 * @param  向方法传入一个Object对象cond，用于放到SQL中
	 * @return List transferlist返回从结果集中取出的多个VO对象
	 * @throws SQLException Problem performing DB exception
	 */
	public Object readObject(Object cond) {
		return null;
	}
	/**
	 * Serialization...
	 *
	 * @param  向方法传入一个Object对象cond，用于放到SQL中
	 * @return int i 用于返回是否更新成功
	 * @throws SQLException Problem performing DB exception
	 */
	public int updateObject(Object ob) {
		ChangeRecordVO changevo = new ChangeRecordVO();
		changevo = (ChangeRecordVO)ob;
		int i = 0;
		String sql = "update change_record set pcr_class ='" 
			+changevo.getPcrClass()+"', pcr_cause='"
			+changevo.getPcrCause()+"', pcr_exdept='"
			+changevo.getPcrExdept()+"', pcr_exstudy='"
			+changevo.getPcrExstudy()+"', pcr_study='"
			+changevo.getPcrStudy()+"', di_id='"
			+changevo.getDeptInfo().getDiId()+"', pcr_time="
			+"to_date('" +changevo.getPcrTime().toString()+"','yyyy-mm-dd'),pcr_range='"
			+changevo.getPcrRange()+"' where pcr_id = " 
			+changevo.getPcrId();
		super.openDBConnection();
		try {
			i = super.dbStatement.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		super.closeDBConnection();
		return i;
	}
	/**
	 * Serialization...
	 *
	 * @param  向方法传入一个String对象cond，用于放到SQL中
	 * @return ChangeRecordVO changevo返回从结果集中取出的单个VO对象
	 * @throws SQLException Problem performing DB exception
	 */
	public ChangeRecordVO get(String cond) {
		super.openDBConnection();
		ChangeRecordVO changevo=new ChangeRecordVO();
		try {
			super.dbResultSet =super.dbStatement.executeQuery(cond);
			while(dbResultSet.next()){
				Long di_id=super.dbResultSet.getLong("di_id");
				Long ui_id=super.dbResultSet.getLong("ui_id");
				DeptInfoDAO depdao=new DeptInfoDAO();
				UserInfoDAO userdao=new UserInfoDAO();
				DeptInfoVO depvo=new DeptInfoVO();
				UserInfoVO uservo=new UserInfoVO();
				/**将外键数据封装到各自的VO中*/
				depvo.setDiId(di_id);
				uservo.setUiId(ui_id);
				/**将外键数据通过各自的dao读出所需数据后又装入各自VO中*/
				depvo=(DeptInfoVO)depdao.readObject(depvo);
				uservo=(UserInfoVO)userdao.readObject(uservo);
				/**将全部数据都放进人事调动VO中*/
				changevo.setUserInfo(uservo);
				changevo.setDeptInfo(depvo);
				changevo.setPcrCause(dbResultSet.getString("pcr_cause"));
				changevo.setPcrClass(dbResultSet.getString("pcr_class"));
				changevo.setPcrExdept(dbResultSet.getString("pcr_exdept"));
				changevo.setPcrExstudy(dbResultSet.getString("pcr_exstudy"));
				changevo.setPcrId(dbResultSet.getLong("pcr_id"));
				changevo.setPcrRange(dbResultSet.getString("pcr_range"));
				changevo.setPcrStudy(dbResultSet.getString("pcr_study"));
				changevo.setPcrTime(dbResultSet.getDate("pcr_time"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		super.closeDBConnection();
		return changevo;

	}
	/**
	 * Serialization...
	 *
	 * @param  向方法传入String类型的查询条件condition，int类型的页面最大输出记录条数pageSize
	 * @param  int类型的数据currentPage表示现在是第几页
	 * @return List showlist返回从结果集中取出的多个VO对象到PageHelp类，用于实现分页功能
	 * @throws SQLException Problem performing DB exception
	 */
	public PageHelp getUserList(String condition, int pageSize, int currentPage) {
		int listSize=0;
		List showlist=new ArrayList();//存放要显示到页面上的部分结果
		PageHelp pageHelp=new PageHelp();	
		String sql ="";
		try { //统计记录总数				
			super.openDBConnection();			
			sql = "select count(*) listSize from (select u.ui_id as u_ui_id, u.ri_id," +
				" u.di_id as u_di_id, u.ui_age,u.ui_realname, u.ui_state," +
				"u.ui_retirestate,u.ui_zhiwu, d.di_id as d_di_id, d.di_name, c.* "
				+"from user_info u, dept_info d, change_record c "
				+"where u.di_id = d.di_id and u.ui_id =c.ui_id)";
			if(!"".equals(condition)||!"null".equals(condition))
			sql = "select count(*) listSize from (select u.ui_id as u_ui_id, u.ri_id," 
				+" u.di_id as u_di_id, u.ui_age,u.ui_realname, u.ui_state,u.ui_zhiwu,"
				+"u.ui_retirestate, d.di_id as d_di_id, d.di_name, c.* "
				+"from user_info u, dept_info d, change_record c "
				+"where u.di_id = d.di_id and u.ui_id =c.ui_id) where 1=1"+condition;	
			sql=sql+" order by pcr_time asc";
			
			pageHelp.setSqlstr(sql);
			super.dbResultSet = super.dbStatement.executeQuery(sql);   //执行sql语句				
			// 执行sql语句
			while (super.dbResultSet.next()) {
				 listSize=super.dbResultSet.getInt("listSize");								
			}
		} 
		catch (SQLException SqlE) {
			SqlE.printStackTrace();	
		} 
		catch (Exception E) {
		E.printStackTrace();			
		} 
		finally {
			// 关闭连接，释放数据库资源：
			super.closeDBConnection();	
			}
		try { //找到要显示的记录
			super.openDBConnection();
			int startNum=(currentPage - 1) * pageSize+1;//由于数据库中没有第0条记录所以要进行+1修正
			int endNum= currentPage* pageSize+1;
			/**三表联合查询，其中有用户表，人事记录表，部门表，此sql用于大多数人事调动的查询操作*/
				sql = "select * from (select a.* ,rownum rc from(select * from (select u.ui_id as u_ui_id," 
				+" u.di_id as u_di_id,u.ui_realname,u.ui_state,u.ui_age,u.ui_zhiwu,"
				+"u.ui_retirestate,d.di_id as d_di_id, d.di_name, c.* "
				+"from user_info u, dept_info d, change_record c "
				+"where u.di_id = d.di_id and u.ui_id =c.ui_id)) a where rownum<"+endNum+") b where rc >="+startNum+"";
				if(!"".equals(condition)&&condition!=null)
				sql = "select * from (select a.* ,rownum rc from(select * from "
				+"(select u.ui_id as u_ui_id, u.di_id as u_di_id," 
				+"u.ui_realname,u.ui_state,u.ui_age,u.ui_zhiwu, "
				+"u.ui_retirestate,d.di_id as d_di_id, c.* "
				+"from user_info u, dept_info d, change_record c "
				+"where u.di_id = d.di_id and u.ui_id =c.ui_id) where 1=1 "+condition+" order by pcr_time desc) a where rownum<"+endNum+") b where rc >="+startNum+"";	
				sql=sql+" order by pcr_time desc";
				
				pageHelp.setSqlstr(sql);
				super.dbResultSet = super.dbStatement.executeQuery(sql);   //执行sql语句				
				// 执行sql语句
				while (super.dbResultSet.next()) {
					ChangeRecordVO changevo=new ChangeRecordVO();
					Long di_id=super.dbResultSet.getLong("d_di_id");
					Long ui_id=super.dbResultSet.getLong("u_ui_id");
					DeptInfoDAO depdao=new DeptInfoDAO();
					UserInfoDAO userdao=new UserInfoDAO();
					DeptInfoVO depvo=new DeptInfoVO();
					DeptInfoVO deptvo=new DeptInfoVO();
					UserInfoVO uservo=new UserInfoVO();
					/**取出前部门号，查出对应的部门名称*/
					Long pcr_exdept=Long.valueOf(super.dbResultSet.getString("pcr_exdept"));
					List deplist=new ArrayList();
					String sqlstr=" and di_id="+pcr_exdept+"";
					deplist=(ArrayList)depdao.queryByDepid(sqlstr);