How to view the SQL queries issued by JPA?

When my code issues a call like this:

entityManag开发者_如何学Cer.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.

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

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

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

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

We can also view the sql in a structured manner by adding these properties in the application.properties file like so:

spring.h2.console.enabled=true
spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.use_sql_comments=true
spring.jpa.properties.hibernate.format_sql=true
logging.level.org.hibernate.type=trace

I created the following class to print the exact generated Oracle SQL/HQL, and working fine-

import java.lang.annotation.Annotation;
import java.lang.reflect.Method;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.Map;

import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.reflect.MethodSignature;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Component;

@Aspect
@Component
public class ICMSPointcut {
  @Around("execution(public * *(..)) && @annotation(org.springframework.data.jpa.repository.Query)")
  public Object findAllReceivedMessagesByRoleAndLocNewerSQLSearch(ProceedingJoinPoint joinPoint) throws Throwable {
    //System.out.println("joinPoint- " + joinPoint.toLongString());
    MethodSignature signature = (MethodSignature) joinPoint.getSignature();
    Method method = signature.getMethod();
    Query query = method.getAnnotation(Query.class);
    final StringBuilder sql = new StringBuilder(query.value());
    if(query.nativeQuery()) {
      Object[] params = joinPoint.getArgs();
      for(int i = 0; i < params.length; i++) {
        Object param = params[i];
        for(int j = 1; j < sql.length(); j++) {
          char c = sql.charAt(j);
          if(c == '?') {
            if(sql.charAt(j-1) == '\\') {
              continue;
            }
            sql.deleteCharAt(j);
            if(param instanceof Number) {
              sql.insert(j, param != null ? param.toString() : "null");
            }
            else if(param instanceof java.util.Date) {
              Timestamp ts = (Timestamp)param;
              sql.insert(j, param != null ? "to_date('DD-MM-YYYY HH24:MI:SS','"+new SimpleDateFormat("dd-MMM-yyyy HH:mm:ss").format(ts)+"')" : "null");
            }
            else {
              sql.insert(j, param != null ? "'" + param.toString() + "'" : "null");
            }
            break;
          }
        }
      }
    }
    else {
      Map<Integer, String> paramNameIndexMap = new HashMap<>();
      Object[] params = joinPoint.getArgs();
      Annotation[][] anotss = method.getParameterAnnotations();
      for(int i = 0; i < anotss.length; i++) {
        Annotation[] anots = anotss[i];
        org.springframework.data.repository.query.Param paramAnnot = null;
        for(int j = 0; j < anots.length; j++) {
          if(anots[j].annotationType().getName().equals("org.springframework.data.repository.query.Param")) {
            paramAnnot = (org.springframework.data.repository.query.Param)anots[j];
            break;
          }
        }
        if(paramAnnot == null) {
          continue;
        }
        paramNameIndexMap.put(i, paramAnnot.value());
      }
      for(int i = 0; i < params.length; i++) {
        String paramName = paramNameIndexMap.get(i);
        Object param = params[i];
        String paramNameWithColon = ":" + paramName;
        int indx = sql.indexOf(paramNameWithColon);
        if(indx != -1) {
          final String paramConstantVal;
          if(param instanceof Number) {
            paramConstantVal = param != null ? param.toString() : "null";
          }
          else if(param instanceof java.util.Date) {
            Timestamp ts = (Timestamp)param;
            paramConstantVal = param != null ? "to_date('DD-MM-YYYY HH24:MI:SS','"+new SimpleDateFormat("dd-MMM-yyyy HH:mm:ss").format(ts)+"')" : "null";
          }
          else {
            paramConstantVal = param != null ? "'" + param.toString() + "'" : "null";
          }
          sql.replace(indx, indx+paramNameWithColon.length(), paramConstantVal);
        }
      }
    }
    System.out.println(method.getName()+ " Exact "+(query.nativeQuery()?"SQL":"HQL")+" from Pointcut:\n" + sql + "\n-------------------------------------------------------------------");
    return joinPoint.proceed();
  }
}

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.