JPA에서 발행 한 SQL 쿼리를 보는 방법은 무엇입니까?

JPA에서 발행 한 SQL 쿼리를 보는 방법은 무엇입니까?

---

내 코드가 다음과 같은 호출을 발행하면

entityManager.find(Customer.class, customerID);

이 호출에 대한 SQL 쿼리를 어떻게 볼 수 있습니까? 호출을 프로파일 링 / 모니터링하기 위해 데이터베이스 서버에 액세스 할 수 없다고 가정하면 JPA 호출에 의해 발행 된 해당 SQL 쿼리를 IDE에서 기록하거나 볼 수있는 방법이 있습니까? jTDS 드라이버를 사용하여 SQL Server 2008 R2에 반대합니다.

---

로깅 옵션은 공급자마다 다릅니다. 어떤 JPA 구현을 사용해야하는지 알아야합니다.

• 최대 절전 모드 ( 여기 참조 ) :

  <property name = "hibernate.show_sql" value = "true" />
  

• EclipseLink ( 여기 참조 ) :

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

• OpenJPA ( 여기 참조 ) :

  <property name="openjpa.Log" value="DefaultLevel=WARN, Runtime=INFO, Tool=INFO, SQL=TRACE"/>
  

• DataNucleus ( 여기 참조 ) :

  로그 카테고리 DataNucleus.Datastore.Native를 같은 레벨로 설정하십시오 DEBUG.

---

또한 EclipseLink를 사용 중이고 SQL 매개 변수 값을 출력하려는 ​​경우이 특성을 persistence.xml 파일에 추가 할 수 있습니다.

<property name="eclipselink.logging.parameters" value="true"/>

---

최대 절전 모드 및 로그 백을 로거로 사용하는 경우 다음을 사용할 수 있습니다 (결과가 아닌 바인딩 만 표시).

<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 가 쿼리를 인쇄합니다

<logger name="org.hibernate.SQL">
    <level value="DEBUG" />
</logger>

org.hibernate.type = TRACE 는 바인딩과 일반적으로 결과를 인쇄하며, 이는 사용자 정의 필터를 통해 억제됩니다.

<logger name="org.hibernate.type">
    <level value="TRACE" />
</logger>

janino 종속성이 필요합니다 (http://logback.qos.ch/manual/filters.html#JaninoEventEvaluator) :

<dependency>
    <groupId>org.codehaus.janino</groupId>
    <artifactId>janino</artifactId>
    <version>2.6.1</version>
</dependency>

---

EclipseLink에서 런타임시 특정 쿼리에 대한 SQL을 가져 오려면 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();

이 SQL은? 매개 변수 인수로 SQL을 변환하려면 매개 변수 값이있는 DatabaseRecord가 필요합니다.

DatabaseRecord recordWithValues= new DatabaseRecord();
recordWithValues.add(new DatabaseField("param1"), "someValue");

String sqlStringWithArgs = 
         databaseQuery.getTranslatedSQLString(session, recordWithValues);

출처 : 쿼리에 대한 SQL을 얻는 방법

---

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>

---

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

---

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

---

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

---

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.

참고URL : https://stackoverflow.com/questions/4362876/how-to-view-the-sql-queries-issued-by-jpa