CODEWITHSUNDEEP
javajdbcprepared-statement
strauberry
strauberry

Reputation: 4199

Where to put the prepared statement OBJECTS in Java?

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 ^^

Upvotes: 5

Views: 3048

Answers (4)

Malakai
Malakai

Reputation: 3131

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)

Upvotes: 0

Zeemee
Zeemee

Reputation: 10704

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.

Upvotes: 5

Bharat Sinha
Bharat Sinha

Reputation: 14363

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");

Upvotes: 2

Aman J
Aman J

Reputation: 1855

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()

Upvotes: 1

Related Questions