econtractdao.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.*;
import com.galaxy.dao.*;

public class EContractDAO extends ConnectDB implements DaoInterface{

	public int addObject(Object ob) {
		// TODO Auto-generated method stub
		EContractVO contractvo=(EContractVO)ob;
		int i=0;
		super.openDBConnection();
		String sql="insert into e_contract values(seq.nextval,?,?,?,?,?,?,?)";
		try {
			PreparedStatement pst=super.dbConnection.prepareStatement(sql);
			pst.setLong(1, contractvo.getUserInfo().getUiId());
			pst.setString(2, contractvo.getEcStation());
			pst.setString(3,contractvo.getEcContent());
			pst.setDate(4,(Date)contractvo.getStartTime());
			pst.setDate(5,(Date) contractvo.getEndTime());
			pst.setString(6,contractvo.getEcProtocol());
			pst.setString(7,"\"\"");
			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 e_contract where ec_id in("+condition.substring(1)+")";
		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
		EContractVO myvo=new EContractVO();
		UserInfoVO uservo=new UserInfoVO();
		String sql=(String)cond;
		super.openDBConnection();
		
		System.out.println("sql------"+sql);
		try {
			ResultSet rs=null;
			rs = super.dbStatement.executeQuery(sql);
			while(rs.next()){
				uservo.setUiRealname(rs.getString("ui_realname"));
				myvo.setUserInfo(uservo);
				myvo.setStartTime(rs.getDate("start_time"));
				myvo.setEndTime(rs.getDate("end_time"));
				myvo.setEcStation(rs.getString("ec_station"));
				myvo.setEcContent(rs.getString("ec_content"));
				myvo.setEcProtocol(rs.getString("ec_protocol"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			super.closeDBConnection();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return myvo;
	}
	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 e_contract e on u.ui_id=e.ui_id and u.ui_retirestate is null ";
			if(!"".equals(condition)&&condition!=null){
			sql = "select count(*) listSize from user_info u join e_contract e on u.ui_id=e.ui_id and 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 e_contract e on u.ui_id=e.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 user_info u join e_contract e on u.ui_id=e.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();
					EContractVO contractvo=new EContractVO();
					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("ui_id"));
					contractvo.setStartTime(dbResultSet.getDate("start_time"));
					contractvo.setEndTime(dbResultSet.getDate("end_time"));
					contractvo.setEcStation(dbResultSet.getString("ec_station"));
					contractvo.setEcContent(dbResultSet.getString("ec_content"));
					contractvo.setEcProtocol(dbResultSet.getString("ec_protocol"));
					contractvo.setEcId(dbResultSet.getLong("ec_id"));
					contractvo.setUserInfo(userinfovo);
					userlist.add(contractvo);
				}
			} 
			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_EmployeeContractServlet");//设置上一页，下一页，首页，末页的显示条
		pageHelp.setObjectlist(userlist);//将list对象存储起来
		return pageHelp;
	}


}