jobdao.java

对2000元以上的资产管理

package com.cdaccp.dao;

import java.sql.*;
import com.util.DBAccess;
import java.sql.PreparedStatement;
import java.util.*;
import com.cdaccp.entity.Job;

/**
 * <p>Title: </p>
 *
 * <p>Description: </p>
 *
 * <p>Copyright: Copyright (c) 2008</p>
 *
 * <p>Company: </p>
 *
 * @author not attributable
 * @version 1.0
 */
public class JobDAO {
  public JobDAO() {
  }

  //添加一个职位
  public static boolean addJob(String name) {
    String sql = "insert job values (?)";
    Connection con = DBAccess.getConnection();
    PreparedStatement pst = null;
    try {
      pst = con.prepareStatement(sql);
      pst.setString(1, name);
      int i = pst.executeUpdate();
      if (i > 0) {
        return true;
      }
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
    finally {
      DBAccess.closeStatement(pst);
      DBAccess.closeConnection(con);
    }
    return false;
  }

  //删除一个职位
  public static boolean deleteJob(int id) {
    String sql = "delete from job where jobid = ?";
    Connection con = DBAccess.getConnection();
    PreparedStatement pst = null;
    try {
      pst = con.prepareStatement(sql);
      pst.setInt(1, id);
      int i = pst.executeUpdate();
      if (i > 0) {
        return true;
      }
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
    finally {
      DBAccess.closeStatement(pst);
      DBAccess.closeConnection(con);
    }
    return false;
  }

  //查询所有职位
  public static List allJob() {
    List list = new ArrayList();
    String sql = "select * from job ";
    Connection con = DBAccess.getConnection();
    PreparedStatement pst = null;
    ResultSet rs = null;
    try {
      pst = con.prepareStatement(sql);
      rs = pst.executeQuery();
      while (rs.next()) {
        Job job = new Job();
        job.setJobId(rs.getInt(1));
        job.setJobName(rs.getString(2));
        list.add(job);
      }
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
    finally {
      DBAccess.closeStatement(pst);
      DBAccess.closeConnection(con);
    }
    return list;
  }

  //查询没有有占用的职位
  public static List ontJob() {
    List list = new ArrayList();
    String sql = "select job.* from job left join employee on (job.jobid = employee.position) where empid is null ";
    Connection con = DBAccess.getConnection();
    PreparedStatement pst = null;
    ResultSet rs = null;
    try {
      pst = con.prepareStatement(sql);
      rs = pst.executeQuery();
      while (rs.next()) {
        Job job = new Job();
        job.setJobId(rs.getInt(1));
        job.setJobName(rs.getString(2));
        list.add(job);
      }
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
    finally {
      DBAccess.closeStatement(pst);
      DBAccess.closeConnection(con);
    }
    return list;
  }

  //修改职们名称
  public static boolean updateJobName(int id, String name) {
    boolean rest = false;
    String sql = "update job set jobName = ? where id = ? ";
    Connection con = DBAccess.getConnection();
    PreparedStatement pst = null;
    ResultSet rs = null;
    try {
      pst = con.prepareStatement(sql);
      pst.setInt(1, id);
      pst.setString(2, name);
      if (pst.executeUpdate() > 0) {
        rest = true;
      }
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
    finally {
      DBAccess.closeStatement(pst);
      DBAccess.closeConnection(con);
    }
    return rest;
  }
}