package edu.buptsse.sxjd.dao;

import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.Hashtable;
import java.util.Vector;

public class DAO {
	
	protected static void close(ResultSet rs) {
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			rs = null;
		}
	}

	
	protected static void close(Statement st) {
		try {
			if (st != null) {
				st.close();
				st = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if (st != null) {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			st = null;
		}
	}

	
	protected static void close(PreparedStatement pstmt) {
		try {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if (pstmt != null) {
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			pstmt = null;
		}
	}

	
	protected static void close(Connection conn) {
		DBManager.close(conn);
	}


	protected static void rollback(Connection conn) {
		try {
			if (conn != null) {
				conn.close();
				conn = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if (conn != null) {
			try {
				conn.rollback();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conn = null;
		}
	}


	protected static void cleanup(Connection conn, Statement st,
			PreparedStatement pst, ResultSet rs) {
		if (rs != null) {
			close(rs);
		}
		if (st != null) {
			close(st);
		}
		if (pst != null) {
			close(pst);
		}
		if (conn != null) {
			close(conn);
		}
	}

	/**
	 * 執(zhí)行查詢
	 * @param sql
	 * @param values
	 * @param conn
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public static Collection executeQuery(String sql, Connection conn)
			throws Exception {
		Statement st = null;
		ResultSet rs = null;
		Vector result;
		ResultSetMetaData rsMetaData = null;
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			result = new Vector();
			rsMetaData = rs.getMetaData();
			while (rs.next()) {
				Hashtable record = new Hashtable();
				for (int iCursor = 1; iCursor <= rsMetaData.getColumnCount(); iCursor++) {
					//字段名均為小寫
					if (rs.getObject(iCursor) != null) {
						record.put(rsMetaData.getColumnName(iCursor)
								.toLowerCase(), rs.getObject(iCursor));
					}
				}
				result.add(record);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			cleanup(null, st, null, rs);
		}
		return result;
	}


	/**
	 * 執(zhí)行查詢
	 *  prepareStatement執(zhí)行insert、update、delete操作
	 * values中對象的順序要和“？”的順序一致
	 * @param sql
	 * @param values
	 * @param conn
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public static Collection executeQuery(String sql, Object[] values,
			Connection conn) throws Exception {
		PreparedStatement pst = null;
		ResultSet rs = null;
		Vector result = null;
		ResultSetMetaData rsMetaData = null;
		try {
			if (values == null || values.length < 1) {
				return executeQuery(sql, conn);
			}
			pst = conn.prepareStatement(sql);
			for (int i = 0; i < values.length; i++) {
				pst.setObject(i + 1, values[i]);
			}
			rs = pst.executeQuery();
			result = new Vector();
			rsMetaData = rs.getMetaData();
			while (rs.next()) {
				Hashtable record = new Hashtable();
				//字段名均為小寫
				for (int iCursor = 1; iCursor <= rsMetaData.getColumnCount(); iCursor++) {
					if (rs.getObject(iCursor) != null) {
						record.put(rsMetaData.getColumnName(iCursor)
								.toLowerCase(), rs.getObject(iCursor));
					}
				}
				result.add(record);
			}
		} catch (Exception e) {
			throw e;
		} finally {
			cleanup(null, null, pst, rs);
		}
		return result;
	}


	/**
	 * Statement執(zhí)行insert、update、delete操作
	 * @param sql
	 * @param conn
	 * @return
	 * @throws Exception
	 */
	public static int executeUpdate(String sql, Connection conn)
			throws Exception {
		Statement st = null;
		int rows = 0;
		try {
			st = conn.createStatement();
			rows = st.executeUpdate(sql);
		} catch (Exception e) {
			throw e;
		} finally {
			cleanup(null, st, null, null);
		}
		return rows;

	}

	/**
	 * prepareStatement執(zhí)行insert、update、delete操作
	 * values中對象的順序要和？的順序一致
	 * @param sql
	 * @param conn
	 * @return
	 * @throws Exception
	 */
	public static int executeUpdate(String sql, Object[] values, Connection conn)
			throws Exception {
		PreparedStatement pst = null;
		int rows = 0;
		try {
			pst = conn.prepareStatement(sql);
			for (int i = 0; i < values.length; i++) {
				pst.setObject(i + 1, values[i]);
			}
			rows = pst.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			cleanup(null, null, pst, null);
		}
		return rows;
	}
}