使用JDBC准备语句使用变量而不是参数索引(Using a variable instead of a parameter index with a JDBC prepared statement)

使用JDBC准备语句使用变量而不是参数索引(Using a variable instead of a parameter index with a JDBC prepared statement)

在许多编程语言中，对于准备好的语句来说，这是可能的：

PreparedStatement statement = connection.prepareStatement( "SELECT id FROM Company WHERE name LIKE ${name}"); statement.setString("name", "IBM");

但不适用于java.sql.PreparedStatement。 在Java中，必须使用参数索引：

PreparedStatement statement = connection.prepareStatement( "SELECT id FROM Company WHERE name LIKE ?"); statement.setString(1, "IBM");

有没有像第一个例子一样使用字符串变量的解决方案？ “$ {。*}”不是在SQL语言的其他地方使用，还是存在冲突？ 因此，我会自己实现它（解析SQL字符串并将每个变量替换为“？”，然后以Java方式执行）。

问候，凯

In many programming languages something like this is possible for prepared statements:

PreparedStatement statement = connection.prepareStatement( "SELECT id FROM Company WHERE name LIKE ${name}"); statement.setString("name", "IBM");

But not with java.sql.PreparedStatement. In Java one has to use parameter indices:

PreparedStatement statement = connection.prepareStatement( "SELECT id FROM Company WHERE name LIKE ?"); statement.setString(1, "IBM");

Is there a solution to work with string variables like in the first example? Is "${.*}" not used somewhere else in the SQL language, or are there any conflicts? Cause then I would implement it by myself (parsing the SQL string and replacing every variable by "?" and then doing it the Java way).

Regards, Kai

最满意答案

标准JDBC PreparedStatements不具备此功能。 Spring JDBC通过NamedParameterJdbcTemplate提供了这个功能。

As kd304 mentioned in the comment to my posting, this is a very nice solution if you don't want to incorporate another 3rd party library (like Spring) into your project: Javaworld Article: Named Parameters for PreparedStatement