[code]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();
}
}
}
}
}
[/code]
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();
}
}
}
}
}
[/code]