使用 Spring JDBC PreparedStatementCreator 的正确方法是什么？

What is proper way to use PreparedStatementCreator of Spring JDBC?

提问by Jignesh Dhua

As per my understanding the use of PreparedStatement in Java is we can use it multiple times. But I have some confusion using PreparedStatementCreator of Spring JDBC.

根据我的理解，在 Java 中使用 PreparedStatement 是我们可以多次使用它。但是我在使用 Spring JDBC 的 PreparedStatementCreator 时有些困惑。

For example consider following code,

例如考虑以下代码，

public class SpringTest {

    JdbcTemplate jdbcTemplate; 
    PreparedStatementCreator preparedStatementCreator; 
    ResultSetExtractor<String> resultSetExtractor;

    public SpringTest() throws SQLException {

        jdbcTemplate = new JdbcTemplate(OracleUtil.getDataSource());

        preparedStatementCreator = new PreparedStatementCreator() {
            String query = "select NAME from TABLE1  where ID=?";
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                return connection.prepareStatement(query);
            }
        };

        resultSetExtractor  = new ResultSetExtractor<String>() {
            public String extractData(ResultSet resultSet) throws SQLException,
            DataAccessException {
                if (resultSet.next()) {
                    return resultSet.getString(1);
                }
                return null;
            }
        };
    }
    public String getNameFromId(int id){
        return jdbcTemplate.query(preparedStatementCreator, new Table1Setter(id), resultSetExtractor);
    }

    private static class Table1Setter implements PreparedStatementSetter{

        private int id;
        public Table1Setter(int id) {
            this.id =id;
        }
        @Override
        public void setValues(PreparedStatement preparedStatement) throws SQLException {
            preparedStatement.setInt(1, id);
        }
    }
    public static void main(String[] args) {
        try {
            SpringTest  springTest = new SpringTest();

            for(int i=0;i<10;i++){
                System.out.println(springTest.getNameFromId(i));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

As per this code when I called springTest.getNameFromId(int id) method, it returns name from given id, Here I've used PreparedStatementCreator for creating PreparedStatement and PreparedStatementSetter for setting input parameters and I got result from ResultSetExtractor. But performance is very slow.

根据这段代码，当我调用 springTest.getNameFromId(int id) 方法时，它从给定的 id 返回名称，这里我使用 PreparedStatementCreator 来创建 PreparedStatement 和 PreparedStatementSetter 来设置输入参数，我从 ResultSetExtractor 得到结果。但是性能非常慢。

After debugging and looking into what happens inside PreparedStatementCreator and JdbcTemplate I got to know that PreparedStatementCreator creates each and every time new PreparedStatement...!!!

在调试并查看 PreparedStatementCreator 和 JdbcTemplate 内部发生的情况后，我知道 PreparedStatementCreator 每次都会创建新的 PreparedStatement ......！！！

Each and every time when I am calls method jdbcTemplate.query(preparedStatementCreator, preparedStatementSetter, resultSetExtractor), it creates new PreparedStatement and this slow downs performance.

每次当我调用方法 jdbcTemplate.query(preparedStatementCreator, PreparedStatementSetter, resultSetExtractor) 时，它都会创建新的 PreparedStatement，这会降低性能。

Is this right way to use PreparedStatementCreator? Because in this code I unable to reuse PreparedStatement. And if this is right way to use PreparedStatementCreator than how to get benefit of re-usability of PreparedStatement?

这是使用 PreparedStatementCreator 的正确方法吗？因为在这段代码中我无法重用 PreparedStatement。如果这是使用 PreparedStatementCreator 的正确方法，那么如何从 PreparedStatement 的可重用性中获益？

采纳答案by Jose Luis Martin

Prepared Statements are usually cached by underlying connection pool, so you don't need to worry about creating a new one every time or not.

Prepared Statements 通常由底层连接池缓存，因此您不必担心每次都创建一个新语句。

So I think that your actually usage is correct.

所以我认为你的实际用法是正确的。

JdbcTemplate closes the statement after executing it, so if you really want to reuse the same prepared statement you could proxy the statement and intercept the close method in the statement creator

JdbcTemplate 在执行后关闭该语句，因此如果您真的想重用相同的准备好的语句，您可以代理该语句并拦截语句创建者中的 close 方法

For example (not tested, only as example):

例如（未测试，仅作为示例）：

public abstract class ReusablePreparedStatementCreator implements PreparedStatementCreator {

    private PreparedStatement statement;

    public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
        if (statement != null)
            return statement;

        PreparedStatement ps = doPreparedStatement(conn);

        ProxyFactory pf = new ProxyFactory(ps);
        MethodInterceptor closeMethodInterceptor = new MethodInterceptor() {

            @Override
            public Object invoke(MethodInvocation invocation) throws Throwable {
                return null;  // don't close statement
            }
        };

        NameMatchMethodPointcutAdvisor closeAdvisor = new NameMatchMethodPointcutAdvisor();
        closeAdvisor.setMappedName("close");
        closeAdvisor.setAdvice(closeMethodInterceptor);
        pf.addAdvisor(closeAdvisor);

        statement = (PreparedStatement) pf.getProxy();

        return statement;       
    }

    public abstract PreparedStatement doPreparedStatement(Connection conn) throws SQLException;

    public void close() {
        try {
            PreparedStatement ps = (PreparedStatement) ((Advised) statement).getTargetSource().getTarget();
            ps.close();
        } catch (Exception e) {
            // handle exception
        }
    }

}

回答by Henry Leu

You are on the right way to use PreparedStatementCreator.

您是使用 PreparedStatementCreator 的正确方法。

1. In each new transaction, you should create brand new PreparedStatementinstance, it's definitely correct. PreparedStatementCreatoris mainly designed to wrap the code block to create PreparedStatementinstance easily, not saying that you should resue the new instance each itme.
2. PreparedStatement is mainly designed to send the templated and pre-compiled SQL statement DBMS which will save some pre-compiled time for SQL execution.

1. 在每一个新的事务中，你都应该创建一个全新的PreparedStatement实例，这绝对是正确的。PreparedStatementCreator主要是为了包装代码块以方便地创建PreparedStatement实例，并不是说每个项目都应该重新创建新实例。
2. PreparedStatement 主要用于发送模板化和预编译的 SQL 语句 DBMS，为 SQL 执行节省一些预编译时间。

To summarize, what you did is correct. use PreparedStatementwill have better performance than Statement.

总而言之，你所做的是正确的。使用PreparedStatement会比Statement有更好的性能。

回答by Ryan Stewart

After debugging and looking into what happens inside PreparedStatementCreator and JdbcTemplate I got to know that PreparedStatementCreator creates each and every time new PreparedStatement...!!!

在调试并查看 PreparedStatementCreator 和 JdbcTemplate 内部发生的情况后，我知道 PreparedStatementCreator 每次都会创建新的 PreparedStatement ......！！！

I'm not sure why that's so shocking since it's your own code that creates a new PreparedStatementeach time by calling connection.prepareStatement(query);. If you want to reuse the same one, then you shouldn't create a new one.

我不知道为什么这会如此令人震惊，因为它是您自己的代码，PreparedStatement每次通过调用connection.prepareStatement(query);. 如果你想重复使用同一个，那么你不应该创建一个新的。