Module org.jooq
Package org.jooq.impl

Class LoggingDiagnosticsListener

java.lang.Object
org.jooq.impl.LoggingDiagnosticsListener
All Implemented Interfaces:
DiagnosticsListener
public class LoggingDiagnosticsListener extends Object implements DiagnosticsListener
A default implementation of a DiagnosticsListener that logs diagnostics.
Author:
Lukas Eder

  • Constructor Details

    • LoggingDiagnosticsListener

      public LoggingDiagnosticsListener()

  • Method Details

    • duplicateStatements

      public void duplicateStatements(DiagnosticsContext ctx)
      Description copied from interface: DiagnosticsListener
      The executed JDBC statement has duplicates.

      Many databases maintain an execution plan cache, which remembers execution plans for a given SQL string. These caches often use the verbatim SQL string (or a hash thereof) as a key, meaning that "similar" but not identical statements will produce different keys. This may be desired in rare cases when querying skewed data, as a hack to force the optimiser to calculate a new plan for a given "similar" but not identical query, but mostly, this is not desirable as calculating execution plans can turn out to be expensive.

      Examples of such duplicate statements include:

      Whitespace differences

       
 SELECT * FROM  actor;
 SELECT  * FROM actor;

      Inline bind values

       
 SELECT * FROM actor WHERE id = 1;
 SELECT * FROM actor WHERE id = 2;

      Aliasing and qualification

       
 SELECT a1.* FROM actor a1 WHERE id = ?;
 SELECT * FROM actor a2 WHERE a2.id = ?;

      Examples of identical statements (which are not considered duplicate, but DiagnosticsListener.repeatedStatements(DiagnosticsContext), if on the same Connection) are: 

       
 SELECT * FROM actor WHERE id = ?;
 SELECT * FROM actor WHERE id = ?;

      This is a system-wide diagnostic that is not specific to individual Connection instances. Its caches are located in the Configuration that this listener pertains to.

      This diagnostic can be turned off using Settings.isDiagnosticsDuplicateStatements().

      Advanced duplicate statement recognition can be turned off using Settings.isDiagnosticsDuplicateStatementsUsingTransformPatterns().

      Specified by:
      duplicateStatements in interface DiagnosticsListener
      Parameters:
      ctx - The context containing information about the diagnostic.

    • repeatedStatements

      public void repeatedStatements(DiagnosticsContext ctx)
      Description copied from interface: DiagnosticsListener
      The executed JDBC statement is repeated consecutively on the same JDBC Connection.

      This problem goes by many names, the most famous one being the N + 1 problem, when a single (1) query for a parent entity requires many (N) subsequent queries for child entities. This could have been prevented by rewriting the parent query to use a JOIN. If such a rewrite is not possible (or not easy), the subsequent N queries could at least profit (depending on the exact query):

      • From reusing the PreparedStatement
      • From being batched
      • From being re-written as a bulk fetch or write query

      This problem can be aggravated if combined with the DiagnosticsListener.duplicateStatements(DiagnosticsContext) problem, in case of which the repeated statements might not be diagnosed as easily.

      Repeated statements may or may not be "identical". In the following example, there are two repeated and identical statements: 

       
 SELECT * FROM actor WHERE id = ?;
 SELECT * FROM actor WHERE id = ?;

      In this example, we have three repeated statements, only some of which are also identical: 

       
 SELECT * FROM actor WHERE id = ?;
 SELECT * FROM actor WHERE id = ?;
 SELECT * FROM actor WHERE id =  ?;

      This is a Connection-specific diagnostic that is reset every time Connection.close() is called for explicitly created DSLContext.diagnosticsConnection(), or if DiagnosticsConnection.ON is specified, also globally on a TransactionContext level (if available), or Configuration level.

      This diagnostic can be turned off using Settings.isDiagnosticsRepeatedStatements().

      Specified by:
      repeatedStatements in interface DiagnosticsListener
      Parameters:
      ctx - The context containing information about the diagnostic.

    • tooManyColumnsFetched

      public void tooManyColumnsFetched(DiagnosticsContext ctx)
      Description copied from interface: DiagnosticsListener
      The fetched JDBC ResultSet returned more columns than necessary.

      An event indicating that a JDBC ResultSet was fetched with A columns, but only B (B < A) were consumed.

      Typically, this problem can be remedied by not running a SELECT * query when this isn't strictly required.

      This diagnostic can be turned off using Settings.isDiagnosticsTooManyColumnsFetched().

      Specified by:
      tooManyColumnsFetched in interface DiagnosticsListener
      Parameters:
      ctx - The context containing information about the diagnostic.

    • tooManyRowsFetched

      public void tooManyRowsFetched(DiagnosticsContext ctx)
      Description copied from interface: DiagnosticsListener
      The fetched JDBC ResultSet returned more rows than necessary.

      An event indicating that a JDBC ResultSet was fetched with A rows, but only B rows (B < A) were consumed.

      Typically, this problem can be remedied by applying the appropriate LIMIT clause in SQL, or SelectLimitStep.limit(Number) clause in jOOQ.

      This diagnostic can be turned off using Settings.isDiagnosticsTooManyRowsFetched().

      Specified by:
      tooManyRowsFetched in interface DiagnosticsListener
      Parameters:
      ctx - The context containing information about the diagnostic.

    • consecutiveAggregation

      public void consecutiveAggregation(DiagnosticsContext ctx)
      Description copied from interface: DiagnosticsListener
      Similar JDBC statements are repeated consecutively with differing aggregation.

      Developers are often unaware of the fact that they can place multiple aggregate functions (even with filters) in a single query, rather than running separate queries producing separate round trips and load in the database.

      In the following example, there are three similar statements aggregating the same data: 

       
 SELECT count(*) FROM actor;
 SELECT count(*) FROM actor WHERE last_name LIKE ?;
 SELECT count(DISTINCT last_name) FROM actor;
 SELECT count(DISTINCT last_name) FROM actor WHERE last_name LIKE ?;

      It would be more efficient to run this query in a single statement: 

       
 SELECT
   count(*),
   count(*) FILTER (WHERE last_name LIKE ?),
   count(DISTINCT last_name),
   count(DISTINCT last_name) FILTER (WHERE last_name LIKE ?)
 FROM actor;

      This is a Connection-specific diagnostic that is reset every time Connection.close() is called for explicitly created DSLContext.diagnosticsConnection(), or if DiagnosticsConnection.ON is specified, also globally on a TransactionContext level (if available), or Configuration level.

      This diagnostic can be turned off using Settings.isDiagnosticsConsecutiveAggregation().

      This is a commercial edition only diagnostic.

      Specified by:
      consecutiveAggregation in interface DiagnosticsListener
      Parameters:
      ctx - The context containing information about the diagnostic.

    • concatenationInPredicate

      public void concatenationInPredicate(DiagnosticsContext ctx)
      Description copied from interface: DiagnosticsListener
      String concatenation was encountered in predicates.

      A predicate of the following form (or similar) was encountered: 

       
 SELECT * FROM actor WHERE first_name || last_name = ?;
 SELECT * FROM actor WHERE first_name || last_name IN (
   SELECT first_name || last_name FROM customer
 );

      While it is possible in some RDBMS to define function based indexes on such expressions, it's usually better (because indexes are more reusable), and more correct (because concatenation produces ambiguous values, e.g. 'John Taylor' || 'Doe' is the same value as 'John' || 'Taylor Doe') to work with separate columns, e.g.: 

       
 SELECT * FROM actor WHERE (first_name, last_name) = (?, ?);
 SELECT * FROM actor WHERE (first_name, last_name) IN (
   SELECT first_name, last_name FROM customer
 );

      This diagnostic can be turned off using Settings.isDiagnosticsConcatenationInPredicate().

      This is a commercial edition only diagnostic.

      Specified by:
      concatenationInPredicate in interface DiagnosticsListener
      Parameters:
      ctx - The context containing information about the diagnostic.

    • possiblyWrongExpression

      public void possiblyWrongExpression(DiagnosticsContext ctx)
      Description copied from interface: DiagnosticsListener
      A possibly wrong expression was encountered.

      Some expressions may be correct in most cases, but wrong in edge cases that developers may have overlooked. Examples include:

      • MOD(x, 2) = 1 instead of MOD(x, 2) != 0. The former will not be true for negative numbers.

      This diagnostic can be turned off using Settings.isDiagnosticsPossiblyWrongExpression().

      This is a commercial edition only diagnostic.

      Specified by:
      possiblyWrongExpression in interface DiagnosticsListener
      Parameters:
      ctx - The context containing information about the diagnostic.

    • trivialCondition

      public void trivialCondition(DiagnosticsContext ctx)
      Description copied from interface: DiagnosticsListener
      A trivial condition was encountered, which can be replaced by a NULL predicate, or even by DSL.trueCondition() or DSL.falseCondition(), but it is more likely just a typo.

      A trivial condition might compare a column to itself, e.g. 

      
 SELECT *
 FROM actor a
 JOIN film_actor fa
 ON a.actor_id = a.actor_id

      The above JOIN predicate is effectively a.actor_id IS NOT NULL, but what the author probably meant was to write a.actor_id = fa.actor_id instead.

      This mistake can often be very subtle, especially when comparing columns of composite constraints. In many cases, it's a bug, and if it isn't there's probably a more straightforward way to declare the condition.

      This diagnostic can be turned off using Settings.isDiagnosticsTrivialCondition().

      This is a commercial edition only diagnostic.

      Specified by:
      trivialCondition in interface DiagnosticsListener
      Parameters:
      ctx - The context containing information about the diagnostic.

    • nullCondition

      public void nullCondition(DiagnosticsContext ctx)
      Description copied from interface: DiagnosticsListener
      A NULL condition was encountered, which is almost always a mistake.

      A NULL condition may appear when users accidentally compare values with NULL 

       
 SELECT *
 FROM actor a
 WHERE last_name = NULL

      The above predicate will never evaluate to TRUE, even when the last_name column is indeed NULL. The Field.isNull() predicate should have been used instead.

      This diagnostic can be turned off using Settings.isDiagnosticsNullCondition().

      This is a commercial edition only diagnostic.

      Specified by:
      nullCondition in interface DiagnosticsListener
      Parameters:
      ctx - The context containing information about the diagnostic.

    • transformPattern

      public void transformPattern(DiagnosticsContext ctx)
      Description copied from interface: DiagnosticsListener
      A Settings.isTransformPatterns() pattern has been matched.

      This diagnostic can be turned off using Settings.isDiagnosticsPatterns().

      This is a commercial edition only diagnostic.

      Specified by:
      transformPattern in interface DiagnosticsListener
      Parameters:
      ctx - The context containing information about the diagnostic.
      See Also: