sqlclass.java

本员工管理系统使公司员工能通过网络来查询信息、请假

package employeemanagersystem;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;
import java.util.Date;
import java.text.SimpleDateFormat;
import javax.swing.table.DefaultTableModel;
import java.sql.ResultSetMetaData;

/**
 * <p>Title: </p>
 *
 * <p>Description: </p>
 *
 * <p>Copyright: Copyright (c) 2007</p>
 *
 * <p>Company: </p>
 *
 * @author not attributable
 * @version 1.0
 */
public class SQLClass
{
  private String conString;
  private String tableHead[]={"月份","员工编号","姓名","薪水","月度表现"};
   private String tableHead1[]={"年份","员工编号","姓名","年终奖金"};
  public SQLClass()
  {
    conString = "jdbc:odbc:employeeManager";
    try
    {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    }
    catch (ClassNotFoundException ce)
    {
      // ce.printStackTrace();
      System.out.println("架桥失败");
    }

  }

  //打开连接
  public Connection getConnection()
  {
    Connection con = null;
    try
    {
      con = DriverManager.getConnection(conString);
    }
    catch (SQLException se)
    {
      se.printStackTrace();
      System.out.println("连接失败");
    }
    return con;
  }

  //关闭连接
  public void closeConnection(Connection con)
  {

    try
    {
      con.close();
    }
    catch (SQLException se)
    {
      se.printStackTrace();
    }
  }

  //登录验证
  public String loginValidate(String id, String password)
  {

    String flag = "";
    Connection con = this.getConnection();
    try
    {
      CallableStatement cs = con.prepareCall(
          "{call loginValidateProcedure(?,?)}");
      cs.setString(1, id);
      cs.setString(2, password);
      ResultSet rs = cs.executeQuery();
      if (rs.next())
      {
        flag = rs.getString("job_id").trim();
      }
      rs.close();
      cs.close();
    }
    catch (SQLException se)
    {
      //se.printStackTrace();
      System.out.println("登录失败");
    }
    this.closeConnection(con); //关闭数据连接
    return flag;

  }

  //返回所有部门编号和姓名
  public void getDepValues(Vector depNames, Vector depIds)
  {
    Connection con = this.getConnection();
    try
    {
      Statement s = con.createStatement();
      ResultSet rs = s.executeQuery("select * from Department");
      while (rs.next())
      {
        depIds.add(rs.getString(1).trim());
        depNames.add(rs.getString(2).trim());
      }
      rs.close();
      s.close();
    }
    catch (SQLException se)
    {
      System.out.println("搜索部门失败！");
    }
    this.closeConnection(con);
  }

  //返回所有工作的编号和姓名
  public void getJobValues(Vector jobNames, Vector jobIds)
  {
    Connection con = this.getConnection();
    try
    {
      Statement s = con.createStatement();
      ResultSet rs = s.executeQuery("select * from JobType");
      while (rs.next())
      {
        jobIds.add(rs.getString(1).trim());
        jobNames.add(rs.getString(2).trim());
      }
      rs.close();
      s.close();
    }
    catch (SQLException se)
    {
      System.out.println("搜索工种失败！");
    }
    this.closeConnection(con);
  }

  //返回所有员工的编号和姓名
  public void getEmployeeAllInfor(Vector employeeIds, Vector employeeNames)
  {
    Connection con = this.getConnection();
    try
    {
      Statement s = con.createStatement();
      ResultSet rs = s.executeQuery(
          "select emp_id,emp_name from Employee");
      while (rs.next())
      {
        employeeIds.add(rs.getString(1).trim());
        employeeNames.add(rs.getString(2).trim());
      }
      rs.close();
      s.close();
    }
    catch (SQLException se)
    {
      se.printStackTrace();
    }
    this.closeConnection(con);
  }

  //按部门的ID号返回此部门内的员工号和员工名字
  public void getEmployeeIdNameByDepId(String depId, Vector employeeIds,
                                       Vector employeeNames)
  {
    Connection con = this.getConnection();

    try
    {
      PreparedStatement prst = con.prepareStatement(
          "select emp_id,emp_name from Employee where dep_id=?");
      prst.setString(1, depId);
      ResultSet rs = prst.executeQuery();
      while (rs.next())
      {
        employeeIds.add(rs.getString(1).trim());
        employeeNames.add(rs.getString(2).trim());
      }
      rs.close();
      prst.close();
    }
    catch (SQLException se)
    {
      System.out.println("员工搜索失败");
    }
    this.closeConnection(con);
  }

  //返回员工基本信息
  public void getEmployeeInforByEmpId(String empId, Vector infor)
  {
    Connection con = this.getConnection();
    String sql = "{call selectEmployeeInfor(?)}";
    try
    {
      CallableStatement cs = con.prepareCall(sql);
      cs.setString(1, empId);
      ResultSet rs = cs.executeQuery();
      if (rs.next())
      {
        for (int i = 1; i <= 4; i++)
        {
          infor.add(rs.getString(i).trim());
        }
        infor.add(rs.getInt(5));
      }
      rs.close();
      cs.close();

    }
    catch (SQLException se)
    {
      se.printStackTrace();
    }
    this.closeConnection(con);
  }

//编制新员工
  public boolean addEmployee(EmployeeDetailInformation infor)
  {
    Connection con = this.getConnection();
    boolean flag = false;
    //System.out.println(infor);
    String sql = "";
    sql = "{call addEmployee(?,?,?,?,?,?,?,?,?,?,?,?,?)}";
    try
    {
      CallableStatement cs = con.prepareCall(sql);
      cs.setString(1, infor.empId);
      cs.setString(2, infor.depId);
      cs.setString(3, infor.jobId);
      cs.setString(4, infor.name);
      cs.setString(5, infor.degree);
      cs.setInt(6, infor.isMarry);
      cs.setString(7, infor.tel);
      cs.setString(8, infor.address);
      cs.setString(9, infor.birth);
      cs.setString(10, infor.idCard);
      cs.setInt(11, infor.contractTime);
      cs.setString(12, infor.beginTime);
      cs.setString(13, infor.password);
      int i = cs.executeUpdate();
      if (i > 0)
      {
        flag = true;
      }

      cs.close();
    }
    catch (SQLException se)
    {
      System.out.println("数据库插入失败");
      se.printStackTrace();
    }
    this.closeConnection(con);
    return flag;
  }

  //返回登录表中的信息
  public String selectLogin(String empId)
  {
    String sqlLogin = "{call selectLogin(?)}";
    String password = "";
    Connection con = this.getConnection();
    try
    {
      CallableStatement cs = con.prepareCall(sqlLogin);
      cs.setString(1, empId);
      ResultSet rs = cs.executeQuery();
      if (rs.next())
      {
        password = rs.getString(1).trim();
      }
      rs.close();
      cs.close();

    }
    catch (SQLException se)
    {
      se.printStackTrace();
    }
    this.closeConnection(con);
    return password;
  }

  //返回薪水表中的信息
  public Vector selectMonthSalary(String empId)
  {
    String sqlMonthSalary = "{call selectMonthSalary(?)}";
    Vector infor = new Vector();
    Connection con = this.getConnection();
    try
    {
      CallableStatement cs = con.prepareCall(sqlMonthSalary);
      cs.setString(1, empId);
      ResultSet rs = cs.executeQuery();
      //String[] eachInfor = new String[2];
      while (rs.next())
      {
        infor.add(rs.getString(1).trim());
        infor.add(rs.getDouble(2));
        infor.add(rs.getInt(3));
      }
      rs.close();
      cs.close();

    }
    catch (SQLException se)
    {
      se.printStackTrace();
    }
    this.closeConnection(con);
    return infor;
  }

  //返回出勤表中的信息
  public Vector selectEmployeeCheck(String empId)
  {
    String sqlEmployeeCheck = "{call selectEmployeeCheck(?)}";
    Vector infor = new Vector();
    Connection con = this.getConnection();
    try
    {
      CallableStatement cs = con.prepareCall(sqlEmployeeCheck);
      cs.setString(1, empId);
      ResultSet rs = cs.executeQuery();
      while (rs.next())
      {
        infor.add(rs.getString(1).trim());
        int temp = 0;
        for (int i = 1; i < 4; i++)
        {
          infor.add(rs.getInt(i + 1));
        }
      }
      rs.close();
      cs.close();

    }
    catch (SQLException se)
    {
      se.printStackTrace();
    }
    this.closeConnection(con);
    return infor;
  }

  //返回年终奖金表中的信息
  public Vector selectEmployeeBonus(String empId)
  {
    String sqlEmployeeBonus = "{call selectEmployeeBonus(?)}";
    Vector infor = new Vector();
    Connection con = this.getConnection();
    try
    {
      CallableStatement cs = con.prepareCall(sqlEmployeeBonus);
      cs.setString(1, empId);
      ResultSet rs = cs.executeQuery();

      while (rs.next())
      {
        infor.add(rs.getString(1).trim());
        infor.add(rs.getDouble(2));
      }
      rs.close();
      cs.close();
    }
    catch (SQLException se)
    {
      se.printStackTrace();
    }
    this.closeConnection(con);
    return infor;
  }

  //按新编号重新添加登录表中相应信息
  public void insertLogin(String password, String empIdNow)
  {
    String sqlLogin = "{call insertLogin(?,?)}";
    Connection con = this.getConnection();
    try
    {
      CallableStatement cs = con.prepareCall(sqlLogin);
      cs.setString(1, empIdNow);
      cs.setString(2, password);
      cs.executeUpdate();
      cs.close();
    }
    catch (SQLException se)
    {
      se.printStackTrace();
    }
    this.closeConnection(con);
  }

  //在每月统计员工出勤情况
  public void insertIntoCheckEachMonth(String dateStr, String empId)
  {
    String sqlStr = "{call insertCheck(?,?,0,0,0)}";
    Connection con = this.getConnection();
    try
    {
      PreparedStatement prst = con.prepareStatement(sqlStr);
      prst.setString(1, dateStr);
      prst.setString(2, empId);
      prst.executeUpdate();
      this.closeConnection(con);
    }
    catch (SQLException se)
    {
      System.out.println("添加记录出错");
      //se.printStackTrace();
    }

  }

  //按新编号重新添加出勤表中相应信息
  public void insertCheck(String[] infor, String empIdNow)
  {
    String sqlCheck = "{call insertCheck(?,?,?,?,?)}";