HikariCP API?

[vagupta]

HikariCP is my first experience with connection pooling for MySQL. Is there any kind of documentation that details the API for HikariCP usage? Or should I just read through the source?

Thank you!

[vagupta]

I understand how to configure and set up the data source, but I'm just wondering how I should properly manage a connection.

[brettwooldridge]

Generally, you want to get a connection from the pool, use it for a query or queries and then close() the connection (returning it to the pool). For most applications, such as web applications, the typically out-of-pool time for a connection is typically on the order of milliseconds or maybe a few seconds. So, for highly-available applications, you want to get a connection, use it, and close() it as quickly as possible so that it becomes available to other threads in the system.

In the case of a data warehouse application, where reports and queries may run for hours, obviously the connection is out of the pool for as long as it takes a query to run.

[vagupta]

Thanks for the response!

One follow-up question: If I have a Connection connection, do I call connection.close() to close() that connection and return it to the connection pool?

If so, why? I was under the impression that calling close() on a Connection actually deletes the underlying resources for that Connection. How does the connection pool know when I call close() on a pooled Connection? Is that where the delegation comes into play? What does isClosed() check?

I hope my question makes sense. I'm really trying to understand the difference between closing a Connection and returning it the connection pool.

[brettwooldridge]

You are correct. HikariCP (and all JDBC connection pools) wrap the real Connection in a delegating proxy. This proxy intercepts (overrides) calls to close(), and instead of closing the underlying Connection instead returns it to the pool. In terms of the JDBC specification, this proxy is called a Logical Connection and the real connection to the database is called a Physical Connection.

Mostly, the application code should not need to know whether it is running against a pooled connections or real connections. The access pattern is the same; call DataSource.getConnection() and after executing queries call Connection.close().

If you obtain a Connection from the pool, then call close(), if you then call isClosed() you will receive a value of true. From the perspective of the application, the Connection is closed. If you try to call methods on the Connection after closing, you will receive a SQLException (just like a non-pooled Connection). In reality, however, the Physical Connection is not closed, but the Logical Connection is closed.

Again, the goal of every pool (and in accordance with the JDBC specification) is to provide identical API behavior from the perspective of an application.

[brettwooldridge]

Just to add a little more detail. There is no way for an application to force a close of the Physical Connection. The pool controls the life-cycle of the Physical Connection. By setting the maximumPoolSize, idleTimeout, and maxLifetime properties you can control the life-cycle of Physical Connections in a very general way.

[vagupta]

Wow, okay. I appreciate your explanation. Now, I've got a much better idea of how the pooling works. I just realized that "Connection" is an interface and not the actual object.

Thank you for your help, and thank you for making HikariCP.