有没有一种更干净的方法来使用try-with-resource和PreparedStatement? [英] Is there a cleaner way to use try-with-resource and PreparedStatement?
问题描述
这是Main.java
:
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数据库,因为它是一个基于文件的文件,易于创建和测试.
因此一切正常,资源将按预期进行清理,但是我只是觉得可以使用更干净的方法从try-with-resources块内部设置PreparedStatement
参数(并且我不想使用嵌套的尝试/抓住那些看起来笨拙"的块).也许在JDBC中已经存在一个可以完成此任务的帮助程序类,但是我却找不到它.
So everything works and resources get cleaned up as expected, however I just feel there may be a cleaner way to set the PreparedStatement
parameters from inside the try-with-resources block (and I don't want to use nested try/catch blocks as those look 'awkward'). Maybe there already exists a helper class in JDBC that does just this, but I have not been able to find one.
最好使用lambda函数来初始化PreparedStatement
,但仍然需要分配AutoCloseable
对象,以便它可以在try-with-resources内部.
Preferably with a lambda function to initialize the PreparedStatement
but it would still require allocating an AutoCloseable
object so it can be inside the try-with-resources.
推荐答案
首先,您的PreparedStatementSetter
类很尴尬:
- 这是一个类型化的类,但未使用该类型.
- 构造函数正在显式调用一个可重写的方法,这是一种不好的做法.
请改用以下界面(灵感来自同名的Spring接口.
Consider the following interface instead (inspired from the Spring interface of the same name).
public interface PreparedStatementSetter {
void setValues(PreparedStatement ps) throws SQLException;
}
此接口定义了PreparedStatementSetter
应该执行的约定:设置PreparedStatement
的值,仅此而已.
This interface defines a contract of what a PreparedStatementSetter
is supposed to do: set values of a PreparedStatement
, nothing more.
然后,最好在单个方法中进行PreparedStatement
的创建和初始化.在您的DatabaseManager
类中考虑以下附加内容:
Then, it would be better to do the creation and initialization of the PreparedStatement
inside a single method. Consider this addition inside your DatabaseManager
class:
public static PreparedStatement prepareStatement(Connection connection, String sql, PreparedStatementSetter setter) throws SQLException {
PreparedStatement ps = connection.prepareStatement(sql);
setter.setValues(ps);
return ps;
}
使用此静态方法,您可以编写:
With this static method, you can then write:
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.
Notice how the PreparedStatementSetter
was written here with a lambda expression. That's one of the advantage of using an interface instead of an abstract class: it actually is a functional interface in this case (because there is a single abstract method) and so can be written as a lambda.
这篇关于有没有一种更干净的方法来使用try-with-resource和PreparedStatement?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!