How to view the SQL queries issued by JPA

Question

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

User · Answer

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

User · Answer

Logging options are provider-specific  You need to know which JPA implementation do you use    Hibernate  see here     lt property name    hibernate show sql  value    true    gt   EclipseLink  see here     lt property name  eclipselink logging level  value  FINE   gt   OpenJPA  see here     lt property name  openjpa Log  value  DefaultLevel WARN Runtime INFO Tool INFO SQL TRACE   gt   DataNucleus  see here    Set the log category DataNucleus Datastore Native to a level  like DEBUG

User · Answer

If you use hibernate and logback as your logger you could use the following  shows only the bindings and not the results     lt appender     name  STDOUT      class  ch qos logback core ConsoleAppender  gt       lt encoder gt           lt pattern gt  d yyyy-MM-dd HH mm ss SSS    thread   -5level  logger 36  -              msg n lt  pattern gt       lt  encoder gt       lt filter class  ch qos logback core filter EvaluatorFilter  gt           lt evaluator gt               lt expression gt return message toLowerCase   contains  org hibernate type    amp amp  amp amp                  logger startsWith  returning    lt  expression gt           lt  evaluator gt           lt OnMismatch gt NEUTRAL lt  OnMismatch gt           lt OnMatch gt DENY lt  OnMatch gt       lt  filter gt   lt  appender gt    org hibernate SQL DEBUG prints the Query   lt logger name  org hibernate SQL  gt       lt level value  DEBUG    gt   lt  logger gt    org hibernate type TRACE prints the bindings and normally the results  which will be suppressed thru the custom filter   lt logger name  org hibernate type  gt       lt level value  TRACE    gt   lt  logger gt    You need the janino dependency  http   logback qos ch manual filters html JaninoEventEvaluator     lt dependency gt       lt groupId gt org codehaus janino lt  groupId gt       lt artifactId gt janino lt  artifactId gt       lt version gt 2 6 1 lt  version gt   lt  dependency gt

User · Answer

In order to view all the SQL and parameters in OpenJPA  put these two parameters in the persistence xml    lt property name  openjpa Log  value  DefaultLevel WARN  Runtime INFO  Tool INFO  SQL TRACE   gt   lt property name  openjpa ConnectionFactoryProperties  value  PrintParameters true    gt

User · Answer

EclipseLink to output the SQL persistence xml config     lt property name  eclipselink logging level sql  value  FINE    gt

User · Answer

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

User · Answer

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

User · Answer

Example using log4j  src log4j xml     lt  xml version  1 0  encoding  UTF-8    gt        lt appender name  CA  class  org apache log4j AsyncAppender  gt       lt param name  BufferSize  value  512   gt       lt appender-ref ref  CA OUTPUT   gt   lt  appender gt   lt appender name  CA OUTPUT  class  org apache log4j ConsoleAppender  gt       lt layout class  org apache log4j PatternLayout  gt           lt param name  ConversionPattern  value    p   d  c  M -  m n   gt       lt  layout gt   lt  appender gt    lt logger name  org hibernate SQL  additivity  false  gt       lt level value  DEBUG   gt       lt appender-ref ref  CA   gt   lt  logger gt    lt root gt       lt level value  WARN   gt       lt appender-ref ref  CA   gt   lt  root gt

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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 lt ExaminationType gt  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

User · Answer

Also  if you re using EclipseLink and want to output the SQL parameter values  you can add this property to your persistence xml file    lt property name  eclipselink logging parameters  value  true   gt

User · Answer

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

User · Answer

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

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

Examples related to java

Examples related to jpa