employeedao.java

一个jsp的oa系统,里面有很多亮点学习!

package com.oa.lp.dao;

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

import com.oa.lp.model.Employee;
import com.oa.lp.model.Menu;
import com.oa.lp.model.Power;
import com.oa.lp.model.Role;
import com.oa.lp.util.DTOPopulator;
import com.oa.lp.util.PageList;
import com.oa.lp.util.Pages;

public class EmployeeDAO {
	private Connection conn;

	public Connection getConn() {
		return conn;
	}

	public void setConn(Connection conn) {
		this.conn = conn;
	}
	/**
	 * 新增一个员工
	 * @throws SQLException 
	 */
	public void addEmployee(Employee employee) throws SQLException{
		String sql = "insert into EMPLOYEE(USER_NAME,USER_PWD,EMP_NAME,SEX," +
				"AGE,BIRTHDAY,SCHOOL_AGE,SEPCIALITY,SCHOOL,EMAIL,QQ,TEL," +
				"MOBILE_TEL,ADDRESS,ID_CARD,WORK_DATE,DIMISSION_DATE,FLAG)" +
				"values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		
		PreparedStatement pstmt = conn.prepareStatement(sql);
		
		
		pstmt.setString(1,employee.getUserName());
		pstmt.setString(2,employee.getUserPwd());
		pstmt.setString(3,employee.getEmpName());
		pstmt.setInt(4,employee.getSex());
		pstmt.setInt(5,employee.getAge());
		pstmt.setString(6,employee.getBirthDay());
		pstmt.setString(7,employee.getSchoolAge());
		pstmt.setString(8,employee.getSepciality());
		pstmt.setString(9,employee.getSchool());
		pstmt.setString(10,employee.getEmail());
		pstmt.setString(11,employee.getQq());
		pstmt.setString(12,employee.getTel());
		pstmt.setString(13,employee.getMobileTel());
		pstmt.setString(14,employee.getAddress());
		pstmt.setString(15,employee.getIdCard());
		pstmt.setDate(16,employee.getWorkDate());
		pstmt.setDate(17,employee.getDimissionDate());
		pstmt.setInt(18,employee.getFlag());
		
		pstmt.executeUpdate();
		pstmt.close();
	}
	/**
	 * 通过ID查找员工
	 * @throws Exception 
	 */
	public Employee getById(int empId) throws Exception{
		
		Employee employee = null;
		String sql = "select * from EMPLOYEE where EMP_ID=?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1,empId);
		ResultSet rs = pstmt.executeQuery();
		List list = DTOPopulator.populate(rs, Employee.class);
		//取集合中的第一个元素返回
		if(list.size()>0){
			employee=(Employee)list.get(0);
		}
		rs.close();
		pstmt.close();
		return employee ;
	}
	
	public List getEmps() throws Exception{ 
		List list = null; 
		String sql = "select * from EMPLOYEE ";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		//将结果集中的每一行记录封装成一个对象,再放进集合返回
		list = DTOPopulator.populate(rs, Employee.class);
		rs.close();
		pstmt.close();
		return list;
	}
	/**
	 * 更新员工信息
	 * @throws SQLException 
	 */
	public void updateEmployee(Employee employee) throws SQLException{
		String sql = "update EMPLOYEE set USER_NAME=?,USER_PWD=?," +
				"EMP_NAME=?,SEX=?,AGE=?,BIRTHDAY=?,SCHOOL_AGE=?,SEPCIALITY=?," +
				"SCHOOL=?,EMAIL=?,QQ=?,TEL=?,MOBILE_TEL=?,ADDRESS=?,ID_CARD=?," +
				"WORK_DATE=?,DIMISSION_DATE=?,FLAG=? where EMP_ID=?";
		
		PreparedStatement pstmt = conn.prepareStatement(sql);

		pstmt.setString(1,employee.getUserName());
		pstmt.setString(2,employee.getUserPwd());
		pstmt.setString(3,employee.getEmpName());
		pstmt.setInt(4,employee.getSex());
		pstmt.setInt(5,employee.getAge());
		pstmt.setString(6,employee.getBirthDay());
		pstmt.setString(7,employee.getSchoolAge());
		pstmt.setString(8,employee.getSepciality());
		pstmt.setString(9,employee.getSchool());
		pstmt.setString(10,employee.getEmail());
		pstmt.setString(11,employee.getQq());
		pstmt.setString(12,employee.getTel());
		pstmt.setString(13,employee.getMobileTel());
		pstmt.setString(14,employee.getAddress());
		pstmt.setString(15,employee.getIdCard());
		pstmt.setDate(16,employee.getWorkDate());
		pstmt.setDate(17,employee.getDimissionDate());
		pstmt.setInt(18,employee.getFlag());
		pstmt.setInt(19,employee.getEmpId());
		pstmt.executeUpdate();
		pstmt.close();
	}
	
	/**
	 * 员工分页列表
	 * @throws Exception 
	 */
	public PageList listAllEmployee(Pages page) throws Exception{
		PageList pageList = new PageList();
		//总录数
		page.setAllRecord(countAllEmployee());
		page.doPage();
		StringBuffer sql = new StringBuffer();
		sql.append("select * from (");
		sql.append("select top "+page.getPageSize()+" * from (");
		sql.append("select top "+(page.getPageSize()*page.getCPage())+" * from ");
		sql.append("EMPLOYEE order by EMP_ID desc) t2 order by EMP_ID asc) t3 order by EMP_ID desc");
		PreparedStatement pstmt = conn.prepareStatement(sql.toString());
		ResultSet rs = pstmt.executeQuery();
		//将结果集中的每一行记录封装成一个对象,再放进集合返回
		List list = DTOPopulator.populate(rs, Employee.class);
		
		pageList.setPage(page);
		pageList.setObjectList(list);
		pstmt.close();
		return pageList;
	}
	/**
	 * 不分页的员工列表
	 * @throws Exception 
	 */
	
	public List listAllEmployee() throws Exception{
		List list=null;
		String sql="select *from EMPLOYEE order by EMP_ID desc";
		PreparedStatement pstmt = conn.prepareStatement(sql.toString());
		ResultSet rs = pstmt.executeQuery();
		//将结果集中的每一行记录封装成一个对象,再放进集合返回
		list=DTOPopulator.populate(rs, Employee.class);
		rs.close();
		pstmt.close();
		return list;
	}
	/**
	 * 员工总记录数
	 * @return
	 * @throws SQLException 
	 */
	public int countAllEmployee() throws SQLException{
		int count = 0;
		String sql = "select count(*) from EMPLOYEE";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery(); 
		if(rs.next()){
			count = rs.getInt(1);
		}
		pstmt.close();
		return count;
	}
	
	/**
	 * 通过用户名获得员工
	 * @throws Exception 
	 */
	public Employee getByUserName(String userName) throws Exception{
		Employee emp=null;
		List list=null;
		String sql = "select * from EMPLOYEE where USER_NAME=?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setString(1,userName);
		ResultSet rs = pstmt.executeQuery();
		list = DTOPopulator.populate(rs, Employee.class);
		if(list.size()>0){
			emp = (Employee)list.get(0);
		}
		rs.close();
		pstmt.close();
		
		return emp;
	}
	
	/**
	 * 通过员工ID查找员工所有权限
	 * @throws Exception 
	 */
	public List getPowersByEmpId(int empId) throws Exception{
		List list = new ArrayList();
		CallableStatement cstmt = conn.prepareCall("{call PROC_GETPOWERS_BY_EMPID(?)}");
		cstmt.setInt(1,empId);
		ResultSet rs = cstmt.executeQuery();
		list = DTOPopulator.populate(rs, Power.class);
		rs.close();
		cstmt.close();
		
		return list;
	}
	
	/**
	 * 通过员工ID查找员工所有菜单
	 * @throws Exception 
	 */
	public List getMenusByEmpId(int empId) throws Exception{
		List list=null;
		CallableStatement cstmt = conn.prepareCall("{call PROC_GETMENUS_BY_EMPID(?)}");
		cstmt.setInt(1,empId);
		ResultSet rs = cstmt.executeQuery();
		list = DTOPopulator.populate(rs, Menu.class);
		rs.close();
		cstmt.close();
		return list;
	}
	
	/**
	 * 删除员工
	 */
	public boolean delEmployee(int empId) throws SQLException{
		boolean flag=true;
		//是否给员工分配权限
		String sql = "select count(*) from SYS_EMPLOYEE_POWER where EMP_ID=?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1,empId);
		ResultSet rs = pstmt.executeQuery();
		if(rs.next()){
			if(rs.getInt(1)>0){
				flag = false;
			}
		}
		rs.close();
		pstmt.close();
		//是否给员工分配角色
		sql = "select count(*) from SYS_EMPLOYEE_ROLE where EMP_ID=?";
	    pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1,empId);
		rs = pstmt.executeQuery();
		if(rs.next()){
			if(rs.getInt(1)>0){
				flag = false;
			}
		}
		rs.close();
		pstmt.close();
		if(flag){
			//删除
			sql = "delete from EMPLOYEE where EMP_ID=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,empId);
			pstmt.executeUpdate();
			pstmt.close();
		}
		return flag;
	}
}