How to view the SQL queries issued by JPA?

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

Hibernate (see here):

EclipseLink (see here):

OpenJPA (see here):

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

---

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.

---

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>

---

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());

---

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

https://i.stack.imgur.com/cV0mR.png

---

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  

---

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

---

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

---

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

---

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

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

---

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

---

For anyone who needs to validate SQL from a javax.persistence.Query

    import org.hibernate.query.Query;

    import org.junit.jupiter.api.Test;
    import org.junit.jupiter.api.Assertions;

    import javax.enterprise.context.RequestScoped;
    import javax.inject.Inject;
    import javax.persistence.EntityManager;
    import javax.persistence.Query;

    @RequestScoped
    public class QueryServices {

       @Inject
       protected EntityManager entityManager;

       public Query buildQuery(String searchCriteria) {
          return entityManager.createNativeQuery("select * from table t where t.animal = ?1"))
          .setParameter(1, searchCriteria);
       }
       

    class QueryServicesTest {
       @Test
       void buildQuerySqlGenerationTest() {
          final String searchFor = "cat"; 

          // Build a query object to check
          org.hibernate.query.Query query = workflowServices.buildQuery(searchFor)
                .unwrap(org.hibernate.query.Query.class).getQueryString();

          // Validate the generated sql contains the search value
          Assertions.assertTrue(query.contains(searchFor);
       }
    }

---

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.

---