changerecorddao.java

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

					if(deplist!=null){
					deplist=(ArrayList)depdao.queryByCondition(sqlstr);
					if(deplist.size() == 0){
						continue;
					}
					deptvo=(DeptInfoVO)deplist.get(0);
					}
					String exdeptname=deptvo.getDiName();
					
					/**将外键数据封装到各自的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(exdeptname);
					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"));
					showlist.add(changevo);									
				}
			} 
			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("PD_MainDispatchServlet");	//设置上一页，下一页，首页，末页的显示条
		pageHelp.setObjectlist(showlist);				//将list对象存储起来
		return pageHelp;
	
	}
	/**
	 * Serialization...
	 *
	 * @param  向方法传入String类型的查询条件condition，int类型的页面最大输出记录条数pageSize
	 * @param  int类型的数据currentPage表示现在是第几页
	 * @return List showlist返回从结果集中取出的多个VO对象到PageHelp类，用于实现分页功能
	 * @throws SQLException Problem performing DB exception
	 */
	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 user_info where ui_retirestate is null";
			if(!"".equals(condition)&&condition!=null){
			sql = "select count(*) listSize from user_info where ui_retirestate is null "+condition;	
			}
			sql=sql+" order by ui_age asc";						
			pageHelp.setSqlstr(sql);
			rs = super.dbStatement.executeQuery(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 user_info where 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 where ui_retirestate is null "+condition+" ) a where rownum<"+endNum+") b where rc >="+startNum+"";	
				sql=sql+" order by ui_age asc";							
				pageHelp.setSqlstr(sql);
				super.dbResultSet = super.dbStatement.executeQuery(sql);   //执行sql语句
				
				// 执行sql语句
				while(super.dbResultSet.next()){
					UserInfoVO uservo=new UserInfoVO();
					uservo.setUiLoadname(dbResultSet.getString("ui_loadname"));
					uservo.setUiId(dbResultSet.getLong("ui_id"));
					uservo.setUiPassword(dbResultSet.getString("ui_password"));
					uservo.setUiRealname(dbResultSet.getString("ui_realname"));
					uservo.setUiState(dbResultSet.getString("ui_state"));
					uservo.setUiSex(dbResultSet.getString("ui_sex"));
					uservo.setUiXueli(dbResultSet.getString("ui_xueli"));
					uservo.setUiDegree(dbResultSet.getString("ui_degree"));
					uservo.setUiZhiwu(dbResultSet.getString("ui_zhiwu"));
					uservo.setUiDuty(dbResultSet.getString("ui_duty"));
					uservo.setUiPhoto(dbResultSet.getString("ui_photo"));
					uservo.setUiPersonid(dbResultSet.getLong("ui_personid"));
					uservo.setUiAge(dbResultSet.getLong("ui_age"));
					uservo.setIDCardNum(dbResultSet.getString("ui_idcardnum"));
					uservo.setUiExtend(dbResultSet.getString("ui_extend"));
					Long depid=dbResultSet.getLong("di_id");
					roleid = dbResultSet.getLong("ri_id");
					
					DeptInfoDAO depdao = new DeptInfoDAO();
					DeptInfoVO depvo = new DeptInfoVO();
					RoleInfoDAO roledao = new RoleInfoDAO();
					RoleInfoVO rolevo = new RoleInfoVO();
					
					rolevo.setRiId(roleid);
					depvo.setDiId(depid);
					
					depvo = (DeptInfoVO)depdao.readObject(depvo);								
					rolevo = (RoleInfoVO)roledao.readObject(rolevo);
					uservo.setDeptInfo(depvo);
					uservo.setRoleInfo(rolevo);
					userlist.add(uservo);
				}
			} 
			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("PD_DispatchQueryServlet");//设置上一页，下一页，首页，末页的显示条				
		pageHelp.setObjectlist(userlist);		//将list对象存储起来
		return pageHelp;
	
	}
	/**
	 * Serialization...
	 *
	 * @param  向方法传入String类型的查询条件condition，int类型的页面最大输出记录条数pageSize
	 * @param  int类型的数据currentPage表示现在是第几页
	 * @return List showlist返回从结果集中取出的多个VO对象到PageHelp类，用于实现分页功能
	 * @throws SQLException Problem performing DB exception
	 */
	public PageHelp UserList(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 desc";
			
			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);
					deptvo=(DeptInfoVO)deplist.get(0);
					String exdeptname=deptvo.getDiName();
					/**将外键数据封装到各自的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(exdeptname);
					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"));
					showlist.add(changevo);									
				}
			} 
			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("PD_DispatchQueryServlet");	//设置上一页，下一页，首页，末页的显示条
		pageHelp.setObjectlist(showlist);				//将list对象存储起来
		return pageHelp;
	
	}
		
}