java sql 模板_java 模板模式+回调+泛型实现数据库查询

一、数据库连接工具类

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

public class ConnectionUtils {

private final String ORACLE_DRIVER = "oracle.jdbc.driver.OracleDriver";

private final String URL = "jdbc:oracle:thin:@###.###.###.###:1521:****";

private final String USERNAME = "username";

private final String PASSWORD = "password";

private ConnectionUtils(){}

private static class DataBaseHolder{

private static final ConnectionUtils instance = new ConnectionUtils();

}

public static ConnectionUtils getInstance(){

return DataBaseHolder.instance;

}

public Connection getConnectionion() {

Connection conn = null;

try {

Class.forName(ORACLE_DRIVER);

conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

}

return conn;

}

}

二、处理ResultSet结果集的公共接口

import java.sql.ResultSet;

public interface ResultSetHandler {

public T handle(ResultSet rs);

}

三、SimpleJdbcTemplate主工具类

import java.sql.Connection; import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class SimpleJdbcTemplate {

public static T query(String queryString, ResultSetHandler rsHandler) {

Connection connection = null;

PreparedStatement stmt = null;

try {

connection = ConnectionUtils.getInstance().getConnectionion();

stmt = connection.prepareStatement(queryString,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);

ResultSet rs = stmt.executeQuery();

return rsHandler.handle(rs);

} catch (SQLException ex) {

close(stmt);    // close the statement

release(connection);    // release connection

throw new RuntimeException("An sql exception occurred.", ex);

} finally {

close(stmt); // close the statement

release(connection); // release connection

}

}

private static void release(Connection conn) {

try {

if (conn != null) {

conn.close();

}

} catch (SQLException e) {

e.printStackTrace();

}

}

private static void close(Statement statement) {

try {

if (statement != null) {

statement.close();

}

} catch (SQLException e) {

e.printStackTrace();

}

}

}