10

How do you get/print the JPQL query string behind a (typed) query, that is after parameters have been set? (e.g. for debugging purposes)

A simple toString() doesn't seem to do the trick...

Thanks

Kawu
  • 13,647
  • 34
  • 123
  • 195
  • http://stackoverflow.com/questions/4362876/how-to-view-the-sql-queries-issued-by-jpa – NimChimpsky Oct 10 '11 at 12:20
  • 2
    I had expected this... I was **not** asking about displaying the provider-generated SQL. – Kawu Oct 10 '11 at 13:43
  • why not define what you mean in terms of the the output? Having a parameter set is irrelevant to the JPQL "string" form ... a parameter is ":myParam" in the JPQL string form. – DataNucleus Oct 10 '11 at 14:00
  • I would like the final JPQL that ultimately gets translated to the final SQL, that is including the concrete arguments been set/used. Looks like you have to compile that string yourself with String.replace...? – Kawu Oct 10 '11 at 15:03
  • There is no such thing as "the final JPQL that ultimately gets translated to the final SQL". How a JPA implementation generates the SQL is down to it, and parameters in general will never be substituted into any String. SQL is generated from expression trees etc not a String. If you want param values inserting in then do it yourself since it only makes sense to you – DataNucleus Oct 10 '11 at 15:06
  • Why don't you put that as an answer? – Kawu Oct 10 '11 at 15:52
  • 1
    In any case, no matter how JPA providers handle their stuff internally, it would definitely be of interest to sometimes see what the final query would look like. – Kawu Oct 10 '11 at 20:47

3 Answers3

4

There is no such thing as "the final JPQL that ultimately gets translated to the final SQL". How a JPA implementation generates the SQL is down to it, and parameters in general will never be substituted into any String. SQL is generated from expression trees etc not a String. If you want param values inserting in then do it yourself since it only makes sense to you

DataNucleus
  • 15,497
  • 3
  • 32
  • 37
3

I know this is old, but the current answer doesn't exactly answer the original/root question.

I believe Kawu was looking for what the JPQL string looked like, not if it gets converted to SQL or not. The desire was the find the concrete parameter.

Anyway Kawu, I was looking for the same thing at one time because my query results were skewed and I couldn't tell what the parameter was. I found that adding the below code to the persistence.xml did the trick. I use EclipseLink, but I am sure other JPA implementaitons have something like this:

<property name="eclipselink.logging.level" value="FINE"/>

This shows the below in your server logs:

[EL Fine]: sql: 2016-10-24 16:02:08.577--ServerSession(13483501)--Connection(6214343)--Thread(Thread[27010968@qtp-10395070-0,5,main])--SELECT ID, Name FROM Test WHERE (ID = ?)
    bind => [<your parameter shows here>]
Jason Washo
  • 536
  • 6
  • 22
0

For JBoss users: make the change in standalone.xml. In that file you will find a logging section under <profile>, eg:

    <subsystem xmlns="urn:jboss:domain:logging:3.0">
        <console-handler name="CONSOLE">
            <level name="INFO"/>
            <formatter>
                <named-formatter name="COLOR-PATTERN"/>
            </formatter>
        </console-handler>
        <logger category="com.arjuna">
            <level name="WARN"/>
        </logger>
        <logger category="org.jboss.as.config">
            <level name="DEBUG"/>
        </logger>
        <logger category="sun.rmi">
            <level name="WARN"/>
        </logger>
        <root-logger>
            <level name="INFO"/>
            <handlers>
                <handler name="CONSOLE"/>
            </handlers>
        </root-logger>
        <formatter name="PATTERN">

Default level is INFO. To get queries etc. to be shown, change the level to DEBUG in two places, as in:

    <subsystem xmlns="urn:jboss:domain:logging:3.0">
        <console-handler name="CONSOLE">
            <level name="DEBUG"/>
            <formatter>
                <named-formatter name="COLOR-PATTERN"/>
            </formatter>
        </console-handler>
        <logger category="com.arjuna">
            <level name="WARN"/>
        </logger>
        <logger category="org.jboss.as.config">
            <level name="DEBUG"/>
        </logger>
        <logger category="sun.rmi">
            <level name="WARN"/>
        </logger>
        <root-logger>
            <level name="DEBUG"/>
            <handlers>
                <handler name="CONSOLE"/>
            </handlers>
        </root-logger>
        <formatter name="PATTERN">

Re-start JBoss and now your console is filled with chatter.

Matt Campbell
  • 1,967
  • 1
  • 22
  • 34