truirsdao.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.TrUiRsVO;
import com.galaxy.vo.TrUiRsIdVO;
import com.galaxy.dao.UserInfoDAO;
import com.galaxy.dao.TrainRecordDAO;
import com.galaxy.vo.TrainRecordVO;
import com.galaxy.vo.UserInfoVO;

public class TrUiRsDAO extends ConnectDB implements DaoInterface {

	public int addObject(Object ob) {
		// TODO Auto-generated method stub
		return 0;
	}

	public int deleteObject(Object cond) {
		// TODO Auto-generated method stub
		return 0;
	}

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

	public int updateObject(Object ob) {
		// TODO Auto-generated method stub
		return 0;
	}

	public List queryByCondition(Object cond) {
		List list = new ArrayList();
		String psql = "select * from tr_ui_rs where 1=1" + (String) cond;
		super.openDBConnection();

		Long tr_id;
		Long ui_id;

		try {
			super.dbResultSet = super.dbStatement.executeQuery(psql);
			while (super.dbResultSet.next()) {
				//TrUiRs是用户表与培训记录表的多对多关系表，该表引用用户ID和培训记录ID做主键
				//通过用户ID转化得用户VO，培训记录ID转化得培训记录VO。将这两个VO封装到TrUiRsIdVO中
				//再将TrUiRsIdVO和其他属性一起封装到TrUiRsVO中
				TrUiRsVO turVo = new TrUiRsVO();
				turVo.setUiExtend(dbResultSet.getString("ui_extend"));
				turVo.setUiIspass(dbResultSet.getString("ui_ispass"));
				turVo.setUiState(dbResultSet.getString("ui_state"));

				tr_id = dbResultSet.getLong("tr_id");
				ui_id = dbResultSet.getLong("ui_id");

				TrainRecordDAO trDao = new TrainRecordDAO();
				TrainRecordVO trVo = new TrainRecordVO();
				trVo = trDao.get(tr_id);

				UserInfoDAO userDao = new UserInfoDAO();
				UserInfoVO userVo = new UserInfoVO();
				userVo = (UserInfoVO) userDao.queryByCondition(
						"and ui_id=" + ui_id).get(0);

				TrUiRsIdVO turiVo = new TrUiRsIdVO();
				turiVo.setTrainRecord(trVo);
				turiVo.setUserInfo(userVo);

				turVo.setId(turiVo);

				list.add(turVo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		super.closeDBConnection();
		return list;
	}

	public int add(TrUiRsVO turVo) {
		super.openDBConnection();
		int userID = 0;
		try {
			String sql = "insert into tr_ui_rs values(?,?,?,?,?)";
			PreparedStatement psm = super.dbConnection.prepareStatement(sql);

			psm.setLong(1, turVo.getId().getTrainRecord().getTrId());
			psm.setLong(2, turVo.getId().getUserInfo().getUiId());
			psm.setString(3, turVo.getUiState());
			psm.setString(4, turVo.getUiIspass());
			psm.setString(5, turVo.getUiExtend());

			int i = psm.executeUpdate();

			return i;
		} catch (SQLException SqlE) {
			SqlE.printStackTrace();
			return -1;
		} catch (Exception E) {
			E.printStackTrace();
			return -2;
		} finally {
			// 关闭连接，释放数据库资源：
			super.closeDBConnection();
		}
	}

	public int update(TrUiRsVO turVo) {
		super.openDBConnection();
		try {

			String sql = "update tr_ui_rs set TR_ID="
					+ turVo.getId().getTrainRecord().getTrId() + ",UI_ID ='"
					+ turVo.getId().getUserInfo().getUiId() + "',UI_STATE='"
					+ turVo.getUiState() + "',UI_ISPASS='"
					+ turVo.getUiIspass() + "',UI_EXTEND='"
					+ turVo.getUiIspass() + "' where TR_ID="
					+ turVo.getId().getTrainRecord().getTrId() + "and UI_ID="
					+ turVo.getId().getUserInfo().getUiId() + "";
			int i = super.dbStatement.executeUpdate(sql); //执行sql语句
			return i;
		} catch (SQLException SqlE) {
			SqlE.printStackTrace();
			return -1;
		} catch (Exception E) {
			E.printStackTrace();
			return -2;
		} finally {
			// 关闭连接，释放数据库资源：
			super.closeDBConnection();
		}
	}

	public int delete(Long tr_id, Long ui_id) {
		super.openDBConnection();
		try {
			String sql = "delete from tr_ui_rs where " + "tr_id in ( " + tr_id
					+ ")" + "and ui_id in ( " + ui_id + ")" + "";

			int i = super.dbStatement.executeUpdate(sql); // 执行sql语句
			return i;
		} catch (SQLException SqlE) {
			SqlE.printStackTrace();
			return -1;
		} catch (Exception E) {
			E.printStackTrace();
			return -2;
		} finally {
			// 关闭连接，释放数据库资源：
			super.closeDBConnection();
		}
	}

	public TrUiRsVO get(TrUiRsIdVO turiVo) {
		super.openDBConnection();
		ResultSet rs = null;
		try {
			//从用户表中取
			String sql = "select * from tr_ui_rs where " + "tr_id = "
					+ turiVo.getTrainRecord().getTrId() + "and ui_id = "
					+ turiVo.getUserInfo().getUiId() + "";
			;
			rs = super.dbStatement.executeQuery(sql); // 执行sql语句
			TrUiRsVO turVo = null;
			if (rs.next()) {
				turVo = new TrUiRsVO();
				turVo.setId(turiVo);
				turVo.setUiState(rs.getString("UI_STATE"));
				turVo.setUiIspass(rs.getString("UI_ISPASS"));
				turVo.setUiExtend(rs.getString("UI_EXTEND"));

				return turVo;
			}
			return turVo;
		} catch (SQLException SqlE) {
			SqlE.printStackTrace();
			return null;
		} catch (Exception E) {
			E.printStackTrace();
			return null;
		} finally {
			// 关闭连接，释放数据库资源：
			//db_conn.CloseDB();
			super.closeDBConnection();
		}
	}

	//	获得培训报名信息上下列表翻页---------参数信息为 sql语句中的部分，每页显示记录数，当前页码
	public PageHelp getTrainRegisterList(String condition, int pageSize,
			int currentPage) {

		int listSize = 0;
		List showlist = new ArrayList();//存放要显示到页面上的部分结果
		PageHelp pageHelp = new PageHelp();
		String sql = "";
		try { //统计记录总数
			super.openDBConnection();
			//db_conn.ConnectDB();
			ResultSet rs = null;
			//System.out.println("uscd="+condition);
			sql = "select count(*) listSize from tr_ui_rs";
			if (!"".equals(condition) || !"null".equals(condition))
				sql = "select count(*) listSize from tr_ui_rs where 1=1 "
						+ condition;
			sql = sql + " order by TR_ID asc";
			//System.out.println("sqlcount------"+sql);			
			pageHelp.setSqlstr(sql);
			//rs = db_conn.sm.executeQuery(sql);   //执行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 {
			// 关闭连接，释放数据库资源：
			//db_conn.CloseDB();	
			super.closeDBConnection();

		}

		try { //找到要显示的记录
			//db_conn.ConnectDB();
			super.openDBConnection();
			ResultSet rs = null;
			int startNum = (currentPage - 1) * pageSize + 1;//由于数据库中没有第0条记录所以要进行+1修正
			int endNum = currentPage * pageSize + 1;
			sql = "select * from (select a.* ,rownum rc from(select * from tr_ui_rs order by tr_id asc) a where rownum<"
					+ endNum + ") b where rc >=" + startNum + "";
			if (!"".equals(condition) && condition != null)
				sql = "select * from (select a.* ,rownum rc from(select * from tr_ui_rs where 1=1 "
						+ condition
						+ " order by tr_id asc) a where rownum<"
						+ endNum + ") b where rc >=" + startNum + "";
			sql = sql + " order by TR_ID asc";

			//System.out.println("sqllist------"+sql);	

			pageHelp.setSqlstr(sql);
			//rs = db_conn.sm.executeQuery(sql);   //执行sql语句
			super.dbResultSet = super.dbStatement.executeQuery(sql);
			rs = super.dbResultSet;

			// 执行sql语句
			while (rs.next()) {

				//					TrUiRs是用户表与培训记录表的多对多关系表，该表引用用户ID和培训记录ID做主键
				//通过用户ID转化得用户VO，培训记录ID转化得培训记录VO。将这两个VO封装到TrUiRsIdVO中
				//再将TrUiRsIdVO和其他属性一起封装到TrUiRsVO中

				TrUiRsVO turVo = new TrUiRsVO();
				Long tr_id = rs.getLong("TR_ID");
				Long ui_id = rs.getLong("UI_ID");

				TrainRecordDAO trDao = new TrainRecordDAO();
				UserInfoDAO userDao = new UserInfoDAO();
				TrainRecordVO trVo = trDao.get(tr_id);

				//System.out.println("ui_id    "+ui_id);
				UserInfoVO userVo = (UserInfoVO) userDao
						.queryTrainRecordByCondition("and ui_id=" + ui_id + "")
						.get(0);

				TrUiRsIdVO turiVo = new TrUiRsIdVO();
				turiVo.setTrainRecord(trVo);
				turiVo.setUserInfo(userVo);

				turVo.setId(turiVo);
				turVo.setUiExtend(rs.getString("UI_EXTEND"));
				turVo.setUiIspass(rs.getString("UI_ISPASS"));
				turVo.setUiState(rs.getString("UI_STATE"));

				showlist.add(turVo);
			}

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

	//	获得培训报名信息上下列表翻页---------参数信息为 sql语句中的部分，每页显示记录数，当前页码
	public PageHelp getTrainScoreList(String condition, int pageSize,
			int currentPage) {

		int listSize = 0;
		List showlist = new ArrayList();//存放要显示到页面上的部分结果
		PageHelp pageHelp = new PageHelp();
		String sql = "";
		try { //统计记录总数
			super.openDBConnection();
			ResultSet rs = null;
			//System.out.println("uscd="+condition);
			sql = "select count(*) listSize from tr_ui_rs";
			if (!"".equals(condition) || !"null".equals(condition))
				sql = "select count(*) listSize from tr_ui_rs where 1=1 "
						+ condition;
			sql = sql + " order by TR_ID asc";
			//System.out.println("sqlcount------"+sql);			
			pageHelp.setSqlstr(sql);
			//rs = db_conn.sm.executeQuery(sql);   //执行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();
			ResultSet rs = null;
			int startNum = (currentPage - 1) * pageSize + 1;//由于数据库中没有第0条记录所以要进行+1修正
			int endNum = currentPage * pageSize + 1;
			sql = "select * from (select a.* ,rownum rc from(select * from tr_ui_rs order by tr_id asc) a where rownum<"
					+ endNum + ") b where rc >=" + startNum + "";
			if (!"".equals(condition) && condition != null)
				sql = "select * from (select a.* ,rownum rc from(select * from tr_ui_rs where 1=1 "
						+ condition
						+ " order by tr_id asc) a where rownum<"
						+ endNum + ") b where rc >=" + startNum + "";
			sql = sql + " order by TR_ID asc";

			//System.out.println("sqllist------"+sql);	

			pageHelp.setSqlstr(sql);
			//rs = db_conn.sm.executeQuery(sql);   //执行sql语句
			super.dbResultSet = super.dbStatement.executeQuery(sql);
			rs = super.dbResultSet;

			// 执行sql语句
			while (rs.next()) {

				//					TrUiRs是用户表与培训记录表的多对多关系表，该表引用用户ID和培训记录ID做主键
				//通过用户ID转化得用户VO，培训记录ID转化得培训记录VO。将这两个VO封装到TrUiRsIdVO中
				//再将TrUiRsIdVO和其他属性一起封装到TrUiRsVO中

				TrUiRsVO turVo = new TrUiRsVO();
				Long tr_id = rs.getLong("TR_ID");
				Long ui_id = rs.getLong("UI_ID");

				TrainRecordDAO trDao = new TrainRecordDAO();
				UserInfoDAO userDao = new UserInfoDAO();
				TrainRecordVO trVo = trDao.get(tr_id);
				UserInfoVO userVo = (UserInfoVO) userDao
						.queryTrainRecordByCondition("and ui_id=" + ui_id + "")
						.get(0);

				TrUiRsIdVO turiVo = new TrUiRsIdVO();
				turiVo.setTrainRecord(trVo);
				turiVo.setUserInfo(userVo);

				turVo.setId(turiVo);
				turVo.setUiExtend(rs.getString("UI_EXTEND"));
				turVo.setUiIspass(rs.getString("UI_ISPASS"));
				turVo.setUiState(rs.getString("UI_STATE"));

				showlist.add(turVo);
			}

		} catch (SQLException SqlE) {
			SqlE.printStackTrace();

		} catch (Exception E) {
			E.printStackTrace();

		} finally {
			// 关闭连接，释放数据库资源：
			//db_conn.CloseDB();	
			super.closeDBConnection();

		}

		// 设置页面有关分页的显示信息	
		pageHelp.setCondition(condition);
		pageHelp.setCurrentpage(currentPage); //要显示的是第几页
		pageHelp.setPagesize(pageSize); //每页显示几条记录
		pageHelp.setRecordcount(listSize); //按当前条件查询结果的全部记录数（总条数）
		pageHelp.getPagecount(); //按照“页数=记录总数/每页显示条数”得到显示页数
		pageHelp.setSqlstr(sql); //将当前的查询条件装入gageHelp对象中
		pageHelp.setPagebar("/training/TR_TrainScoreServlet");//设置上一页，下一页，首页，末页的显示条
		pageHelp.setObjectlist(showlist);//将list对象存储起来
		return pageHelp;
	}
}