PreparedStatement caching - what does it mean (how does it work)

java mysql hibernate jdbc c3p0
adsurbum picture adsurbum · Aug 27, 2012 · Viewed 10.3k times · Source

I'm using for example c3p0 with some defined "maxStatements" for preparedStatement caching. What does this caching really do? What kind of data it caches. On what level (db, application,..)? It will be nice to understand it from example. For example i have a query

select * from sometable where somecolumn=?

Now i send it in prepared statement that is not cached. And now i'm sending it and it is cached. What the difference. What happened in the first case and in the second. What is sent to DB server in the first case and in the second?

Thanks.

Answer

John Watts picture John Watts · Aug 27, 2012

Without caching, you will get a new PreparedStatement each time you request one from the Connection. With caching, you will frequently get the exact same Java object of type PreparedStatement if you provide the same SQL string. If you provide the same SQL to a PreparedStatement, even with different parameters, often the database can reuse information like the execution plan, but only if you continue to use the same PreparedStatement. Caching makes that easier by not requiring your app to hold on to that PreparedStatement reference itself.

Related questions

c3p0 hangs in awaitAvailable with hibernate

I have console application that hangs during execution. Here is my configuration: cfg.setProperty("hibernate.connection.driver_class", "com.mysql.jdbc.Driver"); cfg.setProperty("hibernate.connection.url", "jdbc:mysql://localhost:3306/db?user=db&password=db"); cfg.setProperty("hibernate.connection.…

Read More
JDBC Hibernate - Mysql Connection Error

I have a local mysql server on my Ubuntu 11.10 desktop. The hostname = localhost; username = root; password = root; database name = CBS. I am really confused because when I access mysql using terminal, mysql administrator, and mysql query browser I use those …

Read More
Hibernate: Automatically creating/updating the db tables based on entity classes

I have the following entity class (in Groovy): import javax.persistence.Entity import javax.persistence.Id import javax.persistence.GeneratedValue import javax.persistence.GenerationType @Entity public class ServerNode { @Id @GeneratedValue(strategy = GenerationType.AUTO) Long id String firstName String lastName } and …

Read More