5

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.

ishan
  • 1,202
  • 5
  • 24
  • 44
  • Is enabling query logging in postgres an option (if it's a complicated query, maybe slow log will catch it)? Because, well, Hibernate sends prepared statements. They always come with these pesky question marks where the values are. – mabi Mar 18 '14 at 10:08
  • I tried postgres log too. But i think they don't print queries via Jboss. – ishan Mar 18 '14 at 10:14
  • for the p6spy part, seems like misconfiguration. If you show us more of datasource config, we might be able to help (as it supports Jboss 7 as well as XADatasources). – Peter Butkovic Mar 26 '14 at 07:38
  • 1
    I thought it would be better to make a separate question dedicated to P6spy - http://stackoverflow.com/questions/22659924/p6spydriver-cannot-be-cast-to-javax-sql-xadatasource-exception-while-trying-to-c – ishan Mar 26 '14 at 11:52

1 Answers1

7

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 :)

N0nbot
  • 190
  • 1
  • I have enabled this property already but what i want is the final SQL statement without those ?s – ishan Mar 18 '14 at 11:19
  • In this case, add the following at the file 'log4j.properties': log4j.logger.org.hibernate.SQL=DEBUG. Source: http://stackoverflow.com/questions/2536829/hibernate-show-real-sql – N0nbot Mar 18 '14 at 11:27
  • I am using logback and as you can see in description, i did changed the level to debug in my logback.xml – ishan Mar 18 '14 at 11:31