userinfodao.java

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

			pst.setString(15, uivo.getIdCardNum());
			pst.setString(16, uivo.getUiExtend());
			pst.setLong(17, uivo.getUiId());

			result = pst.executeUpdate();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		super.closeDBConnection();
		return result;
	}

	/**执行人事调动后，将用户表中的用户现在所属部门更新(write by LW)*/
	public int updateInfo(String cond) {
		int result = 0;
		super.openDBConnection();
		try {
			result = super.dbStatement.executeUpdate(cond);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		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 user_info u join dept_info d on u.di_id=d.di_id and u.ui_retirestate is null ";
			if (!"".equals(condition) && condition != null) {
				sql = "select count(*) listSize from user_info u join dept_info d on u.di_id=d.di_id where 1=1 and u.ui_retirestate is null "
						+ condition;
			}
			sql = sql + " order by u.di_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 user_info u join dept_info d on u.di_id=d.di_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 dept_info d on u.di_id=d.di_id where 1=1 and u.ui_retirestate is null "
						+ condition
						+ " ) a where rownum<"
						+ endNum
						+ ") b where rc >=" + startNum + "";
			sql = sql + " order by ui_id desc";
			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("QCSJ_C005000");//?

				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("PA_UserInfoServlet");//设置上一页，下一页，首页，末页的显示条				
		pageHelp.setObjectlist(userlist);//将list对象存储起来
		return pageHelp;
	}

	public PageHelp GetList2(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 u join dept_info d on u.di_id=d.di_id and u.ui_retirestate is null";
			if (!"".equals(condition) && condition != null) {
				sql = "select count(*) listSize from user_info u join dept_info d on u.di_id=d.di_id where 1=1 and u.ui_retirestate is null "
						+ condition;
			}
			sql = sql + " order by u.di_id desc";

			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 user_info u join dept_info d on u.di_id=d.di_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 dept_info d on u.di_id=d.di_id where 1=1 and u.ui_retirestate is null "
						+ condition
						+ " ) a where rownum<"
						+ endNum
						+ ") b where rc >=" + startNum + "";
			sql = sql + " order by ui_id desc";
			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("QCSJ_C005000");//?

				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("SM_UserInfoServlet");//设置上一页，下一页，首页，末页的显示条				
		pageHelp.setObjectlist(userlist);//将list对象存储起来
		return pageHelp;
	}

}