Using a variable instead of a parameter index with a JDBC prepared statement.

Question

Hi.

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

Answer 1

Using a raw PreparedStatement, this is not possible, as you say. It is possible with CallableStatement, but that requires a stored procedure rather than just a SQL statement.

ORM layers like Hibernate also provide named parameter substitution, and Hibernate also allows you to execute native SQL, bypassing the OR mapping functionality completely.

So if you were really keen to use named parameters, you could employ Hibernate as a way of doing this; you'd only be using a tiny fraction of its functionality.

Answer 2

Standard JDBC PreparedStatements don't have this ability. Spring JDBC provides this functionality through NamedParameterJdbcTemplate.

Answer 3

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