CODEWITHSUNDEEP
javajdbcjavadb
Kzhunter
Kzhunter

Reputation: 627

When to create/initialize the prepared statement

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.

Upvotes: 3

Views: 5976

Answers (1)

Dyre
Dyre

Reputation: 547

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

Upvotes: 1

Related Questions