Java 在 JDBCTemplate 中使用准备好的语句

Using prepared statements with JDBCTemplate

提问by

I'm using the JDBC template and want to read from a database using prepared statements. I iterate over many lines in a .csv file, and on every line I execute some SQL select queries with corresponding values.

我正在使用 JDBC 模板并希望使用准备好的语句从数据库中读取数据。我遍历 .csv 文件中的多行，并在每一行上执行一些具有相应值的 SQL 选择查询。

I want to speed up my reading from the database but I don't know how to get the JDBC template to work with prepared statements.

我想加快从数据库中读取的速度，但我不知道如何让 JDBC 模板与准备好的语句一起工作。

There is the PreparedStatementCreatorand the PreparedStatementSetter. As in this exampleboth of them are created with anonymous inner classes. But inside the PreparedStatementSetter class I don't have access to the values I want to set in the prepared statement.

有PreparedStatementCreator和PreparedStatementSetter。在这个例子中，它们都是用匿名内部类创建的。但是在 PreparedStatementSetter 类中，我无权访问要在准备好的语句中设置的值。

Since I'm iterating through a .csv file, I can't hard code them as a String because I don't know them. I also can't pass them to the PreparedStatementSetter because there are no arguments for the constructor. And setting my values to final would be dumb too.

由于我正在遍历 .csv 文件，因此我无法将它们硬编码为 String，因为我不知道它们。我也无法将它们传递给 PreparedStatementSetter，因为构造函数没有参数。将我的值设置为 final 也很愚蠢。

I was used to the creation of prepared statements being fairly simple. Something like

我习惯于创建准备好的语句相当简单。就像是

PreparedStatement updateSales = con.prepareStatement(
    "UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ? ");
updateSales.setInt(1, 75); 
updateSales.setString(2, "Colombian"); 
updateSales.executeUpdate():

as in this Java tutorial.

就像在这个Java 教程中一样。

采纳答案by mezmo

I've tried a select statement now with a PreparedStatement, but it turned out that it was not faster than the Jdbc template. Maybe, as mezmo suggested, it automatically creates prepared statements.

我现在用 a 尝试了一个 select 语句PreparedStatement，但结果证明它并不比 Jdbc 模板快。也许，正如 mezmo 所建议的那样，它会自动创建准备好的语句。

Anyway, the reason for my sql SELECTs being so slow was another one. In the WHEREclause I always used the operator LIKE, when all I wanted to do was finding an exact match. As I've found out LIKEsearches for a pattern and therefore is pretty slow.

无论如何，我的sqlSELECT如此缓慢的原因是另一个原因。在WHERE子句中，我总是使用 operator LIKE，而我想做的只是找到完全匹配。正如我发现的那样LIKE搜索模式，因此速度很慢。

I'm using the operator =now and it's much faster.

我现在正在使用运营商=，而且速度要快得多。

回答by Kevin

Try the following:

请尝试以下操作：

PreparedStatementCreator creator = new PreparedStatementCreator() {
    @Override
    public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
        PreparedStatement updateSales = con.prepareStatement(
        "UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ? ");
        updateSales.setInt(1, 75); 
        updateSales.setString(2, "Colombian"); 
        return updateSales;
    }
};

回答by Donal Fellows

I'd factor out the prepared statement handling to at least a method. In this case, because there are no results it is fairly simple (and assuming that the connection is an instance variable that doesn't change):

我会将准备好的语句处理分解为至少一种方法。在这种情况下，因为没有结果，所以它相当简单（并假设连接是一个不会改变的实例变量）：

private PreparedStatement updateSales;
public void updateSales(int sales, String cof_name) throws SQLException {
    if (updateSales == null) {
        updateSales = con.prepareStatement(
            "UPDATE COFFEES SET SALES = ? WHERE COF_NAME LIKE ?");
    }
    updateSales.setInt(1, sales);
    updateSales.setString(2, cof_name);
    updateSales.executeUpdate();
}

At that point, it is then just a matter of calling:

在这一点上，它只是一个调用的问题：

updateSales(75, "Colombian");

Which is pretty simple to integrate with other things, yes? And if you call the method many times, the update will only be constructed once and that will make things much faster. Well, assuming you don't do crazy things like doing each update in its own transaction...

与其他东西集成非常简单，是吗？如果您多次调用该方法，则更新只会被构造一次，这将使事情变得更快。好吧，假设您不做疯狂的事情，例如在自己的事务中进行每次更新...

Note that the types are fixed. This is because for any particular query/update, they shouldbe fixed so as to allow the database to do its job efficiently. If you're just pulling arbitrary strings from a CSV file, pass them in as strings. There's also no locking; far better to keep individual connections to being used from a single thread instead.

请注意，类型是固定的。这是因为对于任何特定的查询/更新，它们都应该被修复，以允许数据库有效地完成其工作。如果您只是从 CSV 文件中提取任意字符串，请将它们作为字符串传入。也没有锁定；最好保持单个连接从单个线程中使用。

回答by mezmo

By default, the JDBCTemplatedoes its own PreparedStatementinternally, if you just use the .update(String sql, Object ... args)form. Spring, and your database, will manage the compiled query for you, so you don't have to worry about opening, closing, resource protection, etc. One of the saving graces of Spring. A link to Spring 2.5's documentation on this.Hope it makes things clearer. Also, statement caching can be done at the JDBC level, as in the case of at least some of Oracle's JDBC drivers.That will go into a lot more detail than I can competently.

默认情况下，如果你只使用表单，JDBCTemplate它会在PreparedStatement内部执行它自己的操作.update(String sql, Object ... args)。Spring 和你的数据库，会为你管理编译好的查询，让你不用担心打开、关闭、资源保护等。Spring 的优点之一。Spring 2.5 相关文档的链接。希望它使事情更清楚。此外，语句缓存可以在 JDBC 级别完成，至少在某些 Oracle JDBC 驱动程序的情况下。这将比我能胜任的更详细。

回答by Prasobh.K

class Main {
    public static void main(String args[]) throws Exception {
        ApplicationContext ac = new
          ClassPathXmlApplicationContext("context.xml", Main.class);
        DataSource dataSource = (DataSource) ac.getBean("dataSource");
// DataSource mysqlDataSource = (DataSource) ac.getBean("mysqlDataSource");

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

        String prasobhName = 
        jdbcTemplate.query(
           "select first_name from customer where last_name like ?",
            new PreparedStatementSetter() {
              public void setValues(PreparedStatement preparedStatement) throws
                SQLException {
                  preparedStatement.setString(1, "nair%");
              }
            }, 
            new ResultSetExtractor<Long>() {
              public Long extractData(ResultSet resultSet) throws SQLException,
                DataAccessException {
                  if (resultSet.next()) {
                      return resultSet.getLong(1);
                  }
                  return null;
              }
            }
        );
        System.out.println(machaceksName);
    }
}