Why won't my Java JDBC to Firebird database connection disconnect?

Question

I am using the following code to connect to a firebird database

  public static Connection dbStatic;    
  ...
  public void getConnection(){
  FBWrappingDataSource DataSource = new FBWrappingDataSource();
  DataSource.setDatabase("localhost/3050:C:/MyDatabase.FDB");
  DataSource.setDescription("TNS Development Database");
  DataSource.setType("TYPE4");
  DataSource.setEncoding("ISO8859_1");
  DataSource.setLoginTimeout(10);
  try {
    dbStatic = DataSource.getConnection("UserName", "Password");
  } catch (SQLException e) {
    e.printStackTrace();
  } 
}

...and the following to disconnect:

...
dbStatic.close();
...

I am using Firebird 2.1 runing on a Windows 7-32 bit machine, with Java verstion 1.7, Jaybird version 2.2.8, Tomcat version 7.xx running on Win7-32bit, Browser is Chrome version something or other (newish) running Win XP SP3.

I use a third party tool called IBExpert to look at the number of connections and/or I run this statement:

select * from mon$attachments;

When I look at the number of connections to the database after the .close() statement runs the number does not decrease. Why is that? The number of connections do decrease if I wait long enough, or if the Tomcat server is restarted. Closing the browser does not affect the connections.

First line of the javadoc of [`FBWrappingDataSource`](http://www.firebirdsql.org/file/documentation/drivers_documentation/java/2.2.2/docs/org/firebirdsql/pool/FBWrappingDataSource.html): *Implementation of DataSource including **connection pooling**.* — Andreas, Dec 21 '16 at 02:10
@Arioch'The It will eventually be destroyed when the finalizer gets called. I would be surprised if the OP doesn't experience occasional problems with having his connection closed unexpectedly. This must be relatively short-lived application (or the connection is no longer in use at the time the JVM calls the finalizer). — Mark Rotteveel, Dec 21 '16 at 12:37
@MarkRotteveel the keyword here is "occasionally". I would not presume that while DB-aware object is alive it would not be able to re-connect when would feel like it. I also remember that events require a special secondary connection too. — Arioch 'The, Dec 21 '16 at 12:58
@Arioch'The There is no automatic reconnection in Jaybird itself, and the handling of events in Jaybird is separated from normal database connections. — Mark Rotteveel, Dec 21 '16 at 13:10

Mark Rotteveel · Accepted Answer · 2016-12-21T14:06:56.277

As Andreas already pointed out in the comments, FBWrappingDataSource is a connection pool. This means that the pool keeps physical connections open, and it hands out logical connections backed by the physical connections in the connection pool. Once you call close() on that logical connection, the physical connection is returned to the pool as available for reuse. The physical connection remains open.

If you want to close all connections, you need to call FBWrappingDataSource.shutdown(). This closes all physical connections that are not currently in use(!), and marks the data source as shutdown.

However, everything in package org.firebirdsql.pool should be considered deprecated; it will be removed in Jaybird 3. See Important changes to Datasources

If you just want a data source, use org.firebirdsql.pool.FBSimpleDataSource (with Jaybird 3 you will need to use org.firebirdsql.ds.FBSimpleDataSource instead).

If you want connection pooling, use a third party connection pool library like HikariCP, DBCP or c3p0.

That said, I want to point out several things you should consider:

Jaybird 2.2.8 is not the latest version, consider upgrading to 2.2.12, the current latest release of Jaybird.
Using a static field for a connection is generally not a good idea (especially with a web application), consider your design if that is really what you need. You might be better off making a data source the static field, and obtain (and close!) connections for a unit of work (ie: one request). It might also indicate that it would be simpler for you to just use DriverManager to create the connection.
Naming conventions: your variable DataSource should be called dataSource if you follow the common Java conventions
setLoginTimeout(Integer.parseInt(10)) should lead to a compilation error, as there is no method Integer.parseInt that takes an int, and the method itself already accepts an int.

This code is not my production code. It's a paired down version of the original that I created to see if I could figure out why it was not disconnecting from Firebird. In the production code we don't use static variables for the data source. What is the advantage of pooling? Is there that much processing overhead to connect to firebird? If I have a web site that has, on average, 200 to 400 visitors a day with peak usage around, I'm guessing, 30 users at once does it make sense to use connection pooling? Our database server has slowed down a lot since we implemented this web component. — Jonathan Elkins, Dec 22 '16 at 20:47
@JonathanElkins The advantage of connection pooling is that the connection is reused: it saves the time of having to create a new connection and close it. And when used correctly it can also **reduce** the number of connections you use. However if the code shown in your question is anything like the code you use in production, then you are not taking advantage of the pooling feature. Regarding the increased load on the database, impossible to tell without more information, maybe your application is doing some inefficient queries, N+1 query problem, etc, or using transactions incorrectly. — Mark Rotteveel, Dec 23 '16 at 07:49

Why won't my Java JDBC to Firebird database connection disconnect?

1 Answers1