使用带有JDBCTemplate的预准备语句 [英] Using prepared statements with JDBCTemplate

问题描述

我正在使用JDBC模板，并希望使用预准备语句从数据库中读取。我在.csv文件中迭代了很多行，并在每一行上执行一些带有相应值的SQL select查询。

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模板与预处理语句一起使用。

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.

PreparedStatementCreator 和 PreparedStatementSetter 。与此示例一样，它们都是使用匿名内部类创建的。
但是在PreparedStatementSetter类中，我无法访问我想在预准备语句中设置的值。

There is the PreparedStatementCreator and the PreparedStatementSetter. As in this example both 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.

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

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.

我习惯于创建准备好的语句非常简单。类似

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():

，如此 Java教程。

推荐答案

我现在用 PreparedStatement ，但事实证明它并不比Jdbc模板快。也许，正如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.

无论如何，我的sql SELECT 的原因是另一个慢得多。在 WHERE 子句中，我总是使用运算符 LIKE ，而我想要做的就是找到完全匹配。正如我发现 LIKE 搜索模式一样，因此非常慢。

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

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

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

这篇关于使用带有JDBCTemplate的预准备语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！