How to get literals for StatementInspector

Hibernate ORM
1

Hi there,

I’m using the StatementInspector to intercept the SQL being generated by Hibernate. I want the full SQL statement including the literals whereas at the moment I get

select p1_0.id,p1_0.created_at,p1_0.first_name,p1_0.last_name,p1_0.modified_at from person p1_0 where p1_0.id=?

…whereas I actually want

select p1_0.id,p1_0.created_at,p1_0.first_name,p1_0.last_name,p1_0.modified_at from person p1_0 where p1_0.id=1

It looks there’s a couple of Hibernate properties which might help here. Firstly, I tried in my HibernatePropertiesCustomizer I tried doing the following:

hibernateProperties.put(AvailableSettings.CRITERIA_VALUE_HANDLING_MODE, ValueHandlingMode.INLINE);
    hibernateProperties.put(AvailableSettings.STATEMENT_INSPECTOR, statementInspector);

…where statementInspector is my customized StatementInspector but the CRITERIA_VALUE_HANDLING_MODE appeared to have no effect. In Hibernate 5.3 there also appeared to be a property LiteralHandlingMode but it looks like this has been removed in 6.4.4.Final which is the version I’m using with Spring Boot.

Would be very grateful for any assistance with this.

Regards,

John

2

Why do you need the SQL queries generated by Hibernate? This is not the intended use of StatementInspector.

ValueHandlingMode.INLINE

Always inlining parameters into queries comes with a lot of limitations and disadvantages, and also opens up potential SQL Injection risks. Please tell us what you’re trying to achieve and we can figure out a better solution.

3

So I would like to capture the actual SQL string that’s executed against the DB. The idea is to create a bespoke CDC solution where I capture the SQL string being executed against a source DB, translate the SQL into the target database’s dialect and then send that SQL in the form of a command event across the wire where it will be executed against the target DB so that both databases stay in sync

4

This sounds like a perfect use case for Hibernate: the library already takes care of cross-database compatibility for you, so why don’t you simply execute the same query through Hibernate on both databases and let it worry about dialects?

5

In this case, I was planning to use jOOQ to do the translation. The key point, however, is I want to decouple the execution of the SQL on the source database and the execution of the SQL on the target database. So, as stated, I would ideally like the capture the SQL being executed for mutations (UPDATE, INSERT statements) and (possibly translate the dialect but this is less important) and then put the SQL into an event which could be sent via Kaka and the the SQL executed against the target databse when the event is consumed. The decoupling is very important because I can’t make a direct connection to the target database from the service which is connected to the source database.