lendrecorddao.java~38~

对2000元以上的资产管理

package com.cdaccp.dao;

import com.cdaccp.entity.LendRecord;
import java.util.*;
import java.sql.*;
import com.util.DBAccess;

/**
 * 用于对资产借用表进行增删改查操作
 *
 */
public class LendRecordDAO {

  /**
   * 添加借用记录
   * @param lendRecord LendRecord 借用记录对象
   */
  public boolean insert(LendRecord lendRecord) {
    Connection con = DBAccess.getConnection();
    PreparedStatement pst = null;
    String sql =
        "insert lendrecord (assetid,empid,usedate,outlender,remarks) values(?,?,?,?,?)";
    try {
      pst = con.prepareStatement(sql);
      pst.setInt(1, lendRecord.getAssetId());
      pst.setInt(2, lendRecord.getEmpId());
      pst.setString(3, lendRecord.getUsedate());
      pst.setInt(4, lendRecord.getOutLender());
      pst.setString(5, lendRecord.getRemarks());
      int i = pst.executeUpdate();
      if (i > 0) {
        return true;
      }
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
    finally {
      DBAccess.closeStatement(pst);
      DBAccess.closeConnection(con);
    }
    return false;
  }

  /**
   * 获取所有借用记录
   * @return List 借用记录对象集合，如果没有借用记录则返回null
   * 如果传人为0的参数则查询没还的所有资产信息
   */
  public List loadAll(int i) {
    List list = null;
    Connection con = DBAccess.getConnection();
    PreparedStatement pst = null;
    ResultSet rs = null;
    String sql = "select a.assetid,b.assetname,a.empid,c.empname, " +
        "a.usedate,a.returndate,a.outlender,a.inlender,a.remarks from lendrecord as a" +
        " inner join asset as b on(a.assetid = b.assetid) " +
        "join employee as c on(a.empid = c.empid)";
    try {
      if (i == 0) {
        sql = "select a.assetid,b.assetname,a.empid,c.empname, " +
            "a.usedate,a.returndate,a.outlender,a.inlender,a.remarks from lendrecord as a" +
            " inner join asset as b on(a.assetid = b.assetid) " +
            "join employee as c on(a.empid = c.empid)"
            + " where returnDate is null";
      }
      pst = con.prepareStatement(sql);
      rs = pst.executeQuery();
      list = new ArrayList();
      while (rs.next()) {
        LendRecord lend = new LendRecord();
        lend.setAssetId(rs.getInt("assetid"));
        lend.setAssetName(rs.getString("assetname"));
        lend.setEmpId(rs.getInt("empid"));
        lend.setEmpName(rs.getString("empname"));
        lend.setUsedate(rs.getString("usedate"));
        lend.setReturnDate(rs.getString("returndate"));
        lend.setOutLender(rs.getInt("outLender"));
        lend.setInLender(rs.getInt("inLender"));
        lend.setRemarks(rs.getString("remarks"));
        list.add(lend);
      }
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
    finally {
      DBAccess.closeResultSet(rs);
      DBAccess.closeStatement(pst);
      DBAccess.closeConnection(con);
    }
    return list;
  }

  /**
   * 查询某资产的所有借用记录
   * @param assetId int  资产编号
   * @return List 借用记录对象集合，如果没有借用记录则返回null
   */
  public List loadRecordByAssetId(int assetId) {
    List list = null;
    Connection con = DBAccess.getConnection();
    PreparedStatement pst = null;
    ResultSet rs = null;
    String sql = "select a.assetid,b.assetname,a.empid,c.empname, " +
        "a.usedate,a.returndate,a.outlender,a.inlender,a.remarks from lendrecord as a" +
        " inner join asset as b on(a.assetid = b.assetid) " +
        "join employee as c on(a.empid = c.empid) where a.assetid =? ";
    try {
      pst = con.prepareStatement(sql);
      pst.setInt(1, assetId);
      rs = pst.executeQuery();
      list = new ArrayList();
      while (rs.next()) {
        LendRecord lend = new LendRecord();
        lend.setAssetId(rs.getInt("assetid"));
        lend.setAssetName(rs.getString("assetname"));
        lend.setEmpId(rs.getInt("empid"));
        lend.setEmpName(rs.getString("empname"));
        lend.setUsedate(rs.getString("usedate"));
        lend.setReturnDate(rs.getString("returndate"));
        lend.setOutLender(rs.getInt("outLender"));
        lend.setInLender(rs.getInt("inLender"));
        lend.setRemarks(rs.getString("remarks"));
        list.add(lend);
      }
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
    finally {
      DBAccess.closeResultSet(rs);
      DBAccess.closeStatement(pst);
      DBAccess.closeConnection(con);
    }

    return list;
  }

  /**
   * 归还资产
   */
  public boolean updateLendRecord(LendRecord lend) {
    boolean b = false;
    Connection con = DBAccess.getConnection();
    PreparedStatement pst = null;
    ResultSet rs = null;
    String sql =
        "update lendRecord set returnDate = ? ,inlender = ? ,remarks = ? where  assetid = ? and returnDate is null";
    try {
      pst = con.prepareStatement(sql);
      pst.setString(1, lend.getReturnDate());
      pst.setInt(2, lend.getInLender());
      pst.setString(3, lend.getRemarks());
      pst.setInt(4, lend.getAssetId());
      int i = pst.executeUpdate();
      if (i > 0) {
        b = true;
      }
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
    finally {
      DBAccess.closeStatement(pst);
      DBAccess.closeConnection(con);
    }
    return b;
  }

  //根据员工编号查询借用记录
public List loadBYempId(int empId){
    List list = new ArrayList();
    Connection con = DBAccess.getConnection();
    PreparedStatement pst = null;
    String sql = "select lendrecord.*,asset.assetName from lendrecord " +
        "inner join asset on (lendrecord.assetid = asset.assetid)" +
        "where empid = ? and returndate is null";
    ResultSet rs = null;
    try {
      pst = con .prepareStatement(sql);
      pst.setInt(1,empId);
      rs = pst.executeQuery();
      while(rs.next()){
        LendRecord lend = new LendRecord();
        lend.setAssetId(rs.getInt("assetid"));
        lend.setAssetName(rs.getString("assetName"));
        list.add(lend);
      }
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }finally{
      DBAccess.closeResultSet(rs);
      DBAccess.closeStatement(pst);
      DBAccess.closeConnection(con);
    }
    return list ;
  }
}