How to view the SQL queries issued by JPA?

Question

Logging options are provider-specific. You need to know which JPA implementation do you use.

• Hibernate (see here):

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

• EclipseLink (see here):

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

• OpenJPA (see here):

  <property name="openjpa.Log" value="DefaultLevel=WARN,Runtime=INFO,Tool=INFO,SQL=TRACE"/>
  

• DataNucleus (see here):

  Set the log category DataNucleus.Datastore.Native to a level, like DEBUG.

Also, if you're using EclipseLink and want to output the SQL parameter values, you can add this property to your persistence.xml file:

<property name="eclipselink.logging.parameters" value="true"/>

If you use hibernate and logback as your logger you could use the following (shows only the bindings and not the results):

<appender
    name="STDOUT"
    class="ch.qos.logback.core.ConsoleAppender">
    <encoder>
        <pattern>%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{36} -
            %msg%n</pattern>
    </encoder>
    <filter class="ch.qos.logback.core.filter.EvaluatorFilter">
        <evaluator>
            <expression>return message.toLowerCase().contains("org.hibernate.type") &amp;&amp;
                logger.startsWith("returning");</expression>
        </evaluator>
        <OnMismatch>NEUTRAL</OnMismatch>
        <OnMatch>DENY</OnMatch>
    </filter>
</appender>

org.hibernate.SQL=DEBUG prints the Query

<logger name="org.hibernate.SQL">
    <level value="DEBUG" />
</logger>

org.hibernate.type=TRACE prints the bindings and normally the results, which will be suppressed thru the custom filter

<logger name="org.hibernate.type">
    <level value="TRACE" />
</logger>

You need the janino dependency (http://logback.qos.ch/manual/filters.html#JaninoEventEvaluator):

<dependency>
    <groupId>org.codehaus.janino</groupId>
    <artifactId>janino</artifactId>
    <version>2.6.1</version>
</dependency>

In EclipseLink to get the SQL for a specific Query at runtime you can use the DatabaseQuery API:

Query query = em.createNamedQuery("findMe"); 
Session session = em.unwrap(JpaEntityManager.class).getActiveSession(); 
DatabaseQuery databaseQuery = ((EJBQueryImpl)query).getDatabaseQuery(); 
databaseQuery.prepareCall(session, new DatabaseRecord());

String sqlString = databaseQuery.getSQLString();

This SQL will contain ? for parameters. To get the SQL translated with the arguments you need a DatabaseRecord with the parameter values.

DatabaseRecord recordWithValues= new DatabaseRecord();
recordWithValues.add(new DatabaseField("param1"), "someValue");

String sqlStringWithArgs = 
         databaseQuery.getTranslatedSQLString(session, recordWithValues);

Source: How to get the SQL for a Query

I have made a cheat-sheet I think can be useful to others. In all examples, you can remove the format_sql property if you want to keep the logged queries on a single line (no pretty printing).

Pretty print SQL queries to standard out without parameters of prepared statements and without optimizations of a logging framework:

application.properties file:

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

application.yml file:

spring:
  jpa:
    show-sql: true
    properties:
      hibernate:
        format_sql: true

Pretty print SQL queries with parameters of prepared statements using a logging framework:

application.properties file:

spring.jpa.properties.hibernate.format_sql=true
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

application.yml file:

spring:
  jpa:
    properties:
      hibernate:
        format_sql: true
logging:
  level:
    org:
      hibernate:
        SQL: DEBUG
        type:
          descriptor:
            sql:
              BasicBinder: TRACE

Pretty print SQL queries without parameters of prepared statements using a logging framework:

application.properties file:

spring.jpa.properties.hibernate.format_sql=true
logging.level.org.hibernate.SQL=DEBUG

application.yml file:

spring:
  jpa:
    properties:
      hibernate:
        format_sql: true
logging:
  level:
    org:
      hibernate:
        SQL: DEBUG

Source (and more details): https://www.baeldung.com/sql-logging-spring-boot

In order to view all the SQL and parameters in OpenJPA, put these two parameters in the persistence.xml:

<property name="openjpa.Log" value="DefaultLevel=WARN, Runtime=INFO, Tool=INFO, SQL=TRACE"/>
<property name="openjpa.ConnectionFactoryProperties" value="PrintParameters=true" />