When to create / initialize prepared statement
I have the following Query class with two methods, the method is insert()
used frequently and deleteRecord()
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 have converted the Query1 class below where the PreparedStatement used by the method insert()
is initialized in a static block as it is used a lot.
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 for the use of prepared statements, or is it not a good practice? if not how to do it? (I'm new to JDBC and haven't come across code examples like this.)
Any suggestions are greatly appreciated.
source to share
Your reasoning is correct. A frequently used query can benefit from the use of PreparedStatement. Exactly what tradeoffs will vary between DBs. You tagged javadb and if this is what you are using PreparedStatements will never be slower as the regular assertions go through the same compilation process.
However, I agree with those who advise against preparing the expression in a static block. I usually try to prepare statements in a constructor or init method so that I can reuse ps in frequently called methods.
Also note that even ps can be recompiled behind the back due to changes that might affect how the query should / should be executed (adding indexes, changing statistics, changing privileges, etc.).
source to share