assetclassdaoimpl.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 java.util.Map;
import java.util.TreeMap;

import com.shenlong.assetmanage.dao.AssetClassDao;
import com.shenlong.assetmanage.dbhelp.ConnectionFactory;
import com.shenlong.assetmanage.domain.AssetClass1;
import com.shenlong.assetmanage.domain.AssetClass2;



public class AssetClassDaoImpl implements AssetClassDao {
	//－－－－－－－增加主类别－－－－－－－－－－－－－－
	public int saveAssetClass1(AssetClass1 ac1) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		int row = 0;
		try {
			con = ConnectionFactory.getConnection();
			String sql = "INSERT INTO asset_class1 (asset_class1_name, asset_class1_memo) VALUES (?, ?)";
			ps = con.prepareStatement(sql);
			ps.setString(1, ac1.getName());
			ps.setString(2, ac1.getMemo());
			row = ps.executeUpdate();
		} finally {
			ps.close();
			con.close();
		}
		return row;
	}
	//－－－－－－－－－－－－获得所有的主类别－－－－－－－－－－－－－－
	public List<AssetClass1> getAllAssetClass1s() throws SQLException{		
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;		
		List<AssetClass1> allClass1s = new ArrayList<AssetClass1>();
		try {
			con = ConnectionFactory.getConnection();			
			String sql = "SELECT asset_class1_id, asset_class1_name, asset_class1_memo FROM asset_class1 where tag=1";			
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();			
			while (rs.next()) {
				AssetClass1 ac = new AssetClass1(rs.getInt("asset_class1_id"),
						rs.getString("asset_class1_name"),
						rs.getString("asset_class1_memo")
				);
				allClass1s.add(ac);
			}
		} finally {
			rs.close();
			ps.close();
			con.close();
		}
		return allClass1s;
	}
	//－－－－－获得所有的类别－－－－－－－－－－－－　
	public synchronized Map<String, List<AssetClass2>> getAllClasses() throws SQLException{
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ResultSet rs1 = null;
		Map<String, List<AssetClass2>> map = new TreeMap<String, List<AssetClass2>>();
		try {			
			con = ConnectionFactory.getConnection();
			String sql = "SELECT asset_class1_id, asset_class1_name " +
					"FROM asset_class1 " +
					"WHERE tag = 1";			
			ps = con.prepareStatement(sql);			
			rs = ps.executeQuery();
			while(rs.next()){				
				int i = rs.getInt("asset_class1_id");				
				sql = "SELECT asset_class2_id, asset_class2_name " +
						"FROM asset_class2 " +
						"WHERE tag = 1 AND asset_class1_id = ?";
				ps = con.prepareStatement(sql);
				ps.setInt(1, i);
				rs1 = ps.executeQuery();
				List<AssetClass2> class2 = new ArrayList<AssetClass2>();
									
				while(rs1.next()){
					AssetClass2 ac2 = new AssetClass2();
					ac2.setId(rs1.getInt("asset_class2_id"));
					ac2.setName(rs1.getString("asset_class2_name"));
					class2.add(ac2);
				}				
				map.put(rs.getString("asset_class1_name"), class2);
			}
		}finally{
			rs1.close();
			rs.close();
			ps.close();
			con.close();
		}
		return map;
	}
	//－－－－－－－－－－－－获得次满足条件的类别的个数－－－－－
	public int getClassCountByName(String className) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int count = 0;
		try {
			con = ConnectionFactory.getConnection();			
			
			String sql = "SELECT count(asset_class_id) " +
					"FROM asset_class_view " +
					"WHERE tag = 1 ";
			
			if(!className.equalsIgnoreCase("*")){
				sql += "AND asset_class_name LIKE ?";
			}			
			ps = con.prepareStatement(sql);
			if(!className.equalsIgnoreCase("*")) {		
				ps.setString(1, "%" + className + "%");
			}			
			rs = ps.executeQuery();
			while (rs.next()) {
				count = rs.getInt(1);
			}
		} finally {
			rs.close();
			ps.close();
			con.close();
		}
		return count;
	}
	//－－－－－－查询类别－－－－－－－－－－－－－
	public List<AssetClass2> queryClassByName(String className, int page, int total) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<AssetClass2> assetClasses = new ArrayList<AssetClass2>();
		int rowStart = (page - 1) * 5 + 1;
		int rowEnd = page * 5;
		rowEnd = (rowEnd >= total) ? 0 : total - rowEnd;
		try {
			con = ConnectionFactory.getConnection();
			String sql1 = 	"SELECT * FROM asset_class_view " +
							"WHERE parent_name + asset_class_name NOT IN ( " +
								"SELECT TOP " + (rowStart - 1) + " parent_name + asset_class_name " +
								"FROM asset_class_view ";

			String sql2 = 		"ORDER BY parent_name + asset_class_name) " +
							"AND parent_name + asset_class_name NOT IN ( " +
								"SELECT TOP " + rowEnd + " parent_name + asset_class_name " +
								"FROM asset_class_view ";

			String sql3 = 		"ORDER BY parent_name + asset_class_name DESC) ";
												
			String sql4 = 	"ORDER BY parent_name + asset_class_name"; 
			String sql = null;
			if(!className.equalsIgnoreCase("*")) {
				sql = sql1 + "WHERE tag = 1 AND asset_class_name LIKE ? " +
					sql2 + "WHERE tag = 1 AND asset_class_name LIKE ? " +
					sql3 + "AND tag = 1 AND asset_class_name LIKE ? " +
					sql4;
			} else {
				sql = sql1 + "WHERE tag = 1 " +
					sql2 + "WHERE tag = 1 " +
					sql3 + "AND tag = 1 " +
					sql4;
			}
			ps = con.prepareStatement(sql);
			if(!className.equalsIgnoreCase("*")) {	
				ps.setString(1, "%" + className + "%");
				ps.setString(2, "%" + className + "%");
				ps.setString(3, "%" + className + "%");
			}
			rs = ps.executeQuery();
			while (rs.next()) {				
				AssetClass2 assetClass = new AssetClass2(
						rs.getInt("asset_class_id"), rs.getString("parent_name"),
						rs.getString("asset_class_name"), (rs.getString("asset_class_memo"))
				);
				assetClass.setParentId(rs.getInt("parent_id"));
				assetClasses.add(assetClass);
			}		
		} finally {
			rs.close();
			ps.close();
			con.close();
		}
		return assetClasses;
	}
	//－－－－－－－修改次类别－－－－－－－－－
	public int modifyAssetClass1(AssetClass1 assetClass1 ) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		int row = 0;
		try{
			con = ConnectionFactory.getConnection();
			String sql = "UPDATE asset_class1 " +
					"SET asset_class1_name=?, asset_class1_memo=? " +
					"WHERE asset_class1_id=? and tag=1";
			ps = con.prepareStatement(sql);
			ps.setString(1, assetClass1.getName());
			ps.setString(2, assetClass1.getMemo());
			ps.setInt(3, assetClass1.getId());
			row = ps.executeUpdate();
		} finally {
			ps.close();
			con.close();			
		}	
		return row;
	}
	
	public int deleteAssetClass1ById(int id) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		int row = 0;
		try{
			con = ConnectionFactory.getConnection();
			String sql = "UPDATE asset_class1 " +
					"SET tag = 0 " +
					"WHERE asset_class1_id=? and tag=1";
			ps = con.prepareStatement(sql);
			ps.setInt(1, id);
			row = ps.executeUpdate();
		} finally {
			ps.close();
			con.close();			
		}	
		return row;
	}
}