[java] How to view the SQL queries issued by JPA?

When my code issues a call like this:

entityManager.find(Customer.class, customerID);

How can I see the SQL query for this call? Assuming I don't have access to database server to profile/monitor the calls, is there way to log or view within my IDE the corresponding SQL queries issued by JPA calls? I'm going against SQL Server 2008 R2 using the jTDS driver.

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

If you want to see the exact queries altogether with parameter values and return values you can use a jdbc proxy driver. It will intercept all jdbc calls and log their values. Some proxies:

• log4jdbc
• jdbcspy

They may also provide some additional features, like measuring execution time for queries and gathering statistics.

Another good option if you have too much log and you want to only put as a temporal System.out.println(), you can, depending on your provider do:

CriteriaBuilder criteriaBuilder = getEntityManager().getCriteriaBuilder();
CriteriaQuery<ExaminationType> criteriaQuery = criteriaBuilder.createQuery(getEntityClass()); 

/* For Hibernate */
System.out.println(getEntityManager().createQuery(criteriaQuery).unwrap(org.hibernate.query.Query.class).getQueryString());

/* For OpenJPA */ 
System.out.println(getEntityManager().createQuery(criteriaQuery).unwrap(org.apache.openjpa.persistence.QueryImpl.class).getQueryString());

/* For EclipseLink */
System.out.println(getEntityManager().createQuery(criteriaQuery).unwrap(JpaQuery.class).getSQLString());

JPA provider can set it for you - incase if someone doesn't want to control through JPA properties

public static JpaProperties properties() {
        final JpaProperties jpaProperties = new JpaProperties();
        jpaProperties.setShowSql(true);

See Can't make hibernate stop showing SQL using Spring JPA Vendor Adapter

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.

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>

During explorative development, and to focus the SQL debugging logging on the specific method I want to check, I decorate that method with the following logger statements:

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import ch.qos.logback.classic.Level;

((ch.qos.logback.classic.Logger) LoggerFactory.getLogger("org.hibernate.SQL")).setLevel(Level.DEBUG);
entityManager.find(Customer.class, customerID);
((ch.qos.logback.classic.Logger) LoggerFactory.getLogger("org.hibernate.SQL")).setLevel(Level.INFO);

With Spring Boot simply add: spring.jpa.show-sql=true to application.properties. This will show the query but without the actual parameters (you will see ? instead of each parameter).

There's a file called persistence.xml Press Ctrl+Shift+R and find it, then, there's a place written something like showSQL.

Just put it as true

I'm not sure if the server must be started as Debug mode. Check the SQLs created on console.

EclipseLink to output the SQL(persistence.xml config):

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

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

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" />

Additionally, if using WildFly/JBoss, set the logging level of org.hibernate to DEBUG

Example using log4j (src\log4j.xml):

<?xml version="1.0" encoding="UTF-8" ?>

<appender name="CA" class="org.apache.log4j.AsyncAppender">
    <param name="BufferSize" value="512"/>
    <appender-ref ref="CA_OUTPUT"/>
</appender>
<appender name="CA_OUTPUT" class="org.apache.log4j.ConsoleAppender">
    <layout class="org.apache.log4j.PatternLayout">
        <param name="ConversionPattern" value="[%p] %d %c %M - %m%n"/>
    </layout>
</appender>

<logger name="org.hibernate.SQL" additivity="false">
    <level value="DEBUG"/>
    <appender-ref ref="CA"/>
</logger>

<root>
    <level value="WARN"/>
    <appender-ref ref="CA"/>
</root>

If you are using Spring framework. Modify your application.properties file as below

#Logging JPA Queries, 1st line Log Query. 2nd line Log parameters of prepared statements 
logging.level.org.hibernate.SQL=DEBUG  
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE  

#Logging JdbcTemplate Queries, 1st line Log Query. 2nd line Log parameters of prepared statements 
logging.level.org.springframework.jdbc.core.JdbcTemplate=DEBUG  
logging.level.org.springframework.jdbc.core.StatementCreatorUtils=TRACE  

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"/>