CODEWITHSUNDEEP
javajdbc
medihack
medihack

Reputation: 16627

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

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

Upvotes: 37

Views: 43493

Answers (3)

medihack
medihack

Reputation: 16627

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

Upvotes: 15

laz
laz

Reputation: 28648

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

Upvotes: 30

skaffman
skaffman

Reputation: 403591

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.

Upvotes: 6

Related Questions