employeerecorddao.java

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

package com.galaxy.dao;

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

import com.galaxy.base.DaoInterface;
import com.galaxy.db.ConnectDB;
import com.galaxy.util.PageHelp;
import com.galaxy.vo.DeptInfoVO;
import com.galaxy.vo.RewardPunishVO;
import com.galaxy.vo.UserInfoVO;
import com.galaxy.vo.EmployeeRecordVO;

public class EmployeeRecordDAO extends ConnectDB implements DaoInterface{

	public int addObject(Object ob) {
		// TODO Auto-generated method stub
		EmployeeRecordVO ervo=(EmployeeRecordVO)ob;
		int i=0;
			super.openDBConnection();
		String sql="insert into employee_record values(seq.nextval,?,?,?,?,?,?,?,?,?)";
		try {
			PreparedStatement pst=super.dbConnection.prepareStatement(sql);
			pst.setLong(1, ervo.getUserInfo().getUiId());
			pst.setString(2, ervo.getErFactory());
			pst.setString(3,ervo.getErDepartment());
			pst.setString(4,ervo.getErGrade());
			pst.setDate(5,(Date)ervo.getStartTime());
			pst.setDate(6,(Date)ervo.getEndTime());
			pst.setString(7,ervo.getErPosition());
			pst.setString(8,ervo.getErContent());
			pst.setString(9,"\"\"");
			i=pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			super.closeDBConnection();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}

	public int deleteObject(Object cond) {
		// TODO Auto-generated method stub
		String condition=(String)cond;
		int i=0;
		super.openDBConnection();
		String sql="delete from employee_record where er_id in("+condition+")";
		System.out.println("sql------"+sql);
		try {
			i=super.dbStatement.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			super.closeDBConnection();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}

	public List queryByCondition(Object cond) {
		// TODO Auto-generated method stub
		return null;
	}

	public Object readObject(Object cond) {
		// TODO Auto-generated method stub
		EmployeeRecordVO ervo=new EmployeeRecordVO();
		String sql=(String)cond;
		super.openDBConnection();
		
		System.out.println("sql------"+sql);
		try {
			ResultSet rs=null;
			rs = super.dbStatement.executeQuery(sql);
			while(rs.next()){
				UserInfoVO userinfovo=new UserInfoVO();
				userinfovo.setUiRealname(rs.getString("ui_realname"));
				userinfovo.setUiPersonid(rs.getLong("ui_personid"));
				userinfovo.setUiSex(rs.getString("ui_sex"));
				userinfovo.setUiId(rs.getLong("ui_id"));
				ervo.setStartTime(rs.getDate("start_time"));
				ervo.setEndTime(rs.getDate("end_time"));
				ervo.setErDepartment(rs.getString("er_department"));
				ervo.setErFactory(rs.getString("er_factory"));
				ervo.setErPosition(rs.getString("er_position"));
				ervo.setErContent(rs.getString("er_content"));
				ervo.setErGrade(rs.getString("er_grade"));
				ervo.setErId(rs.getLong("er_id"));
				ervo.setUserInfo(userinfovo);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			super.closeDBConnection();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return ervo;
	}

	public Long GetUIID(Object cond)
	{
		Long UIID=0L;
		String sql=(String)cond;
		super.openDBConnection();
		
		System.out.println("sql------"+sql);
		try {
			ResultSet rs=null;
			rs = super.dbStatement.executeQuery(sql);
			while(rs.next()){
				UIID=rs.getLong("ui_id");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			super.closeDBConnection();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return UIID;
	}
	
	public int updateObject(Object ob) {
		// TODO Auto-generated method stub
		int i=0;
		String sql=(String)ob;
		super.openDBConnection();
		
		System.out.println("sql------"+sql);
		try {
			i=super.dbStatement.executeUpdate(sql);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			super.closeDBConnection();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}

	public PageHelp getList(String condition, int pageSize, int currentPage) throws SQLException {
		int listSize=0;
		List userlist=new ArrayList();//存放要显示到页面上的部分结果
		PageHelp pageHelp=new PageHelp();	
		String sql ="";
		try { //统计记录总数
			
			super.openDBConnection();
			ResultSet rs = null;
			 sql="select count(*) listSize from user_info u join employee_record d on u.ui_id=d.ui_id and u.ui_retirestate is null ";
			if(!"".equals(condition)&&condition!=null){
			sql = "select count(*) listSize from user_info u join employee_record d on u.ui_id=d.ui_id where u.ui_retirestate is null "+condition;	
			}
			sql=sql+" order by d.er_id desc";
			System.out.println("sqlcount------"+sql);			
			pageHelp.setSqlstr(sql);
			rs = super.dbStatement.executeQuery(sql);   //执行sql语句
			
			// 执行sql语句
			while (rs.next()) {
				 listSize=rs.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 = "select * from (select a.*, rownum rc  from" +
						"(select * from user_info u join employee_record d on u.ui_id=d.ui_id and u.ui_retirestate is null order by d.er_id desc) a" +
						" where rownum<"+endNum+" ) b where rc >="+startNum+"";
				if(!"".equals(condition)&&condition!=null)
				sql = "select * from (select a.*,rownum rc  from(select * from user_info u join employee_record d on u.ui_id=d.ui_id where u.ui_retirestate is null "+condition+
				") a where rownum<"+endNum+") b where rc >="+startNum+"";	
				//sql=sql+" order by d.er_id desc";
				System.out.println("sqllist------"+sql);			
				pageHelp.setSqlstr(sql);
				super.dbResultSet = super.dbStatement.executeQuery(sql);   //执行sql语句
				
				// 执行sql语句
				while(super.dbResultSet.next()){
					UserInfoVO userinfovo=new UserInfoVO();
					EmployeeRecordVO recordvo=new EmployeeRecordVO();
					DeptInfoVO deptvo=new DeptInfoVO();
					DeptInfoDAO deptdao=new DeptInfoDAO();
					deptvo.setDiId(dbResultSet.getLong("di_id"));
					deptvo=(DeptInfoVO)deptdao.readObject(deptvo);
					userinfovo.setDeptInfo(deptvo);
					userinfovo.setUiRealname(dbResultSet.getString("ui_realname"));
					userinfovo.setUiId(dbResultSet.getLong("QCSJ_C005000"));
					recordvo.setStartTime(dbResultSet.getDate("start_time"));
					recordvo.setEndTime(dbResultSet.getDate("end_time"));
					recordvo.setErDepartment(dbResultSet.getString("er_department"));
					recordvo.setErFactory(dbResultSet.getString("er_factory"));
					recordvo.setErPosition(dbResultSet.getString("er_position"));
					recordvo.setErContent(dbResultSet.getString("er_content"));
					recordvo.setErGrade(dbResultSet.getString("er_grade"));
					recordvo.setErId(dbResultSet.getLong("er_id"));
					recordvo.setUserInfo(userinfovo);
					userlist.add(recordvo);
				}
			} 
			catch (SQLException SqlE) {
				SqlE.printStackTrace();
				
			} 
			catch (Exception E) {
			E.printStackTrace();
				
			} 
			finally {
				// 关闭连接，释放数据库资源：
				try {
					super.closeDBConnection();
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}	
				
			}
		
			// 设置页面有关分页的显示信息	
		pageHelp.setCondition(condition);
		pageHelp.setCurrentpage(currentPage);	//要显示的是第几页
		pageHelp.setPagesize(pageSize);			//每页显示几条记录
		pageHelp.setRecordcount(listSize); 	//按当前条件查询结果的全部记录数（总条数）
		pageHelp.getPagecount();                //按照“页数=记录总数/每页显示条数”得到显示页数
		pageHelp.setSqlstr(sql);                //将当前的查询条件装入gageHelp对象中
		pageHelp.setPagebar("PA_EmployeeRecordServlet");//设置上一页，下一页，首页，末页的显示条
		pageHelp.setObjectlist(userlist);//将list对象存储起来
		return pageHelp;
	}


}