Using a variable instead of a parameter index using a prepared JDBC statement

Question

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, you need to use parameter indices:

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

Is there a solution for working with string variables, as in the first example? Is "$ {. *}" Not to be used elsewhere in SQL or conflicts? Because then I would implement it myself (parsing the SQL string and replacing each variable with "?", And then executing that Java path).

Regards, Kai

Answer 1

As kd304 is mentioned in a comment on my publication, this is a very nice solution if you do not want to include another third-party library (for example, Spring) in your project: Javaworld Article: Named parameters for PreparedStatement

Answer 2

Standard JDBC PreparedStatements do not have this capability. Spring JDBC provides this functionality through NamedParameterJdbcTemplate .

Answer 3

Using the original PreparedStatement, this is not possible, as you say. This is possible with CallableStatement, but this requires a stored procedure, not just an SQL statement.

ORM levels such as Hibernate also provide named parameter substitution, and Hibernate also allows you to run your own SQL, completely bypassing the OR mapping functionality.

So, if you really wanted to use named parameters, you can use Hibernate as a way to do this; you will use only a small part of your functionality.