trainrecorddao.java

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

package com.galaxy.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.sql.Date;

import com.galaxy.util.PageHelp;
import com.galaxy.vo.TrainRecordVO;
import com.galaxy.dao.TrainClassDAO;
import com.galaxy.vo.TrainClassVO;

import com.galaxy.base.DaoInterface;
import com.galaxy.db.ConnectDB;

public class TrainRecordDAO 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 List queryByCondition(Object cond) {
		// TODO Auto-generated method stub
		return null;
	}

	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 int add(TrainRecordVO tcVo) {
		super.openDBConnection();
		try {

			java.util.Date Date1 = tcVo.getStartTime();
			java.util.Date Date2 = tcVo.getEndTime();
			//实现util类型和sql类型转换
			java.sql.Date startTime = new java.sql.Date(Date1.getTime());
			java.sql.Date endTime = new java.sql.Date(Date2.getTime());
			//设置输入数据库的格式yyyy-mm-dd
			SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-mm-dd");
			dateFormat.format(startTime);
			dateFormat.format(endTime);

			//			tc_extend字段用于标识该条记录是否可用。为1则可用。为0不可用。
			//在添加时，初始设为1，即可用。修改时，不能修改该字段。只有在删除时，将该字段设置为0。即不可用。
			//显示列表时，只显示可用（tr_extend='1')的记录
			String sql = "insert into train_record values(galaxy.seq.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,?,'1')";
			PreparedStatement psm = super.dbConnection.prepareStatement(sql);
			psm.setLong(1, tcVo.getTrainClass().getTcId());
			psm.setString(2, tcVo.getTrName());
			psm.setString(3, tcVo.getTrCharacter());
			psm.setDate(4, startTime);
			psm.setDate(5, endTime);
			psm.setDate(6, null);
			psm.setLong(7, tcVo.getTrFee());
			psm.setString(8, tcVo.getTrPlace());
			psm.setString(9, tcVo.getTrOrgnization());
			psm.setString(10, tcVo.getTrForm());
			psm.setString(11, tcVo.getTrManager());
			psm.setString(12, tcVo.getTrTeacher());
			psm.setString(13, tcVo.getTrCourse());
			psm.setString(14, tcVo.getTrState());
			//psm.setString(15, tcVo.getTrExtend());

			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(TrainRecordVO tcVo) {
		super.openDBConnection();
		try {

			//			tc_extend字段用于标识该条记录是否可用。为1则可用。为0不可用。
			//在添加时，初始设为1，即可用。修改时，不能修改该字段。只有在删除时，将该字段设置为0。即不可用。
			//显示列表时，只显示可用（tr_extend='1')的记录
			String sql = "update train_record set TR_ID=" + tcVo.getTrId()
					+ ",TC_ID =" + tcVo.getTrainClass().getTcId()
					+ ",TR_NAME ='" + tcVo.getTrName() + "',TR_CHARACTER ='"
					+ tcVo.getTrCharacter() + "',START_TIME =to_date('"
					+ tcVo.getStartTime()
					+ "','yyyy-mm-dd'),END_TIME =to_date('" + tcVo.getEndTime()
					+ "','yyyy-mm-dd'),TR_FEE =" + tcVo.getTrFee()
					+ ",TR_PLACE ='" + tcVo.getTrPlace() + "',TR_ORGNIZATION='"
					+ tcVo.getTrOrgnization() + "',TR_FORM ='"
					+ tcVo.getTrForm() + "',TR_MANAGER ='"
					+ tcVo.getTrManager() + "',TR_TEACHER ='"
					+ tcVo.getTrTeacher() + "',TR_COURSE ='"
					+ tcVo.getTrCourse() + "',TR_STATE ='" + tcVo.getTrState()
					+ "' where TR_ID=" + tcVo.getTrId() + "";
			//System.out.println(sql);
			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(int id) {
		super.openDBConnection();
		try {
			//			tc_extend字段用于标识该条记录是否可用。为1则可用。为0不可用。
			//在添加时，初始设为1，即可用。修改时，不能修改该字段。只有在删除时，将该字段设置为0。即不可用。
			//显示列表时，只显示可用（tr_extend='1')的记录

			String sql = "update train_record set tr_extend='0' where tr_id in ( "
					+ 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();
		}
	}

	//通过用户ID获取用户信息
	public TrainRecordVO get(Long id) {
		super.openDBConnection();
		ResultSet rs = null;
		try {
			//从用户表中取
			String sql = "select * from train_record where tr_id = " + id + "";
			rs = super.dbStatement.executeQuery(sql); // 执行sql语句
			TrainRecordVO trVo = null;
			if (rs.next()) {

				trVo = new TrainRecordVO();
				TrainClassDAO tcDao = new TrainClassDAO();
				TrainClassVO tcVo = new TrainClassVO();
				tcVo.setTcId(rs.getLong("TC_ID"));
				tcVo = tcDao.get(rs.getLong("TC_ID"));
				trVo.setTrId(id);
				trVo.setTrainClass(tcVo);
				trVo.setTrName(rs.getString("TR_NAME"));
				trVo.setTrCharacter(rs.getString("TR_CHARACTER"));
				trVo.setStartTime(rs.getDate("START_TIME"));
				trVo.setEndTime(rs.getDate("END_TIME"));
				trVo.setTrCoursetime(null);
				trVo.setTrFee(Long.valueOf(rs.getString("TR_FEE")));
				trVo.setTrPlace(rs.getString("TR_PLACE"));
				trVo.setTrOrgnization(rs.getString("TR_ORGNIZATION"));
				trVo.setTrForm(rs.getString("TR_FORM"));
				trVo.setTrManager(rs.getString("TR_MANAGER"));
				trVo.setTrTeacher(rs.getString("TR_TEACHER"));
				trVo.setTrCourse(rs.getString("TR_COURSE"));
				trVo.setTrState(rs.getString("TR_STATE"));

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

	//在培训报名时，用户输入培训编号后，后台查询该培训是否还可以报名。
	public TrainRecordVO getNengBaoMing(Long id) {
		super.openDBConnection();
		ResultSet rs = null;
		try {
			String sql = "select * from train_record where tr_state='报名中' and tr_id = "
					+ id + "";
			rs = super.dbStatement.executeQuery(sql); // 执行sql语句
			TrainRecordVO trVo = null;
			if (rs.next()) {

				trVo = new TrainRecordVO();
				TrainClassDAO tcDao = new TrainClassDAO();
				TrainClassVO tcVo = new TrainClassVO();
				tcVo.setTcId(rs.getLong("TC_ID"));
				tcVo = tcDao.get(rs.getLong("TC_ID"));
				trVo.setTrId(id);
				trVo.setTrainClass(tcVo);
				trVo.setTrName(rs.getString("TR_NAME"));
				trVo.setTrCharacter(rs.getString("TR_CHARACTER"));
				trVo.setStartTime(rs.getDate("START_TIME"));
				trVo.setEndTime(rs.getDate("END_TIME"));
				trVo.setTrCoursetime(null);
				trVo.setTrFee(Long.valueOf(rs.getString("TR_FEE")));
				trVo.setTrPlace(rs.getString("TR_PLACE"));
				trVo.setTrOrgnization(rs.getString("TR_ORGNIZATION"));
				trVo.setTrForm(rs.getString("TR_FORM"));
				trVo.setTrManager(rs.getString("TR_MANAGER"));
				trVo.setTrTeacher(rs.getString("TR_TEACHER"));
				trVo.setTrCourse(rs.getString("TR_COURSE"));
				trVo.setTrState(rs.getString("TR_STATE"));

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

	//返回所有可以报名的培训记录
	/*public List getByState()
	 {
	 List trainRecordList = new ArrayList();
	 String psql = "select * from train_record where tr_state = '报名中' ";
	 super.openDBConnection();
	 try
	 {
	 super.dbResultSet = super.dbStatement.executeQuery(psql);
	 while(super.dbResultSet.next())
	 {
	 TrainRecordVO trVo = new TrainRecordVO();
	 
	 tcVo.setTcId(dbResultSet.getLong("tc_id"));
	 tcVo.setTcName(dbResultSet.getString("tc_name"));
	 tcVo.setTcTag(dbResultSet.getString("tc_tag"));
	 tcVo.setTcExtend("");
	 
	 trainRecordList.add(trVo);
	 }
	 }
	 catch(SQLException e)
	 {
	 e.printStackTrace();
	 }
	 super.closeDBConnection();
	 return trainRecordList;
	 }*/

	//	获得培训类别信息上下列表翻页---------参数信息为 sql语句中的部分，每页显示记录数，当前页码
	public PageHelp getTrainRecordList(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;

			//			tc_extend字段用于标识该条记录是否可用。为1则可用。为0不可用。
			//在添加时，初始设为1，即可用。修改时，不能修改该字段。只有在删除时，将该字段设置为0。即不可用。
			//显示列表时，只显示可用（tr_extend='1')的记录
			sql = "select count(*) listSize from train_record where tr_extend='1'";
			if (!"".equals(condition) || !"null".equals(condition))
				sql = "select count(*) listSize from train_record where tr_extend='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 { //找到要显示的记录
			//db_conn.ConnectDB();
			super.openDBConnection();
			ResultSet rs = null;
			int startNum = (currentPage - 1) * pageSize + 1;//由于数据库中没有第0条记录所以要进行+1修正
			int endNum = currentPage * pageSize + 1;

			//			tc_extend字段用于标识该条记录是否可用。为1则可用。为0不可用。
			//在添加时，初始设为1，即可用。修改时，不能修改该字段。只有在删除时，将该字段设置为0。即不可用。
			//显示列表时，只显示可用（tr_extend='1')的记录
			sql = "select * from (select a.* ,rownum rc from(select * from train_record where tr_extend='1' 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 train_record where tr_extend='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()) {
				TrainRecordVO trVo = new TrainRecordVO();
				TrainClassDAO tcDao = new TrainClassDAO();
				TrainClassVO tcVo = new TrainClassVO();
				tcVo = tcDao.get(rs.getLong("TC_ID"));

				trVo.setTrId(rs.getLong("TR_ID"));
				trVo.setTrainClass(tcVo);
				trVo.setTrName(rs.getString("TR_NAME"));
				trVo.setTrCharacter(rs.getString("TR_CHARACTER"));
				trVo.setStartTime((rs.getDate("START_TIME")));
				trVo.setEndTime((rs.getDate("END_TIME")));
				trVo.setTrCoursetime(null);
				trVo.setTrFee(Long.valueOf(rs.getString("TR_FEE")));
				trVo.setTrPlace(rs.getString("TR_PLACE"));
				trVo.setTrOrgnization(rs.getString("TR_ORGNIZATION"));
				trVo.setTrForm(rs.getString("TR_FORM"));
				trVo.setTrManager(rs.getString("TR_MANAGER"));
				trVo.setTrTeacher(rs.getString("TR_TEACHER"));
				trVo.setTrCourse(rs.getString("TR_COURSE"));
				trVo.setTrState(rs.getString("TR_STATE"));

				showlist.add(trVo);
			}

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

}