assetdao.java

来自「struts做得固定资产管理系统 带毕业论文完整版」· Java 代码 · 共 281 行

package com.qrsx.appcam.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.qrsx.appcam.model.Asset;

public class AssetDAO extends BaseDAO {

	/**
	 * 创建资产
	 * 
	 * @param Asset
	 */
	public void create(Asset asset) throws SQLException {

		String sql = " Insert Into Asset( code, name ,assetTypeId, model, clientId,leaveFactoryDate, buyDate ,life, price , storageId,addTypeId ,state) Values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

		// 设置参数值
		ps = conn.prepareStatement(sql);
		ps.setString(1, asset.getCode());
		ps.setString(2, asset.getName());
		ps.setInt(3, asset.getAssetTypeId());
		ps.setString(4, asset.getModel());
		ps.setInt(5, asset.getClientId());
		ps.setString(6, asset.getLeaveFactoryDate());
		ps.setString(7, asset.getBuyDate());
		ps.setInt(8, asset.getLife());
		ps.setDouble(9, asset.getPrice());
		ps.setInt(10, asset.getStorageId());
		ps.setInt(11, asset.getAddTypeId());
		ps.setInt(12, asset.getState());

		// 执行
		ps.executeUpdate();
	}

	/**
	 * 更新资产
	 * 
	 * @param Asset
	 */
	public void update(Asset asset) throws SQLException {

		String sql = " update Asset set code=?, name =?,assetTypeId=?, model=?, "
				+ "clientId=?, leaveFactoryDate=?, buyDate=? ,life=?, price=? ,"
				+ " storageId=?, addTypeId=? ,state=? where id=? ";

		// 设置参数值
		ps = conn.prepareStatement(sql);

		ps.setString(1, asset.getCode());
		ps.setString(2, asset.getName());
		ps.setInt(3, asset.getAssetTypeId());
		ps.setString(4, asset.getModel());
		ps.setInt(5, asset.getClientId());
		ps.setString(6, asset.getLeaveFactoryDate());
		ps.setString(7, asset.getBuyDate());
		ps.setInt(8, asset.getLife());
		ps.setDouble(9, asset.getPrice());
		ps.setInt(10, asset.getStorageId());
		ps.setInt(11, asset.getAddTypeId());
		ps.setInt(12, asset.getState());
		ps.setInt(13, asset.getId());
		// 执行
		ps.executeUpdate();
	}

	/**
	 * 更新资产状态
	 * 
	 * @param asset
	 * @throws SQLException
	 */
	public void lendUpdate(Integer assetId) throws SQLException {

		String sql = "Update asset set state=? where id=?";
		// 设置参数并执行更新操作
		ps = conn.prepareStatement(sql);
		ps.setInt(1, 1);// 1表示已经借出
		ps.setInt(2, assetId);
		ps.executeUpdate();
	}

	/**
	 * 更新资产的状态
	 * 
	 * @param assetId
	 * @throws SQLException
	 */
	public void givebackUpdate(Integer assetId) throws SQLException {

		String sql = "Update asset set state=? where id=?";
		// 设置参数并执行更新操作
		ps = conn.prepareStatement(sql);
		ps.setInt(1, 2);// 2表示已经归还
		ps.setInt(2, assetId);
		ps.executeUpdate();
	}

	/**
	 * 删除资产
	 * 
	 * @param AssetId
	 * 
	 * @return 删除的资产数目
	 */
	public int delete(Integer assetId) throws SQLException {
		String sql = "Delete from Asset where id=? ";
		// 设置参数值
		ps = conn.prepareStatement(sql);
		ps.setInt(1, assetId);
		// 执行
		int total = ps.executeUpdate();
		return total;
	}

	/**
	 * 根据主键检索资产
	 * 
	 * @param AssetId
	 *            要检索的资产户主键
	 * @return Asset
	 */
	public Asset findById(Integer assetId) throws SQLException {
		String sql = "Select * from Asset where id=? ";
		// 设置参数值并执行查询
		ps = conn.prepareStatement(sql);
		ps.setInt(1, assetId);
		ResultSet rs = ps.executeQuery();

		ClientDAO factoryDao = new ClientDAO();
		StorageDAO storageDao = new StorageDAO();
		AddTypeDAO addTypeDao = new AddTypeDAO();
		AssetTypeDAO assetTypeDao = new AssetTypeDAO();

		Asset asset = null;
		if (rs.next()) {
			asset = new Asset();
			asset.setId(rs.getInt("id"));
			asset.setCode(rs.getString("code"));
			asset.setName(rs.getString("name"));
			asset.setAssetTypeId(rs.getInt("assetTypeId"));
			asset.setModel(rs.getString("model"));
			asset.setClientId(rs.getInt("clientId"));
			asset.setLeaveFactoryDate(rs.getString("leaveFactoryDate"));
			asset.setBuyDate(rs.getString("buyDate"));
			asset.setLife(rs.getInt("life"));
			asset.setPrice(rs.getDouble("price"));

			asset.setStorageId(rs.getInt("storageId"));
			asset.setAddTypeId(rs.getInt("addTypeId"));
			asset.setState(rs.getInt("state"));
			asset.setFactory(factoryDao.findById(rs.getInt("clientId")));
			asset.setStorage(storageDao.findById(rs.getInt("storageId")));
			asset.setAddType(addTypeDao.findById(rs.getInt("addTypeId")));
			asset.setAssetType(assetTypeDao.findById(rs.getInt("assetTypeId")));
		}

		return asset;
	}

	/**
	 * 检索资产
	 * 
	 * @param Asset
	 * @return List<Asset>
	 */
	public List<Asset> findAll() throws SQLException {
		String sql = "Select * from Asset ";
		// 执行查询
		ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		// 将查询结果组装至Asset实体对象
		Asset asset = null;
		List<Asset> list = new ArrayList<Asset>();
		ClientDAO factoryDao = new ClientDAO();
		StorageDAO storageDao = new StorageDAO();
		AddTypeDAO addTypeDao = new AddTypeDAO();
		AssetTypeDAO assetTypeDao = new AssetTypeDAO();

		while (rs.next()) {
			asset = new Asset();
			asset.setId(rs.getInt("id"));
			asset.setCode(rs.getString("code"));
			asset.setName(rs.getString("name"));
			asset.setAssetTypeId(rs.getInt("assetTypeId"));
			asset.setModel(rs.getString("model"));
			asset.setClientId(rs.getInt("clientId"));
			asset.setLeaveFactoryDate(rs.getString("leaveFactoryDate"));
			asset.setBuyDate(rs.getString("buyDate"));
			asset.setLife(rs.getInt("life"));
			asset.setPrice(rs.getDouble("price"));
			asset.setStorageId(rs.getInt("storageId"));
			asset.setAddTypeId(rs.getInt("addTypeId"));
			asset.setState(rs.getInt("state"));
			asset.setFactory(factoryDao.findById(rs.getInt("clientId")));
			asset.setStorage(storageDao.findById(rs.getInt("storageId")));
			asset.setAddType(addTypeDao.findById(rs.getInt("addTypeId")));
			asset.setAssetType(assetTypeDao.findById(rs.getInt("assetTypeId")));
			list.add(asset);
		}

		return list;
	}

	/**
	 * 根据条件检索资产
	 * 
	 * @param Asset
	 * @return List<Asset>
	 */
	public List<Asset> list(Asset asset) throws SQLException {
		String sql = "Select * from Asset where 1=1 ";

		// 根据资产代码检索
		String code = asset.getCode();
		if (code != null && !code.equals("")) {
			sql = sql + "  and code like '%" + code + "%' ";
		}
		// 根据资产名称检索
		String name = asset.getName();
		if (name != null && !name.equals("")) {
			sql = sql + "  and name like '%" + name + "%' ";
		}
		// 根据资产型号检索
		String model = asset.getModel();
		if (model != null && !model.equals("")) {
			sql = sql + "  and model like '%" + model + "%' ";
		}
		// 根据资产类型名称检索
		Integer assetTypeId = asset.getAssetTypeId();
		if (assetTypeId != null) {
			sql = sql + "  and assetTypeId = " + assetTypeId + "  ";
		}
		// 根据资产的状态检索
		Integer state = asset.getState();
		if (state != null) {
			sql = sql + "  and state = " + state + "  ";
		}
		// 执行查询
		ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();

		// 将查询结果组装至Asset实体对象
		Asset asset1 = null;
		List<Asset> list = new ArrayList<Asset>();

		ClientDAO factoryDao = new ClientDAO();
		StorageDAO storageDao = new StorageDAO();
		AddTypeDAO addTypeDao = new AddTypeDAO();
		AssetTypeDAO assetTypeDao = new AssetTypeDAO();
		while (rs.next()) {

			asset1 = new Asset();
			asset1.setId(rs.getInt("id"));
			asset1.setCode(rs.getString("code"));
			asset1.setName(rs.getString("name"));
			asset1.setAssetTypeId(rs.getInt("assetTypeId"));
			asset1.setModel(rs.getString("model"));
			asset1.setClientId(rs.getInt("clientId"));
			asset1.setLeaveFactoryDate(rs.getString("leaveFactoryDate"));
			asset1.setBuyDate(rs.getString("buyDate"));
			asset1.setLife(rs.getInt("life"));
			asset1.setPrice(rs.getDouble("price"));
			asset1.setStorageId(rs.getInt("storageId"));
			asset1.setAddTypeId(rs.getInt("addTypeId"));
			asset1.setState(rs.getInt("state"));
			asset1.setFactory(factoryDao.findById(rs.getInt("clientId")));
			asset1.setStorage(storageDao.findById(rs.getInt("storageId")));
			asset1.setAddType(addTypeDao.findById(rs.getInt("addTypeId")));
			asset1
					.setAssetType(assetTypeDao.findById(rs
							.getInt("assetTypeId")));
			list.add(asset1);
		}
		return list;
	}
}