Manage query strings in JDBC methods

Question

I am trying to find out which is the best practice when organising the PreparedStatement strings in a application which uses JDBC to connect to the database.

Usually this is the way I retrieve the data from the database:

PreparedStatement select = null;
String prepQuery = "SELECT * FROM user JOIN details ON user.id = details.user_id WHERE details.email = ? AND details.password = ?";
select = con.prepareStatement(prepQuery);
select.setString(1, email);
select.setString(2, pass);
ResultSet result = select.executeQuery();
// Handle the result.

This is a method which retrieves a user entity using the email and a password. For all my methods this is how I save the query strings.

The first drawback which comes to mind is that this is quite messy and difficult to manage if I'm changing the SQL provider (Maybe if I would place all the queries in a Constants file would be a better solution). Furthermore JPA offers tools like @NamedQuery which is said to improve the performance of the application.

Is there a best practice when it comes to organising the query strings in an application using JDBC. Thank you!

Matthew Flynn · Answer 1 · 2012-05-14T14:37:59.107

2

Your best bet is to use a IoC framework such as Spring to externalize your query strings so they sit in XML-based config files instead of in the middle of your Java classes. Spring also will give you some nice features for controlling your transactions and the like. This is basically what the JPA @NamedQuery defines, and Spring or Hibernate have decent implementations of JPA.

Another possibility is to use the Query Object pattern. This would involve separating the logic for the query into its own class/object, thus having a single place to maintain it.

edited May 14 '12 at 14:37

answered May 13 '12 at 21:17

Matthew Flynn

13,345
2
38
57

Thanks for your advice. The thing is that I would like to use simple JDBC in my application. – Ionut May 14 '12 at 09:08
Spring's JDBC Template is pretty darned close, but you don't even have to use that. You can use Spring to simply inject your SQL into your query object and write your own jdbc manager. – Matthew Flynn May 14 '12 at 21:56

score 2 · Accepted Answer · answered May 14 '12 at 08:20

Consider JDBI

If you want to work closely with JDBC so that you create your own SQL and have your application manage the binding between the query parameters, then JDBI might be a good approach for you.

Essentially, you create an annotated interface that describes your DAO, and JDBI will provide an implementation at runtime. A typical DAO could look like this:

public interface MyDAO
{
  @SqlUpdate("create table something (id int primary key, name varchar(100))")
  void createSomethingTable();

  @SqlUpdate("insert into something (id, name) values (:id, :name)")
  void insert(@Bind("id") int id, @Bind("name") String name);

  @SqlQuery("select name from something where id = :id")
  String findNameById(@Bind("id") int id);

  /**
   * close with no args is used to close the connection
   */
  void close();
}

This approach lends itself to a very lightweight handling of SQL. If your needs are more geared towards supporting multiple databases or abstracting those databases away from the SQL and into a pure object model, then JPA is probably a better approach.

Manage query strings in JDBC methods

2 Answers2