certrecorddao.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.CertRecordVO;
import com.galaxy.vo.DeptInfoVO;
import com.galaxy.vo.LevelInfoVO;
import com.galaxy.vo.UserInfoVO;

public class CertRecordDAO extends ConnectDB implements DaoInterface {

	public int addObject(Object ob) {
		
		int result = 0;
		
		CertRecordVO CRVo = new CertRecordVO();
		CRVo=(CertRecordVO)ob;
		String sql = "insert into cert_record values (seq.nextval,?,?,?,?,?,?)";
		
		super.openDBConnection();
		try {
			PreparedStatement psm = dbConnection.prepareStatement(sql);
			psm.setLong(1, CRVo.getUserInfo().getUiId());
			psm.setString(2,CRVo.getCrName());
			psm.setDate(3, (Date) CRVo.getCrDate());
			psm.setLong(4, CRVo.getCrNumber());
			psm.setString(5, CRVo.getCrClass());
			psm.setString(6, CRVo.getCrExtend());
			
			result = psm.executeUpdate();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		super.closeDBConnection();
		return result;
	}

	public int deleteObject(Object cond) {
		int result = 0;

		String sql = "delete from cert_record where cr_id in (" + ((String)cond) + ")";
		super.openDBConnection();
		try {

			super.dbStatement.executeUpdate(sql);

		}
		catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		super.closeDBConnection();
		return 1;
	}

	public List queryByCondition(Object cond) {
		List certlist = new ArrayList();
		super.openDBConnection();
		
		String sql = "select * from  cert_record c join user_info u on c.ui_id=u.ui_id where 1=1" 
				+ ((String)cond)  + " order by cr_id";
		System.out.println(sql);

		try
		{
			super.dbResultSet = super.dbStatement.executeQuery(sql);
			while(dbResultSet.next()){
				CertRecordVO certVO = new CertRecordVO();
				
				certVO.setCrId(dbResultSet.getLong("cr_id"));
				certVO.setCrName(dbResultSet.getString("cr_name"));
				certVO.setCrDate(dbResultSet.getDate("cr_date"));
				certVO.setCrNumber(dbResultSet.getLong("cr_number"));
				certVO.setCrClass(dbResultSet.getString("cr_class"));
				certVO.setCrExtend(dbResultSet.getString("cr_extend"));
				
				//根据外键在用户信息表中查询用户的其他信息
				UserInfoVO uiVO = new UserInfoVO();
				uiVO.setUiId(dbResultSet.getLong("ui_id"));
				uiVO.setUiPersonid(dbResultSet.getLong("ui_personid"));
				uiVO.setUiRealname(dbResultSet.getString("ui_realname"));
				
				certVO.setUserInfo(uiVO);
				
				certlist.add(certVO);
			
			}
			
		}
		catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		super.closeDBConnection();
		return certlist;
	}

	public Object readObject(Object cond) {
		CertRecordVO certVO = new CertRecordVO();
		super.openDBConnection();
		
		String sql = "select * from  cert_record c join user_info u on c.ui_id=u.ui_id where cr_id = " 
				+ ((String)cond);
		System.out.println(sql);

		try
		{
			super.dbResultSet = super.dbStatement.executeQuery(sql);
			while(dbResultSet.next())
			{
				certVO.setCrId(dbResultSet.getLong("cr_id"));
				certVO.setCrName(dbResultSet.getString("cr_name"));
				certVO.setCrDate(dbResultSet.getDate("cr_date"));
				certVO.setCrNumber(dbResultSet.getLong("cr_number"));
				certVO.setCrClass(dbResultSet.getString("cr_class"));
				certVO.setCrExtend(dbResultSet.getString("cr_extend"));
				
				//根据外键在用户信息表中查询用户的其他信息
				UserInfoVO uiVO = new UserInfoVO();
				uiVO.setUiId(dbResultSet.getLong("ui_id"));
				uiVO.setUiPersonid(dbResultSet.getLong("ui_personid"));
				uiVO.setUiRealname(dbResultSet.getString("ui_realname"));
				uiVO.setUiSex(dbResultSet.getString("ui_sex"));
				uiVO.setUiZhiwu(dbResultSet.getString("ui_zhiwu"));
				
				certVO.setUserInfo(uiVO);
			}
			
		}
		catch (SQLException e) {
			
			e.printStackTrace();
		}
		
		super.closeDBConnection();
		return certVO;
	}

	public int updateObject(Object ob) {
		int result = 0;
		
		CertRecordVO crvo = new CertRecordVO();
		crvo=(CertRecordVO)ob;
		String sql = "update cert_record set cr_name=?, cr_date=?, " +
		            " cr_number=?, cr_class=?, cr_extend=? where cr_id=?";
		System.out.println(sql);
		super.openDBConnection();
		try {
			PreparedStatement psm = dbConnection.prepareStatement(sql);
			//psm.setLong(1, CRVo.getUserInfo().getUiId());
			psm.setString(1,crvo.getCrName());
			psm.setDate(2, (Date) crvo.getCrDate());
			psm.setLong(3, crvo.getCrNumber());
			psm.setString(4, crvo.getCrClass());
			psm.setString(5, crvo.getCrExtend());
			psm.setLong(6, crvo.getCrId());
			
			result = psm.executeUpdate();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		super.closeDBConnection();
		return result;
	}

	public PageHelp getList(String condition, int pageSize, int currentPage) 
	{
		int listSize=0;
		List certlist=new ArrayList();//存放要显示到页面上的部分结果
		PageHelp pageHelp=new PageHelp();	
		String sql ="";
		try 
		{ //统计记录总数
			super.openDBConnection();
			ResultSet rs = null;
		
			sql="select count(*) listSize from cert_record c join user_info u on c.ui_id=u.ui_id ";
			if(!"".equals(condition)&&condition!=null)
			{
				sql = "select count(*) listSize from cert_record c join user_info u on c.ui_id=u.ui_id  where 1=1 "+condition;	
			}
			sql=sql+" order by c.cr_id";
			//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 cert_record c join user_info u on c.ui_id=u.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 cert_record c join user_info u on c.ui_id=u.ui_id   where 1=1 "+condition+" ) a where rownum<"+endNum+") b where rc >="+startNum+"";	
			sql=sql+" order by di_id";
			System.out.println("sqllist------"+sql);			
			pageHelp.setSqlstr(sql);
			super.dbResultSet = super.dbStatement.executeQuery(sql);   //执行sql语句
			System.out.println("list------");
			// 执行sql语句
			
			while(super.dbResultSet.next())
			{
				CertRecordVO certVO = new CertRecordVO();
				
				certVO.setCrId(dbResultSet.getLong("cr_id"));
				certVO.setCrName(dbResultSet.getString("cr_name"));
				certVO.setCrDate(dbResultSet.getDate("cr_date"));
				certVO.setCrNumber(dbResultSet.getLong("cr_number"));
				certVO.setCrClass(dbResultSet.getString("cr_class"));
				certVO.setCrExtend(dbResultSet.getString("cr_extend"));
				
				//根据外键在用户信息表中查询用户的其他信息
				UserInfoVO uiVO = new UserInfoVO();
				uiVO.setUiId(dbResultSet.getLong("QCSJ_C005000"));
				uiVO.setUiPersonid(dbResultSet.getLong("ui_personid"));
				uiVO.setUiRealname(dbResultSet.getString("ui_realname"));
				
				certVO.setUserInfo(uiVO);
				
				certlist.add(certVO);
			
			}
			System.out.println("list------"+certlist.size());
		} 
		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("TR_CertRecordServlet");//设置上一页，下一页，首页，末页的显示条
		pageHelp.setObjectlist(certlist);//将list对象存储起来
		return pageHelp;

	}
}