assetclass2daoimpl.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.AssetClass2Dao;
import com.shenlong.assetmanage.dbhelp.ConnectionFactory;
import com.shenlong.assetmanage.domain.AssetClass2;


public class AssetClass2DaoImpl implements AssetClass2Dao{
	
	public int saveAssetClass2(AssetClass2 ac2) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		int row = 0;
		try {
			con = ConnectionFactory.getConnection();
			String sql = "INSERT INTO asset_class2 (asset_class2_name, asset_class1_id, asset_class2_memo) " +
					"VALUES (?,?,?)";
			ps = con.prepareStatement(sql);
			ps.setString(1, ac2.getName());
			ps.setInt(2, ac2.getParentId());
			ps.setString(3, ac2.getMemo());
			row = ps.executeUpdate();
		} finally {
			ps.close();
			con.close();
		}
		return row;
	}
	
	public List<AssetClass2> getAllAssetClass2s() throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<AssetClass2> allClass2s = new ArrayList<AssetClass2>();
		try {
			con = ConnectionFactory.getConnection();
			String sql = "SELECT asset_class2_id, asset_class1_id, asset_class2_name, asset_class2_memo " +
					"FROM asset_class2 where tag=1";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				AssetClass2 ac = new AssetClass2(rs.getInt("asset_class2_id"),
						rs.getInt("asset_class1_id"),
						rs.getString("asset_class2_name"),
						rs.getString("asset_class2_memo"));
				allClass2s.add(ac);
			}
		} finally {
			rs.close();
			ps.close();
			con.close();
		}
		return allClass2s;
	}
	
	public List<AssetClass2> getAllAssetClass(String split) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<AssetClass2> allClass2s = new ArrayList<AssetClass2>();		
		try {			
			con = ConnectionFactory.getConnection();			
			String sql = "SELECT a.asset_class2_id, a.asset_class1_id, b.asset_class1_name, a.asset_class2_name, a.asset_class2_memo " +
					"FROM asset_class2 a, asset_class1 b " +
					"WHERE  a.asset_class1_id=b.asset_class1_id and a.tag = 1 and b.tag = 1" +
					"ORDER BY b.asset_class1_id";			
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				AssetClass2 ac = new AssetClass2(rs.getInt("asset_class2_id"),
						rs.getInt("asset_class1_id"), 
						rs.getString("asset_class1_name") + split + rs.getString("asset_class2_name"),
						rs.getString("asset_class2_memo"));				
				allClass2s.add(ac);
			}
		} finally {
			rs.close();
			ps.close();
			con.close();
		}
		return allClass2s;
	}
	
	public int modifyAssetClass2(AssetClass2 assetClass2 ) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		int row = 0;
		try{
			con = ConnectionFactory.getConnection();
			String sql = "UPDATE asset_class2 " +
					"SET asset_class1_id=?, asset_class2_name=?, asset_class2_memo=? " +
					"WHERE  tag = 1 and asset_class2_id=?";
			ps = con.prepareStatement(sql);
			ps.setInt(1, assetClass2.getParentId());
			ps.setString(2, assetClass2.getName());
			ps.setString(3, assetClass2.getMemo());
			ps.setInt(4, assetClass2.getId());
			row = ps.executeUpdate();
		} finally {
			ps.close();
			con.close();			
		}	
		return row;
	}
	
	public int deleteAssetClass2ById(int id) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		int row = 0;
		try{
			con = ConnectionFactory.getConnection();
			String sql = "UPDATE asset_class2 " +
					"SET tag = 0 " +
					"WHERE asset_class2_id=?";
			ps = con.prepareStatement(sql);
			ps.setInt(1, id);
			row = ps.executeUpdate();
		} finally {
			ps.close();
			con.close();			
		}	
		return row;
	}
	
	public int getAssetClass2CountByClass1(int class1Id) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int row = 0;
		try{
			con = ConnectionFactory.getConnection();
			String sql = "SELECT count(asset_class2_id) AS count FROM asset_class2 " +
					"WHERE asset_class1_id = ? and tag = 1 ";
			ps = con.prepareStatement(sql);
			ps.setInt(1, class1Id);
			rs = ps.executeQuery();
			while(rs.next()){
				row = rs.getInt("count");
			}
		} finally {
			rs.close();
			ps.close();
			con.close();			
		}
		return row;
	}
}