assetdaoimpl.java

固定资产管理系统包括一个基本的部分

package com.shenlong.assetmanage.daoImplSQLserver;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.shenlong.assetmanage.dao.AssetDao;
import com.shenlong.assetmanage.dbhelp.ConnectionFactory;
import com.shenlong.assetmanage.domain.Asset;



public class AssetDaoImpl implements AssetDao {

	//－－－－－－－根据ＩＤ查询固定资产－－－－－－－－－－－－－－
	public Asset queryAssetById(int id) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Asset asset = null;
		try {
			con = ConnectionFactory.getConnection();
			String sql = "SELECT asset_id, asset_class2_id, asset_name, asset_type," +
					"asset_value, asset_purchase_date, asset_state_id, asset_emp_id," +
					"asset_memo FROM assets WHERE asset_id=? and tag=1";
			ps = con.prepareStatement(sql);			
			ps.setInt(1, id);
			rs = ps.executeQuery();			
			while (rs.next()) {
				asset = new Asset(rs.getInt("asset_id"), rs.getInt("asset_class2_id"), rs.getString("asset_name"),
						rs.getString("asset_type"), rs.getString("asset_value"), rs.getString("asset_purchase_date"),
						rs.getInt("asset_state_id"), rs.getInt("asset_emp_id"),	(rs.getString("asset_memo"))
				);
			}		
		} finally {
			rs.close();
			ps.close();
			con.close();
		}
		return asset;
	}
	//－－－－－－－－－－增加固定资产－－－－－－－－－－－－－
	public int saveNewAsset (Asset asset) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		int update = 0;

		try {
			con = ConnectionFactory.getConnection();
			con.setAutoCommit(false);
			String sql = "INSERT INTO assets (asset_class2_id, asset_name," +
					"asset_type, asset_value, asset_purchase_date, asset_state_id, asset_memo) " +
					"VALUES (?,?,?,?,?,?,?)";
			ps = con.prepareStatement(sql);
			ps.setInt(1, asset.getClass2Id());
			ps.setString(2, asset.getName());
			ps.setString(3, asset.getType());
			
			ps.setString(4, asset.getValue());
			ps.setString(5, asset.getPurchaseDate());
			ps.setInt(6, 1);
			ps.setString(7, asset.getMemo());
			update = ps.executeUpdate();
			con.commit();
		} catch(SQLException e) {
			con.rollback();
			e.printStackTrace();			
		} finally {			
			ps.close();
			con.close();
		}
		return update;
	}
	//－－－－－－根据名称查询固定资产－－－－－－－－－－－－－－
	public List<Asset> queryAssetByName(String name) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Asset> assets = new ArrayList<Asset>();
		try {
			con = ConnectionFactory.getConnection();
			
			String sql = "SELECT a.asset_id, e.asset_class1_name, a.asset_class2_id, " + 
					" b.asset_class2_name,a.asset_name, a.asset_type, a.asset_value, " + 
					"a.asset_purchase_date, a.asset_state_id, c.asset_state_name, a.asset_emp_id, " +
					"d.emp_name, a.asset_memo " +
					"FROM assets a " +
					"INNER JOIN asset_class2 b ON a.asset_class2_id = b.asset_class2_id " +
					"INNER JOIN asset_state c ON a.asset_state_id = c.asset_state_id " +
					"INNER JOIN asset_class1 e ON e.asset_class1_id = b.asset_class1_id " +
					"LEFT JOIN employee d ON a.asset_emp_id = d.emp_id ";
			
			if(!name.equalsIgnoreCase("*")){
				sql += "WHERE a.asset_name LIKE ? and a.tag=1";
			}
			ps = con.prepareStatement(sql);
			if(!name.equalsIgnoreCase("*")) {				
				ps.setString(1, "%" + name + "%");
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				Asset asset;
		
				asset = new Asset(rs.getInt("asset_id"), rs.getInt("asset_class2_id"), 
						rs.getString("asset_name"),	rs.getString("asset_type"),
						rs.getString("asset_value"), rs.getString("asset_purchase_date").substring(0,10),
						rs.getInt("asset_state_id"), rs.getInt("asset_emp_id"),
						(rs.getString("asset_memo"))
				);			
				asset.setClassName(rs.getString("asset_class1_name") + "��" + rs.getString("asset_class2_name"));
				asset.setState(rs.getString("asset_state_name"));
				if(rs.getString("emp_name")==null){
					asset.setEmpName("----");
				}else{
					asset.setEmpName(rs.getString("emp_name"));
				}
				 
				assets.add(asset);
			}
		} finally {
			rs.close();
			ps.close();
			con.close();
		}
		return assets;
	}
	//－－－－－－－－－－－－－－根据名称分页显示固定资产－－－－－－
	public List<Asset> queryAssetByName(String name, int page, int total) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Asset> assets = new ArrayList<Asset>();
		int rowStart = (page - 1) * 5 + 1;
		int rowEnd = page * 5;
		rowEnd = (rowEnd >= total) ? 0 : total - rowEnd;
		try {
			con = ConnectionFactory.getConnection();
	
			String sql1 = "SELECT a.asset_id, e.asset_class1_name, a.asset_class2_id, " + 
						"b.asset_class2_name,a.asset_name, a.asset_type, a.asset_value, " + 
						"a.asset_purchase_date, a.asset_state_id, c.asset_state_name, a.asset_emp_id, " + 
						"d.emp_name, a.asset_memo " +
					"FROM assets a " +
					"INNER JOIN asset_class2 b ON a.asset_class2_id = b.asset_class2_id " + 
					"INNER JOIN asset_state c ON a.asset_state_id = c.asset_state_id " +
					"INNER JOIN asset_class1 e ON e.asset_class1_id = b.asset_class1_id " + 
					"LEFT JOIN employee d ON a.asset_emp_id = d.emp_id " +
				"WHERE a.asset_id NOT IN (SELECT TOP " + (rowStart - 1) + " a.asset_id FROM assets a " +
							"INNER JOIN asset_class2 b ON a.asset_class2_id = b.asset_class2_id " + 
							"INNER JOIN asset_state c ON a.asset_state_id = c.asset_state_id " +
							"INNER JOIN asset_class1 e ON e.asset_class1_id = b.asset_class1_id " +
							"LEFT JOIN employee d ON a.asset_emp_id = d.emp_id ";

			String sql2 = "ORDER BY a.asset_id) " +
						"AND a.asset_id NOT IN (SELECT TOP " + rowEnd + " a.asset_id FROM assets a " +
							"INNER JOIN asset_class2 b ON a.asset_class2_id = b.asset_class2_id " + 
							"INNER JOIN asset_state c ON a.asset_state_id = c.asset_state_id " +
							"INNER JOIN asset_class1 e ON e.asset_class1_id = b.asset_class1_id " + 
							"LEFT JOIN employee d ON a.asset_emp_id = d.emp_id ";
			String sql3 = "ORDER BY a.asset_id DESC) ";
												
			String sql4 = "ORDER BY a.asset_id "; 
			String sql = null;
			if(!name.equalsIgnoreCase("*")) {
				sql = sql1 + "WHERE a.tag = 1 AND a.asset_name LIKE ? " +
					sql2 + "WHERE a.tag = 1 AND  a.asset_name LIKE ? " +
					sql3 + "AND a.tag = 1 AND  a.asset_name LIKE ? " +
					sql4;
			} else {
				sql = sql1 + "WHERE a.tag = 1 " +
					sql2 + "WHERE a.tag = 1 " +
					sql3 + "AND a.tag = 1 " +
					sql4;
			}
			ps = con.prepareStatement(sql);
			if(!name.equalsIgnoreCase("*")) {	
				ps.setString(1, "%" + name + "%");
				ps.setString(2, "%" + name + "%");
				ps.setString(3, "%" + name + "%");
			}
			rs = ps.executeQuery();
			while (rs.next()) {				
				Asset asset = new Asset(rs.getInt("asset_id"), rs.getInt("asset_class2_id"), 
						rs.getString("asset_name"),	rs.getString("asset_type"),
						rs.getString("asset_value"), rs.getString("asset_purchase_date").substring(0,10),
						rs.getInt("asset_state_id"), rs.getInt("asset_emp_id"),
						(rs.getString("asset_memo"))
				);
				asset.setClassName(rs.getString("asset_class1_name") + " - " + rs.getString("asset_class2_name"));
				asset.setState(rs.getString("asset_state_name"));
				if(rs.getString("emp_name")==null){
					asset.setEmpName("----");
				}else{
					asset.setEmpName(rs.getString("emp_name"));
				}
				assets.add(asset);
			}		
		} finally {
			rs.close();
			ps.close();
			con.close();
		}
		return assets;
	}
	//－－－－－－－根据固定资产的名称查询满足条件的固定资产的数量－－－－－－
	public int getAssetCountByName(String name) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int count = 0;
		try {
			con = ConnectionFactory.getConnection();			
			
			String sql = "SELECT count(a.asset_id) " +
					"FROM assets a " +
					"INNER JOIN asset_class2 b ON a.asset_class2_id = b.asset_class2_id " +
					"INNER JOIN asset_state c ON a.asset_state_id = c.asset_state_id " +
					"INNER JOIN asset_class1 e ON e.asset_class1_id = b.asset_class1_id " +
					"LEFT JOIN employee d ON a.asset_emp_id = d.emp_id " +
					"WHERE a.tag = 1";
			
			if(!name.equalsIgnoreCase("*")){
				sql += "AND a.asset_name LIKE ?";
			}
			ps = con.prepareStatement(sql);
			if(!name.equalsIgnoreCase("*")) {		
				ps.setString(1, "%" + name + "%");
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				count = rs.getInt(1);
			}
		} finally {
			rs.close();
			ps.close();
			con.close();
		}
		return count;
	}
	//－－－－－－根据次类别查询固定资产，并分页显示－－－－－－－
	public List<Asset> queryAssetByClass(int class2Id, int page, int total) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Asset> assets = new ArrayList<Asset>();
		int rowStart = (page - 1) * 5 + 1;
		int rowEnd = page * 5;
		rowEnd = (rowEnd >= total) ? 0 : total - rowEnd;
		try {
			con = ConnectionFactory.getConnection();