Java 如何查看JPA发出的SQL查询？

How to view the SQL queries issued by JPA?

提问by Sajee

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.

如何查看此调用的 SQL 查询？假设我无权访问数据库服务器来分析/监视调用，有没有办法在我的 IDE 中记录或查看 JPA 调用发出的相应 SQL 查询？我将使用 jTDS 驱动程序反对 SQL Server 2008 R2。

回答by Gondim

There's a file called persistence.xml Press Ctrl+Shift+R and find it, then, there's a place written something like showSQL.

有个叫persistence.xml的文件，按Ctrl+Shift+R找到，然后，有个地方写了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.

我不确定服务器是否必须以调试模式启动。检查在控制台上创建的 SQL。

回答by btiernay

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

请参阅无法使用 Spring JPA Vendor Adapter 使休眠停止显示 SQL

回答by axtavt

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

日志选项是特定于提供程序的。您需要知道您使用的是哪种 JPA 实现。

• 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.Nativeto a level, like DEBUG.

• 休眠（见这里）：

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

回答by ThePizzle

Also, if you're using EclipseLink and want to output the SQL parameter values, you can add this property to your persistence.xml file:

此外，如果您正在使用 EclipseLink 并希望输出 SQL 参数值，您可以将此属性添加到您的 persistence.xml 文件中：

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

回答by Tomasz

In EclipseLink to get the SQL for a specific Query at runtime you can use the DatabaseQuery API:

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

This SQL will contain ? for parameters. To get the SQL translated with the arguments you need a DatabaseRecord with the parameter values.

此 SQL 将包含 ? 对于参数。要使用参数转换 SQL，您需要一个带有参数值的 DatabaseRecord。

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

来源：如何获取查询的 SQL

回答by Kuhpid

If you use hibernate and logback as your logger you could use the following (shows only the bindings and not the results):

如果您使用 hibernate 和 logback 作为记录器，则可以使用以下内容（仅显示绑定而不显示结果）：

<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=DEBUGprints the Query

org.hibernate.SQL=DEBUG打印查询

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

org.hibernate.type=TRACEprints the bindings and normally the results, which will be suppressed thru the custom filter

org.hibernate.type=TRACE打印绑定和通常的结果，这将通过自定义过滤器被抑制

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

You need the janino dependency (http://logback.qos.ch/manual/filters.html#JaninoEventEvaluator):

您需要 janino 依赖项（http://logback.qos.ch/manual/filters.html#JaninoEventEvaluator）：

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

回答by mateusz.fiolka

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:

如果您想查看带有参数值和返回值的确切查询，您可以使用 jdbc 代理驱动程序。它将拦截所有 jdbc 调用并记录它们的值。一些代理：

• log4jdbc
• jdbcspy

• 日志4jdbc
• jdbcspy

They may also provide some additional features, like measuring execution time for queries and gathering statistics.

它们还可能提供一些附加功能，例如测量查询的执行时间和收集统计信息。

回答by jfcorugedo

In order to view all the SQL and parameters in OpenJPA, put these two parameters in the persistence.xml:

为了查看OpenJPA中的所有SQL和参数，将这两个参数放在persistence.xml中：

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

回答by Marcus Becker

Example using log4j (src\log4j.xml):

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

回答by Αλ?κο?

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

此外，如果使用 WildFly/JBoss，请将 org.hibernate 的日志级别设置为 DEBUG