How to use a PreparedStatement in a multi-threaded environment to take
advantage of precompilation? Is a statement once prepared usable across
a different thread?
I don't know.
For example in the following method, would it matter if I use a
PreparedStatement vs Statement assuming updateStat() can be called my
multiple threads and I execute only once in this method.
public void updateStat() {
Connection con = getConnection(); // this is retrieved in thread safe
manner
PreparedStatement stmt = con.prepareStatement(
"UPDATE MYTABLE SET STATUS= ? WHERE ID = ? ");
..
..
}
I'd pessimistically assume that this would recompile the statement every
time.
I'd be tempted to take charge of my own destiny and do something like:
public class StatusUpdater {
private Connection conn ;
private PreparedStatement updateStmt ;
public StatusUpdater(Connection conn) throws SQLException {
this.conn = conn ;
conn.setAutoCommit(true) ;
updateStmt = conn.prepareStatement("UPDATE MYTABLE SET STATUS = ? WHERE ID = ? ") ;
}
public void updateStatus(String id, String status) throws SQLException {
updateStmt.setString(1, status) ;
updateStmt.setString(2, id) ;
int rows = updateStmt.executeUpdate() ;
if (rows != 1) throw new SQLException("bad row count: " + rows) ;
}
public boolean isValid() {
try {
return conn.isValid() ;
}
catch (SQLException e) {
return false ;
}
}
public void close() throws SQLException {
conn.close() ;
}
}
import org.apache.commons.pool.PoolableObjectFactory ;
public class StatusUpdaterFactory implements PoolableObjectFactory {
private String dbURL ;
private Properties dbProps ;
public Object makeObject() throws SQLException {
Connection conn = DriverManager.getConnection(dbURL, dbProps) ;
return new StatusUpdater(conn) ;
}
public boolean validateObject(Object obj) {
return ((StatusUpdater)obj).isValid() ;
}
public void destroyObject(Object obj) throws SQLException {
((StatusUpdater)obj).close() ;
}
public void activateObject(Object obj) {}
public void passivateObject(Object obj) {}
}
import org.apache.commons.pool.impl.StackObjectPool ;
import org.apache.commons.pool.impl.ObjectPool ;
String dbURL ;
Properties dbProps ;
ObjectPool statusUpdaterPool = new StackObjectPool(new StatusUpdaterFactory(dbURL, dbProps), 10) ;
And then:
public void updateStat(String id, String status) {
StatusUpdater updater = (StatusUpdater)statusUpdaterPool.borrowObject() ;
try {
updater.updateStatus(id, status) ;
}
finally {
statusUpdaterPool.returnObject(updater) ;
}
}
Hopefully, the Apache Commons guys will get round to making the pool stuff
generic at some point. Should be pretty trivial.
tom
