swirl
Home Software Blog Wallpapers Webtools
Behavior of various JDBC DataSources
Sunday 31, May 2020   |   Post link

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:

  1. Executing a query which returns the number of authors
  2. Check the number of connections in MySQL
  3. Execute a query which returns two authors
  4. Check the number of connections in MySQL
  5. Close the DataSource's internal connection
  6. 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;

Mysql screenshot

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.




Comments

Posts By Year

2020 (6)
2019 (6)
2018 (8)
2017 (11)
2016 (6)
2015 (18)
2014 (2)
2013 (4)
2012 (2)

Posts By Category

.NET (1)
.NET Core (2)
ASP.NET MVC (4)
AWS API Gateway (1)
Android (1)
Architecture (1)
Azure (2)
Book review (3)
Business (1)
C# (3)
C++ (1)
Containers (3)
Corporate culture (1)
Database (3)
Database migration (1)
Desktop (1)
DotNet (2)
Entity Framework (3)
Git (2)
IIS (1)
JDBC (1)
Java (5)
Life (6)
Linux (1)
Lucene (1)
Multi-threading (1)
OData (1)
Office (1)
PHP (1)
PowerShell (2)
Programming (21)
Rants (5)
SQL (2)
SQL Server (1)
Software Engineering (1)
Software development (1)
Solr (1)
Sql Server (2)
Storage (1)
T-SQL (1)
TDD (1)
TSQL (5)
Tablet (1)
Technology (1)
Test Driven (1)
Unit Testing (1)
Utilities (2)
VC++ (1)
VMWare (1)
VSCode (1)
Visual Studio (2)
Wallpapers (1)
Windows (9)
XML (2)

Posts By Tags

.NET(3) API Gateway(1) ASP.NET(4) AWS(1) Adults(1) Advertising(1) Android(1) Anti-forgery(1) Azure(2) Backup(1) Beliefs(1) BlockingQueue(1) Book review(2) Books(1) Busy(1) C#(3) C++(3) CLR(1) CORS(1) CSRF(1) CTE(1) Callbacks(1) Checkbox(1) Cmdlet(1) Commons(1) Company culture(1) Complexity(1) Consumer(1) Consumerism(1) Containers(3) Core(2) DPI(1) Data-time(1) Database(4) Debugging(1) Delegates(1) Developer(2) Dockers(2) EF 1.0(1) Entity framework(1) Events(1) File copy(1) File history(1) Font(1) Git(2) GradleApache(1) HierarchyID(1) IIS(1) Installing(1) Intelli J(1) JDBC(1) JSON(1) JUnit(1) Java(1) JavaScript(1) Linux(1) Localization(1) Log4J(1) Lucene(1) MVC(4) Management(2) Migration history(1) Mobile Apps(1) Modern Life(1) Money(1) OData(1) OPENXML(1) Objects(1) Office(1) Organization(1) PHP(1) Paths(1) PowerShell(2) Producer(1) Programming(1) Python(1) Quality(1) REDIS(2) Runtimes(1) SD card(1) SQL(2) SQL Code-first Migration(1) SSH(1) Sattelite assemblies(1) School(1) Self reliance(1) Shell(1) Solr(1) Sony VAIO(1) Spirituality(1) Sql Express(1) System Image(1) TDD(1) TSQL(3) Table variables(1) Tables(1) Tablet(1) Url rewrite(1) VMWare(1) Validation(2) Wallpaper(1) Wallpapers(1) Web Development(4) Windows(2) Windows 10(2) Windows 2016(2) Windows 8.1(1) Work culture(1) XML(1) Yii(1)