All versions: 3.11 | Development versions: 3.12

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.

The jOOQ Logo