departmentemployeedao.java

基于j2ee的物流软件

package com.wuliu.dao;

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.wuliu.DBConnection.DBConnection;
import com.wuliu.entity.DepartmentEmployee;
import com.wuliu.entity.DepartmentInfo;
import com.wuliu.entity.EmployeeInfo;

/**
 * @author 刘海鹏
 */
public class DepartmentEmployeeDAO {
	private Connection conn = null;
	private PreparedStatement ps = null;
	private DBConnection dao = null;

	public DepartmentEmployeeDAO() {
       this.dao = new DBConnection();
	}
	
	//查询所有的部门信息
	public List<DepartmentEmployee> selectDepartmentEmployeePage() {
		List<DepartmentEmployee> list = new ArrayList<DepartmentEmployee>();
		DepartmentEmployee departmentEmployee = null;
		this.conn = this.dao.getConnection();
		try {
			this.ps = this.conn
					.prepareStatement("select d.DepartmentId, DepartmentName,e.EmployeeName,f.EmployeeName from DepartmentInfoTable as d left outer join EmployeeInfoTable as e on(d.DepartmentId = e.DepartmentId) left outer join EmployeeInfoTable as f on(e.DepartmentId = f.DepartmentId) where e.JobsId = 'BMZZ' and f.JobsId = 'BMJL'");
			ResultSet rs = this.ps.executeQuery();
			while (rs.next()) {
				String departmentId = rs.getString(1);
				String departmentName = rs.getString(2);
				String bmzz = rs.getString(3);
				String bmjl = rs.getString(4);
				departmentEmployee = new DepartmentEmployee(departmentId, departmentName, bmzz, bmjl);
				list.add(departmentEmployee);
			}
			this.dao.closeResultSet(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			this.dao.closePrepStmt(ps);
			try {
				this.conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}
	
	//查询指定编号的部门信息
	public DepartmentEmployee selectDepartmentEmployeeById(String departmentId){
		DepartmentEmployee de = null;
		this.conn = this.dao.getConnection();
		try {
			this.ps = this.conn.prepareStatement("select d.DepartmentId, DepartmentName,e.EmployeeName,f.EmployeeName from DepartmentInfoTable as d left outer join EmployeeInfoTable as e on(d.DepartmentId = e.DepartmentId) left outer join EmployeeInfoTable as f on(e.DepartmentId = f.DepartmentId) where e.JobsId = 'BMZZ' and f.JobsId = 'BMJL' and d.departmentId =?");
			this.ps.setString(1, departmentId);
			ResultSet rs = this.ps.executeQuery();
			while(rs.next()){
				String id = rs.getString(1);
				String name = rs.getString(2);
				String bmzz = rs.getString(3);
				String bmjl = rs.getString(4);
				de = new DepartmentEmployee(id, name, bmzz, bmjl);
			}
			this.dao.closeResultSet(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				this.conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return de;
	}

	//删除一个部门
	public void deleteDepartmentEmployee(String departmentId){
		this.conn = this.dao.getConnection();
		
		try {
			this.ps = this.conn.prepareStatement("delete from DepartmentInfoTable where DepartmentId=?");
			this.ps.setString(1, departmentId);
			this.ps.executeUpdate();
			this.dao.closePrepStmt(ps);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				this.conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

//	//修改部门信息
	public void updateDepartmentEmployeeInfo(EmployeeInfo employeeInfo){
		this.conn = this.dao.getConnection();
		try {
			this.ps = this.conn.prepareStatement("update employeeInfoTable set DepartmentId=?, JobsId=? where EmployeeName=?");
			this.ps.setString(1, employeeInfo.getDepartmentId());
			this.ps.setString(2, employeeInfo.getJobsId());
			this.ps.setString(3, employeeInfo.getEmployeeName());
			this.ps.executeUpdate();
			this.dao.closePrepStmt(ps);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			this.dao.closeConnection(conn);
		}
	}
	
	//删除这个部门的员工
	public void deleteEmployee(String departmentId){
		this.conn = this.dao.getConnection();
		try {
			this.ps = this.conn.prepareStatement("delete from employeeInfoTable where DepartmentId=?");
			this.ps.setString(1, departmentId);
			this.ps.executeUpdate();
			this.dao.closePrepStmt(ps);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			this.dao.closeConnection(conn);
		}
	}
	
	//添加一个部门
	public void insertDepartment(DepartmentInfo departmentInfo){
		this.conn = this.dao.getConnection();
		try {
			this.ps = this.conn.prepareStatement("insert into departmentInfoTable(departmentId,departmentName) values(?,?)");
			this.ps.setString(1, departmentInfo.getDepartmentId());
			this.ps.setString(2, departmentInfo.getDepartmentName());
			this.ps.executeUpdate();
			this.ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			this.dao.closeConnection(conn);
		}
	}
	
	public static void main(String[] args){
//		DepartmentEmployeeDAO d = new DepartmentEmployeeDAO();
//		DepartmentEmployee de;
//		d.updateDepartmentEmployeeInfo(new EmployeeInfo("黄老协",0,"","","","QQ","wwww"));
//		List<DepartmentEmployee> list = new ArrayList<DepartmentEmployee>();
//		list = d.selectDepartmentEmployeePage();
//		Iterator<DepartmentEmployee> it = list.iterator();
//		while(it.hasNext()){
//			de = it.next();
//			System.out.println(de.getDepartmentName()+" "+de.getBmjl());
//		}
//		de = d.selectDepartmentEmployeeById("CJ");
//		System.out.println(de.getBmjl()+" "+de.getDepartmentName());
//		d.deleteDepartmentEmployee("CJ");
	}
}