Where to put the prepared statement OBJECTS in Java?

Question

I want to use prepared statements. I have read that the advantage of prepared statements is that they don't have to be parsed/compiled every time again so one reduces load. Now my question is, where the "recognition" of an already prepared statement takes place, in Java or within my DB system? I ask, because I want to know where to store my PreparedStatement object in my code: as class attribute and set the parameters with every request OR create a new prepared statement object whenever there is a request.

    public class Option1 {
       private PreparedStatement myStatement;
       public Option1() {
          // create the myStatement object
          myStatement = conn.prepareStatement("");
       }
       public List<Items> query() {
          // just use the myStatement object
          myStatement.setString(1, "foo");
       }
   }

    public class Option2 {
       public List<Items> query() {
          PreparedStatement myLocalStatement = conn.prepareStatement("");;
          // create and use the statement
          myLocalStatement.setString(1, "foo");
       }
   }

Now my question is, what is the better way to do it, option 1 or 2? However, I have to do a "cleanup" after every execution by doing a myStatement.close() right?

Maybe I should ask it in another way: how to reuse it in the most effective way?

UPDATE: in the case there are two answers, one prefering option 1 and one option 2, I would kindly ask the community to vote for their choice ^^

@Tichodroma Avoiding SQL injection (granted, there are other ways to get the same) — SJuan76, Sep 03 '12 at 08:51
The question is *how* to reuse it in the most effective way. — Zeemee, Sep 03 '12 at 08:51
@Tichodroma that is my point ;-) Hence, I asked, whether the DB system recognizes the already prepared statement (string comparison or whatever) OR if I have to take care for this by using the same object... Maybe the usage of "preparedStatement()" adds a flag to the query when it is sent to the DB sytem?! — strauberry, Sep 03 '12 at 08:52
First of all - don't blindly trust advice you read. Measure! Some database drivers do so much work when preparing that it takes hundreds of calls to the prepared statement before the work pays of in total time. — Thorbjørn Ravn Andersen, Sep 03 '12 at 08:59
javadocs for `prepareStatement(...)` clearly state that the fact of the query being precompiled or not depends on the driver implementation, and drivers implementations are responsibility of the database vendors so you should go to your driver's docs to see whether the one you are using supports precompiled queries or not. — Alonso Dominguez, Sep 03 '12 at 09:00
Thank you @AlonsoDominguez I guessed something like this... since I'm using the MySQL JConnector, I think that it is implemented ^^ — strauberry, Sep 03 '12 at 09:02

score 5 · Accepted Answer · edited May 23 '17 at 12:17

SQL databases are caching the execution plan for statement with the complete query (including where clause) as the key. By using prepared statements, the query is the same, no matter what values you are using (they are always '?').

So from the database cache perspective there is no difference between your two options. See this article, that also describes some Java EE specific issues.

But of course there are some other factors from the code perspective, as others mentioned, especially when you are reusing it very often (like in this example). On important thing ist that some JDBC drivers are supporting precompilation.
Keep in mind that prepared statements are initially overheading while catching up with every subsequent use - here's a great chapter about that.

score 2 · Answer 2 · answered Sep 03 '12 at 08:54

I would recommend Option 2

public class Option2 {
   public List<Items> query() {
      PreparedStatement myLocalStatement = conn.prepareStatement("");;
      // create and use the statement
      myLocalStatement.setString(1, "foo");
   }
}

as if the logical unit is distributed across a number of methods and data members point of failure increases and it's error prone.

Or I would do like:

public class Option2 {
   public List<Items> query(String sql, String parameter) {
      PreparedStatement myLocalStatement = conn.prepareStatement(sql);
      // create and use the statement
      myLocalStatement.setString(1, parameter);
   }
}

and use it like:

Option2 myQuery = new Option2();
myQyery.query("SELECT * FROM PERSON WHERE NAME = :?","ANY_NAME");

score 1 · Answer 3 · answered Sep 03 '12 at 08:54

I would go for Option 1: ie.

public class Option1 {
       private PreparedStatement myStatement;
       public Option1() {
          // create the myStatement object
          myStatement.setString(1, "foo");
       }
       public List<Items> query() {
          // just use the myStatement object
       }
   }

Reason : I can assign it new Object when needed. By doing some thing like this

pst = con.prepareStatement(myQuery); in Function, in your case function would be Option1()

score 0 · Answer 4 · answered Mar 16 '17 at 12:21

Personally, i like second option, and this is how i imagine

public void addAccount(String username, String password, String name) {
String query="INSERT INTO Accounts (username, password, name) VALUES (?,?,?)";
try(Connection connection = dataSource.getConnection()) {
  try(PreparedStatement statement = connection.preparedStatement(query)){
     statement.setString(1, username);
     statement.setString(2, password);
     statement.setString(3, name);
     statement.executeUpdate();
  }
 } catch(SQLException e) {
  e.printStacktrace();
 }

The reason, why you should use try-with-resource is just because it handles common coding mistakes such as close if error occures. This also guarantees so that after execution, your statement/connection/resultset will be closed (or any other issues)

Where to put the prepared statement OBJECTS in Java?

4 Answers4