employeedao.java

来自「这是一个基于大唐电信的一个任务管理模块程序」· Java 代码 · 共 379 行

package com.datang.struts.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.datang.struts.common.Constant;
import com.datang.struts.dto.SelectCheck;
import com.datang.struts.dto.TangEmployee;
import com.datang.struts.form.Employee_listForm;
import com.datang.struts.form.OrderEmployeeInfoForm;
import com.datang.struts.util.DbUtil;

public class EmployeeDAO {
	public List usersel() {

		List list = new ArrayList();
		DbUtil db = new DbUtil();
		Connection conn = db.getConnection();
		String sql = "select * from tang_employee";
		try {
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(sql);
			while (rs.next()) {
				OrderEmployeeInfoForm oeif = new OrderEmployeeInfoForm();
				oeif.setEmployee_id(rs.getString("employee_id"));
				oeif.setEmployee_password(rs.getString("employee_password"));
				oeif.setEmployee_name(rs.getString("employee_name"));
				oeif.setEmployee_sex(rs.getString("employee_sex"));
				// oeif.setEmployee_birthday((rs.getDate("employee_birthday")).toString());
				oeif.setEmployee_position(rs.getString("employee_position"));
				oeif.setEmployee_hire_date((rs.getDate("employee_hire_date"))
						.toString());
				// oeif.setManager_id(rs.getString("manager_id"));
				// oeif.setEmployee_experience(rs.getString("employee_experience"));
				// oeif.setEmployee_major(rs.getString("employee_major"));
				// oeif.setEmployee_degree(rs.getString("employee_degree"));
				list.add(oeif);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}

	}

	public List getChoiceUserInfo(String employee_id) {

		List list = new ArrayList();
		DbUtil db = new DbUtil();
		Connection conn = db.getConnection();
		String sql = "select * from tang_employee where employee_id = '"
				+ employee_id + "'";
		try {
			Statement stat = conn.createStatement();
			ResultSet rs = stat.executeQuery(sql);
			while (rs.next()) {
				OrderEmployeeInfoForm oeif = new OrderEmployeeInfoForm();
				oeif.setEmployee_id(rs.getString("employee_id"));
				oeif.setEmployee_password(rs.getString("employee_password"));
				oeif.setEmployee_name(rs.getString("employee_name"));
				oeif.setEmployee_sex(rs.getString("employee_sex"));
				oeif.setEmployee_birthday((rs.getDate("employee_birthday"))
						.toString());
				oeif.setEmployee_position(rs.getString("employee_position"));
				oeif.setEmployee_hire_date((rs.getDate("employee_hire_date"))
						.toString());
				oeif.setManager_id(rs.getString("manager_id"));
				oeif
						.setEmployee_experience(rs
								.getString("employee_experience"));
				oeif.setEmployee_major(rs.getString("employee_major"));
				oeif.setEmployee_degree(rs.getString("employee_degree"));
				list.add(oeif);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	// asfsafsafsafsaf

	private String ADD_EMPLOYEE = "insert into tang_employee(employee_id,"
			+ "employee_password," + "employee_name," + "employee_sex,"
			+ "employee_birthday," + "employee_degree," + "employee_major,"
			+ "employee_experience," + "employee_hire_date,"
			+ "employee_position)" + " values (?,?,?,?,?,?,?,?,?,?)";

	private String GET_ALL_EMPLOYEE = "select * from tang_employee";

	private String GET_ONE_INFO = "select * from tang_employee where employee_id=?";

	private String GET_ALL_SEL = "select employee_id,employee_name from tang_employee where employee_position='manager'";

	private String GET_ONE_SEL = "select manager_id from tang_employee where employee_id=?";

	private String UPDATE_MANAGER = "update tang_employee set manager_id=? where employee_id=?";

	private String DEL_EMPLOYEE = "delete tang_employee";

	private String UP_EMPLOYEE = "update tang_employee set employee_password=?,"
			+ "employee_name=?,"
			+ "employee_sex=?,"
			+ "employee_birthday=?,"
			+ "employee_degree=?,"
			+ "employee_major=?,"
			+ "employee_experience=?,"
			+ "employee_hire_date=?,"
			+ "employee_position=? where " + "employee_id=?";

	public int addEmployee(TangEmployee emp) {
		Connection conn = DbUtil.getConnection();
		System.out.println("emp is:" + emp);
		System.out.println("SQL is:" + ADD_EMPLOYEE);
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(ADD_EMPLOYEE);
			ps.setString(1, emp.getEmployee_id());
			ps.setString(2, emp.getEmployee_password());
			ps.setString(3, emp.getEmployee_name());
			ps.setString(4, emp.getEmployee_sex());
			ps.setDate(5, emp.getEmployee_birthday());
			ps.setString(6, emp.getEmployee_degree());
			ps.setString(7, emp.getEmployee_major());
			ps.setString(8, emp.getEmployee_experience());
			ps.setDate(9, emp.getEmployee_hire_date());
			ps.setString(10, emp.getEmployee_position());
			ps.executeUpdate();
			return Constant.OK;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return Constant.ERROR;
		} finally {
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public List getEmployee(Employee_listForm form) throws SQLException {
		int begin = (form.getCurrentPage() - 1) * 5 + 1;
		int size = form.getSize();
		DbUtil db = new DbUtil();
		List list = new ArrayList();
		Connection conn = DbUtil.getConnection();
		try {
			PreparedStatement ps = conn.prepareStatement(GET_ALL_EMPLOYEE,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			ResultSet rs = ps.executeQuery();
			rs.absolute(begin);
			for (int i = 0; i < size; i++) {
				TangEmployee emp = new TangEmployee();
				emp.setEmployee_id(rs.getString("employee_id"));
				emp.setEmployee_password(rs.getString("employee_password"));
				emp.setEmployee_name(rs.getString("employee_name"));
				emp.setEmployee_sex(rs.getString("employee_sex"));
				emp.setEmployee_birthday(rs.getDate("employee_birthday"));
				emp.setEmployee_position(rs.getString("employee_position"));
				list.add(emp);
				if (!rs.next()) {
					break;
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return list;
	}

	public TangEmployee getOneEmployee(String id) throws SQLException {
		Connection conn = DbUtil.getConnection();
		TangEmployee emp = null;
		try {
			PreparedStatement ps = conn.prepareStatement(GET_ONE_INFO,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			ps.setString(1, id);
			System.out.println("ps:" + ps);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				emp = new TangEmployee();
				emp.setEmployee_id(rs.getString("employee_id"));
				emp.setEmployee_password(rs.getString("employee_password"));
				emp.setEmployee_name(rs.getString("employee_name"));
				emp.setEmployee_sex(rs.getString("employee_sex"));
				emp.setEmployee_birthday(rs.getDate("employee_birthday"));
				emp.setEmployee_position(rs.getString("employee_position"));
				emp.setEmployee_degree(rs.getString("employee_degree"));
				emp.setEmployee_experience(rs.getString("employee_experience"));
				emp.setEmployee_hire_date(rs.getDate("employee_hire_date"));
				emp.setEmployee_major(rs.getString("employee_major"));
				emp.setManager_id(rs.getString("manager_id"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return emp;
	}

	public List getSelectCheck(String id) throws SQLException {
		List list = new ArrayList();
		Connection conn = DbUtil.getConnection();
		SelectCheck sc = null;
		String flage = null;
		try {
			PreparedStatement ps = conn.prepareStatement(GET_ONE_SEL,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			ps.setString(1, id);
			System.out.println("ps:" + id);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				flage = rs.getString("manager_id");
				System.out.println("managerid:" + flage);
			}
			PreparedStatement ps1 = conn.prepareStatement(GET_ALL_SEL,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			System.out.println("ps:" + ps);
			ResultSet rs1 = ps1.executeQuery();
			while (rs1.next()) {
				sc = new SelectCheck();
				sc.setId(rs1.getString("employee_id"));
				System.out
						.println("employeeid:" + rs1.getString("employee_id"));
				sc.setName(rs1.getString("employee_name"));
				if (rs1.getString("employee_id").equals(flage)) {
					System.out.println("flageid:"
							+ rs1.getString("employee_id"));
					sc.setFlage("selected");
				}
				list.add(sc);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return list;
	}

	public int update(String mid, String id) {
		Connection conn = DbUtil.getConnection();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(UPDATE_MANAGER);
			ps.setString(1, mid);
			ps.setString(2, id);
			System.out.println("mid:" + mid + "id:" + id);
			ps.executeUpdate();
			return Constant.OK;
		} catch (SQLException e) {
			e.printStackTrace();
			return Constant.ERROR;
		} finally {
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public int delEmployee(TangEmployee emp) {
		Connection conn = DbUtil.getConnection();
		PreparedStatement ps = null;
		DEL_EMPLOYEE += " where employee_id=" + emp.getEmployee_id();
		try {
			ps = conn.prepareStatement(DEL_EMPLOYEE);
			ps.execute();
			ps.close();
			conn.close();
			return Constant.OK;
		} catch (SQLException e) {
			e.printStackTrace();
			return Constant.ERROR;
		}

	}

	public TangEmployee showEmployee(String id) {
		Connection conn = DbUtil.getConnection();
		GET_ALL_EMPLOYEE += " where employee_id='" + id+"'";
		System.out.println("GET_ALL_EMPLOYEE is :" + GET_ALL_EMPLOYEE);
		PreparedStatement ps;
		TangEmployee emp = new TangEmployee();
		try {
			ps = conn.prepareStatement(GET_ALL_EMPLOYEE);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				emp.setEmployee_id(rs.getString("Employee_id"));
				emp.setEmployee_password(rs.getString("Employee_password"));
				emp.setEmployee_name(rs.getString("Employee_name"));
				emp.setEmployee_sex(rs.getString("Employee_sex"));
				emp.setEmployee_birthday(rs.getDate("Employee_birthday"));
				emp.setEmployee_hire_date(rs.getDate("Employee_hire_date"));
				emp.setEmployee_degree(rs.getString("Employee_degree"));
				emp.setEmployee_experience(rs.getString("Employee_experience"));
				emp.setEmployee_major(rs.getString("Employee_major"));
				emp.setEmployee_position(rs.getString("Employee_position"));
			}
			ps.close();
			conn.close();
			return emp;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}

	}

	public int updateEmployee(TangEmployee emp, String id) {
		Connection conn = DbUtil.getConnection();
		PreparedStatement ps = null;

		try {
			ps = conn.prepareStatement(UP_EMPLOYEE);
			ps.setString(1, emp.getEmployee_password());
			ps.setString(2, emp.getEmployee_name());
			ps.setString(3, emp.getEmployee_sex());
			ps.setDate(4, emp.getEmployee_birthday());
			ps.setString(5, emp.getEmployee_degree());
			ps.setString(6, emp.getEmployee_major());
			ps.setString(7, emp.getEmployee_experience());
			ps.setDate(8, emp.getEmployee_hire_date());
			System.out.println("hire_date:" + emp.getEmployee_birthday());
			ps.setString(9, emp.getEmployee_position());
			ps.setString(10, id);
			ps.executeUpdate();
			System.out.println(emp);
			return Constant.OK;
		} catch (SQLException e) {
			e.printStackTrace();
			return Constant.ERROR;
		} finally {
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

}