Creating database connection is resource intensive and it is very crucial to have a connection pool mechanism to reduce system overload, especially when you aim to do many interaction with database. Most of enterprise frameworks such as EJB, Hibernate have connection pool automatically by default and you just need to configure slightly corresponding XML file to determine pool size, database name and so on.
However, JDBC does not provide any pool mechanism and the only remaining option is utilizing a third party library. I personally after much of research and reading forums and different stuff came to conclusion that programmers should not use JDBC at all in production. It is more suitable for school, college assignments/projects. Though, for sack of experiment and learning more things, it is worth trying. Anyhow, the best and most suitable library for making connection pool using JDBC is Tomcat JDBC Connection Pool.
In Maven adding this library is pretty simple. You just open pom.xml file and add the following code to dependencies section
Now let’s move on to coding portion. The first thing you need to do is to create a class let’s call it
DataSource.java. We are going to apply connection pooling and all configuration in this class.
The class looks like the following,
The value of
DATA_SOURCE_CLASS_NAME depends on type of database you would like to use. In my case I use Postgresql. Keep in your mind, for other databases such as MySQL, Oracle, SQLite the value is different.
PortNumber should be configured once the class has initialized, otherwise the program will throw exception.
The most important part in this class is a function called
setupPool. This function is responsible for creating the pool and configuring it. It is quite similar to Hibernate XML and has the same purpose. Much of the code of this function is copied from official The Tomcat JDBC Connection Pool website, click here.
You should call this function once your program started before getting any connection. And last function is
getConnection which whenever you need to connect to database, you should call this function.
Now let’s have a look on how to use
As you can see in the code,
configureConnection method is called in the first line of main function. This function basically initializes DataSource class and set appropriate values such as username, password, host, etc. The values can be from anywhere, most probably from a XML file or hard coded, though it is not a good idea to hard-code any values.
Then in the main a simple database activity is done. Pay attention that for making a database connection, we use
getConnection method that coded earlier.