How to view the SQL queries issued by JPA?
JavaJpaJava Problem Overview
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.
Java Solutions
Solution 1 - Java
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, likeDEBUG
.
Solution 2 - Java
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"/>
Solution 3 - Java
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
Solution 4 - Java
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") &&
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>
Solution 5 - Java
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
Solution 6 - Java
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" />
Solution 7 - Java
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.
Solution 8 - Java
Example using log4j (src\log4j.xml):
<?xml version="1.0" encoding="UTF-8" ?>
http://logging.apache.org/log4j/1.2/apidocs/org/apache/log4j/xml/doc-files/log4j.dtd">
<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>
Solution 9 - Java
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());
Solution 10 - Java
Solution 11 - Java
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
Solution 12 - Java
Solution 13 - Java
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).
Solution 14 - Java
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);
Solution 15 - Java
EclipseLink to output the SQL(persistence.xml config):
<property name="eclipselink.logging.level.sql" value="FINE" />
Solution 16 - Java
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);
Solution 17 - Java
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);
}
}
Solution 18 - Java
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.