Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 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 previous 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 }
And then:
// 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.
Table of contents
- 4.22.1.
- Too Many Rows
- 4.22.2.
- Too Many Columns
- 4.22.3.
- Duplicate Statements
- 4.22.4.
- Repeated statements
- 4.22.5.
- WasNull calls
previous : next |
Feedback
Do you have any feedback about this page? We'd love to hear it!