[hibernate-issues] [JIRA] (HHH-13888) Allow custom SQL loggers by subclassing SqlStatementLogger

[Ivan Březina (JIRA)]

Ivan Březina ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5e5ec8333df51b0c93763e2d ) *updated* an issue

Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiZjM2NWNkZWE1OTNmNDA2YWE1ZDk3YjFhZWNlY2VkZTciLCJwIjoiaiJ9 ) / Improvement ( https://hibernate.atlassian.net/browse/HHH-13888?atlOrigin=eyJpIjoiZjM2NWNkZWE1OTNmNDA2YWE1ZDk3YjFhZWNlY2VkZTciLCJwIjoiaiJ9 ) HHH-13888 ( https://hibernate.atlassian.net/browse/HHH-13888?atlOrigin=eyJpIjoiZjM2NWNkZWE1OTNmNDA2YWE1ZDk3YjFhZWNlY2VkZTciLCJwIjoiaiJ9 ) Allow custom SQL loggers by subclassing SqlStatementLogger ( https://hibernate.atlassian.net/browse/HHH-13888?atlOrigin=eyJpIjoiZjM2NWNkZWE1OTNmNDA2YWE1ZDk3YjFhZWNlY2VkZTciLCJwIjoiaiJ9 )

Change By: Ivan Březina ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=5e5ec8333df51b0c93763e2d )

Logging of slow SQLs from Hibernate is a cool feature, but it would be even better if you added custom loggers.
By adding setSqlStatementLogger into JdbcServices.

The background:

* it is not guaranteed that calling Statement.toString() will return actual SQL statement. For example Oracle's data replay datasource generates instances of proxy for TxnReplayableStatement. This class implements java.sql.Statement interface but toString() returns hexadecimal number.
* as a DBA I face situations where I need to correlate customer's action which slow running query. A custom logger can be extended and can also use Thread context to log slow SQL together with applications request id.
* Oracle internally identifies SQL by hash string called SQL_ID (last 8 bytes from MD5 checksum). For me it is hard to to translate fragments of SQLs logged into SQL_ID. But I can implement custom logger, which can compute and log the same SQL_ID log it also on application's side.

The is a link to sample POC:
[https://github.com/ibre5041/ucptest/tree/master/standalone-jpa/src/main/java|https://github.com/ibre5041/ucptest/tree/master/standalone-jpa/src/main/java]
The class

* CustomSqlStatementLogger used several reflection steps to get SQL from datatype TxnReplayableStatement. It also contains sample how SQL_ID is computed.
* AppHibernate is a sample application which uses Oracle's UCP connection pool and uses reflection to replace instance of SqlStatementLogger with CustomSqlStatementLogger.

At the end to logs something like this:
 {{ SlowQuery: 2582 milliseconds. SQL_ID: 'bd4aqyt8dzfvf' }} 

The rest about the query I can find in Oracle's Active session history:

\{\{SQL> select sql_text from gv$SQL where sql_id = ‘bd4aqyt8dzfvf’;  }} 

\{\{ SQL_TEXT  }} 
\{\{ select bookentity0_.id as id1_0_0_, bookentity0_.author as author2_0_0_, bookentity0_.name as name3_0_0_ from BOOK bookentity0_ where bookentity0_.id=:1
 }} 

So practically I need to add setSqlStatementLogger and setSqlExceptionHelper to jdbcService.
(or some config option identifying a class used to format SQLs logged by Hibernate).

( https://hibernate.atlassian.net/browse/HHH-13888#add-comment?atlOrigin=eyJpIjoiZjM2NWNkZWE1OTNmNDA2YWE1ZDk3YjFhZWNlY2VkZTciLCJwIjoiaiJ9 ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-13888#add-comment?atlOrigin=eyJpIjoiZjM2NWNkZWE1OTNmNDA2YWE1ZDk3YjFhZWNlY2VkZTciLCJwIjoiaiJ9 )

Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.core&referrer=utm_source%3DNotificationLink%26utm_medium%3DEmail ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailNotificationLink&mt=8 ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100121- sha1:d2b7993 )
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jboss.org/pipermail/hibernate-issues/attachments/20200304/5f59f990/attachment.html