houseinfodaoimpl.java

本程序主要实现对管理系统的初步管理

package org.ads123.goodsmanagers.dao.impl;

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

import org.JavaChina.myjdbc.ConnectPool;
import org.ads123.goodsmanagers.dao.HouseInfoDao;
import org.ads123.goodsmanagers.dto.HouseInfo;

public class HouseInfoDaoImpl implements HouseInfoDao {

	public HouseInfo findHouseInfoDaoByH_no(String H_no) {
		HouseInfo houseInfo = null;
		Connection conn = ConnectPool.getInstance().getConnection();
		PreparedStatement pstm = null;
		ResultSet rst = null;
		String sql = "select H_no, H_type, C_name, R_use, R_nouse, H_big from house, rent, company where H_no = ? and house.H_no=rent.R_no and rent.C_no=company.C_no";
		try {
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, H_no);
			rst = pstm.executeQuery();
			if (rst.next()) {
				String h_no = rst.getString("H_no");
				String h_type = rst.getString("H_type");
				String c_name = rst.getString("C_name");
				float r_use = rst.getFloat("R_use");
				float r_nouse = rst.getFloat("R_nouse");
				float h_big = rst.getFloat("H_big");
				houseInfo = new HouseInfo(h_no, h_type, c_name, r_use, r_nouse,
						h_big);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rst != null) {
					rst.close();
				}
				if (pstm != null) {
					pstm.close();
				}
				if (conn != null && !conn.isClosed()) {
					ConnectPool.getInstance().destroyConnection(conn);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return houseInfo;
	}

	public ArrayList<HouseInfo> findAllHouseInfo() {
		ArrayList<HouseInfo> allHouseList = new ArrayList<HouseInfo>();
		HouseInfo houseInfo = null;

		Connection conn = ConnectPool.getInstance().getConnection();
		PreparedStatement pstm = null;
		ResultSet rst = null;
		String sql = "select H_no, H_type, C_name, R_use, R_nouse, H_big from house, rent, company where house.H_no=rent.R_no and rent.C_no=company.C_no order by H_no";
		try {
			pstm = conn.prepareStatement(sql);
			rst = pstm.executeQuery();
			while (rst.next()) {
				String h_no = rst.getString("H_no");
				String h_type = rst.getString("H_type");
				String c_name = rst.getString("C_name");
				float r_use = rst.getFloat("R_use");
				float r_nouse = rst.getFloat("R_nouse");
				float h_big = rst.getFloat("H_big");
				houseInfo = new HouseInfo(h_no, h_type, c_name, r_use, r_nouse,
						h_big);
				allHouseList.add(houseInfo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rst != null) {
					rst.close();
				}
				if (pstm != null) {
					pstm.close();
				}
				if (conn != null && !conn.isClosed()) {
					ConnectPool.getInstance().destroyConnection(conn);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return allHouseList;
	}

	public ArrayList<HouseInfo> findAllHouseInfoByH_type(String H_type) {
		ArrayList<HouseInfo> list = new ArrayList<HouseInfo>();
		HouseInfo houseInfo = null;

		Connection conn = ConnectPool.getInstance().getConnection();
		PreparedStatement pstm = null;
		ResultSet rst = null;
		String sql = "select H_no, H_type, C_name, R_use, R_nouse, H_big from house, rent, company where H_type = ? and house.H_no=rent.R_no and rent.C_no=company.C_no order by H_no";
		try {
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, H_type);
			rst = pstm.executeQuery();
			while (rst.next()) {
				String h_no = rst.getString("H_no");
				String h_type = rst.getString("H_type");
				String c_name = rst.getString("C_name");
				float r_use = rst.getFloat("R_use");
				float r_nouse = rst.getFloat("R_nouse");
				float h_big = rst.getFloat("H_big");
				houseInfo = new HouseInfo(h_no, h_type, c_name, r_use, r_nouse,
						h_big);
				list.add(houseInfo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rst != null) {
					rst.close();
				}
				if (pstm != null) {
					pstm.close();
				}
				if (conn != null && !conn.isClosed()) {
					ConnectPool.getInstance().destroyConnection(conn);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	public ArrayList<HouseInfo> findAllHouseInfoByH_hire(String H_hire) {
		ArrayList<HouseInfo> list = new ArrayList<HouseInfo>();
		HouseInfo houseInfo = null;

		Connection conn = ConnectPool.getInstance().getConnection();
		PreparedStatement pstm = null;
		ResultSet rst = null;
		String sql = "select H_no, H_type, C_name, R_use, R_nouse, H_big from house, rent, company where H_hire = ? and house.H_no=rent.R_no and rent.C_no=company.C_no order by H_no";
		try {
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, H_hire);
			rst = pstm.executeQuery();
			while (rst.next()) {
				String h_no = rst.getString("H_no");
				String h_type = rst.getString("H_type");
				String c_name = rst.getString("C_name");
				float r_use = rst.getFloat("R_use");
				float r_nouse = rst.getFloat("R_nouse");
				float h_big = rst.getFloat("H_big");
				houseInfo = new HouseInfo(h_no, h_type, c_name, r_use, r_nouse,
						h_big);
				list.add(houseInfo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rst != null) {
					rst.close();
				}
				if (pstm != null) {
					pstm.close();
				}
				if (conn != null && !conn.isClosed()) {
					ConnectPool.getInstance().destroyConnection(conn);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	public static void main(String[] args) {
		HouseInfo houseInfo = new HouseInfoDaoImpl()
				.findHouseInfoDaoByH_no("1006");
		System.out.println(houseInfo.getH_no() + "  " + houseInfo.getC_name()
				+ "  " + houseInfo.getH_type() + "  " + houseInfo.getR_use()
				+ "  " + houseInfo.getR_nouse());
	}
}