prodaojdbcimpl.java

医院化验单系统

package com.asnk120.EMH.persist.DAOJdbcImpl;

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

import com.asnk120.EMH.persist.JdbcUtil;
import com.asnk120.EMH.persist.DAO.ProDAO;

public class ProDAOJdbcImpl implements ProDAO {

	public String[] findAllNames(Connection con) {
		// TODO Auto-generated method stub
		String sql = "select name " + "from project";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int rows = countPros(con);
		if (rows == 0) {// 如果还么有任何项目（项目数为0）
			return null;
		}
		String[] allNames = new String[rows];
		try {
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			for (int i = 0; i < rows; i++) {
				rs.next();
				allNames[i] = rs.getString(1);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtil.release(rs);
			JdbcUtil.release(pstmt);
		}
		return allNames;
	}

	public int countPros(Connection con) {
		int rows = 0;
		String sql = "select count(*)" + "from project";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				rows = rs.getInt(1);
				// System.out.println(rs.getInt(1));//test,这可结果集肯定有结果的，如果project表中什么都没有，结果为1，结果集中有东西
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtil.release(rs);
			JdbcUtil.release(pstmt);
		}
		return rows;
	}

	public int countRows(String proName, Connection con) {
		int rows = 0;
		int id = findIdByName(proName, con);
		String sql = "select count(*)" + "from pr_" + id;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				rows = rs.getInt(1);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtil.release(rs);
			JdbcUtil.release(pstmt);
		}
		return rows;
	}

	public int findIdByName(String proName, Connection con) {
		int id = 0;
		String sql = "select id " + "from project " + "where name=?";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, proName);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				id = rs.getInt(1);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtil.release(rs);
			JdbcUtil.release(pstmt);
		}
		return id;
	}

	public int countRecords(String proName, Connection con) {// 其实这个方法不该放在这里边，为了简便
		int rows = 0;
		int id = findIdByName(proName, con);
		String sql = "select count(distinct number)" + "from pv_" + id;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				rows = rs.getInt(1);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtil.release(rs);
			JdbcUtil.release(pstmt);
		}
		return rows;
	}
}