assetdao.java

对2000元以上的资产管理

package com.cdaccp.dao;

import com.cdaccp.entity.Asset;
import com.util.DBAccess;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.List;
import java.util.ArrayList;
import java.sql.ResultSet;
import java.sql.*;

/**
 * 用于资产(Asset)类的增、删、改、查操作
 * <p>Title: 固定资产管理系统</p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2006</p>
 * <p>Company: 北大青鸟常德中心</p>
 * @author cdaccp.com
 * @version 1.0
 */
public class AssetDAO {

  /**
   * @param a Asset 包含资产数据的实体对象，从界面上收集
   * @return int 插入成功返回真，否则返回假
   */
  public boolean insert(Asset asset) {
    boolean result = false;
    String sql = "insert into asset (assetname,cateid,subcateid,price," +
        "purchasedate,status,useby,onuse,remarks) values (?,?,?,?,?,?,?,?,?)";
    Connection con = DBAccess.getConnection();
    PreparedStatement pst = null;
    try {
      pst = con.prepareStatement(sql);
      pst.setString(1, asset.getName());
      pst.setInt(2, asset.getCateId());
      pst.setInt(3, asset.getSubCateId());
      pst.setDouble(4, asset.getPrice());
      pst.setString(5, asset.getPurchaseDate());
      pst.setInt(6, asset.getStatus());
      pst.setInt(7, asset.getUseBy());
      pst.setInt(8, 0);
      pst.setString(9, asset.getRemarks());
      int i = pst.executeUpdate();
      if (i > 0) {
        result = true;
      }
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
    finally {
      DBAccess.closeStatement(pst);
      DBAccess.closeConnection(con);
    }
    return result;
  }

  String select =
      " select a.* ,b.catename ,c.catename as subname ,d.name from asset as a " +
      " inner join category as b on(a.cateid = b.cateid) " +
      " join category as c on(a.subcateid = c.cateid) " +
      "join operator as d on (a.useby =d.id)"; //查询语句；

  private Connection Connection;

  /**
   * 删除资产方法。同时要删除该资产对应的“借用”记录
   * @param assetId int 资产在数据库中的主键
   * @return boolean 返回删除是否成功，成功为true
   */
  public boolean deleteById(int assetId) {
    boolean result = false;
    Connection con = DBAccess.getConnection();
    PreparedStatement pst = null;
    String sql = " delete from lendrecord where assetid = ? ";
    String sql2 = "  delete from asset where assetid = ?  ";
    try {
      pst = con.prepareStatement(sql); //删除借用记录
      pst.setInt(1, assetId);
      pst.executeUpdate();
      pst = con.prepareStatement(sql2); //删除资产记录
      pst.setInt(1, assetId);
      int i = pst.executeUpdate();
      if (i > 0) {
        result = true;
      }
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
    finally {
      DBAccess.closeStatement(pst);
      DBAccess.closeConnection(con);
    }
    return result;
  }

  /**
   * 修改资产方法。
   * @param a Asset 修改后的资产对象
   * @return boolean 返回修改是否成功，成功为true
   */
  public boolean modify(Asset asset) {
    boolean result = false;
    Connection con = DBAccess.getConnection();
    PreparedStatement pst = null;
    String sql = "update asset set assetname = ?,cateid = ? ," +
        "subcateid =? ,price = ? , status = ? ,useBy = ? ,remarks = ? where assetid = ? ";
    try {
      pst = con.prepareStatement(sql);
      pst.setString(1, asset.getName());
      pst.setInt(2, asset.getCateId());
      pst.setInt(3, asset.getSubCateId());
      pst.setDouble(4, asset.getPrice());
      pst.setInt(5, asset.getStatus());
      pst.setInt(6, asset.getUseBy());
      pst.setString(7, asset.getRemarks());
      pst.setInt(8, asset.getId());
      int i = pst.executeUpdate();
      if (i > 0) {
        result = true;
      }
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
    finally {
      DBAccess.closeStatement(pst);
      DBAccess.closeConnection(con);
    }
    return result;
  }

  /**
   * 按主键编号读取一个资产。
   * @param assetId int 资产主键编号
   * @return Asset 资产实体对象。如果该编号的资产不存在，则返回null
   */
  public List loadById(int assetId) {
    List list = null;
    Asset asset = null;
    String sql = select + " where a.assetid = ?";
    Connection con = DBAccess.getConnection();
    PreparedStatement pst = null;
    ResultSet rs = null;
    try {
      pst = con.prepareStatement(sql);
      pst.setInt(1, assetId);
      rs = pst.executeQuery();
      list = new ArrayList();
      if (rs.next()) {
        list.add(addAssetOrver(rs)); //调用方法为Asset每个属性赋值，最后将asset对象加入到集合中
      }
      rs.close();
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
    finally {
      DBAccess.closeStatement(pst);
      DBAccess.closeConnection(con);
    }
    return list;
  }

  /**
   * 获取所有资产信息集合
   * 当有0或1的参数传入的时候查询没被借出或以借去的资产 并且状态为正常和资产信息
   * 其它参数传入查询所有资产信息
   * @return List
   */
  public List loadAllAsset(int onuse) {
    List list = new ArrayList();
    Asset asset = null;
    String sql = select;
    Connection con = DBAccess.getConnection();
    PreparedStatement pst = null;
    ResultSet rs = null;
    try {
      if (onuse != 1 && onuse != 0) {
        pst = con.prepareStatement(sql);
        rs = pst.executeQuery();
      }
      else {
        sql = select + " where a.onuse = ? and a.status = 0 ";
        pst = con.prepareStatement(sql);
        pst.setInt(1, onuse);
        rs = pst.executeQuery();
      }
      while (rs.next()) {
        list.add(addAssetOrver(rs)); //调用方法为Asset每个属性赋值，最后将asset对象加入到集合中
      }
      rs.close();
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
    finally {
      DBAccess.closeStatement(pst);
      DBAccess.closeConnection(con);
    }
    return list;
  }

  /**
   * 该方法可用于取得最后一个插入数据库的资产的编号（自动增长而来），
   * @return int 资产编号
   */
  private int getNewAssetId() {
    int newAssetId = -1;
    Connection conn = DBAccess.getConnection();
    PreparedStatement stmt = null;
    ResultSet rs = null;
    String sql = "select @@identity"; //取得数据库中最后插入的资产的编号(自动增长的值)
    try {
      stmt = conn.prepareStatement(sql);
      rs = stmt.executeQuery();
      if (rs.next()) {
        newAssetId = rs.getInt(1);
      }
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
    finally {
      DBAccess.closeConnection(conn);
    }
    return newAssetId;
  }

  /**
   * 根据大类编号读取该大类下所有资产
   * @param cateId int 大类编号
   * @return List 资产实体对象集合。如果该大类下一个资产都没有，则此list中没有元素
   */
  public List loadByCateId(int cateId) {
    List list = new ArrayList();
    String sql = select + " where a.cateid = " +
        "(select cateid from category where cateid = ? and parentid = 0) ";
    Connection con = DBAccess.getConnection();
    PreparedStatement pst = null;
    ResultSet rs = null;
    try {
      if (cateId == 0) {
        sql = select;
        pst = con.prepareStatement(sql);
        rs = pst.executeQuery();
      }
      else {
        pst = con.prepareStatement(sql);
        pst.setInt(1, cateId);
        rs = pst.executeQuery();
      }
      while (rs.next()) {
        list.add(addAssetOrver(rs)); //调用方法为Asset每个属性赋值，最后将asset对象加入到集合中
      }
      rs.close();
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
    finally {
      DBAccess.closeStatement(pst);
      DBAccess.closeConnection(con);
    }
    return list;
  }

  /**
   * 读取某个大类编号的一个小类编号下所有资产
   * @param cateId int 大类编号
   * @param subcateId int 该大类下的小类编号
   * @return List 资产实体对象集合。如果该类下一个资产都没有，则此list中没有元素
   */
//
  public List loadBySubcateId(int cateId, int subcateId) {
    List list = new ArrayList();
    String sql = select + "where a.cateid = ? and a.subcateid = ? ";
    Connection con = DBAccess.getConnection();
    PreparedStatement pst = null;
    ResultSet rs = null;
    try {
      pst = con.prepareStatement(sql);
      pst.setInt(1, cateId);
      pst.setInt(2, subcateId);
      rs = pst.executeQuery();
      while (rs.next()) {
        list.add(addAssetOrver(rs)); //调用方法为Asset每个属性赋值，最后将asset对象加入到集合中
      }
      rs.close();
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
    finally {
      DBAccess.closeStatement(pst);
      DBAccess.closeConnection(con);
    }

    return list;
  }

  /**
   * 根据资产名字模糊查找。例如传入“计”可以找到“计算机”和“计时器”等所有以“计”开头
   * 的资产。模糊查询的原理是使用SQL语句的通配符。 ... where name like '计%'
   * @param name String 资产名称片段
   * @return List 资产实体对象集合。如果没有找到任何资产，则此list中没有元素
   */
  public List loadByName(String name) {
    List list = new ArrayList();
    String sql = select + "where a.assetname like '" + name + "%' ";
    Connection con = DBAccess.getConnection();
    PreparedStatement pst = null;
    ResultSet rs = null;
    try {
      pst = con.prepareStatement(sql);
      rs = pst.executeQuery();
      while (rs.next()) {
        list.add(addAssetOrver(rs)); //调用方法为Asset每个属性赋值，最后将asset对象加入到集合中
      }
      rs.close();
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
    finally {
      DBAccess.closeStatement(pst);
      DBAccess.closeConnection(con);
    }
    return list;
  }

  /**
   * 查询指定状态(已借出或没借出)所有的资产
   * @return List 资产实体对象集合。如果没有找到任何资产，则此list中没有元素
   * @param onUse int 1—借出，0—未借出
   * @return List
   */
  public List loadByUse(int onUse) {
    List list = new ArrayList();
    Connection conn = DBAccess.getConnection(); //定义连接对象
    PreparedStatement pstmt = null; //定义SQL语句执行对象
    ResultSet rs = null; //定义结果集对象
    String sql = select + " where a.onuse =" + onUse;
    try {
      pstmt = conn.prepareStatement(sql);
      rs = pstmt.executeQuery(); //执行查询
      while (rs.next()) {
        list.add(addAssetOrver(rs)); //调用方法为Asset每个属性赋值，最后将asset对象加入到集合中
      }
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
    finally {
      DBAccess.closeConnection(conn);
    }
    return list; //返回集合对象
  }

  /**
   * 修改资产使用状态
   * @param args String[]
   */
  public boolean updateAssetOnuse(int assetid, int onuse) {
    boolean b = false;
    Connection con = DBAccess.getConnection();
    PreparedStatement pst = null;
    String sql = "update asset set onuse = ? where assetid = ?";
    try {
      pst = con.prepareStatement(sql);
      pst.setInt(1, onuse);
      pst.setInt(2, assetid);
      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 loadNumber(int id){
  List list = new ArrayList();
  Connection conn = DBAccess.getConnection(); //定义连接对象
    PreparedStatement pstmt = null; //定义SQL语句执行对象
    ResultSet rs = null; //定义结果集对象
    String sql = select + " where assetid like '"+id+"%'" ;
    try {
      pstmt = conn.prepareStatement(sql);
      rs = pstmt.executeQuery(); //执行查询
      while (rs.next()) {
        list.add(addAssetOrver(rs)); //调用方法为Asset每个属性赋值，最后将asset对象加入到集合中
      }
    }
    catch (Exception ex) {
      ex.printStackTrace();
    }
    finally {
      DBAccess.closeConnection(conn);
    }

  return list;
}
  /**
   * 用来为资产表的每个属性赋值
   * @param rs ResultSet
   * @return Asset 返回一条资产信息
   * @throws SQLException
   */
  public Asset addAssetOrver(ResultSet rs) throws SQLException {
    Asset asset = new Asset();
    asset.setId(rs.getInt("assetId"));
    asset.setName(rs.getString("assetname"));
    asset.setCateId(rs.getInt("cateId"));
    asset.setSubCateId(rs.getInt("subcateId"));
    asset.setPrice(rs.getDouble("price"));
    asset.setPurchaseDate(rs.getString("purchaseDate"));
    asset.setStatus(rs.getInt("status"));
    asset.setUseBy(rs.getInt("useby"));
    asset.setRemarks(rs.getString("remarks"));
    asset.setOnUse(rs.getInt("onUse"));
    asset.setCateName(rs.getString("catename"));
    asset.setSubName(rs.getString("subname"));
    asset.setUseByName(rs.getString("name"));
    return asset;
  }
}