leavedao.java

演示如何基于OSWorkflow构建业务系统

package com.qiny.dao;

import java.util.List;
import java.util.ArrayList;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;

import javax.naming.InitialContext;
import javax.naming.NamingException;

import com.qiny.leave.LeaveApply;
import com.qiny.leave.LeaveApprove;

public class LeaveDAO {

  protected static DataSource ds = null;

  public LeaveDAO() {
    if (ds == null) {
      try {
        InitialContext ic = new InitialContext();
        ds = (DataSource) ic.lookup("java:JSQLConnectDB_LeaveApply");
      } catch (NamingException ne) {
        throw new RuntimeException("NamingException while looking");
      }
    }
  }

  public void addLeaveApply(LeaveApply leaveApply) {
    try {
      String exeSql = ""
          + " INSERT INTO bus_leaveApply (applicant,reason,applyTime,dayCount,wfid)"
          + " VALUES ('" + leaveApply.getApplicant() + "','" + leaveApply.getReason()
          + " ',CONVERT(VARCHAR(20),GETDATE(),121 ),'" + leaveApply.getDayCount()
          + " '," + leaveApply.getWfid() + ")";
      Connection connection = ds.getConnection();
      Statement statement = connection.createStatement();
      statement.execute(exeSql);
      statement.close();
      connection.close();
    } catch (SQLException ex) {
      throw new RuntimeException("DAO addLeaveApply failed!\n" + ex.getMessage());
    }
  }

  public void addLeaveApprove(LeaveApprove leaveApproval) {
    try {
      String exeSql = ""
          + " INSERT INTO bus_leaveApprove (applyID,approver,approveTime,opinion)"
          + " VALUES ('" + leaveApproval.getApplyID() + "','" + leaveApproval.getApprover()
          + " ',CONVERT(VARCHAR(20),GETDATE(),121 )," + leaveApproval.getOpinion() + ")";
      Connection connection = ds.getConnection();
      Statement statement = connection.createStatement();
      statement.execute(exeSql);
      statement.close();
      connection.close();
    } catch (SQLException ex) {
      throw new RuntimeException("DAO addLeaveApprove failed!\n" + ex.getMessage());
    }
  }

  public LeaveApply getLeaveApply(long wfid) {
    LeaveApply leaveApply = new LeaveApply();
    try {
      String querySql = ""
          + " SELECT applyID,applicant,reason,applyTime,dayCount,wfid "
          + " FROM bus_leaveApply WHERE wfid=" + wfid;
      Connection connection = ds.getConnection();
      Statement statement = connection.createStatement();
      ResultSet rs = statement.executeQuery(querySql);
      if (rs.next()) {
        leaveApply.setApplyID(rs.getInt("applyID"));
        leaveApply.setApplicant(rs.getString("applicant"));
        leaveApply.setReason(rs.getString("reason"));
        leaveApply.setApplyTime(rs.getString("applyTime"));
        leaveApply.setDayCount(rs.getInt("dayCount"));
        leaveApply.setWfid(rs.getLong("wfid"));
      }
      rs.close();
      statement.close();
      connection.close();
    } catch (SQLException ex) {
      throw new RuntimeException("DAO getLeaveApply failed!\n" + ex.getMessage());
    }
    return leaveApply;
  }

  public List getLeaveAprovals(int applyID) {
    List approvals = new ArrayList();
    try {
      String querySql = ""
          + " SELECT applyID,approver,approveTime,opinion "
          + " FROM bus_leaveApprove WHERE applyID=" + applyID;
      Connection connection = ds.getConnection();
      Statement statement = connection.createStatement();
      ResultSet rs = statement.executeQuery(querySql);
      while (rs.next()) {
        LeaveApprove leaveApproval = new LeaveApprove();
        leaveApproval.setApplyID(rs.getInt("applyID"));
        leaveApproval.setApprover(rs.getString("approver"));
        leaveApproval.setApproveTime(rs.getString("approveTime"));
        leaveApproval.setOpinion(rs.getInt("opinion"));
        approvals.add(leaveApproval);
      }
      rs.close();
      statement.close();
      connection.close();
    } catch (SQLException ex) {
      throw new RuntimeException("DAO getLeaveAprovals failed!\n" + ex.getMessage());
    }
    return approvals;
  }

}