Get SQL String from Hibernate query

Question

I need to get the string from an Hibernate query and process it later (so I can't solve it with "hibernate.show_sql").

I have already looked at How to get SQL from Hibernate Criteria API (*not* for logging) but with that workaround I get the SQL query string but instead of showing the parameters value it shows '?'... Is there any way to get the full SQL string with the parameters values?

I mean, with that solution I get "SELECT * FROM USER WHERE NAME=? AND SURNAME=?" but I need to get "SELECT * FROM USER WHERE NAME='John' AND SURNAME='Doe'"...

Ideas?

have you checked any of the answers? – jelies Sep 25 '12 at 17:34 — jelies, Sep 25 '12 at 17:34

jelies · Answer 1 · 2012-07-30T12:49:47.567

You have to set TRACE level of logging to this hibernate package and parameter binding should show in your application log:

<category name="org.hibernate.type">
      <priority value="TRACE"/>
</category>

Output example:

13:58:51,505 DEBUG [SQL] 
 insert 
        into
            s.audit
            (action, e_s, ip, time, userid, id) 
        values
            (?, ?, ?, ?, ?, ?)
13:58:51,505 TRACE [StringType] binding 'Modify user' to parameter: 1
13:58:51,505 TRACE [StringType] binding 'E' to parameter: 2
13:58:51,505 TRACE [StringType] binding '164.20.81.65' to parameter: 3
13:58:51,505 TRACE [TimestampType] binding '2012-07-30 13:58:51' to parameter: 4
13:58:51,505 TRACE [IntegerType] binding '158' to parameter: 5
13:58:51,505 TRACE [IntegerType] binding '8851' to parameter: 6

And don't forget 'hibernate.show_sql=true' property you said previously to show also the related SQL.

That's ok, but I needed the "whole" query, like: insert into s.audit (action, e_s, ip, time, userid, id) values ('Modify user','E','164.20.81.65','2012-07-30 13:58:51','158','8851')... — diminuta, Sep 26 '12 at 16:33
"whole" query as you said is not possible, this is the closest solution I think :( — jelies, Sep 26 '12 at 18:33

score 1 · Answer 2 · answered Jul 30 '12 at 08:42

There is no such thing as "the full SQL string with the parameters values".

Hibernate uses prepared statements, therefore it sends query string with ?s and parameter values to the database separately, so that the actual query is never constructed.

Perhaps you can find a way extract parameter values from QueryImpl, but even in this case you won't be able to get a query ready for execution, because you'll need to handle escaping, conversion of parameter values to SQL literals, etc.

score 1 · Accepted Answer · edited Oct 03 '21 at 09:40

1

There is a tool called p6spy. It's a bit older and I am afraid it isn't maintained anymore. It gave me good results in the past, anyway.

Edit: just found that it is being actively developed again. Works like a charm for me!

edited Oct 03 '21 at 09:40

asherbret

5,439
4
38
58

answered Apr 15 '14 at 07:18

sorencito

2,517
20
21

score 0 · Answer 4 · answered Aug 12 '14 at 16:45

0

JdbcLogger does this form you. http://jdbclogger.sourceforge.net/. Supports mysql, postgres, and spring integration.

answered Aug 12 '14 at 16:45

bluesockets

56
4

score 0 · Answer 5 · answered Apr 30 '19 at 13:49

0

Another option is to use datasource-proxy which allows to listen to different JDBC events including query execution.

You can implement QueryExecutionListener.beforeQuery that receives QueryInfo with SQL query text and all parameters.

answered Apr 30 '19 at 13:49

Roman-Stop RU aggression in UA

14,905
3
48
53

Get SQL String from Hibernate query

5 Answers5

Linked