0

In 2006 I wrote my own JDBC-connection-pooling for Oracle-Connections. I have stored the collections in a Vector and every night I instantiated a new Vector-object to initialize the connection-pool:

connections = new Vector(poolsize);

Thus, all the existing connections were deleted by the garbage-collector and Oracle deleted the connections.

To be honest, it's a very poor solution - but it works for 12 years without problems!

This year we updated our Oracle-version to 12.2.0.1.0 and I updated the Oracle-JDBC-driver in my high-sophisticated programs.

I am currently using the Oracle Database 12.2.0.1 JDBC Driver (ojdbc8.jar), downloaded from this website:

https://www.oracle.com/technetwork/database/features/jdbc/jdbc-ucp-122-3110062.html

The database-access is working fine - except my poor connection-pool. After calling "connections = new Vector(poolsize)" the Oracle-DB does NOT remove the open connections, and the amount of open JDBC-connections increase every day - until Oracle breaks down (too many open JDBC-connections).

I know that I must close every JDBC-connection with close() instead of only initialize the Vector holding the collections. But I am wondering why the new Oracle JDBC-driver does not remove all the connections after the garbage-collection was running.

Is this an error from the new JDBC-driver?

In all older JDBC-drivers this error will not occur - it occurs only with the new ojdbc8.jar.

A JDBC-driver should automatically close all database-related objects (for example ResultSets) if there are not reachable. I do not believe that every JDBC-developer will close the ResultSet-objects after the database-operation has finished. I have not tested whether ojdbc8.jar will close these kind of unclosed ResultSet-Objects, but if not, some programs will blow up in the future.

What do you think, is there a bug in the new JDBC-driver because unreachable JDBC-connections were not closed automatically?

SipCat
  • 47
  • 1
  • 8
  • 2
    I don't believe there has ever been a guarantee that connections are automatically closed in GC. – Compass Aug 20 '18 at 15:38
  • Possible duplicate: https://stackoverflow.com/questions/8066501/how-should-i-use-try-with-resources-with-jdbc – Tim Biegeleisen Aug 20 '18 at 15:43
  • 2
    _"[..] I wrote my own JDBC-connection-pooling for Oracle-Connections."_ There is your mistake... – Mark Rotteveel Aug 20 '18 at 15:44
  • 1
    *A JDBC-driver should automatically close all database-related objects (for example ResultSets) if there are not reachable* - No. You, as the developer, are required to do it if you don't need a connection anymore. If it *worked* for 12 years you have just been really lucky. – BackSlash Aug 20 '18 at 15:46
  • 1
    And indeed, relying on the finalizer to close connections for you is bad form and an accident waiting to happen, depending on the memory needs of your application and the generation the objects are living in, the garbage collector used, etc, the finalizer may never be invoked at all (or maybe hours later). Not to mention the connection never being eligible for garbage collection if it may still be strongly referenced somewhere. – Mark Rotteveel Aug 20 '18 at 15:48

3 Answers3

2

Depending on finalizers in Java to discard resources is discouraged 1, 2.

You should switch to a well tested pooling library e.g. HikariCP. There are a lot of gotchas e.g. how to properly reset connection after rollback occured, see Pool Analysis or Bad Behavior: Handling Database Down. Writing and maintaining this code yourself is counterproductive.

Karol Dowbecki
  • 43,645
  • 9
  • 78
  • 111
0

Not every JDBC developer will close resources, but every good developer will.

If you have connections in a pool, they're strongly reachable. This means they won't be GC'd and the finalize() method (if one exists in the driver's classes) that would free resources won't be called.

Don't blame the driver first, they tend to be heavily tested unlike your code.

Kayaman
  • 72,141
  • 5
  • 83
  • 121
  • Thank you for the answers. Yes, it was my own mistake, but I have never realized it - until I installed the new JDBC-driver. – SipCat Aug 21 '18 at 15:36
0

You can use Universal Connection Pool (UCP) which is the Java connection pool. Refer to the UCPSample.java. Also, refer to UCP Developer's guide for more details.

Nirmala
  • 1,278
  • 1
  • 10
  • 11