Does hibernate use PreparedStatement by default

Question

"Hibernate always uses PreparedStatement for calls to the database" Quoted here. If so then where does hibernate cache compiled queries, does DB driver cache them.

I read about c3p0. If hibernate caches PreparedStatement by default then what is the use of hibernate.c3p0.max_statements in c3p0. If hibernate does not do it by default then is connection pooling mandatory for caching prepared statements.

Could somebody please clarify these.

Solution

Caching prepared statements only makes sense in the scope of a specific JDBC connection. So, you will only gain something out of caching prepared statements when a sort of connection pooling is available to the ORM layer. Otherwise, you get a new "physical" JDBC connection every time you create a Hibernate Session (which is not very efficient normally). Without any connection pooling caching prepared statements is only useful in the scope of a single JDBC connection / Hibernate Session. This happens because without any connection pooling the "physical" connection is actually closed and won't be reused - instead always a new connection will be created using the database driver, whenever it is required.

One other thing that you need to take into account, is that the number of open prepared statements on a single JDBC connection is limited (the limitation is vendor dependent and varies between driver implementations as far as I know). So, in a pooled connections scenario, the pooling implementation will likely need to know how many open prepared statements may be maintained on each of the pool's "physical" underlying JDBC connections. Likely, a "least used prepared statements gets closed first" policy is implemented, but this is pure speculation on my part.

I hope this makes some sense. Whenever I mention a "physical" JDBC connection I mean an actually new TCP/IP connection to the database. Connections obtained by a connection pool will typically decorate/wrap a "physical" one.

Edits to answer your questions more directly:

Hibernate most likely uses and caches PreparedStatements (this is very basic JDBC optimization). The question is does this caching happen on the statements created by a "physical" or a pool provided JDBC connection. Without a pool caching the PreparedStatements only optimizes the part of the application execution that uses a specific PreparedStatement twice in the scope of a specific Hibernate Session. With a pool the same PreparedStatement will (effectively) be used across many Hibernate Session instances that will happen to use the same underlying "physical" connection.

The property hibernate.c3p0.max_statements of your hibernate configuration will most likely configure the C3PO pool instance (which I am pretty sure is created automatically for you) and this configuration has something to do with the fact about the number of open prepared statements being limited in a "physical" JDBC connection.

Comments

[cooper]

so nice of you, a neat explanation. Is PreparedStatement achievable through criteria or save/update/saveOrUpdate in hibernate?

[dkateros]

I'd expect every SQL statement generated by Hibernate to be prepared and then executed. In a word, yes.

[Sumit Jain]

So caching happens at connection & pool level (by caching connections), but doesn't hibernate have its own query cache apmblog.dynatrace.com/2009/02/16/… ?

[dkateros]

The query cache aka 2nd level cache (the first level cache being the hibernate session) caches entities (query results). The discussion here is about caching prepared statements. Caching prepared statements has nothing to do with the results produced when a prepared statement is executed.

[petertc]

jdbc4 supports preparestatment in driver level according to github.com/brettwooldridge/HikariCP

[Arun Raaj]

@dkateros Is every sql statement 'prepared' in Spring-data JPA as well?