New versions: Dev (3.15) | Latest (3.14) | 3.13 | 3.12 | 3.11

Diagnostics

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

jOOQ includes a powerful diagnostics SPI, which can be used to detect problems and inefficiencies on different levels of your database interaction:

  • On the jOOQ API level
  • On the JDBC level
  • On the SQL level

Just like the parsing connection, which was documented in the previuos section, this functionality does not depend on using the jOOQ API in a client application, but can expose itself through a JDBC java.sql.Connection that proxies your real database connection.

// A custom DiagnosticsListener SPI implementation
class MyDiagnosticsListener extends DefaultDiagnosticsListener {
    // Override methods here
}

// Configuration is configured with the target DataSource, SQLDialect, etc. for instance Oracle.
try (Connection c = DSL.using(configuration.derive(new MyDiagnosticsListener()))
                       .diagnosticsConnection();
     Statement s = c.createStatement()) {
     
    // The tooManyRowsFetched() event is triggered.
    // --------------------------------------------
    // This logic does not consume the entire ResultSet. There is more than one row
    // ready to be fetched into the client, but the client only fetches one row.
    try (ResultSet rs = s.executeQuery("SELECT id, title FROM book WHERE id > 1")) {
        if (rs.next())
            System.out.println("ID: " + rs.getInt(1) + ", title: " + rs.getInt(2));
    }

    // The duplicateStatements() event is triggered.
    // ---------------------------------------------
    // The statement is the same as the previous one, apart from a different "bind variable".
    // Unfortunately, no actual bind variables were used, which may
    // 1) hint at a SQL injection risk
    // 2) can cause a lot of pressure / contention on execution plan caches and SQL parsers
    //
    // The tooManyColumnsFetched() event is triggered.
    // -----------------------------------------------
    // When iterating the ResultSet, we're actually only ever reading the TITLE column, never
    // the ID column. This means we probably should not have projected it in the first place
    try (ResultSet rs = s.executeQuery("SELECT id, title FROM book WHERE id > 2")) {
        while (rs.next())
            System.out.println("Title: " + rs.getString(2));
    }
}

This feature incurs a certain overhead over normal operation as it requires:

  • Parsing SQL statements and re-rendering them back to normalised SQL.
  • Storing a limited size list of such normalised SQL in a cache to gather statistics on that cache.

The following sections describe each individual event, how it can happen, how and why it should be remedied.

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo