sqlclass.java

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

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

  }

  //请假
  public boolean askForLeave(String empId, String date,int leaveDays)
  {
    Connection con = this.getConnection();
    boolean flag = false;
    try
    {
      CallableStatement cs = con.prepareCall("{call updateCheck(?,?,?)}");
      cs.setString(1, empId);
      cs.setString(2, date);
      cs.setInt(3,leaveDays);
      int i = cs.executeUpdate();
      if (i > 0)
      {
        flag = true;
      }
      cs.close();
      this.closeConnection(con);
    }
    catch (SQLException se)
    {
      se.printStackTrace();
    }
    return flag;
  }

  //初始化个人薪水、表现表模式
  public void initDefaultTableModel(DefaultTableModel dtm, String empId)
  {
    Connection con = this.getConnection();
    int exhibition = 0;
    try
    {
      PreparedStatement prst = con.prepareStatement("select month_id,MonthSalary.emp_id,emp_name,salary,emp_month_exhibition from MonthSalary,employee where MonthSalary.emp_id=? and employee.emp_id=?");
      prst.setString(1, empId);
      prst.setString(2, empId);
      ResultSet rs = prst.executeQuery();
      ResultSetMetaData rsmt = rs.getMetaData();
      int colCount = rsmt.getColumnCount();
      for (int i =0; i < colCount; i++)
      {
        dtm.addColumn(tableHead[i]);
      }
      while (rs.next())
      {
        Object[] cellValue = new Object[colCount];
        cellValue[0] = rs.getString(1);
        cellValue[1] = rs.getString(2);
        cellValue[2]=rs.getString(3);
        cellValue[3] = rs.getDouble(4);
        exhibition = rs.getInt(5);
        if (exhibition == 1)
        {
          cellValue[4] = "优秀";
        }
        else if (exhibition == -1)
        {
          cellValue[4] = "差劲";
        }
        else
        {
          cellValue[4] = "一般";
        }
        dtm.addRow(cellValue);
      }
    }
    catch (SQLException se)
    {
      se.printStackTrace();
    }
  }

  //初始化员工薪水表模式(按部门查询)
  public void initEmployeeDefaultTableModel(DefaultTableModel dtm, String depId)
  {
    Connection con = this.getConnection();
    int exhibition = 0;
    try
    {
      CallableStatement cs = con.prepareCall("{call selectSalaryByDep(?)}");
      cs.setString(1, depId);
      ResultSet rs = cs.executeQuery();
      ResultSetMetaData rsmt = rs.getMetaData();
      int colCount = rsmt.getColumnCount();
      for (int i = 0; i < colCount; i++)
      {
        dtm.addColumn(tableHead[i]);
      }
      while (rs.next())
      {
        Object[] cellValue = new Object[colCount];
        cellValue[0] = rs.getString(1);
        cellValue[1] = rs.getString(2);
        cellValue[2]=rs.getString(3);
        cellValue[3] = rs.getDouble(4);
        exhibition = rs.getInt(5);
        if (exhibition == 1)
        {
          cellValue[4] = "优秀";
        }
        else if (exhibition == -1)
        {
          cellValue[4] = "差劲";
        }
        else
        {
          cellValue[4] = "一般";
        }

        dtm.addRow(cellValue);
      }
    }
    catch (SQLException se)
    {
      se.printStackTrace();
    }
  }

//初始化员工薪水表模式(按部门,日期查询)
  public void initEmployeeDefaultTableModel(DefaultTableModel dtm, String depId,
                                            String dt)
  {
    Connection con = this.getConnection();
    int exhibition = 0;
    try
    {
      CallableStatement cs = con.prepareCall(
          "{call selectSalaryByDepAndMonth(?,?)}");
      cs.setString(1, depId);
      cs.setString(2, dt);
      ResultSet rs = cs.executeQuery();
      ResultSetMetaData rsmt = rs.getMetaData();
      int colCount = rsmt.getColumnCount();
      for (int i = 0; i <colCount; i++)
      {
        dtm.addColumn(tableHead[i]);
      }
      while (rs.next())
      {
        Object[] cellValue = new Object[colCount];
        cellValue[0] = rs.getString(1);
        cellValue[1] = rs.getString(2);
        cellValue[2]=rs.getString(3);
        cellValue[3] = rs.getDouble(4);
        exhibition = rs.getInt(5);
        if (exhibition == 1)
        {
          cellValue[4] = "优秀";
        }
        else if (exhibition == -1)
        {
          cellValue[4] = "差劲";
        }
        else
        {
          cellValue[4] = "一般";
        }

        dtm.addRow(cellValue);
      }
    }
    catch (SQLException se)
    {
      se.printStackTrace();
    }
  }

//返回个人年终奖金
  public void initSelfBonusTableModel(DefaultTableModel dtm, String empId)
  {
    Connection con = this.getConnection();
    try
    {
      PreparedStatement prst = con.prepareStatement(
          "select year_id,employeeBonus.emp_id,emp_name,bonus from EmployeeBonus,employee where employeeBonus.emp_id=? and employeeBonus.emp_id=employee.emp_id");
      prst.setString(1, empId);
      ResultSet rs = prst.executeQuery();
      ResultSetMetaData rsmt = rs.getMetaData();
      int colCount = rsmt.getColumnCount();
      for (int i = 0; i <colCount; i++)
      {
        dtm.addColumn(tableHead1[i]);
      }
      while (rs.next())
      {
        Object[] cellValue = new Object[colCount];
        cellValue[0] = rs.getString(1);
        cellValue[1] = rs.getString(2);
        cellValue[2]=rs.getString(3);
        cellValue[3] = rs.getDouble(4);
        dtm.addRow(cellValue);
      }
    }
    catch (SQLException se)
    {
      se.printStackTrace();
    }
  }

  //初始化员工奖金表模式(按部门查询)
  public void initEmployeeBonusTableModel(DefaultTableModel dtm, String depId)
  {
    Connection con = this.getConnection();
    try
    {
      CallableStatement cs = con.prepareCall("{call selectBonusByDep(?)}");
      cs.setString(1, depId);
      ResultSet rs = cs.executeQuery();
      ResultSetMetaData rsmt = rs.getMetaData();
      int colCount = rsmt.getColumnCount();
      for (int i = 0; i < colCount; i++)
      {
        dtm.addColumn(tableHead1[i]);
      }
      while (rs.next())
      {
        Object[] cellValue = new Object[colCount];
        cellValue[0] = rs.getString(1);
        cellValue[1] = rs.getString(2);
        cellValue[2]=rs.getString(3);
        cellValue[3] = rs.getDouble(4);
        dtm.addRow(cellValue);
      }
      rs.close();
      cs.close();
    }
    catch (SQLException se)
    {
      se.printStackTrace();
    }
  }

//初始化员工奖金表模式(按部门,日期查询)
  public void initEmployeeBonusTableModel(DefaultTableModel dtm, String depId,
                                          String dt)
  {
    Connection con = this.getConnection();

    try
    {
      CallableStatement cs = con.prepareCall(
          "{call selectBonusByDepAndYear(?,?)}");
      cs.setString(1, depId);
      cs.setString(2, dt);
      ResultSet rs = cs.executeQuery();
      ResultSetMetaData rsmt = rs.getMetaData();
      int colCount = rsmt.getColumnCount();
      for (int i = 0; i <colCount; i++)
      {
        dtm.addColumn(tableHead1[i]);
      }
      while (rs.next())
      {
        Object[] cellValue = new Object[colCount];
        cellValue[0] = rs.getString(1);
        cellValue[1] = rs.getString(2);
        cellValue[2]=rs.getString(3);
        cellValue[3] = rs.getDouble(4);
        dtm.addRow(cellValue);
      }
      rs.close();
      cs.close();
    }
    catch (SQLException se)
    {
      se.printStackTrace();
    }
    this.closeConnection(con);
  }

  //返回员工所有信息并用以初始化表模式(变量type用以表示查询的类型)1:EmpId; 2:EmpName; 3:DepId; 4:JobId; 5:查询所有;
  public Vector initEmployeeInforByEmpIdTableModel(
                                                 String value, int type)
  {
    Connection con = this.getConnection();
    Vector infor=new Vector();
    int marryStatue = 0;
    int exhibition = 0;
    String sqlExecute = "";
    String sqlSelectByEmpId = "{call selectEmployeeDetailInforByEmpId(?)}";
    String sqlSelectByEmpName = "{call selectEmployeeDetailInforByEmpName(?)}";
    String sqlSelectByDepId = "{call selectEmployeeDetailInforByDepId(?)}";
    String sqlSelectByJobId = "{call selectEmployeeDetailInforByJobId(?)}";
    String sqlSelectAll = "{call selectAllEmployeeDetailInfor}";
    if (type == 1)
    {
      sqlExecute = sqlSelectByEmpId;
    }
    else if (type == 2)
    {
      sqlExecute = sqlSelectByEmpName;
    }
    else if (type == 3)
    {
      sqlExecute = sqlSelectByDepId;
    }
    else if (type == 4)
    {
      sqlExecute = sqlSelectByJobId;
    }
    else if (type == 5)
    {
      sqlExecute = sqlSelectAll;
    }
    try
    {
      CallableStatement cs = con.prepareCall(sqlExecute);
      if (type != 5)
      {
        cs.setString(1, value);
      }
      ResultSet rs = cs.executeQuery();


      while (rs.next())
      {

        EmployeeDetailInformation empInfor = new  EmployeeDetailInformation();
        empInfor.setEmpId(rs.getString(1).trim());
        empInfor.setDepName(rs.getString(2).trim());
        empInfor.setJobName(rs.getString(3).trim());
        empInfor.setName(rs.getString(4).trim());
        empInfor.setDegree(rs.getString(5).trim());

        marryStatue = rs.getInt(6);
        if (marryStatue == 1)
        {
          empInfor.setMarryString("已婚");
        }
        else
        {
          empInfor.setMarryString("未婚");
        }

        empInfor.setTel(rs.getString(7).trim());
        empInfor.setAddress(rs.getString(8).trim());
        empInfor.setBirth(rs.getString(9).trim());
        empInfor.setIdCard(rs.getString(10).trim());

        empInfor.setContractTime( rs.getInt(11));
        empInfor.setBeginTime( rs.getString(12).trim());
        exhibition = rs.getInt(13);
        if (exhibition == 1)
        {
          empInfor.setExhibitionStr("优秀");
        }
        else if (exhibition == -1)
        {
          empInfor.setExhibitionStr( "差劲");
        }
        else
        {
          empInfor.setExhibitionStr( "一般");
        }

        infor.add(empInfor);

      }

      rs.close();
      cs.close();
    }
    catch (SQLException se)
    {
      se.printStackTrace();
    }
    this.closeConnection(con);
    return infor;
  }
  //更新表中的信息
  public boolean updateValuesInTableModel(String []updateValues)
  {
    boolean flag=false;
    int isMarry=0;
    Connection con=this.getConnection();
    try
    {
      CallableStatement cs=con.prepareCall("{call updateValuesInTableModel(?,?,?,?,?,?,?)}");

      for(int i=0;i<3;i++)
      {
        cs.setString(i+1,updateValues[i]);
      }
      if(updateValues[3].equals("已婚"))
        isMarry=1;
      cs.setInt(4,isMarry);
      for(int i=4;i<7;i++)
      {
        cs.setString(i+1,updateValues[i]);
      }

      int i=cs.executeUpdate();
      if(i>0)
        flag=true;
      cs.close();
    }
    catch(SQLException se)
    {
      se.printStackTrace();
    }
    this.closeConnection(con);
    return flag;
  }
  //删除列表中的一行???????????????
  public boolean deleteEmployeeInTableModel(String empId)
  {
    boolean flag=false;
    Connection con=this.getConnection();
    try
    {
      CallableStatement cs=con.prepareCall("call deleteEmployee(?)");
      cs.setString(1,empId);
      int i=cs.executeUpdate();
      if(i>0)
        flag=true;
      cs.close();
    }
    catch(SQLException se)
    {
      se.printStackTrace();
    }
    this.closeConnection(con);
    return flag;
  }
}