prdaojdbcimpl.java

医院化验单系统

package com.asnk120.EMH.persist.DAOJdbcImpl;

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

import com.asnk120.EMH.model.entity.PR;
import com.asnk120.EMH.persist.JdbcUtil;
import com.asnk120.EMH.persist.DAO.PRDAO;

public class PRDAOJdbcImpl implements PRDAO {
	public void createTable(PR pp, Connection con) {// 建表PR_X，插数据;建表PV_X插数据
		int id;
		String ppName = pp.getPpName();
		Vector<Vector> dataList = pp.getDataList();
		PreparedStatement pstmt = null;
		String sql = "";
		// System.out.println(isExistName(ppName,con));//测试
		if (!isExistName(ppName, con)) {
			id = findMaxId(con) + 1;// 建第一张表的时候函数没有找到值，最后返回来-1，加1从0开始正好

			sql = "create table pr_" + id + "(" + "param char(10),"
					+ "reference char(50)" + ")";
			try {
				pstmt = con.prepareStatement(sql);// 建表
				pstmt.executeUpdate();

				insertData2PR(dataList, "pr_" + id, con);// 插数据
				insertProject(ppName, id, con);// 更新project表
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				// throw new DataException("create error");
			} // 暂时不释放pstmt

			sql = "create table pv_" + id + "(" + "number char(13),"
					+ "param char(10)," + "value char(20)" + ")";
			try {
				pstmt = con.prepareStatement(sql);// 建表
				pstmt.executeUpdate();

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				// throw new DataException("create error");
			} finally {
				JdbcUtil.release(pstmt);// 最终释放
			}
		}
	}

	public int findMaxId(Connection con) {
		int id = -1;// 如果表中没有值，返回null值，那么定id为-1
		String sql0 = "select *" + "from project";
		String sql1 = "select max(id) " + "from project";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			if (con.prepareStatement(sql0).executeQuery().next()) {
				pstmt = con.prepareStatement(sql1);
				rs = pstmt.executeQuery();
				if ((rs != null) && rs.next()) {
					id = rs.getInt(1);
					// System.out.println(id);//测试
				}

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

		return id;
	}

	public boolean isExistName(String ppName, Connection con) {
		boolean isExist = false;
		String sql = "";
		sql = "select * " + "from project " + "where name=?";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, ppName);
			rs = pstmt.executeQuery();
			if (rs != null && rs.next()) {
				isExist = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtil.release(rs);
			JdbcUtil.release(pstmt);
		}
		return isExist;
	}

	public void insertData2PR(Vector<Vector> dataList, String tableName,
			Connection con) {
		PreparedStatement pstmt = null;
		String sql = "";
		for (int i = 0; i < dataList.size(); i++) {// 从展现层传过来的数据绝对不会为空，有可能是“”值，以为在展现层就把没有参数的可能行过滤掉了
			String param = (String) dataList.elementAt(i).elementAt(0);
			String preference = (String) dataList.elementAt(i).elementAt(1);

			sql = "insert into " + tableName + " values('" + param + "','"
					+ preference + "')";
			try {
				pstmt = con.prepareStatement(sql);// 建表
				pstmt.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				// throw new DataException("create error");
			} finally {
				JdbcUtil.release(pstmt);
			}
		}
	}

	// public void insertData2PV(Vector<Vector> dataList, String tableName//没用
	// Connection con) {
	// PreparedStatement pstmt = null;
	// String sql = "";
	// for (int i = 0; i < dataList.size(); i++) {//
	// 从展现层传过来的数据绝对不会为空，有可能是“”值，以为在展现层就把没有参数的可能行过滤掉了
	// String param = (String) dataList.elementAt(i).elementAt(0);
	//
	// sql = "insert into " + tableName + "(param) values('" + param + "')";
	// try {
	// pstmt = con.prepareStatement(sql);// 建表
	// pstmt.executeUpdate();
	// } catch (SQLException e) {
	// // TODO Auto-generated catch block
	// e.printStackTrace();
	// // throw new DataException("create error");
	// } finally {
	// JdbcUtil.release(pstmt);
	// }
	// }
	// }

	public void insertProject(String ppName, int id, Connection con) {
		PreparedStatement pstmt = null;
		String sql = "insert into project values('" + ppName + "','" + id
				+ "')";
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			// throw new DataException("create error");
		} finally {
			JdbcUtil.release(pstmt);
		}
	}

	public PR findPpByName(String proName, Connection con) {
		PR pp = null;
		ProDAOJdbcImpl pdji = new ProDAOJdbcImpl();// 这里就直接用了，用工厂的方法太麻烦
		int id = pdji.findIdByName(proName, con);
		Vector<Vector> dataList = new Vector<Vector>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select param,reference " + "from pr_" + id;
		try {
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Vector<Object> oneRow = new Vector<Object>();
				oneRow.add(rs.getString(1));
				oneRow.add(rs.getString(2));
				dataList.add(oneRow);
			}
			pp = new PR(proName, dataList);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			// throw new DataException("create error");
		} finally {
			JdbcUtil.release(rs);
			JdbcUtil.release(pstmt);
		}

		return pp;
	}
}