2

I don't want to use an ORM or a dynamic SQL builder. All I need is a class that lets me to create prepared statements with named parameters. Or, at least, securely replace parameter placeholders for parameters of different data types.

EDIT: I know JDBC does not support named parameters. That is why I'm asking about a library.

user151851
  • 198
  • 1
  • 2
  • 8
  • 2
    There's nothing like that with plain JDBC. You can create a class that handles this as shown in [this tutorial](http://www.javaworld.com/javaworld/jw-04-2007/jw-04-jdbc.html) (but it will be like a SQL builder) or use [Spring JDBC Template](http://static.springsource.org/spring/docs/3.0.x/spring-framework-reference/html/jdbc.html) as shown [here](http://www.mkyong.com/spring/spring-named-parameters-examples-in-simplejdbctemplate/). – Luiggi Mendoza Jun 09 '13 at 18:19
  • Good example. Thanks. Did you use it in production? – user151851 Jun 09 '13 at 18:24
  • No, we use Hibernate as ORM (it's not up to me to change this but I would want to). – Luiggi Mendoza Jun 09 '13 at 18:25

1 Answers1

3

JDBC doesn't use named parameters as far as I'm aware, but you don't need a library at all for positional parameters, which is enough to "securely replace parameter placeholders for parameters of different data types":

PrepareStatement statement = connection.prepareStatement
    ("SELECT Foo FROM Bar WHERE Baz = ?");
try {
    statement.setString(1, "John O'Reilly"); // No problem with quote here
    ResultSet results = statement.executeQuery();
    ...
} finally {
   statement.close();
}

See the JDBC tutorial on prepared statements for more details.

EDIT: If you really want to use something like named parameters, and you're in control of the SQL itself (so you can make sure you use some form that won't be used elsewhere) you could fairly easily create your own query translator:

  • Create a TranslatedQuery instance (this is your new class) with something like "SELECT Foo FROM Bar WHERE Baz = @Baz"
  • Work out the position of each parameter and replace it with ?
  • Have one method to return the position for a given name, and another to return the translated SQL

It shouldn't be more than a few hours' work to make the class complete with tests - it doesn't actually need to touch JDBC.

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • That's the problem, I need to use named parameters, not positioned. – user151851 Jun 09 '13 at 18:21
  • 3
    @user151851: Why to you *need* to use named parameters? You said you wanted a way of securely replacing parameter placeholders - that's fine with positional parameters. – Jon Skeet Jun 09 '13 at 18:22
  • Named parameters make the statement easier to read and, more important, query decorator classes don't need to track what positional parameters were added by other decorators. – user151851 Jun 09 '13 at 18:31
  • @user151851: Then I suggest you follow the advice in the linked question, which is similar to my suggestion but already built for you :) – Jon Skeet Jun 09 '13 at 19:02
  • @Jon_Skeet: Thanks for the idea. I just wanted to spare the few hours :) – user151851 Jun 09 '13 at 19:10