companyinfodaoimpl.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.CompanyInfoDao;
import org.ads123.goodsmanagers.dto.CompanyInfo;

public class CompanyInfoDaoImpl implements CompanyInfoDao {

	public ArrayList<CompanyInfo> findAllCompanyInfo() {
		ArrayList<CompanyInfo> companyInfolist = new ArrayList<CompanyInfo>();
		CompanyInfo companyInfo = null;

		Connection conn = ConnectPool.getInstance().getConnection();
		PreparedStatement pstm = null;
		ResultSet rst = null;
		String sql = "select company.C_no, C_name, C_lead, C_conn, C_add, C_mode, C_ind, M_name, R_no, G_name, R_use, R_nouse from house, rent, company where C_name != '空' and house.H_no=rent.R_no and rent.C_no=company.C_no order by company.C_no;";
		try {
			pstm = conn.prepareStatement(sql);
			rst = pstm.executeQuery();
			while (rst.next()) {
				String c_no = rst.getString("company.C_no");
				String c_name = rst.getString("C_name");
				String c_lead = rst.getString("C_lead");
				String c_conn = rst.getString("C_conn");
				String c_add = rst.getString("C_add");
				String c_mode = rst.getString("C_mode");
				String c_ind = rst.getString("C_ind");
				String m_name = rst.getString("M_name");
				String r_no = rst.getString("R_no");
				String g_name = rst.getString("G_name");
				float r_use = rst.getFloat("R_use");
				float r_nouse = rst.getFloat("R_nouse");
				companyInfo = new CompanyInfo(c_no, c_name, c_lead, c_conn,
						c_add, c_mode, c_ind, m_name, r_no, g_name, r_use,
						r_nouse);
				companyInfolist.add(companyInfo);
			}
		} 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 companyInfolist;
	}

	public ArrayList<CompanyInfo> findCompanyInfoByC_name(String C_name) {
		ArrayList<CompanyInfo> companyInfolist = new ArrayList<CompanyInfo>();
		CompanyInfo companyInfo = null;

		Connection conn = ConnectPool.getInstance().getConnection();
		PreparedStatement pstm = null;
		ResultSet rst = null;
		String sql = "select company.C_no, C_name, C_lead, C_conn, C_add, C_mode, C_ind, M_name, R_no, G_name, R_use, R_nouse from house, rent, company where C_name = ? and house.H_no=rent.R_no and rent.C_no=company.C_no order by company.C_no;";
		try {
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, C_name);
			rst = pstm.executeQuery();
			while (rst.next()) {
				String c_no = rst.getString("company.C_no");
				String c_name = rst.getString("C_name");
				String c_lead = rst.getString("C_lead");
				String c_conn = rst.getString("C_conn");
				String c_add = rst.getString("C_add");
				String c_mode = rst.getString("C_mode");
				String c_ind = rst.getString("C_ind");
				String m_name = rst.getString("M_name");
				String r_no = rst.getString("R_no");
				String g_name = rst.getString("G_name");
				float r_use = rst.getFloat("R_use");
				float r_nouse = rst.getFloat("R_nouse");
				companyInfo = new CompanyInfo(c_no, c_name, c_lead, c_conn,
						c_add, c_mode, c_ind, m_name, r_no, g_name, r_use,
						r_nouse);
				companyInfolist.add(companyInfo);
			}
		} 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 companyInfolist;
	}

	public ArrayList<CompanyInfo> findCompanyInfoByR_no(String R_no) {
		ArrayList<CompanyInfo> companyInfolist = new ArrayList<CompanyInfo>();
		CompanyInfo companyInfo = null;

		Connection conn = ConnectPool.getInstance().getConnection();
		PreparedStatement pstm = null;
		ResultSet rst = null;
		String sql = "select company.C_no, C_name, C_lead, C_conn, C_add, C_mode, C_ind, M_name, R_no, G_name, R_use, R_nouse from house, rent, company where R_no = ? and house.H_no=rent.R_no and rent.C_no=company.C_no order by company.C_no;";
		try {
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, R_no);
			rst = pstm.executeQuery();
			if (rst.next()) {
				String c_no = rst.getString("company.C_no");
				String c_name = rst.getString("C_name");
				String c_lead = rst.getString("C_lead");
				String c_conn = rst.getString("C_conn");
				String c_add = rst.getString("C_add");
				String c_mode = rst.getString("C_mode");
				String c_ind = rst.getString("C_ind");
				String m_name = rst.getString("M_name");
				String r_no = rst.getString("R_no");
				String g_name = rst.getString("G_name");
				float r_use = rst.getFloat("R_use");
				float r_nouse = rst.getFloat("R_nouse");
				companyInfo = new CompanyInfo(c_no, c_name, c_lead, c_conn,
						c_add, c_mode, c_ind, m_name, r_no, g_name, r_use,
						r_nouse);
				companyInfolist.add(companyInfo);
			}
		} 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 companyInfolist;
	}

	public static void main(String[] args) {
		ArrayList<CompanyInfo> list = new CompanyInfoDaoImpl().findCompanyInfoByR_no("1002");
		for(CompanyInfo l: list){
			System.out.println(l.getC_name());
		}
	}
}