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
– jelies

2012-09-25 17:34:39 +00:00
Commented Sep 25, 2012 at 17:34 — jelies
– jelies, Commented Sep 25, 2012 at 17:34

jelies · Accepted Answer · 2012-07-30 12:49:47Z

8

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.

edited Jul 30, 2012 at 12:49

answered Jul 30, 2012 at 12:11

jelies

9,3106 gold badges52 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

diminuta Over a year ago

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')...

jelies Over a year ago

"whole" query as you said is not possible, this is the closest solution I think :(

asherbret · Accepted Answer · 2021-10-03 09:40:29Z

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 3, 2021 at 9:40

asherbret

6,0794 gold badges44 silver badges67 bronze badges

answered Apr 15, 2014 at 7:18

sorencito

2,6251 gold badge23 silver badges21 bronze badges

Comments

axtavt · Accepted Answer · 2012-07-30 08:42:55Z

1

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.

answered Jul 30, 2012 at 8:42

axtavt

243k42 gold badges518 silver badges486 bronze badges

Comments

bluesockets · Accepted Answer · 2014-08-12 16:45:22Z

0

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

answered Aug 12, 2014 at 16:45

bluesockets

564 bronze badges

Comments

Roman-Stop RU aggression in UA · Accepted Answer · 2019-04-30 13:49:51Z

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, 2019 at 13:49

Roman-Stop RU aggression in UA

16k3 gold badges52 silver badges59 bronze badges

Collectives™ on Stack Overflow

Get SQL String from Hibernate query

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related