employeeinfodao.java

基于j2ee的物流软件

package com.wuliu.dao;

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

import com.wuliu.DBConnection.DBConnection;
import com.wuliu.entity.EmployeeInfo;

public class EmployeeInfoDAO
{

	/**
	 * @author 崔斌 － 职员信息表
	 */
	public void insertEmployeeInfo(EmployeeInfo employee)
	{
		DBConnection db = new DBConnection();
		Connection cn = db.getConnection();
		try
		{
			PreparedStatement ps = cn.prepareStatement("insert into "
					+ "EmployeeInfoTable(EmployeeId,EmployeeName,"
					+ "EmployeeSex,EmployeeAge,EmployeeIdCard,"
					+ "EmployeeBirthDate,EmployeeDiploma,EmployeeAddr,"
					+ "EmployeePhone,EmployeeMobile,DepartmentId,JobsId,EmployeePhoto) "
					+ "values(?,?,?,?,?,?,?,?,?,?,?,?,?)");
			ps.setString(1, employee.getEmployeeId());
			ps.setString(2, employee.getEmployeeName());
			ps.setString(3, employee.getEmployeeSex());
			ps.setInt(4, employee.getEmployeeAge());
			ps.setString(5, employee.getEmployeeIdCard());
			ps.setString(6, employee.getEmployeeBirthDate());
			ps.setString(7, employee.getEmployeeDiploma());
			ps.setString(8, employee.getEmployeeAddr());
			ps.setString(9, employee.getEmployeePhone());
			ps.setString(10, employee.getEmployeeMobile());
			ps.setString(11, employee.getDepartmentId());
			ps.setString(12, employee.getJobsId());
			ps.setString(13, employee.getEmployeePhoto());//袁子鹏 2009.1.14 添加 
			ps.addBatch();
			ps.executeUpdate();

		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally
		{
			db.closeConnection(cn);
		}
	}

	/* 删除一个职工 */
	public void deleteEmployeeInfo(String EmployeeId)
	{
		DBConnection db = new DBConnection();
		Connection cn = db.getConnection();
		try
		{
			Statement st = cn.createStatement();
			st.executeUpdate("delete from EmployeeInfoTable where EmployeeId='"
					+ EmployeeId + "'");
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally
		{
			db.closeConnection(cn);
		}
	}

	/* 修改职工信息 */
	public void updateEmployeeInfo(String EmployeeId, String EmployeeName,
			String EmployeeSex, int EmployeeAge, String EmployeeIdCard,
			String EmployeeBirthDate, String EmployeeDiploma,
			String EmployeeAddr, String EmployeePhone, String EmployeeMobile,
			String DepartmentId, String JobsId)
	{
		DBConnection db = new DBConnection();
		Connection cn = db.getConnection();
		try
		{
			PreparedStatement ps = cn.prepareStatement("update "
					+ "EmployeeInfoTable set EmployeeName=?,"
					+ "EmployeeSex=?,EmployeeAge=?,EmployeeIdCard=?,"
					+ "EmployeeBirthDate=?,EmployeeDiploma=?,"
					+ "EmployeeAddr=?,EmployeePhone=?,EmployeeMobile=?,"
					+ "DepartmentId=?,JobsId=? where EmployeeId=?");
			ps.setString(1, EmployeeName);
			ps.setString(2, EmployeeSex);
			ps.setInt(3, EmployeeAge);
			ps.setString(4, EmployeeIdCard);
			ps.setString(5, EmployeeBirthDate);
			ps.setString(6, EmployeeDiploma);
			ps.setString(7, EmployeeAddr);
			ps.setString(8, EmployeePhone);
			ps.setString(9, EmployeeMobile);
			ps.setString(10, DepartmentId);
			ps.setString(11, JobsId);
			ps.setString(12, EmployeeId);	
			ps.addBatch();
			ps.executeUpdate();
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally
		{
			db.closeConnection(cn);
		}
	}

	/* 查找一位职工 */
	public EmployeeInfo selectEmployeeById(String EmployeeId){
		EmployeeInfo el = null;
		DBConnection db = new DBConnection();
		Connection cn = db.getConnection();
		try
		{
			Statement st = cn.createStatement();
			ResultSet rs = st.executeQuery("select * from EmployeeInfoTable where EmployeeId='"+EmployeeId+"'");
			if(rs.next()){
				int id = rs.getInt("EmployeeInfoTableId");
				String EmployeeName = rs.getString("EmployeeName");
				String EmployeeSex = rs.getString("EmployeeSex");
				int EmployeeAge = rs.getInt("EmployeeAge");
				String EmployeeIdCard = rs.getString("EmployeeIdCard");
				String EmployeeBirthDate = rs.getString("EmployeeBirthDate");
				String EmployeeDiploma = rs.getString("EmployeeDiploma");
				String EmployeeAddr = rs.getString("EmployeeAddr");
				String EmployeePhone = rs.getString("EmployeePhone");
				String EmployeeMobile = rs.getString("EmployeeMobile");
				String DepartmentId = rs.getString("DepartmentId");
				String JobsId = rs.getString("JobsId");
				String EmployeePhoto = rs.getString("EmployeePhoto");//袁子鹏 2009.1.14 添加 
				el = new EmployeeInfo(id,EmployeeId,EmployeeName,EmployeeSex,EmployeeAge,EmployeeIdCard,EmployeeBirthDate,EmployeeDiploma,EmployeeAddr,EmployeePhone,EmployeeMobile,DepartmentId,JobsId,EmployeePhoto);
			}
			
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally{
			db.closeConnection(cn);
		}
		return el;
	}
	/*查找全部员工*/
	public List<EmployeeInfo> selectEmployeeInfo(){
		List<EmployeeInfo> list = new ArrayList<EmployeeInfo>();
		DBConnection db = new DBConnection();
		Connection cn = db.getConnection();
		try
		{
			Statement st = cn.createStatement();
			ResultSet rs = st.executeQuery("select * from EmployeeInfoTable");
			while(rs.next()){
				int id = rs.getInt("EmployeeInfoTableId");
				String EmployeeId = rs.getString("EmployeeId");
				String EmployeeName = rs.getString("EmployeeName");
				String EmployeeSex = rs.getString("EmployeeSex");
				int EmployeeAge = rs.getInt("EmployeeAge");
				String EmployeeIdCard = rs.getString("EmployeeIdCard");
				String EmployeeBirthDate = rs.getString("EmployeeBirthDate");
				String EmployeeDiploma = rs.getString("EmployeeDiploma");
				String EmployeeAddr = rs.getString("EmployeeAddr");
				String EmployeePhone = rs.getString("EmployeePhone");
				String EmployeeMobile = rs.getString("EmployeeMobile");
				String DepartmentId = rs.getString("DepartmentId");
				String JobsId = rs.getString("JobsId");
				String EmployeePhoto = rs.getString("EmployeePhoto");//袁子鹏 2009.1.14 添加 
				EmployeeInfo el = new EmployeeInfo(id,EmployeeId,EmployeeName,EmployeeSex,EmployeeAge,EmployeeIdCard,EmployeeBirthDate,EmployeeDiploma,EmployeeAddr,EmployeePhone,EmployeeMobile,DepartmentId,JobsId,EmployeePhoto);
				list.add(el);
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally{
			db.closeConnection(cn);
		}
		return list;
	}
	//按照部门查询员工
	public List<EmployeeInfo> selectEmployeeInfoByDepar(String deparmentId){
		List<EmployeeInfo> list = new ArrayList<EmployeeInfo>();
		DBConnection db = new DBConnection();
		Connection cn = db.getConnection();
		try
		{
			Statement st = cn.createStatement();
			ResultSet rs = st.executeQuery("select * from EmployeeInfoTable where DepartmentId='"+deparmentId+"'");
			while(rs.next()){
				int id = rs.getInt("EmployeeInfoTableId");
				String EmployeeId = rs.getString("EmployeeId");
				String EmployeeName = rs.getString("EmployeeName");
				String EmployeeSex = rs.getString("EmployeeSex");
				int EmployeeAge = rs.getInt("EmployeeAge");
				String EmployeeIdCard = rs.getString("EmployeeIdCard");
				String EmployeeBirthDate = rs.getString("EmployeeBirthDate");
				String EmployeeDiploma = rs.getString("EmployeeDiploma");
				String EmployeeAddr = rs.getString("EmployeeAddr");
				String EmployeePhone = rs.getString("EmployeePhone");
				String EmployeeMobile = rs.getString("EmployeeMobile");
				String DepartmentId = rs.getString("DepartmentId");
				String JobsId = rs.getString("JobsId");
				String EmployeePhoto = rs.getString("EmployeePhoto");//袁子鹏 2009.1.14 添加 
				EmployeeInfo el = new EmployeeInfo(id,EmployeeId,EmployeeName,EmployeeSex,EmployeeAge,EmployeeIdCard,EmployeeBirthDate,EmployeeDiploma,EmployeeAddr,EmployeePhone,EmployeeMobile,DepartmentId,JobsId,EmployeePhoto);
				list.add(el);
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally{
			db.closeConnection(cn);
		}
		return list;
	}
	
	
	
	//通过页面大小获取总页数
	public int getPageNum(int pageSize) {
		DBConnection db = new DBConnection();
		Connection cn = db.getConnection();
		int num = 0;
		try {
			PreparedStatement ps = cn.prepareStatement("select count(*) from EmployeeInfoTable");
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				num = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeConnection(cn);
		}
		if (num % pageSize == 0) {
			return num / pageSize;
		} else {
			return num / pageSize + 1;
		}
	}
	// 分页查询商品信息
	public List<EmployeeInfo> selectEmployeeInfoPage(int pageSize, int pageNum) {
		DBConnection db = new DBConnection();
		Connection cn = db.getConnection();
		List<EmployeeInfo> list = new ArrayList<EmployeeInfo>();
		try {
			PreparedStatement ps = cn.prepareStatement("select top " + pageSize
							+ "* from EmployeeInfoTable where EmployeeInfoTableId not in(select top "
							+ pageSize * pageNum
							+ " EmployeeInfoTableId from EmployeeInfoTable order by EmployeeInfoTableId)order by EmployeeInfoTableId");
			ResultSet rs =ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt("EmployeeInfoTableId");
				String EmployeeId = rs.getString("EmployeeId");
				String EmployeeName = rs.getString("EmployeeName");
				String EmployeeSex = rs.getString("EmployeeSex");
				int EmployeeAge = rs.getInt("EmployeeAge");
				String EmployeeIdCard = rs.getString("EmployeeIdCard");
				String EmployeeBirthDate = rs.getString("EmployeeBirthDate");
				String EmployeeDiploma = rs.getString("EmployeeDiploma");
				String EmployeeAddr = rs.getString("EmployeeAddr");
				String EmployeePhone = rs.getString("EmployeePhone");
				String EmployeeMobile = rs.getString("EmployeeMobile");
				String DepartmentId = rs.getString("DepartmentId");
				String JobsId = rs.getString("JobsId");
				String EmployeePhoto = rs.getString("EmployeePhoto");//袁子鹏 2009.1.14 添加 
				EmployeeInfo el = new EmployeeInfo(id,EmployeeId,EmployeeName,EmployeeSex,EmployeeAge,EmployeeIdCard,EmployeeBirthDate,EmployeeDiploma,EmployeeAddr,EmployeePhone,EmployeeMobile,DepartmentId,JobsId,EmployeePhoto);
				list.add(el);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeConnection(cn);
		}
		return list;
	}
	
	public static void main(String[] args)
	{
	    EmployeeInfoDAO edao = new EmployeeInfoDAO();
//		EmployeeInfo el = new EmployeeInfo("Ep0900","黄","M",20,"610629191111111","1985-07-06","专科","桃花岛100号","02000000","1212059121","QH","BMZZ");
//		edao.insertEmployeeInfo(el);
//		edao.updateEmployeeInfo("EP0901","黄12","F",21,"610629192222","1985-07-01","1科","桃花岛101号","02000111","1212051111","HR","BMJL");
//		edao.deleteEmployeeInfo("EP0916");
//		el = edao.selectEmployeeById("EP0915");
//		System.out.print(el.getEmployeeName());
//		
//		List<EmployeeInfo> list = edao.selectEmployeeInfoByDepar("ck");
//		Iterator<EmployeeInfo> it = list.iterator();
//		while(it.hasNext()){
//			EmployeeInfo el1 = it.next();
//			System.out.println(el1.getEmployeeId());
//		
//		}
		
//		List<EmployeeInfo> list = edao.selectEmployeeInfoPage(3,1);
//		Iterator<EmployeeInfo> it = list.iterator();
//		while(it.hasNext()){
//			EmployeeInfo el1 = it.next();
//			System.out.println(el1.getEmployeeName());
//		
//		}   
	}
}