Allow custom SQL loggers by subclassing SqlStatementLogger

Description

Logging of slow SQLs from Hibernate is a cool feature, but it would be even better if you allowed 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 with 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
The class

  • CustomSqlStatementLogger uses several reflection steps to get SQL from datatype TxnReplayableStatement. It also contains example 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 the app 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).

 

thx Ivan

Environment

None

Assignee

Unassigned

Reporter

Ivan Březina

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

Yes, likely

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Trivial
Configure