是否有更简洁的方式来使用try-with-resource和PreparedStatement?
这里是Main.java
:是否有更简洁的方式来使用try-with-resource和PreparedStatement?
package foo.sandbox.db;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Main {
public static void main(String[] args) {
final String SQL = "select * from NVPAIR where name=?";
try (
Connection connection = DatabaseManager.getConnection();
PreparedStatement stmt = connection.prepareStatement(SQL);
DatabaseManager.PreparedStatementSetter<PreparedStatement> ignored = new DatabaseManager.PreparedStatementSetter<PreparedStatement>(stmt) {
@Override
public void init(PreparedStatement ps) throws SQLException {
ps.setString(1, "foo");
}
};
ResultSet rs = stmt.executeQuery()
) {
while (rs.next()) {
System.out.println(rs.getString("name") + "=" + rs.getString("value"));
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
这里是DatabaseManager.java
package foo.sandbox.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
/**
* Initialize script
* -----
* CREATE TABLE NVPAIR;
* ALTER TABLE PUBLIC.NVPAIR ADD value VARCHAR2 NULL;
* ALTER TABLE PUBLIC.NVPAIR ADD id int NOT NULL AUTO_INCREMENT;
* CREATE UNIQUE INDEX NVPAIR_id_uindex ON PUBLIC.NVPAIR (id);
* ALTER TABLE PUBLIC.NVPAIR ADD name VARCHAR2 NOT NULL;
* ALTER TABLE PUBLIC.NVPAIR ADD CONSTRAINT NVPAIR_name_pk PRIMARY KEY (name);
*
* INSERT INTO NVPAIR(name, value) VALUES('foo', 'foo-value');
* INSERT INTO NVPAIR(name, value) VALUES('bar', 'bar-value');
*/
public class DatabaseManager {
/**
* Class to allow PreparedStatement to initialize parmaters inside try-with-resource
* @param <T> extends Statement
*/
public static abstract class PreparedStatementSetter<T extends Statement> implements AutoCloseable {
public PreparedStatementSetter(PreparedStatement pstmt) throws SQLException {
init(pstmt);
}
@Override
public void close() throws Exception {
}
public abstract void init(PreparedStatement pstmt) throws SQLException;
}
/* Use local file for database */
private static final String JDBC_CONNECTION = "jdbc:h2:file:./db/sandbox_h2.db;MODE=PostgreSQL";
static {
try {
Class.forName("org.h2.Driver"); // Init H2 DB driver
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* @return Database connection
* @throws SQLException
*/
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(JDBC_CONNECTION, "su", "");
}
}
我使用H2数据库简单,因为它是一个基于文件的一个,很容易创建和测试上。
所以一切工作和资源得到清理不如预期,但我只是觉得有可能是设定与尝试,与资源块内的PreparedStatement
参数的更清洁的方式(我不想使用嵌套尝试/抓住块看起来'尴尬')。也许在JDBC中已经存在一个助手类来做到这一点,但我一直没有找到。
最好用一个lambda函数来初始化PreparedStatement
,但它仍然需要分配一个AutoCloseable
对象,以便它可以在try-with-resources中。
首先,你PreparedStatementSetter
类是尴尬:
- 它是一种类型化的类,但不使用的类型。
- 该构造函数明确调用一个可覆盖的方法which is a bad practice。
请考虑改用以下接口(受相同名称的Spring interface启发)。
public interface PreparedStatementSetter {
void setValues(PreparedStatement ps) throws SQLException;
}
这个接口定义了一个什么样的PreparedStatementSetter
是应该做合同:一PreparedStatement
的设定值,仅此而已。
然后,最好在单个方法内创建和初始化PreparedStatement
。考虑这个除了你DatabaseManager
类中:
public static PreparedStatement prepareStatement(Connection connection, String sql, PreparedStatementSetter setter) throws SQLException {
PreparedStatement ps = connection.prepareStatement(sql);
setter.setValues(ps);
return ps;
}
有了这个静态方法,你就可以写:
try (
Connection connection = DatabaseManager.getConnection();
PreparedStatement stmt = DatabaseManager.prepareStatement(connection, SQL, ps -> ps.setString(1, "foo"));
ResultSet rs = stmt.executeQuery()
) {
// rest of code
}
注意如何PreparedStatementSetter
用lambda表达式写在这里。这是使用接口而不是抽象类的好处之一:在这种情况下,它实际上是一个功能接口(因为有一个抽象方法),因此可以写成lambda表达式。
我相信你的代码有和@Trejkaz在这里提到的一样的缺陷:https://*.com/questions/8066501/how-should-i-use-try-with-resources-with-jdbc#comment-23568725 - setter.setValues(ps)抛出的异常绕过了本地构造的PreparedStatement的返回,因此PreparedStatement不会被关闭。 – AjahnCharles
从@ Tunaki的答案扩展,它也可以因子在尝试 - 与资源和rs.executeQuery()
使得DatabaseManager
处理这一切为你,只要求提供SQL,一个PreparedStatementSetter
和ResultSet
处理器。
这样可以避免在查询的任何位置重复此操作。实际API将取决于您的使用情况 - 例如你会用同一个连接做几个查询吗?
假如你愿意,我提出以下建议:
public class DatabaseManager implements AutoCloseable {
/* Use local file for database */
private static final String JDBC_CONNECTION = "jdbc:h2:file:./db/sandbox_h2.db;MODE=PostgreSQL";
static {
try {
Class.forName("org.h2.Driver"); // Init H2 DB driver
} catch (Exception e) {
e.printStackTrace();
}
}
private final Connection connection;
private DatabaseManager() throws SQLException {
this.connection = getConnection();
}
@Override
public void close() throws SQLException {
connection.close();
}
public interface PreparedStatementSetter {
void setValues(PreparedStatement ps) throws SQLException;
}
public interface Work {
void doWork(DatabaseManager manager) throws SQLException;
}
public interface ResultSetHandler {
void process(ResultSet resultSet) throws SQLException;
}
/**
* @return Database connection
* @throws SQLException
*/
private static Connection getConnection() throws SQLException {
return DriverManager.getConnection(JDBC_CONNECTION, "su", "");
}
private PreparedStatement prepareStatement(String sql, PreparedStatementSetter setter) throws SQLException {
PreparedStatement ps = connection.prepareStatement(sql);
setter.setValues(ps);
return ps;
}
public static void executeWork(Work work) throws SQLException {
try (DatabaseManager dm = new DatabaseManager()) {
work.doWork(dm);
}
}
public void executeQuery(String sql, PreparedStatementSetter setter, ResultSetHandler handler) throws SQLException {
try (PreparedStatement ps = prepareStatement(sql, setter);
ResultSet rs = ps.executeQuery()) {
handler.process(rs);
}
}
}
它包装连接作为DatabaseManager
实例字段,这将处理连接的生命周期,这要归功于其实施的AutoCloseable
。
它还定义了2个新的功能接口(另外,以@ Tunaki的PreparedStatementSetter
):
-
Work
通过executeWork
静态方法 -
ResultSetHandler
定义ResultSet
必须如何处理定义了一些工作,做一个DatabaseManager
当通过新的executeQuery
实例方法执行查询时。
可以使用如下:
final String SQL = "select * from NVPAIR where name=?";
try {
DatabaseManager.executeWork(dm -> {
dm.executeQuery(SQL, ps -> ps.setString(1, "foo"), rs -> {
while (rs.next()) {
System.out.println(rs.getString("name") + "=" + rs.getString("value"));
}
});
// other queries are possible here
});
} catch (Exception e) {
e.printStackTrace();
}
正如你看到的,你不必担心资源处理任何 更多。
我离开SQLException
在api之外进行处理,因为您可能想让它传播。
该解决方案受Design Patterns in the Light of Lambda Expressions by Subramaniam启发。
我发现这样做的另一种方式,也可能是有用的人:
PreparedStatementExecutor.java:
/**
* Execute PreparedStatement to generate ResultSet
*/
public interface PreparedStatementExecutor {
ResultSet execute(PreparedStatement pstmt) throws SQLException;
}
PreparedStatementSetter.java:
/**
* Lambda interface to help initialize PreparedStatement
*/
public interface PreparedStatementSetter {
void prepare(PreparedStatement pstmt) throws SQLException;
}
JdbcTriple.java:
/**
* Contains DB objects that close when done
*/
public class JdbcTriple implements AutoCloseable {
Connection connection;
PreparedStatement preparedStatement;
ResultSet resultSet;
/**
* Create Connection/PreparedStatement/ResultSet
*
* @param sql String SQL
* @param setter Setter for PreparedStatement
* @return JdbcTriple
* @throws SQLException
*/
public static JdbcTriple create(String sql, PreparedStatementSetter setter) throws SQLException {
JdbcTriple triple = new JdbcTriple();
triple.connection = DatabaseManager.getConnection();
triple.preparedStatement = DatabaseManager.prepareStatement(triple.connection, sql, setter);
triple.resultSet = triple.preparedStatement.executeQuery();
return triple;
}
public Connection getConnection() {
return connection;
}
public PreparedStatement getPreparedStatement() {
return preparedStatement;
}
public ResultSet getResultSet() {
return resultSet;
}
@Override
public void close() throws Exception {
if (resultSet != null)
resultSet.close();
if (preparedStatement != null)
preparedStatement.close();
if (connection != null)
connection.close();
}
}
个
DatabaseManager.java:
/**
* Initialize script
* -----
* CREATE TABLE NVPAIR;
* ALTER TABLE PUBLIC.NVPAIR ADD value VARCHAR2 NULL;
* ALTER TABLE PUBLIC.NVPAIR ADD id int NOT NULL AUTO_INCREMENT;
* CREATE UNIQUE INDEX NVPAIR_id_uindex ON PUBLIC.NVPAIR (id);
* ALTER TABLE PUBLIC.NVPAIR ADD name VARCHAR2 NOT NULL;
* ALTER TABLE PUBLIC.NVPAIR ADD CONSTRAINT NVPAIR_name_pk PRIMARY KEY (name);
*
* INSERT INTO NVPAIR(name, value) VALUES('foo', 'foo-value');
* INSERT INTO NVPAIR(name, value) VALUES('bar', 'bar-value');
*/
public class DatabaseManager {
/* Use local file for database */
private static final String JDBC_CONNECTION = "jdbc:h2:file:./db/sandbox_h2.db;MODE=PostgreSQL";
static {
try {
Class.forName("org.h2.Driver"); // Init H2 DB driver
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* @return Database connection
* @throws SQLException
*/
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(JDBC_CONNECTION, "su", "");
}
/** Prepare statement */
public static PreparedStatement prepareStatement(Connection conn, String SQL, PreparedStatementSetter setter) throws SQLException {
PreparedStatement pstmt = conn.prepareStatement(SQL);
setter.prepare(pstmt);
return pstmt;
}
/** Execute statement */
public static ResultSet executeStatement(PreparedStatement pstmt, PreparedStatementExecutor executor) throws SQLException {
return executor.execute(pstmt);
}
}
Main.java:
public class Main {
public static void main(String[] args) {
final String SQL = "select * from NVPAIR where name=?";
try (
JdbcTriple triple = JdbcTriple.create(SQL, pstmt -> { pstmt.setString(1, "foo"); })
){
while (triple.getResultSet().next()) {
System.out.println(triple.getResultSet().getString("name") + "=" + triple.getResultSet().getString("value"));
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
虽然这不处理,你可能需要从插入或交易返回一个ID的情况下,它确实提供了一个快速的方法运行一个查询,设置参数并获得一个ResultSet,在我的情况下这是大量的DB代码。
[我应该如何在JDBC中使用try-with-resources?](http://*.com/questions/8066501/how-should-i-use-try-with-resources-with-jdbc ) –
我希望找到一种方法来使用lambda代替类实例来完成PreparedStatement的init,就像将参数传入PreparedStatement一样。 – AlexC