成长（二）

package com.cxz.util;

import java.sql.*;

public class DBTemplate {
	private static String driverClass = "com.mysql.jdbc.Driver";

	private static String url = "jdbc:mysql://localhost:3306/ceramic";

	private static String userName = "root";

	private static String password = "19841230";

	static {
		try {// register the jdbc driver only once
			Class.forName(driverClass);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		// should reading
	}

	public DBTemplate() {
	}

	private Connection getConnection() throws SQLException {
		return DriverManager.getConnection(url, userName, password);
	}

	private PreparedStatement getPreparedStatement(Connection conn, String sql)
			throws SQLException {
		return conn.prepareStatement(sql);
	}

	private void setParams(PreparedStatement pstmt, Object[] params) throws SQLException{
		for (int i = 0; i < params.length; i++) {
			if (Integer.class == params[i].getClass()) {
				pstmt.setInt(i+1, (Integer)params[i]);
			} else if (String.class == params[i].getClass()) {
				pstmt.setString(i+1, (String)params[i]);
			} else if (Date.class == params[i].getClass()) {
				pstmt.setDate(i+1, (Date)params[i]);
			} else if (Float.class == params[i].getClass()) {
				pstmt.setFloat(i+1, (Float)params[i]);
			} else if (Double.class == params[i].getClass()){
				pstmt.setDouble(i+1, (Double)params[i]);
			} else {
				throw new IllegalArgumentException(
						"The method query can only treat types: String, Date, int, float, double");
			}
		}
	}

	public boolean execute(String sql, Object[] params) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean succ = false;
		if (sql == null) {
			// to check whether the params if null;
			throw new NullPointerException(
					"The parameters of DBTemplate::query(...) should not be null！");
		}
		try {
			conn = this.getConnection();
			pstmt = this.getPreparedStatement(conn, sql);
			if (params != null) {// if it has ?
				this.setParams(pstmt, params);
			}
			succ = pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return succ;
	}
	
	public void query(String sql, Object[] params, QueryManager qm) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		if (sql == null || qm == null) {
			// to check whether the params if null;
			throw new NullPointerException(
					"The parameters of DBTemplate::query(...) should not be null！");
		}
		try {
			conn = this.getConnection();
			pstmt = this.getPreparedStatement(conn, sql);
			if (params != null) {// if it has ?
				this.setParams(pstmt, params);
			}
			rs = pstmt.executeQuery();
			try {
				qm.doQuery(rs);
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}

	}
}