什么是使用Spring JDBC的PreparedStatementCreator的正确方法? [英] What is proper way to use PreparedStatementCreator of Spring JDBC?

问题描述

据我了解，在Java中使用PreparedStatement是可以多次使用它. 但是我对使用Spring JDBC的PreparedStatementCreator感到有些困惑.

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.

例如考虑以下代码，

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();
        }
    }
}

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

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.

在调试并查看PreparedStatementCreator和JdbcTemplate内部发生了什么之后，我知道PreparedStatementCreator每次都会创建新的PreparedStatement ... !!!

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

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

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

这是使用PreparedStatementCreator的正确方法吗?因为在这段代码中我无法重用PreparedStatement.如果这是使用PreparedStatementCreator的正确方法，而不是如何从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?

推荐答案

Prepared Statement通常由底层连接池缓存，因此您不必担心是否每次都会创建一个新的语句.

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.

所以我认为您的实际用法是正确的.

So I think that your actually usage is correct.

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

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

例如(未经测试，仅作为示例):

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
        }
    }

}

这篇关于什么是使用Spring JDBC的PreparedStatementCreator的正确方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！