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