employeefiledao.java

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

/**
 * @作者：程杰
 */

package com.galaxy.dao;



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.EmployeeFileVO;
import com.galaxy.vo.RoleInfoVO;
import com.galaxy.vo.UserInfoVO;


public class EmployeeFileDAO extends ConnectDB implements DaoInterface{

	//参数为EmployeeFileVO对象，其中除了ID，其他字段都应该为有效值
	public int addObject(Object ob) {
		int result = 0;
		
		EmployeeFileVO efvo = new EmployeeFileVO();
		efvo = (EmployeeFileVO)ob;
		
		String psql = "insert into employee_file values(seq.nextval,?,?,?,?,?,?)";
		super.openDBConnection();
		try {
			PreparedStatement pst = dbConnection.prepareStatement(psql);
			pst.setLong(1, efvo.getUserInfo().getUiId());
			pst.setString(2, efvo.getEfName());
			pst.setString(3, efvo.getEfContent());
			pst.setString(4, efvo.getEfClass());
			pst.setString(5, efvo.getEfRemark());
			pst.setString(6, efvo.getEfExtend());
			
			result = pst.executeUpdate();
		} catch (SQLException e) {			
			e.printStackTrace();
		}		
		super.closeDBConnection();
		return result;
	}

	//参数为EmployeeFileVO的ID串，ID之间以逗号相隔
	public int deleteObject(Object cond) {
		int result = 0;
		String psql = "delete from employee_file where ef_id in(?)";
		super.openDBConnection();
		try {
			PreparedStatement pst = dbConnection.prepareStatement(psql);
			pst.setString(1, (String)cond);
			result = pst.executeUpdate();
		} catch (SQLException e) {			
			e.printStackTrace();
		}
		
		super.closeDBConnection();
		return result;
	}

	//参数为查询条件，查询条件头部要有'and'逻辑运算符
	public List queryByCondition(Object cond) {
		List efList = new ArrayList();
		String sql = "select * from employee_file e join user_info u on e.ui_id=u.ui_id  where 1=1 " + (String)cond
		             + "order by e.ef_id";
		super.openDBConnection();
		try {
			dbResultSet = dbStatement.executeQuery(sql);
			while (dbResultSet.next())
			{
				EmployeeFileVO efvo = new EmployeeFileVO();
				UserInfoVO uivo = new UserInfoVO();
				uivo.setUiId(dbResultSet.getLong("ui_id"));
				UserInfoDAO uidao = new UserInfoDAO();
				uivo = (UserInfoVO)uidao.readObject(uivo);
				efvo.setEfId(dbResultSet.getLong("ef_id"));
				efvo.setUserInfo(uivo);
				efvo.setEfName(dbResultSet.getString("ef_name"));
				efvo.setEfContent(dbResultSet.getString("ef_content"));
				efvo.setEfClass(dbResultSet.getString("ef_class"));
				efvo.setEfRemark(dbResultSet.getString("ef_remark"));
				efvo.setEfExtend(dbResultSet.getString("ef_extend"));
				
				efList.add(efvo);
				
			}
		} catch (SQLException e) {			
			e.printStackTrace();
		}
		
		super.closeDBConnection();
		return efList;
	}

	//参数为EmployeeFileVO对象，其中ID应该为有效值
	public Object readObject(Object cond) {
		EmployeeFileVO efvo = new EmployeeFileVO();
		String psql = "select * from employee_file where ef_id = ?";
		
		super.openDBConnection();
		try {
			PreparedStatement pst = dbConnection.prepareStatement(psql);
			pst.setLong(1, ((EmployeeFileVO)cond).getEfId());
			
			dbResultSet = pst.executeQuery();
			while(dbResultSet.next())
			{
				UserInfoVO uivo = new UserInfoVO();
				uivo.setUiId(dbResultSet.getLong("ui_id"));
				UserInfoDAO uidao = new UserInfoDAO();
				uivo = (UserInfoVO)uidao.readObject(uivo);
				efvo.setEfId(dbResultSet.getLong("ef_id"));
				efvo.setUserInfo(uivo);
				efvo.setEfName(dbResultSet.getString("ef_name"));
				efvo.setEfContent(dbResultSet.getString("ef_content"));
				efvo.setEfClass(dbResultSet.getString("ef_class"));
				efvo.setEfRemark(dbResultSet.getString("ef_remark"));
				efvo.setEfExtend(dbResultSet.getString("ef_extend"));				
			}
		} catch (SQLException e) {			
			e.printStackTrace();
		}
		
		super.closeDBConnection();
		
		return efvo;
	}

	//参数为EmployeeFileVO对象，其中所有字段应该为有效值
	public int updateObject(Object ob) {
		int result = 0;
		EmployeeFileVO efvo = new EmployeeFileVO();
		efvo = (EmployeeFileVO)ob;
		
		String psql = "update employee_file set ui_id=?,ef_name=?,ef_content=?," +
				"ef_class=?,ef_remark=?,ef_extend=? where ef_id=?";
		super.openDBConnection();
		try {
			PreparedStatement pst = dbConnection.prepareStatement(psql);
			
			pst.setLong(1, efvo.getUserInfo().getUiId());
			pst.setString(2, efvo.getEfName());
			pst.setString(3, efvo.getEfContent());
			pst.setString(4, efvo.getEfClass());
			pst.setString(5, efvo.getEfRemark());
			pst.setString(6, efvo.getEfExtend());
			pst.setLong(7, efvo.getEfId());	
			
			result = pst.executeUpdate();
		} catch (SQLException e) {			
			e.printStackTrace();
		}		
		super.closeDBConnection();
		return result;
	}
	
	public PageHelp GetList(String condition, int pageSize, int currentPage) {
		int listSize=0;
		List userlist=new ArrayList();//存放要显示到页面上的部分结果
		PageHelp pageHelp=new PageHelp();	
		String sql ="";
		try { //统计记录总数
			
			super.openDBConnection();
			ResultSet rs = null;
			 sql="select count(*) listSize from employee_file e join user_info u on e.ui_id=u.ui_id and u.ui_retirestate is null";
			if(!"".equals(condition)&&condition!=null){
			sql = "select count(*) listSize from employee_file e join user_info u on e.ui_id=u.ui_id where u.ui_retirestate is null "+condition;	
			}
			sql=sql+" order by e.ui_id desc";						
			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();
			
			Long roleid = 0l;
			int startNum=(currentPage - 1) * pageSize+1;//由于数据库中没有第0条记录所以要进行+1修正
			int endNum= currentPage* pageSize+1;
				sql = "select * from (select a.* ,rownum rc from(select * from employee_file e join user_info u on e.ui_id = u.ui_id and u.ui_retirestate is null) a where rownum<"+endNum+") b where rc >="+startNum+"";
				if(!"".equals(condition)&&condition!=null)
				sql = "select * from (select a.* ,rownum rc from(select * from employee_file e join user_info u on e.ui_id = u.ui_id where u.ui_retirestate is null "+condition+" ) a where rownum<"+endNum+") b where rc >="+startNum+"";	
				sql=sql+" order by ef_id desc";							
				pageHelp.setSqlstr(sql);
				super.dbResultSet = super.dbStatement.executeQuery(sql);   //执行sql语句
				
				// 执行sql语句
				while(super.dbResultSet.next()){
					EmployeeFileVO filevo=new EmployeeFileVO();
					filevo.setEfId(dbResultSet.getLong("ef_id"));					
					filevo.setEfName(dbResultSet.getString("ef_name"));
					filevo.setEfContent(dbResultSet.getString("ef_content"));
					filevo.setEfClass(dbResultSet.getString("ef_class"));
					filevo.setEfRemark(dbResultSet.getString("ef_remark"));
					filevo.setEfExtend(dbResultSet.getString("ef_extend"));
					Long userid=dbResultSet.getLong("QCSJ_C005000");//?
									
					UserInfoDAO userdao = new UserInfoDAO();
					UserInfoVO uservo = new UserInfoVO();
									
					uservo.setUiId(userid);
					uservo = (UserInfoVO)userdao.readObject(uservo);	
					filevo.setUserInfo(uservo);					
					userlist.add(filevo);
				}
			} 
			catch (SQLException SqlE) {
				SqlE.printStackTrace();
				
			} 
			catch (Exception E) {
			E.printStackTrace();
				
			} 
			finally {
				super.closeDBConnection();	
				
			}
		
			// 设置页面有关分页的显示信息	
		pageHelp.setCondition(condition);
		pageHelp.setCurrentpage(currentPage);	//要显示的是第几页
		pageHelp.setPagesize(pageSize);			//每页显示几条记录
		pageHelp.setRecordcount(listSize); 	//按当前条件查询结果的全部记录数（总条数）
		pageHelp.getPagecount();                //按照“页数=记录总数/每页显示条数”得到显示页数
		pageHelp.setSqlstr(sql);                //将当前的查询条件装入gageHelp对象中
		pageHelp.setPagebar("PA_EmployeeFileServlet");//设置上一页，下一页，首页，末页的显示条				
		pageHelp.setObjectlist(userlist);//将list对象存储起来
		return pageHelp;
	}


}