Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Preventing JDBC Resource Leaks With JDBC and jOOQ

DZone's Guide to

Preventing JDBC Resource Leaks With JDBC and jOOQ

When it comes to resources, it is important to constantly remind ourselves that the scope that acquires the resource closes the resource.

· Performance Zone
Free Resource

Discover 50 of the latest mobile performance statistics with the Ultimate Guide to Digital Experience Monitoring, brought to you in partnership with Catchpoint.

In a recent consulting gig, I was analyzing a client’s connection pool issue in a productive system. During some peak loads, all the Java processes involving database interactions just started queueing up until nothing really worked anymore. No exceptions, though, and when the peak load was gone in the evening, everything returned back to normal. The database load looked pretty healthy at the time, so no actual database problem was involved – the problem had to be a client side problem.

Weblogic operations teams quickly identified the connection pool to be the bottleneck. All the connections were constantly allocated to some client process. The immediate thought was: A resource leak is happening and it didn’t show before because this was an exceptional situation, around the beginning of the new year when everyone wanted to download their electronic documents from the bank (and some new features introduced many more document related database calls).

The Obvious Problem

That particular system still runs a lot of legacy code in Java 6 style, which means, there are tons of code elements of the following kind:

Connection connection = null;
try {

  connection = JDBCHelper.getConnection();
}
finally {

  JDBCHelper.close(connection); 
}

While the above code is perfectly fine and 99% of all database interactions were of the above type, there was an occasional instance of someone badly copying and pasting some code and doing something like this:

Connection connection = JDBCHelper.getConnection();
PreparedStatement stmt = null;
try {
  stmt = connection.prepareStatement("SELECT ...");
}
finally {

  JDBCHelper.close(stmt);
}

Sometimes, things were even more subtle, as a utility method expected a connection like this:

public void databaseCalls(Connection connection) {
  try {
    stmt = connection.prepareStatement("SELECT ...");
  }
  finally {

    JDBCHelper.close(stmt);
  }
}
public void businessLogic() {
  databaseCalls(JDBCHelper.getConnection());
}

Thoroughly Fixing These Things

There’s a quick fix to all these problems. The easiest fix is to just continue rigorously using the JDBCHelper.close() method (or just call connection.close() with appropriate error handling) every time.

Apparently, that’s not easy enough, as there will always be a non-vigilant developer (or a junior developer who doesn’t know these things) who will get it wrong, who will simply forget things.

I mean, even the official JDBC tutorial gets it “wrong” on their first page.

The bad example being:

public void connectToAndQueryDatabase(
    String username, String password) {
    Connection con = DriverManager.getConnection(
                         "jdbc:myDriver:myDatabase",
                         username,
                         password);
    Statement stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery(
        "SELECT a, b, c FROM Table1");
    while (rs.next()) {
        int x = rs.getInt("a");
        String s = rs.getString("b");
        float f = rs.getFloat("c");
    }
}

All resources leak in this example!

Of course, it’s just an example, and of course, it’s not a terrible situation because resources can usually clean up themselves when they go out of scope, i.e., when the GC kicks in. However, as software engineers, we shouldn’t rely on that, and as the productive issues have shown, there are always edge cases where precisely this lack of vigilance will cause great harm. After all, "it works on my machine" is simply not an excuse. We should design our software for productive use.

Fix #1: Use Try-With-Resources. Always.

If you want to stay on the safe side, always follow this rule: The scope that acquires the resource closes the resource.

As long as you’re working with JDBC, save yourself the trouble of writing those JDBCUtilities classes that close non-null resources and safely catch exceptions that may arise. Just use try-with-resources, all the time. For instance, take the example from the Oracle JDBC tutorial, which should read:

public void connectToAndQueryDatabase(
     String username, String password) {
    
    try (Connection con = DriverManager.getConnection(
            "jdbc:myDriver:myDatabase", username, password);
         Statement stmt = con.createStatement();
         ResultSet rs = stmt.executeQuery(
            "SELECT a, b, c FROM Table1")) {
        while (rs.next()) {
            int x = rs.getInt("a");
            String s = rs.getString("b");
            float f = rs.getFloat("c");
        }
    }
}

This already feels that much better and cleaner, doesn’t it? All the resources are acquired in the above method, and the try-with-resources block will close all of them when they go out of scope. It’s just syntax sugar for something we’ve been doing manually all the time. Now, we will (hopefully) never again forget!

Of course, you could introduce automatic leak detection in your integration tests, because it’s rather easy to proxy the JDBC DataSource and count all connection acquisitions and closings. An example can be seen in this post.

Fix #2: Use jOOQ, Which Manages Resources for You

Historically, JDBC works on lazy resources that are kept around for a while. The assumption in 1997 (when JDBC was introduced) was that database interactions were relatively slow and it made sense to fetch and process one record at a time, even for moderately sized result sets.

In fact, it was even common to abort fetching records from a cursor when we’ve had enough results and close it eagerly before consuming all the rows.

Today, these assumptions are (mostly) no longer true, and jOOQ (like other, more modern database APIs) invert the lazy and eager API default behavior. In jOOQ, the JDBC types have the following corresponding counterparts:

JDBC DataSource / Connection => jOOQ ConnectionProvider

jOOQ doesn’t know the concept of an “open connection” like JDBC. jOOQ only has the ConnectionProvider, which works in a similar way to JDBC’s and JavaEE’s DataSource. The semantics here is that the connection or session is “managed” and jOOQ will acquire and release it once per statement. This happens automatically, so users don’t have to worry about any connection resource.

JDBC Statement (and Subtypes) => jOOQ Query

While the JDBC statement (especially the PreparedStatement) is a resource that binds some server-side objects, such as an execution plan, jOOQ again doesn’t have such a resourceful thing. The Query just wraps the SQL string (or AST) and bind variables. All resources are created lazily only when the query is actually executed and released immediately after execution. Again, users don’t have to worry about any statement resource.

JDBC ResultSet => jOOQ Result

The JDBC ResultSet corresponds to a server-side cursor, another object that possibly binds quite a few resources, depending on your fetch mode. Again, in jOOQ no resources are bound / exposed, because jOOQ by default eagerly fetches your entire result set – the assumption being that a low-level optimisation here doesn’t add much value for moderately sized result sets

With the above-inverted defaults (from lazy to eager resource allocation and freeing), the jOOQ-ified Oracle JDBC tutorial code would look like this:

Working with a standalone connection:

public void connectToAndQueryDatabase(
    String username, String password) {

    try (Connection con = DriverManager.getConnection(
            "jdbc:myDriver:myDatabase", username, password)) {
    
        for (Record record : DSL.using(con).fetch(
                "SELECT a, b, c FROM Table1")) {
            int x = rs.get("a", int.class);
            String s = rs.get("b", String.class);
            float f = rs.get("c", float.class);
        }
    }
}

Working with a connection pool or DataSource:

@Inject
DataSource ds;
public void connectToAndQueryDatabase(
    String username, String password) {

    for (Record record : DSL
           .using(ds, SQLDialect.ORACLE)
           .fetch("SELECT a, b, c FROM Table1")) {
        int x = rs.get("a", int.class);
        String s = rs.get("b", String.class);
        float f = rs.get("c", float.class);
    }
}

With jOOQ, all resource management is automatic by default because by default, you don’t want to worry about this low-level stuff. It’s not 1997 anymore. The JDBC API really is too low-level for most use-cases.

If you do want to optimize resource management and not fetch everything eagerly, you can, of course. jOOQ will allow you to fetch your results lazily, in two ways:

Using a cursor:

@Inject
DataSource ds;
public void connectToAndQueryDatabase(
    String username, String password) {
    
    try (Cursor<Record> cursor : DSL
            .using(ds, SQLDialect.ORACLE)
            .fetchLazy("SELECT a, b, c FROM Table1")) {
        for (Record record : cursor) {
            int x = record.get("a", int.class);
            String s = record.get("b", String.class);
            float f = record.get("c", float.class);
        }
    }
}

Using a Java 8 stream (lazy, resourceful version):

@Inject
DataSource ds;
public void connectToAndQueryDatabase(
    String username, String password) {
    
    try (Stream<Record> stream : DSL
        .using(ds, SQLDialect.ORACLE)
        .fetchStream("SELECT a, b, c FROM Table1")) {
        stream.forEach(record -> {
            int x = record.get("a", int.class);
            String s = record.get("b", String.class);
            float f = record.get("c", float.class);
        });
    }
}

Unfortunately, there are no auto-closing streams in Java, which is why we have to resort to using the try-with-resources statement, breaking the fluency of jOOQ’s API.

Do note, though, that you can use the Stream API in an eager fashion:

Using a Java 8 stream (eager version):

@Inject
DataSource ds;
public void connectToAndQueryDatabase(
    String username, String password) {

    DSL.using(ds, SQLDialect.ORACLE)
       .fetch()
       .stream("SELECT a, b, c FROM Table1")
       .forEach(record -> {
            int x = record.get("a", int.class);
            String s = record.get("b", String.class);
            float f = record.get("c", float.class);
        });
}

Conclusion

Developers, unfortunately, often suffer from, "Works on my machine!" This leads to problems that can be discovered only in production under load. When it comes to resources, it is important to constantly remind ourselves that the scope that acquires the resource closes the resource.

JDBC (and the JDK’s IO APIs), “unfortunately,” deal with resources on a very low level. This way, their default behavior is very resource-efficient. For instance, when you only need to read a file header, you don’t load the entire file into memory through the InputStream. You can explicitly, manually, only load the first few lines.

However, in many applications, this default and its low-level nature gets in the way of correctness (accidental resource leaks are easy to create), and convenience (a lot of boilerplate code needs to be written).

With database interactions, it’s usually best to migrate your JDBC code towards a more modern API like jOOQ, which abstracts resource handling away in its API and inverts the lazy and eager semantics: Eager by default, lazy on demand.

More information about the differences between jOOQ and JDBC can be seen here, in the manual.

Is your APM strategy broken? This ebook explores the latest in Gartner research to help you learn how to close the end-user experience gap in APM, brought to you in partnership with Catchpoint.

Topics:
performance ,tutorial ,jdbc ,jooq ,resource leaks

Published at DZone with permission of Lukas Eder, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

THE DZONE NEWSLETTER

Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

X

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}