capture final SQL query generated by hibernate JPA in JBoss AS 7

Question

I have been trying really hard to capture the exact SQL of a really complicated JPA query in my project for the purpose of optimization but so far i am unable to do so. I have tried the following ways:

1) Tried enabling hibernate logs via logback by putting following in my logback.xml and enabling show_SQL

    <logger name="org.hibernate.type" level="ALL" />
 <logger name="org.hibernate" level="TRACE">
    <appender-ref ref="fdpAdminAppender" />
 </logger>

2) Tried using P6spy-2 drivers with Jboss AS7 and postgres but just couldn't make it working. It gives the following exception.

com.p6spy.engine.spy.P6SpyDriver cannot be cast to javax.sql.XADataSource

3) Enabled inbuild Datasource spy in JBoss AS 7 and it did work. But the logs show way too much data all of which is useless to me as they again append the values as '?'. Even if i can get a way to get complete SQL statements here, my problem will be solved.

So far i have succeeded with step 3 only but either i am missing out something or it works this way, either case my actual purpose is not resolved. So please somebody help me get the actual SQL query from JPA. I am not concerned about how i get it, i just need to get it. I have experienced the need to verify the actual SQL generated by JPA with values so many times during development. It is strange why there is no standard way to do that.

Solution

Just add the following inside the 'persistence.xml':

<property name="hibernate.show_sql" value="true"/>
<property name="hibernate.format_sql" value="true"/>

Make sure to put it inside the <properties> tag, just like this:

<persistence>
    <persistence-unit>
        <properties>
            <property name="hibernate.show_sql" value="true"/>
            <property name="hibernate.format_sql" value="true"/>
        </properties>
    </persistence-unit>
</persistence>

Good luck :)

Comments

[ishan]

I have enabled this property already but what i want is the final SQL statement without those ?s

[N0nbot]

In this case, add the following at the file 'log4j.properties': log4j.logger.org.hibernate.SQL=DEBUG. Source: stackoverflow.com/questions/2536829/hibernate-show-real-sql

[ishan]

I am using logback and as you can see in description, i did changed the level to debug in my logback.xml