Configuring Log4J to see query parameters' values?

bLaDe00 · **Joined:** Thu Jul 24, 2008 10:13 am **Posts:** 17

Hibernate version: 3.2.5.ga

Hi all. I'm currently using SpringMVC with Hibernate, and I need to see the values of the parameters in SQL queries when the INSERT or UPDATE is launched.

Finding information about this, I'm trying to configure Log4J to achieve this, but I've not found a way to do this. Finally, I got all information about the transaction, session... in the console, but nothing about the parameters.

A related topic about this can be found in atlassian (http://opensource.atlassian.com/project ... e/HHH-2835). Currently I'm trying to configure the "workaround", but perhaps exists a better way in my current/upper version of Hibernate to get this. Can anyone help me with this fact?

Thanks in advance

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

I created a detailed post here, in which is shown how to log the generated SQL to a separate file, with or without parameters.

Basically, all you have to do is add another category to you logging configuration file, one that watches org.hibernate.types.

A sample log4j configuration file is also available at my post.

bLaDe00 · **Joined:** Thu Jul 24, 2008 10:13 am **Posts:** 17

Thanks for the reply gonzao_diaz, but the solution you provided didn't work in the Hibernate version that I'm currently using.

A piece of the debug code (as you can see, the value of the parameters ? not appears ):

Code:

2008-09-18 16:33:04,218 [btpool0-7] DEBUG (AbstractBatcher.java:401) - 
    insert 
    into
        thesis
        (ths_auth, ths_date, ths_desc, ths_dire, ths_earn_title, ths_facul, ths_file, ths_title, ths_univ, ths_year) 
    values
        (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2008-09-18 16:33:04,218 [btpool0-7] DEBUG (AbstractBatcher.java:374) - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
2008-09-18 16:33:04,218 [btpool0-7] DEBUG (AbstractBatcher.java:366) - about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
2008-09-18 16:33:04,218 [btpool0-7] DEBUG (AbstractBatcher.java:401) - 
    select
        currval('thesis_ths_id_seq')
2008-09-18 16:33:04,218 [btpool0-7] DEBUG (IdentifierGeneratorFactory.java:37) - Natively generated identity: 8
2008-09-18 16:33:04,218 [btpool0-7] DEBUG (AbstractBatcher.java:374) - about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
2008-09-18 16:33:04,218 [btpool0-7] DEBUG (AbstractFlushingEventListener.java:154) - dirty checking collections
2008-09-18 16:33:04,218 [btpool0-7] DEBUG (CollectionEntry.java:177) - Collection dirty: [es.udc.lbd.portal.model.researcher.entity.Researcher.thesisList#4]
2008-09-18 16:33:04,218 [btpool0-7] DEBUG (Collections.java:176) - Collection found: [es.udc.lbd.portal.model.researcher.entity.Researcher.congressList#4], was: [es.udc.lbd.portal.model.researcher.entity.Researcher.congressList#4] (uninitialized)
2008-09-18 16:33:04,218 [btpool0-7] DEBUG (Collections.java:176) - Collection found: [es.udc.lbd.portal.model.researcher.entity.Researcher.researcherStays#4], was: [es.udc.lbd.portal.model.researcher.entity.Researcher.researcherStays#4] (uninitialized)

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

There was a small error in the log4j configuration file, that one of my users detected and corrected.
Try again, please.
Later in the post, there is a sample of the kind of logging messages you should be getting for parameter values.

bLaDe00 · **Joined:** Thu Jul 24, 2008 10:13 am **Posts:** 17

If the error you are refering to is

Code:

"<appender-ref ref="appenderFileSQL" />"

I've already noticed and change it to:

Code:

<appender-ref ref="appenderSQLFile" />

Was this the problem?

gonzao_diaz · **Joined:** Mon Nov 26, 2007 2:29 pm **Posts:** 443

Yes.
Did it solve the problem?

bLaDe00 · **Joined:** Thu Jul 24, 2008 10:13 am **Posts:** 17

No, it didn't. As you can see, in the identifier generation the value appears, but the parameters in queries continue "hidden".

kutzi · **Posted:** Tue Mar 24, 2009 12:03 pm

If you're still looking for a solution (or like me found this forum entry via web search), here is how I solved it:

<logger name="org.hibernate.engine.query.HQLQueryPlan" additivity="false">
<level value="TRACE"/>
<appender-ref ref="appenderSQLFile"/>
</logger>

<logger name="org.hibernate.engine.QueryParameters" additivity="false">
<level value="TRACE"/>
<appender-ref ref="appenderSQLFile"/>
</logger>

(Hibernate 3.2.6.ga)