empdao.java

办公自动化系统,有人事资源管理模块,考勤管理,审批管理,权限设定等.

package com.lovo.dao.vicky;

import java.sql.*;
import java.util.Vector;

import com.lovo.po.vicky.*;

import com.lovo.util.DBConnection;

public class EmpDao {

	// 查询一共有多少条记录
	public int count() {
		Connection con = DBConnection.getConnection();
		String sql = "select * from t_basicinfo b";
		Statement stmt = null;
		ResultSet set = null;
		try {
			stmt = con.createStatement();
			set = stmt.executeQuery(sql);
			int count = 0;
			while (set.next()) {
				count++;
			}
			return count;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (set != null)
					set.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			DBConnection.close(con);
		}
		return 0;
	}

	// 简要查询最后一页的信息
	public Vector<EmpPo> selectEnd(int count) {
		Connection con = DBConnection.getConnection();
		String sql = "select  b.ID,b.name,p.postName,d.depName,b.phone,w.rank from t_basicinfo b,t_workinfo w,t_department d,t_position p where b.ID=w.empId and w.position = p.postID and w.department =d.depID limit "
				+ count + ",14";
		Statement stmt = null;
		ResultSet set = null;
		try {
			stmt = con.createStatement();
			set = stmt.executeQuery(sql);
			Vector<EmpPo> selectV = new Vector<EmpPo>();
			while (set.next()) {
				EmpPo emp = new EmpPo();
				Integer id = set.getInt(1);
				emp.setId(id.toString());
				emp.setName(set.getString(2));
				emp.myWorkInfoPo.myPosition.setName(set.getString(3));
				emp.myWorkInfoPo.myDepartment.setName(set.getString(4));
				emp.setPhone(set.getString(5));
				emp.myWorkInfoPo.setRank(set.getString(6));
				selectV.add(emp);
			}
			return selectV;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (set != null)
					set.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			DBConnection.close(con);
		}
		return null;
	}

	// 删除记录
	public Vector<EmpPo> delete(String[] str) {
		Connection con = DBConnection.getConnection();
		PreparedStatement pstmt = null;
		PreparedStatement pstmt1 = null;
		PreparedStatement pstmt2 = null;
		PreparedStatement pstmt3 = null;
		PreparedStatement pstmt4 = null;
		String sql = "delete from t_basicinfo where ID=?";
		String sql1 = "delete from t_workinfo where empId=?";
		String sql2 = "delete from t_awardinfo where empId=?";
		String sql3 = "delete from t_familyinfo where empId=?";
		String sql4 = "delete from t_leave where empId=?";
		try {
			for (int i = 0; i < str.length; i++) {
				Integer integer = Integer.parseInt(str[i]);
				pstmt = con.prepareStatement(sql);
				pstmt.setInt(1, integer);
				pstmt1 = con.prepareStatement(sql1);
				pstmt1.setInt(1, integer);
				pstmt2 = con.prepareStatement(sql2);
				pstmt2.setInt(1, integer);
				pstmt3 = con.prepareStatement(sql3);
				pstmt3.setInt(1, integer);
				pstmt4 = con.prepareStatement(sql4);
				pstmt4.setInt(1, integer);
				pstmt.executeUpdate();
				pstmt1.executeUpdate();
				pstmt2.executeUpdate();
				pstmt3.executeUpdate();
				pstmt4.executeUpdate();
			}
			Vector<EmpPo> selectV = this.selectEnd(0);
			return selectV;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (pstmt != null)
					pstmt.close();
				if (pstmt1 != null)
					pstmt1.close();
				if (pstmt2 != null)
					pstmt2.close();
				if (pstmt3 != null)
					pstmt3.close();
				if (pstmt4 != null)
					pstmt4.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			DBConnection.close(con);
		}
		return null;
	}

	// 详细查询
	public EmpPo particularSelect(EmpPo po) {
		Connection con = DBConnection.getConnection();
		EmpPo empPo = new EmpPo();
		String id = po.getId();
		String str = "select *  from t_basicinfo where ID=" + id;
		String str1 = "select * from t_awardinfo where empId =" + id;
		String str2 = "select * from t_familyinfo where empId=" + id;
		String str3 = "select w.*,d.depName,p.postName  from t_workinfo w,t_department d,t_position p where d.depID=w.department and p.postID = w.position and w.empId ="
				+ id;
		Statement stmt = null;
		ResultSet set = null;
		try {
			stmt = con.createStatement();
			set = stmt.executeQuery(str);
			System.out.println(set);
			if (set.next()) {
				Integer ID = set.getInt(1);
				empPo.setId(ID.toString());
				empPo.setName(set.getString(2));
				empPo.setOldName(set.getString(3));
				empPo.setIdCard(set.getString(4));
				empPo.setSex(set.getInt(5));
				empPo.setNation(set.getString(6));
				if (set.getDate(7) != null) {
					empPo.setBirthDay(set.getDate(7).toString());
				}
				empPo.setBirthPlace(set.getString(8));
				empPo.setHomeTown(set.getString(9));
				empPo.setBackGround(set.getString(10));
				empPo.setPoliticsFeature(set.getString(11));
				empPo.setDegree(set.getString(12));
				empPo.setIsMarryed(set.getInt(13));
				empPo.setGraduateSchool(set.getString(14));
				empPo.setPhone(set.getString(15));
				if (set.getDate(16) != null) {
					empPo.setFirstWorkDate(set.getDate(16).toString());
				}
				empPo.setRemark(set.getString(17));
				empPo.setDiscipline(set.getString(18));
			}
			set = stmt.executeQuery(str1);
			while (set.next()) {
				AwardInfoPo awardPo = new AwardInfoPo();
				awardPo.setAward(set.getString(3));
				awardPo.setPunishment(set.getString(4));
				empPo.myAwardInfoPo.add(awardPo);

			}
			set = stmt.executeQuery(str2);
			if (set.next()) {
				empPo.myFamilyInfoPo.setMName(set.getString(3));
				empPo.myFamilyInfoPo.setFName(set.getString(4));
				empPo.myFamilyInfoPo.setOtherName(set.getString(5));
				empPo.myFamilyInfoPo.setMVocation(set.getString(6));
				empPo.myFamilyInfoPo.setFVocation(set.getString(7));
				empPo.myFamilyInfoPo.setOtherVocation(set.getString(8));
				empPo.myFamilyInfoPo.setChildName(set.getString(9));
				empPo.myFamilyInfoPo.setDegree(set.getString(10));
				empPo.myFamilyInfoPo.setPhone(set.getString(11));
				empPo.myFamilyInfoPo.setPostcode(set.getString(12));
				empPo.myFamilyInfoPo.setPosition(set.getString(13));
			}
			set = stmt.executeQuery(str3);
			if (set.next()) {
				empPo.myWorkInfoPo.setStartTime(set.getDate(3).toString());
				empPo.myWorkInfoPo.setRank(set.getString(6));
				empPo.myWorkInfoPo.setWorkDescribe(set.getString(7));
				empPo.myWorkInfoPo.setAchievement(set.getString(8));
				empPo.myWorkInfoPo.myDepartment.setName(set.getString(9));
				empPo.myWorkInfoPo.myPosition.setName(set.getString(10));
			}
			return empPo;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (set != null)
					set.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (stmt != null)
					stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			DBConnection.close(con);
		}
		return null;
	}

	// 查询所有职务
	public Vector<PositionPo> getPosition() {
		Connection con = DBConnection.getConnection();
		Statement stmt = null;
		ResultSet set = null;
		Vector<PositionPo> positionV = new Vector<PositionPo>();
		String str = "select * from t_position";
		try {
			stmt = con.createStatement();
			set = stmt.executeQuery(str);
			while (set.next()) {
				PositionPo po = new PositionPo();
				Integer id = set.getInt(1);
				po.setId(id.toString());
				po.setName(set.getString(2));
				positionV.add(po);
			}
			return positionV;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (set != null) {
				try {
					set.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			DBConnection.close(con);
		}
		return null;

	}

	// 查询所有部门
	public Vector<DepartmentPo> getDepartment() {
		Connection con = DBConnection.getConnection();
		Statement stmt = null;
		ResultSet set = null;
		Vector<DepartmentPo> departmentV = new Vector<DepartmentPo>();
		String sql = "select * from t_department";
		try {
			stmt = con.createStatement();
			set = stmt.executeQuery(sql);
			while (set.next()) {
				DepartmentPo po = new DepartmentPo();
				Integer id = set.getInt(1);
				po.setId(id.toString());
				po.setName(set.getString(2));
				departmentV.add(po);

			}
			return departmentV;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (set != null) {
				try {
					set.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			DBConnection.close(con);
		}
		return null;

	}

	public Vector<EmpPo> cQuery(EmpPo po,int c) {		
		String str ="select b.ID,b.name,e.depName,e.rank,e.postName,b.phone from t_basicinfo b ,   (    select  w.empId,w.rank,p.postName,d.depName     from t_position p, t_department d,   (  select w.empId ,w.rank,w.position,  w.department    from t_workinfo w    where w.position ="+po.myWorkInfoPo.myPosition.getId()+"  and w.department = "+po.myWorkInfoPo.myDepartment.getId()+"    ) w   where p.postId = w.position and d.depId = w.department  ) e where  b.ID = e.empId limit "+c+",14";		
		Connection con = DBConnection.getConnection();
		Statement stmt = null;
		ResultSet set = null;
		Vector<EmpPo> poV = new Vector<EmpPo>();
		try {
			stmt = con.createStatement();
			set = stmt.executeQuery(str);
			while(set.next()){
				EmpPo emp = new EmpPo();
				Integer id = set.getInt(1);
				emp.setId(id.toString());
				emp.setName(set.getString(2));
				emp.myWorkInfoPo.myDepartment.setName(set.getString(3));
				emp.myWorkInfoPo.setRank(set.getString(4));
				emp.myWorkInfoPo.myPosition.setName(set.getString(5));
				emp.setPhone(set.getString(6));
				poV.add(emp);				
			}
	
			return poV;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			if(set!=null){
				try {
					set.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(stmt!=null){
				try {
					set.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			DBConnection.close(con);
		}
		
		return null;
	} 
	//组合查询的一共有多少条
	public int cCount(EmpPo po){
		String str ="select b.ID,b.name,e.depName,e.rank,e.postName,b.phone from t_basicinfo b ,   (    select  w.empId,w.rank,p.postName,d.depName     from t_position p, t_department d,   (  select w.empId ,w.rank,w.position,  w.department    from t_workinfo w    where w.position ="+po.myWorkInfoPo.myPosition.getId()+"  and w.department = "+po.myWorkInfoPo.myDepartment.getId()+"    ) w   where p.postId = w.position and d.depId = w.department  ) e where  b.ID = e.empId";		
		Connection con = DBConnection.getConnection();
		Statement stmt = null;
		ResultSet set = null;	
		int count = 0;
		try {
			stmt = con.createStatement();
			set = stmt.executeQuery(str);
			
			while (set.next()) {
				count++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	finally{
			try {
				set.close();
				stmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			DBConnection.close(con);
			
		}	
		return count;
	}

}