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 implements DiagnosticsListener {
// 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.
This is why it isn't active by default on all Connections, only when proxying a connection explicitly as above. However, using Settings.diagnosticsConnection, this can be changed. To turn on debug logging, refer to Settings.diagnosticsLogging.
The following sections describe each individual event, how it can happen, how and why it should be remedied.
Table of contents
- 4.23.1.
- Too Many Rows
- 4.23.2.
- Too Many Columns
- 4.23.3.
- Duplicate Statements
- 4.23.4.
- Repeated statements
- 4.23.5.
- Consecutive aggregation (new)
- 4.23.6.
- WasNull calls
- 4.23.7.
- Concatenation in predicates (new)
- 4.23.8.
- Possibly wrong expressions (new)
- 4.23.9.
- Trivial condition
- 4.23.10.
- Transform patterns (new)
| previous : next |
Feedback
Do you have any feedback about this page? We'd love to hear it!