deptdao.java

实现统一的人员日志管理系统管理后台

package com.tb.log.model.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import com.tb.log.factory.OracleDAOFactory;
import com.tb.log.model.dao.idao.IDeptDAO;
import com.tb.log.model.po.Dept;
import com.tb.log.system.SystemException;
import com.tb.log.util.pages.PageBean;

public class DeptDAO implements IDeptDAO {
	
	
	private int totalResults = 0;
	private static final int pageSize = 5;

	private static final String SAVE_DEPT = "insert into t_department values(scq_dept_id.nextval,?,?,?,?,to_date(?,'YYYY-MM-DD'),?,?)";
	private static final String UPDATE_DEPT = "update t_department set dept_code=?,dept_name=?,supper_id=?,dept_grade=?,"
											+ "dept_state=?,dept_desc=? where dept_id=?";
	private static final String REMOVE_DEPT = "delete from t_department where dept_id = ?";
	private static final String FIND_ALL_DEPT = "select * from (select row_.*, rownum rownum_ from (select * from t_department ) row_ where rownum <=?) where rownum_ >?";
	private static final String FIND_ALL = "select * from t_department";
	private static final String FIND_BY_ID = "select * from t_department where dept_id =?";
	private static final String GET_PAGES_ALL = "select count(*) from (select row_.*, rownum rownum_ from (select * from t_department ) row_ where rownum <=?) where rownum_ >?";
	private static final String GET_TOTALRESULTS = "select count(*) from t_department ";
	private static final String FIND_BY_GRADE = "select distinct dept_name ,dept_id from t_department where dept_grade=?";
	private static final String FIND_SUBID_BY_ID = "select dept_id from t_department where supper_id=?";
	public void save(Object obj) throws SystemException {
		Dept dept = (Dept) obj;
		
		String sql = "insert into t_department values(scq_dept_id.nextval,"+dept.getDept_code()+","+dept.getDept_name()+","+dept.getSupper_id()+","+dept.getDept_grade()+",to_date("+ dept.getCreat_date()+",'YYYY-MM-DD'),"+dept.getDept_desc()+","+dept.getDept_state()+")";
		System.out.println(sql);
		try {
			Connection conn = OracleDAOFactory.getConnection();
			PreparedStatement psm = conn.prepareStatement(SAVE_DEPT);
			psm.setInt(1, dept.getDept_code());
			psm.setString(2, dept.getDept_name());
			psm.setString(3, dept.getSupper_id());
			psm.setInt(4, dept.getDept_grade());
			psm.setString(5, dept.getCreat_date());
			psm.setString(6, dept.getDept_desc());
			psm.setString(7, dept.getDept_state());
			psm.executeUpdate();
		} catch (SQLException e) {
			String errStr = "SQLException in DeptDAO:save()--" + e;
			throw new SystemException(errStr);
		}
	}

	public void update(Object obj) throws SystemException {
		Dept dept = (Dept) obj;

		
		try {
			Connection conn = OracleDAOFactory.getConnection();
			PreparedStatement psm = conn.prepareStatement(UPDATE_DEPT);
			psm.setInt(1, dept.getDept_code());
			psm.setString(2, dept.getDept_name());
			psm.setString(3, dept.getSupper_id());
			psm.setInt(4, dept.getDept_grade());
			psm.setString(5, dept.getDept_state());
			psm.setString(6, dept.getDept_desc());
			psm.setInt(7, dept.getDept_id());
			psm.executeUpdate();

		} catch (SQLException e) {
			String errStr = "SQLException in DeptDAO:update()-" + e;
			// System.err.println(errorStr);
			throw new SystemException(errStr);
		}
	}

	/**
	 * 删除用户
	 * 
	 * @param dept
	 */
	public void remove(Object obj) throws SystemException {
	//	System.out.println("DeptDAO:remove()---");
		Dept dept = (Dept) obj;
		try {
			Connection conn = OracleDAOFactory.getConnection();
			//conn.setAutoCommit(false);
			PreparedStatement psm = conn.prepareStatement(REMOVE_DEPT);
			psm.setInt(1, dept.getDept_id());
			psm.executeUpdate();
		} catch (SQLException e) {
			String errStr = "SQLExeption in DeptDAO:remove--" + e;
			throw new SystemException(errStr);
		}
	}
	
	public void remove(List deptList) throws SystemException{
		Dept dept = null;
		if(deptList.isEmpty()){
			for (Iterator iterator = deptList.iterator(); iterator.hasNext();) {
				dept = (Dept) iterator.next();
				
				
			}
		}
	}

	public Dept findById(String id) throws SystemException {
		Dept dept = null;
		try {
			Connection conn = OracleDAOFactory.getConnection();
			;
			PreparedStatement psm = conn.prepareStatement(FIND_BY_ID);
			psm.setString(1, id);
			ResultSet rs = psm.executeQuery();

			while (rs.next()) {
				dept = new Dept(rs.getInt(1), rs.getInt(2), rs.getString(3), rs
						.getString("supper_id"), rs.getInt(5), rs.getString(6),
						rs.getString(8), rs.getString(7));
			}

		} catch (SQLException e) {
			String errStr = "SQLException in DeptDAO:fidndbyId--" + e;
			throw new SystemException(errStr);
		}
		return dept;

	}
	
	

	public List findAll(int currentPage) throws SystemException {
		List deptList = new ArrayList();
		Dept dept = null;
		try {
			Connection conn = OracleDAOFactory.getConnection();

			PreparedStatement psm = conn.prepareStatement(FIND_ALL_DEPT);
			psm.setInt(1, pageSize * currentPage);
			psm.setInt(2, pageSize * (currentPage - 1));
			ResultSet rs = psm.executeQuery();
			while (rs.next()) {
				dept = new Dept();
				dept.setDept_id(rs.getInt("dept_id"));
				dept.setDept_code(rs.getInt("dept_code"));
				dept.setDept_name(rs.getString("dept_name"));
				dept.setDept_grade(rs.getInt("dept_grade"));
				deptList.add(dept);
			}
		} catch (SQLException e) {
			String errStr = "SQLException in DeptDAO:findAll()--" + e;
			throw new SystemException(errStr);
		}
		return deptList;
	}
	
	public List findAll() throws SystemException {
		List deptList = new ArrayList();
		Dept dept = null;
		try {
			Connection conn = OracleDAOFactory.getConnection();

			PreparedStatement psm = conn.prepareStatement(FIND_ALL);
			ResultSet rs = psm.executeQuery();
			while (rs.next()) {
				dept = new Dept();
				dept.setDept_id(rs.getInt("dept_id"));
				dept.setDept_code(rs.getInt("dept_code"));
				dept.setDept_name(rs.getString("dept_name"));
				dept.setDept_grade(rs.getInt("dept_grade"));
				deptList.add(dept);
			}
		} catch (SQLException e) {
			String errStr = "SQLException in DeptDAO:findAll()--" + e;
			throw new SystemException(errStr);
		}
		return deptList;
	}

	public List findByCondition(String consql, int currentPage)
			throws SystemException {
		List deptlist = new ArrayList();
		Dept dept = null;
		try {
			Connection conn = OracleDAOFactory.getConnection();
			PreparedStatement psm = conn.prepareStatement(consql);
			psm.setInt(1, pageSize*currentPage);
			psm.setInt(2, pageSize*(currentPage-1));
		   ResultSet rs = psm.executeQuery();
		   while(rs.next()){
			   dept = new Dept(rs.getInt(1), rs.getInt(2), rs.getString(3),
						rs.getString(4), rs.getInt(5), rs.getString(6),rs.getString(7),
						rs.getString(8));
			  deptlist.add(dept);
		   }
		} catch (SQLException e) {
			String errStr = "SQLException in DeptDAO:findAll()--" + e;
			throw new SystemException(errStr);
		}
		return deptlist;
	}

	public List findByGrade(String deptGrade) throws SystemException {

		List deptlist = new ArrayList();

		try {
			Connection conn = OracleDAOFactory.getConnection();

			PreparedStatement psm = conn.prepareStatement(FIND_BY_GRADE);
			psm.setString(1, deptGrade);
			ResultSet rs = psm.executeQuery();
			Dept dept = null;
			while (rs.next()) {
				dept = new Dept();
				dept.setDept_name(rs.getString(1));
				dept.setDept_id(rs.getInt(2));
				deptlist.add(dept);
			}

		} catch (Exception e) {
			String errStr = "SQLException in DeptDAO:findAll()--" + e;
			throw new SystemException(errStr);
		}
		return deptlist;
	}
	
	public List findIdByGrade(String deptGrade) throws SystemException {

		List deptlist = new ArrayList();

		try {
			Connection conn = OracleDAOFactory.getConnection();

			PreparedStatement psm = conn.prepareStatement(FIND_BY_GRADE);
			psm.setString(1, deptGrade);
			ResultSet rs = psm.executeQuery();
			while (rs.next()) {
				deptlist.add(rs.getString(2));
			}

		} catch (Exception e) {
			String errStr = "SQLException in DeptDAO:findAll()--" + e;
			throw new SystemException(errStr);
		}
		return deptlist;
	}
	
	public PageBean getTotalResults() throws SystemException {

		Connection conn = OracleDAOFactory.getConnection();
		try {
			PreparedStatement psm = conn.prepareStatement(GET_TOTALRESULTS);
			ResultSet rs = psm.executeQuery();
			if (rs.next()) {
				totalResults = rs.getInt(1);
			}
		} catch (SQLException e) {
			String errStr = "SQLException in DeptDAO:findAll()--" + e;
			throw new SystemException(errStr);
		}
		return new PageBean(totalResults, pageSize);
	}

	public PageBean getTotalResultsByCondition(String sql)
			throws SystemException {
		// String sql = "select count(*) from t_department ";
		Connection conn = OracleDAOFactory.getConnection();
		try {
			PreparedStatement psm = conn.prepareStatement(sql);

			ResultSet rs = psm.executeQuery();
			if (rs.next()) {
				totalResults = rs.getInt(1);
			}
		} catch (SQLException e) {
			String errStr = "SQLException in DeptDAO:findAll()--" + e;
			throw new SystemException(errStr);
		}
		return new PageBean(totalResults, pageSize);
	}
	
	
	public List findSubidById(int id) throws SystemException{
		List idList = new ArrayList();
		Connection conn = OracleDAOFactory.getConnection();
		try {
			PreparedStatement psm = conn.prepareStatement(FIND_SUBID_BY_ID);
			psm.setInt(1, id);
			ResultSet rs = psm.executeQuery();
			while(rs.next()){
				idList.add(rs.getString(1));
			}
		} catch (SQLException e) {
				String errStr = "SQLException in DeptDAO:findAll()--" + e;
				throw new SystemException(errStr);
		}
		return idList; 
	}
	
	public static void main(String[] args) {
		Dept dept = new Dept();
		dept.setDept_id(56);
		try {
			System.out.println(new DeptDAO().findIdByGrade("3"));
		} catch (SystemException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
}