Skip to main content

JDBC connection pooling

In this post, I am going to talk briefly about what JDBC connection pooling is and why it is important to use this technique in the DB driven applications.

A typical JDBC connection revolves around following aspects - 
  • open a connection to the database by using the corresponding JDBC driver
  • open a TCP socket to read and write data over the socket
  • close the socket
  • close the connection
As can be deduced from the above aspects, creation of a JDBC connection is pretty time expensive operation. Generally speaking, the application shouldn't spend much time on JDBC connection creation while performing an operation involving DB interaction as it would add to the latency. Until unless, it becomes very much needed, JDBC connection creation for each and every DB operation should be avoided for the sake of having a faster application. The alternative to this is to have a pool of readily available JDBC connections in the application runtime. Whenever the application needs a DB connection, it can get one from the pool and if all the pooled connections have been used, then a new connection can be created. Once the DB operation is done, the connection can be returned to the pool. 

There are several JDBC connection pooling implementation available to choose from. Some of the mostly used libraries are - 
  • Apache Commons DBCP
  • HikariCP
  • C3PO
These libraries support various features like maintaining minimum and maximum number of idle connections, connection timeout settings, prepared statement caching, maximum ResultSet size etc (all of these may not be supported by a single library). 

Whenever a JDBC connection is used, it's important to close the Connection object along with ResultSet (if used) and Statement/PreparedStatement objects. This means that a Connection object retrieved from a JDBC connection pool also needs to be closed once the DB operation is done. But the catch is that the Connection object goes back to the pool instead of getting actually closed, the connection pooling libraries use wrappers to achieve that. Otherwise if the Connection object gets actually closed, it can't be reused and hence the whole point of connection pooling gets defeated. In the context of Spring, a Connection object gets automatically closed by either JDBC template or Spring managed transaction based on which one is used.

(Note: JDBC connections can be created using java.sql.DriverManager and javax.sql.DataSource classes. The former is DB agnostic whereas the latter is DB dependent.)

Comments

Post a Comment

Popular posts from this blog

Working in India

The day I started working in SRA India(Indian arm of Japan's Software Research Associates, Inc ), I never thought that I world become an onsite team member in just one and half years. Because, the branch was very small & it was very illogical for a novice like me to think of an onsite tour that time. But the fact was that they would make you do your work in almost Japanese style. The very first day I started coding in SRA India, I was told that the Japanese were simply put - perfectionists. This simple word had a very large inner meaning. The code that you wrote should be totally bug free, robust, modifiable without introducing regression etc. The first project I was assigned to, it took a hell lot days to prepare only the detailed design(Java Doc) for a very tiny function, every molecular level detail was described on that. But somehow I made myself adjusted to such work environment. My performance was good in my batch. And in one day, one of my managers told me of an onsite a...
Post a Comment
Read more

Java collection series - miscellaneous

Java Vector is a legacy class. And it is significantly faster in comparison to a list obtained through Collections.synchronizedList(). Vector has loads of legacy operations and hence the manipulations in Vector needs to be done through the List interface, otherwise you won't be able to replace the implementation at a later time. Arrays.asList() is better choice if the list is of fixed size and any kind of size mutation of the collection results in UnsupportedOperationException. The underlying array is updated whenever the list is updated (or vice-versa), but the array reference isn't retained. Collections.nCopies() is another convenient mini-implementation which can be useful in two ways - initialize a newly created list with n null values (need not be only null values) -  new ArrayList (Collections.nCopies(1000, (Type)null)  grow an existing list -  lovablePets.addAll(Collections.nCopies(69, "fruit bat")) Collections.singleton()/Collectio...
Post a Comment
Read more

DB transaction ACID properties

DB transaction is a combination of different operations. If not performed in a proper manner, different transactions working on the same data at the same time may leave the data in corrupted state, effecting the application. In this article, I am going to illustrate DB transaction ACID properties through an example of money transfer application between two different accounts A and B. To begin with, lets suppose that accounts A and B both have initial balance of $100. ACID stands for Atomicity , Consistency , Isolation and Durability . Let's try to understand these one by one. Atomicity : This is the property that mandates that if a transaction is started, either all the operations which are part of the transaction need to be completed by end of the transaction completion as a single unit of work or none of the operations needs to be completed. It is maintained by transaction management component. If a debit of $10 is made from account A, then the corresponding credit of $10 al...
Post a Comment
Read more