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.

Answer 1

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.