java - How to use MySQL prepared statement caching?

Question

How do i take advantage of MySQL's ability to cache prepared statements? One reason to use prepared statements is that there is no need to send the prepared statement itself multiple times if the same prepared statement is to be used again.

Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb" +
        "?cachePrepStmts=true", "user", "pass");
for (int i = 0; i < 5; i++) {
    PreparedStatement ps = conn.prepareStatement("select * from MYTABLE where id=?");
    ps.setInt(1, 1);
    ps.execute();
}
conn.close()

When running the above Java example I see 5 pairs of Prepare and Execute commands in the mysqld log file. Moving the ps assignment outside of the loop results in a single Prepare and 5 Execute commands of course. The connection parameter "cachePrepStmts=true" doesn't seem to make any difference here.
When running a similar program using Spring and Hibernate the number of Prepare commands sent (1 or 5) depends on whether the cachePrepStmts connection parameter is enabled. How does Hibernate execute prepared statements to take advantage of the cachePrepStmts setting? Is it possible to mimic this using pure JDBC?
I was running this on MySQL Server 4.1.22 and mysql-connector-java-5.0.4.jar

Answer 1

You should prepare your statement only once, outside of the loop, and then bind the parameters in the loop. This is why prepared statements have bind parameters - so you can reuse the prepared statement.

Hibernate does exactly this, treating all SQL as a prepared statement behind the scenes, though you can obviously abuse this if you use literal rather than bind parameters.