When to create/initialize the prepared statement

Question

I have the following Query class with 2 methods, insert() method is used frequently and deleteRecord() is not.

public class Query1 {

private final static String INSERT = "insert into info values (?, current_timestamp)";
private final static String DELETE_RECORD = "delete from info where stamp = ?";

private static final Connection con = DatabaseConnection.getInstance();

//This method is used frequently
public static void insert(List<String> numList) throws SQLException {
    try (PreparedStatement st_insert = con.prepareStatement(INSERT)) {
        for (int i = 0; i < numList.size(); i++) {
            st_insert.setString(1, numList.get(i));
            st_insert.addBatch();
        }
        st_insert.executeBatch();
    }
}

// This method is NOT used frequently
public static void deleteRecord(Timestamp stamp) throws SQLException {
    try (PreparedStatement st = con.prepareStatement(DELETE_RECORD)) {
        st.setTimestamp(1, stamp);
        st.execute();
    }
}

I converted Query1 class to below in which the PreparedStatement used by the insert() method is initialized in the static block since it's frequently used.

public class Query2 {
private final static String INSERT = "insert into info values (?, current_timestamp)";
private final static String DELETE_RECORD = "delete from info where stamp = ?";

private static final Connection con = DatabaseConnection.getInstance();

// Frequently used statements
private static PreparedStatement st_insert;
static {
    try {
        st_insert = con.prepareStatement(INSERT);
    } catch (SQLException ex) {            
    }
}

//This method is used frequently
public static void insert(List<String> numList) throws SQLException {        
    for (int i = 0; i < numList.size(); i++) {
        st_insert.setString(1, numList.get(i));            
        st_insert.addBatch();
    }
    st_insert.executeBatch();
}

// This method is NOT used frequently
public static void deleteRecord(Timestamp stamp) throws SQLException {
    try (PreparedStatement st = con.prepareStatement(DELETE_RECORD)) {
        st.setTimestamp(1, stamp);
        st.execute();
    }
}

Does this optimize the code considering the use of prepared statements or is this not a good practice? if not how to do it? (I'm a beginner to JDBC and haven't come across any code examples like this.)

Any suggestions would be greatly appreciated.

Could depend on how often your method is called, but I'd suggest sticking with the former so that you periodically guarantee resources are cleaned up. In the latter (static) approach you have no way to ever clean up resources. Neither case handles connection updates, but that would be another reason to use the former, so you handle other DB issues better. — user1676075, Aug 27 '14 at 16:48
from this book http://oreilly.com/catalog/jorajdbc/chapter/ch19.html I think the PreparedStatement, if you are not reusing it for many statements, is actually a little bit slow than a regular Statement. Of course, PreparedStatement is a good approach to avoid SQL injection. — Leo, Aug 27 '14 at 17:00

score 1 · Accepted Answer · answered Aug 29 '14 at 08:07

Your reasoning is correct. A frequently executed query could benefit from using a PreparedStatement. Exactly what the tradeoffs are will vary between DBs. You tagged with javadb, and if that is what you use PreparedStatements will never be slower, as regular statements go through the same compilation process.

That said, I agree with those who advise against preparing the statement in a static block. I typically try to prepare statements in the constructor or an init method, so that I can reuse the ps in frequently called methods.

Also note that even a ps can be recompiled "behind your back" because of changes that may affect how the query must/should be executed (addition of indices, change in statistics, change in privileges etc.)

When to create/initialize the prepared statement

1 Answers1