Database connections are an expensive resource, in fact every network trip is expensive which is why we are told to not create chatty interfaces. Pooling database connections allow applications to not physically close the network connection but keep the connections for later use.
With .NET using database connections is quite simple, you usually depend on the database vendor's ADO.NET driver which may or may not support connection pooling. As a developer, all you need to do is to set pooling = true in the connection string.
Dealing with connection pools in Java is a bit different. You need to use the correct DataSource implementation and there are quite a few! Each of these DataSource implementations behave differently. This post tries to capture these differences.
The test code
Integer authorCount = _jdbc.queryForObject("select count(*) from authors", Integer.class);
System.out.println("Author count is [" + authorCount.toString() + "]");
System.out.println("Press enter to continue...");
System.in.read();
List authors = _jdbc.query("select * from authors where id < 3;", (rs, rowNum) -> {
if (rowNum == 1) {
System.out.println("Press enter to continue...");
System.console().readLine();
}
Author author = new Author();
author
.setId(rs.getInt("id"))
.setFname(rs.getString("fname"))
.setLname(rs.getString("lname"));
return author;
});
if (_jdbc.getDataSource() instanceof SingleConnectionDataSource) {
System.out.println("Press enter to close the connection...");
System.console().readLine();
_jdbc.getDataSource().getConnection().close();
}
else {
System.out.println("Press enter to close the connection...");
System.console().readLine();
_jdbc.getDataSource().getConnection().close();
}
for (Author author : authors) {
System.out.println(author);
}
System.out.println("Press enter to end...");
System.console().readLine();
What we are doing is:
- Executing a query which returns the number of authors
- Check the number of connections in MySQL
- Execute a query which returns two authors
- Check the number of connections in MySQL
- Close the DataSource's internal connection
- Check the number of connections in MySQL
The number of connections was monitored the MySQL command select user, db, command, state from processlist where db is not null;
The resuts
| DataSource | After first data access | After second data access | After connection close |
|---|---|---|---|
| DriverManagerDataSource | 0 | 0 | 0 |
| Hikari | 10 | 10 | 10 |
| BasicDataSource | 1 | 1 | 1 |
| ComboPooledDataSource | 3 | 3 | 3 |
| SingleConnectionDataSource | 1 | 1 | 0 |
As you see even among the pooled connections there is a difference between the behavior of Apache Common BasicDataSource, Hikari & C3PO ComboPooledDataSource. Unlike Hikari and C3PO, Apache's pooling doesn't seem to create an initial number of connections upfront.
Now, suppose you are firing more than one jdbc template methods, there really is no need to close the connections between these call, but jdbc template method doesn't know this. In such scenarios, its better to use the SingleConnectionDataSource as jdbc template doesn't close it's connection. The only way to close the connection is to call destory() on the SingleConnectionDataSource object or by calling getConnection().close(). Note, SingleConnectionDataSource cannot be shared across threads.