companydaoimpl.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.CompanyDao;
import org.ads123.goodsmanagers.dto.Company;

public class CompanyDaoImpl implements CompanyDao{

	public ArrayList<Company> findCompanyAll() {
		ArrayList<Company> company_list = new ArrayList<Company>();
		
		Connection conn = ConnectPool.getInstance().getConnection();
		PreparedStatement pstm = null;
		ResultSet rst = null;
		String sql = "select C_no, C_name, C_lead, C_conn, C_add, C_mode, C_ind from company";

		try {
			pstm = conn.prepareStatement(sql);
			rst = pstm.executeQuery();
			while (rst.next()) {
				String c_no = rst.getString("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");
				company_list.add(new Company(c_no, c_name, c_lead, c_conn, c_add,
						c_mode, c_ind, m_name));
			}
		} 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 company_list;
	}

	public Company findCompanyByC_no(String C_no) {
		Company company = null;
		
		Connection conn = ConnectPool.getInstance().getConnection();
		PreparedStatement pstm = null;
		ResultSet rst = null;
		String sql = "select C_name, C_lead, C_conn, C_add, C_mode, C_ind, M_name from company where C_no = ?";
		
		try {
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, C_no);
			rst = pstm.executeQuery();
			if(rst.next()){
				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");
				
				company = new Company(C_no, c_name, c_lead, c_conn, c_add, c_mode, c_ind, m_name);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return company;
	}

	public ArrayList<Company> findCompanyListByM_name(String M_name) {
		ArrayList<Company> company_list = new ArrayList<Company>();
		
		Connection conn = ConnectPool.getInstance().getConnection();
		PreparedStatement pstm = null;
		ResultSet rst = null;
		String sql = "select C_no, C_name, C_lead, C_conn, C_add, C_mode, C_ind from company where M_name = ?";

		try {
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, M_name);
			rst = pstm.executeQuery();
			while (rst.next()) {
				String c_no = rst.getString("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");
				company_list.add(new Company(c_no, c_name, c_lead, c_conn, c_add,
						c_mode, c_ind, M_name));
			}
		} 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 company_list;
	}

	public ArrayList<String> findAllCompanyName() {
		ArrayList<String> company_name = new ArrayList<String>();
		Connection conn = ConnectPool.getInstance().getConnection();
		PreparedStatement pstm = null;
		ResultSet rst = null;
		String sql = "select C_name from company where C_name != '空'";

		try {
			pstm = conn.prepareStatement(sql);
			rst = pstm.executeQuery();
			while (rst.next()) {
				String c_name = rst.getString("C_name");
				company_name.add(c_name);
			}
		} 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 company_name;
	}

}