Using a variable instead of a parameter index with a JDBC prepared 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 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

Hey kd304, I wonder why you didn't leave your message as answer instead of using a comment. Cause I think it's the way for me to solve that problem. — medihack
– medihack, Commented Jul 8, 2009 at 13:32
@tokel: I wasn't sure about your question being theoretical or not and my comment does not really answer your typed question. — akarnokd
– akarnokd, Commented Jul 8, 2009 at 21:24
"In Java one has to use parameter indices:" - actually, it doesn't look like that in your sample. In Java, one cannot even use parameter indices in the SQL string. — O. R. Mapper
– O. R. Mapper, Commented Nov 25, 2014 at 15:04

laz · Accepted Answer · 2009-07-08 13:07:18Z

31

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

answered Jul 8, 2009 at 13:07

laz

28.7k6 gold badges56 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

medihack Over a year ago

Thanks for the very good solution. The problem for me is that it will be part of another open source framework and I don't want to import so much code there. Otherwise I would have used your solution :-)

medihack · Accepted Answer · 2009-07-08 21:17:17Z

14

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

answered Jul 8, 2009 at 21:17

medihack

16.7k21 gold badges92 silver badges140 bronze badges

2 Comments

XelaNimed Over a year ago

Link no longer works...

t3chb0t Over a year ago

@XelaNimed that's why link-only answer aren't accepted anymore, but it might have moved to this. Looks at least similar to the old page.

skaffman · Accepted Answer · 2009-07-08 13:05:53Z

6

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.

answered Jul 8, 2009 at 13:05

skaffman

405k96 gold badges825 silver badges775 bronze badges

3 Comments

skaffman Over a year ago

Forget my idea, @laz has a much better one.

Oliv Over a year ago

I think Oracle JDBC driver supports calling regular SQL statements with named parameters when using CallableStatement, not just for procedures. PostgreSQL says "Not yet implements" (postgresql-9.1-901.jdbc4.jar)

Bohdan Mart Over a year ago

That's so sad, as DBMSsupports this, at least SQL server does, and JSBC intrnaly creates named parameters, like following: @P0 nvarchar(4000),@P1 varchar(8000),@P2 nvarchar(4000),@P3 smallint,@P4 nvarchar(4000),@P5 int

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related