blocks|key|1071894|text|我发现执行以下操作更有用，即启用Hibernate的日志记录来记录SQL和绑定变量(这样您就可以看到传递到调用中的值，并在编辑器中轻松地复制SQL或其他方式)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1071895|在Config.groovy中，将以下内容添加到log4j块中：|offset|length|style|CODE|1071896|log4j+=+{

++++//+Enable+Hibernate+SQL+logging+with+param+values
++++trace+'org.hibernate.type'
++++debug+'org.hibernate.SQL'
++++//the+rest+of+your+logging+config
++++//+...
++++}|code-block|syntax|javascript|1071897|entityMap^0|0|1|D|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@$D|Q|E|R|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|S|8|@]|9|@]|A|$K|L]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

I find it more useful to do the following, which is to enable Hibernate's logging to log the SQL along with bind variables (so you can see the values passed into your calls, and easily replicate the SQL in your editor or otherwise).

In your <code>Config.groovy</code>, add the following to your log4j block:

<pre><code>log4j = {

 // Enable Hibernate SQL logging with param values
 trace 'org.hibernate.type'
 debug 'org.hibernate.SQL'
 //the rest of your logging config
 // ...
 }
</code></pre>

blocks|key|1072119|text|对于grails+3。*|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1072120|选项#1将以下内容添加到logback.groovy|1072121|logger("org.hibernate.SQL",+DEBUG,+["STDOUT"],+false)
logger("org.hibernate.type.descriptor.sql.BasicBinder",+TRACE,+["STDOUT"],+false)|code-block|syntax|javascript|1072122|或|1072123|选项#2将以下内容添加到application.yml中的dataSource。但是，此方法不会记录参数值|1072124|environments:
++local:
++++dataSource:
++++++++logSql:+true
++++++++formatSql:+true|1072125|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|U|8|@]|9|@]|A|$]]|$1|M|3|N|5|F|7|V|8|@]|9|@]|A|$G|H]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

For grails 3.*

Option #1 add the following to logback.groovy

<pre><code>logger("org.hibernate.SQL", DEBUG, ["STDOUT"], false)
logger("org.hibernate.type.descriptor.sql.BasicBinder", TRACE, ["STDOUT"], false)
</code></pre>

or 

Option #2 add the following to dataSource in the application.yml. However this approach does not log the parameter values

<pre><code>environments:
 local:
 dataSource:
 logSql: true
 formatSql: true
</code></pre>

blocks|key|1286791|text|试试这个：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1286792|log4j+=+{
+++...
+++debug+'org.hibernate.SQL'
+++trace+'org.hibernate.type.descriptor.sql.BasicBinder'
}|code-block|syntax|javascript|1286793|它避免了跟踪日志记录Hibernate+type包的性能问题。这适用于Hibernate+3.6和更高版本。这篇文章来自：https://burtbeckwith.com/blog/?p=1604|offset|length|style|CODE|1286794|entityMap|0|LINK|mutability|MUTABLE|url|https://burtbeckwith.com/blog/?p=1604^0|0|0|K|4|1P|11|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@$I|X|J|Y|K|L]]|9|@$I|Z|J|10|1|11]]|A|$]]|$1|M|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

Try this:

<pre><code>log4j = {
 ...
 debug 'org.hibernate.SQL'
 trace 'org.hibernate.type.descriptor.sql.BasicBinder'
}
</code></pre>

It avoids the performance problems of trace logging the Hibernate <code>type</code> package. This works with Hibernate 3.6 and above. I got this from: <a href="https://burtbeckwith.com/blog/?p=1604">https://burtbeckwith.com/blog/?p=1604</a>

blocks|key|1071992|text|解决方案只适用于开发，不适用于生产。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1071993|上面的所有答案都是有效的，都是正确的。但它们不能以人类可读的方式显示完整的查询。如果想看决赛(没有任何?，?)查询您有两个选项。|1071994|A)使用log4jdbc或p6Spy代理您的jdbc连接。|1071995|B)在数据库级别上查看它。例如，用mysql很容易做到。|1071996|找出你的general_log_file在哪里。激活的常规日志(如果尚未激活)。|1071997|mysql+command+line>+show+variables+like+"%25general_log%25";
mysql+command+line>+set+global+general_log+=+true;|code-block|syntax|javascript|1071998|现在，所有内容都记录到您的日志文件中。Mac+/+linux示例，显示您的查询流。|1071999|tail+-f+path_to_log_file+|1072000|entityMap^0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|X|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|Y|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|Z|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|10|8|@]|9|@]|A|$]]|$1|Q|3|R|5|L|7|11|8|@]|9|@]|A|$M|N]]|$1|S|3|-4|5|6|7|12|8|@]|9|@]|A|$]]]|T|$]]

Solution is only for development, not production. 

All the answers above work and are correct. But they do not show the complete query in a nice human readable way. If want to see the final (without any ?, ?) query you have two options.

A) proxy your jdbc connection with log4jdbc or p6Spy.

B) look at it on database level. For example really easy to do with mysql.

Find out where you general_log_file is. Active general log if no activated already.

<pre><code>mysql command line&gt; show variables like "%general_log%";
mysql command line&gt; set global general_log = true;
</code></pre>

Now everything is logged to you log file. Mac / linux example to show nice stream of your queries. 

<pre><code>tail -f path_to_log_file 
</code></pre>

blocks|key|1072213|text|Pure仅供参考，但我使用p6spy来记录SQL查询。它是一个小型的中间jdbc驱动程序。准确的查询将按照发送到服务器的方式进行记录(包括参数)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1072214|将其包含在您的项目中：|1072215|runtime+'p6spy:p6spy:3.0.0'|code-block|syntax|javascript|1072216|更改数据源驱动程序：|1072217|driverClassName:+com.p6spy.engine.spy.P6SpyDriver|1072218|和您的jdbc+url：|1072219|url:+jdbc:p6spy:mysql://|1072220|使用spy.properties配置它(在grails-app/conf中)。|1072221|driverlist=org.h2.Driver,com.mysql.jdbc.Driver
autoflush=true
appender=com.p6spy.engine.spy.appender.StdoutLogger
databaseDialectDateFormat=yyyy-MM-dd
logMessageFormat=com.p6spy.engine.spy.appender.MultiLineFormat|1072222|不要忘了在生产环境中禁用它！|1072223|entityMap^0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Z|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|10|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|11|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|12|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|13|8|@]|9|@]|A|$]]|$1|O|3|P|5|F|7|14|8|@]|9|@]|A|$G|H]]|$1|Q|3|R|5|6|7|15|8|@]|9|@]|A|$]]|$1|S|3|T|5|F|7|16|8|@]|9|@]|A|$G|H]]|$1|U|3|V|5|6|7|17|8|@]|9|@]|A|$]]|$1|W|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|X|$]]

Pure for reference only, but I use p6spy to log the SQL queries. It's a small intermediate jdbc driver. The exact query is logged as it would be send to the server (with parameters included). 

include it in your project:

<pre><code>runtime 'p6spy:p6spy:3.0.0'
</code></pre>

Change your datasource driver: 

<pre><code>driverClassName: com.p6spy.engine.spy.P6SpyDriver
</code></pre>

And your jdbc url: 

<pre><code>url: jdbc:p6spy:mysql://
</code></pre>

Configure it using spy.properties (in grails-app/conf). 

<pre><code>driverlist=org.h2.Driver,com.mysql.jdbc.Driver
autoflush=true
appender=com.p6spy.engine.spy.appender.StdoutLogger
databaseDialectDateFormat=yyyy-MM-dd
logMessageFormat=com.p6spy.engine.spy.appender.MultiLineFormat
</code></pre>

Don't forget to disable this for production!

blocks|key|1072325|text|下一步对我有用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1072326|grails-app/conf/application.yml|1072327|#+...
hibernate:
++++format_sql:+true+#+<<<<<<<+ADD+THIS+<<<<<<<
++++cache:
++++++++queries:+false
++++++++use_second_level_cache:+true
#+...
environments:
++++development:
++++++++dataSource:
++++++++++++logSql:+true+//+<<<<<<<+ADD+THIS+<<<<<<<
++++++++++++dbCreate:+create-drop
++++++++++++url:+jdbc:h2:mem:...
#+...|code-block|syntax|javascript|1072328|grails-app/conf/logback.groovy|1072329|//+...
appender('STDOUT',+ConsoleAppender)+{
++++encoder(PatternLayoutEncoder)+{
++++++++pattern+=+"%25level+%25logger+-+%25msg%25n"
++++}
}

//+>>>>>>>+ADD+IT+>>>>>>>
logger+'org.hibernate.type.descriptor.sql.BasicBinder',+TRACE,+['STDOUT']
logger+'org.hibernate.SQL',+TRACE,+['STDOUT']
//+<<<<<<<+ADD+IT+<<<<<<<

root(ERROR,+['STDOUT'])

def+targetDir+=+BuildSettings.TARGET_DIR
//+...|1072330|来源：http://sergiodelamo.es/log-sql-grails-3-app/|offset|length|1072331|entityMap|0|LINK|mutability|MUTABLE|url|http://sergiodelamo.es/log-sql-grails-3-app/^0|0|0|0|0|0|3|18|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Z|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|10|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|11|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|12|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|13|8|@]|9|@$O|14|P|15|1|16]]|A|$]]|$1|Q|3|-4|5|6|7|17|8|@]|9|@]|A|$]]]|R|$S|$5|T|U|V|A|$W|X]]]]

Next works for me:

<h3>grails-app/conf/application.yml</h3>

<pre class="lang-yaml prettyprint-override"><code># ...
hibernate:
 format_sql: true # &lt;&lt;&lt;&lt;&lt;&lt;&lt; ADD THIS &lt;&lt;&lt;&lt;&lt;&lt;&lt;
 cache:
 queries: false
 use_second_level_cache: true
# ...
environments:
 development:
 dataSource:
 logSql: true // &lt;&lt;&lt;&lt;&lt;&lt;&lt; ADD THIS &lt;&lt;&lt;&lt;&lt;&lt;&lt;
 dbCreate: create-drop
 url: jdbc:h2:mem:...
# ...
</code></pre>

<h3>grails-app/conf/logback.groovy</h3>

<pre class="lang-groovy prettyprint-override"><code>// ...
appender('STDOUT', ConsoleAppender) {
 encoder(PatternLayoutEncoder) {
 pattern = "%level %logger - %msg%n"
 }
}

// &gt;&gt;&gt;&gt;&gt;&gt;&gt; ADD IT &gt;&gt;&gt;&gt;&gt;&gt;&gt;
logger 'org.hibernate.type.descriptor.sql.BasicBinder', TRACE, ['STDOUT']
logger 'org.hibernate.SQL', TRACE, ['STDOUT']
// &lt;&lt;&lt;&lt;&lt;&lt;&lt; ADD IT &lt;&lt;&lt;&lt;&lt;&lt;&lt;

root(ERROR, ['STDOUT'])

def targetDir = BuildSettings.TARGET_DIR
// ...
</code></pre>

Source: <a href="http://sergiodelamo.es/log-sql-grails-3-app/" rel="nofollow noreferrer">http://sergiodelamo.es/log-sql-grails-3-app/</a>

blocks|key|1072073|text|我知道很久以前就有人问过并回答了这个问题，我只是碰巧看到了这个问题，并忍不住要在项目中回答或分享我们的.But日志实现方法。希望能有所帮助。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1072074|目前它还在开发环境中。我们使用“sql”来记录log4jdbc。|1072075|配置：|1072076|在您的BuildConfig.groovy中:添加以下依赖项：|1072077|dependencies+{
.....
runtime+'org.lazyluke:log4jdbc-remix:0.2.7'
}|code-block|syntax|javascript|1072078|在您的DataSource或其他相关配置中:在定义了数据源相关配置的任何地方，添加：|1072079|datasources{
.....
driverClassName:+"net.sf.log4jdbc.DriverSpy",
url:+"jdbc:log4jdbc:oracle:thin:@(DESCRIPTION+=(ADDRESS_LIST+=+(ADDRESS+=+(PROTOCOL+=+TCP)(HOST+=+XXXXX.XX>XXX)(PORT+=+1521)))+(CONNECT_DATA+=+(SID+=+XXXX)(SERVER+=DEDICATED)))",
....
}
log4j+=+{

++++info+'jdbc.sqlonly'+//,+'jdbc.resultsettable'

}|1072080|根据我的个人经验，我发现它在调试时非常有用和有帮助。还有更多信息，你可以在这个网站上找到。https://code.google.com/p/log4jdbc-remix/|offset|length|1072081|国王问候|1072082|entityMap|0|LINK|mutability|MUTABLE|url|https://code.google.com/p/log4jdbc-remix/^0|0|0|0|0|0|0|0|19|15|0|0|0^^$0|@$1|2|3|4|5|6|7|14|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|15|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|16|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|17|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|18|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|19|8|@]|9|@]|A|$]]|$1|O|3|P|5|J|7|1A|8|@]|9|@]|A|$K|L]]|$1|Q|3|R|5|6|7|1B|8|@]|9|@$S|1C|T|1D|1|1E]]|A|$]]|$1|U|3|V|5|6|7|1F|8|@]|9|@]|A|$]]|$1|W|3|-4|5|6|7|1G|8|@]|9|@]|A|$]]]|X|$Y|$5|Z|10|11|A|$12|13]]]]

I know this was asked and answered long back .But I just happened to see this question and couldn't stop myself in answering or sharing our sql logging implementation approach in our project.
Hope it be of some help.

Currently it is in development environment.
We are using "log4jdbc Driver Spy " to log sql.

<h2>Configuration:</h2>

In your BuildConfig.groovy:
add below dependencies:

<pre><code>dependencies {
.....
runtime 'org.lazyluke:log4jdbc-remix:0.2.7'
}
</code></pre>

And in your DataSource or other config related :[wherever you have defined the data source related configuration] ,
Add :

<pre><code>datasources{
.....
driverClassName: "net.sf.log4jdbc.DriverSpy",
url: "jdbc:log4jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = XXXXX.XX&gt;XXX)(PORT = 1521))) (CONNECT_DATA = (SID = XXXX)(SERVER =DEDICATED)))",
....
}
log4j = {

 info 'jdbc.sqlonly' //, 'jdbc.resultsettable'

}
</code></pre>

From my personal experience I found it quite useful and helpful while debugging.
Also more information you can find in this site. <a href="https://code.google.com/p/log4jdbc-remix/" rel="nofollow">https://code.google.com/p/log4jdbc-remix/</a>

King Regards

blocks|key|1072301|text|如果你安装了sql，你可以使用这个小的代码片段来获取console日志。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1072302|//+grails+2.3
def+logger=ctx.sessionFactory.settings.sqlStatementLogger

//+grails+3.3++
def+logger+=+ctx.sessionFactory.currentSession.jdbcCoordinator.statementPreparer.jdbcService.sqlStatementLogger

logger.logToStdout=true++++
try+{
+++<code+that+will+log+sql+queries>
}
finally+{
++++logger.logToStdout+=+false
}|code-block|syntax|javascript|1072303|这是上述许多解决方案的变体，但允许您在运行时调整该值。就像其他处理logToStdout的解决方案一样，它只显示查询，而不显示绑定值。|style|CODE|1072304|这个想法是从几年前我读到的一篇关于burtbeckwith的帖子中窃取的，现在我找不到了。它已经过编辑，可以与grails+3.3一起使用。|1072305|可以使用类似的技术来打开特定集成测试的日志记录：|1072306|class+SomeIntegrationSpec+extends+IntegrationSpec+{

++++def+sessionFactory

++++def+setup()+{
++++++++sessionFactory.settings.sqlStatementLogger.logToStdout+=+true
++++}

++++def+cleanup()+{
++++++++sessionFactory.settings.sqlStatementLogger.logToStdout+=+false
++++}

++++void+"some+test"()+{
+++++++++++...
++++}|1072307|这将为这一个文件中的测试打开sql日志记录。|1072308|entityMap|0|LINK|mutability|MUTABLE|url|http://plugins.grails.org/plugin/sheehan/console^0|Q|7|0|0|0|X|B|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@$A|13|B|14|1|15]]|C|$]]|$1|D|3|E|5|F|7|16|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|17|8|@$A|18|B|19|K|L]]|9|@]|C|$]]|$1|M|3|N|5|6|7|1A|8|@]|9|@]|C|$]]|$1|O|3|P|5|6|7|1B|8|@]|9|@]|C|$]]|$1|Q|3|R|5|F|7|1C|8|@]|9|@]|C|$G|H]]|$1|S|3|T|5|6|7|1D|8|@]|9|@]|C|$]]|$1|U|3|-4|5|6|7|1E|8|@]|9|@]|C|$]]]|V|$W|$5|X|Y|Z|C|$10|11]]]]

If you have the <a href="http://plugins.grails.org/plugin/sheehan/console" rel="nofollow noreferrer">console</a> plugin installed, you can get sql logging with this little code snippet.
<pre><code>// grails 2.3
def logger=ctx.sessionFactory.settings.sqlStatementLogger

// grails 3.3 
def logger = ctx.sessionFactory.currentSession.jdbcCoordinator.statementPreparer.jdbcService.sqlStatementLogger

logger.logToStdout=true 
try {
 &lt;code that will log sql queries&gt;
}
finally {
 logger.logToStdout = false
}
</code></pre>
This is a variation on many of the solutions above, but allows you to tweak the value at runtime. And just like the other solutions that deal with <code>logToStdout</code> it only shows the queries and not the bind values.
The idea was stolen from a burtbeckwith post I read some years ago that I can't find right now. It has been edited to work with grails 3.3.
A similar technique can be used to turn on logging for specific integration tests:
<pre><code>class SomeIntegrationSpec extends IntegrationSpec {

 def sessionFactory

 def setup() {
 sessionFactory.settings.sqlStatementLogger.logToStdout = true
 }

 def cleanup() {
 sessionFactory.settings.sqlStatementLogger.logToStdout = false
 }

 void &quot;some test&quot;() {
 ...
 }
</code></pre>
This will turn on sql logging for just the tests in this one file.

blocks|key|1072176|text|对于特定的代码块，我们还可以创建一个接受闭包的方法。例如：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1072177|+static+def+executeBlockAndGenerateSqlLogs(Closure+closure)+{
++++Logger+sqlLogger+=+Logger.getLogger("org.hibernate.SQL");
++++Level+currentLevel+=+sqlLogger.level
++++sqlLogger.setLevel(Level.TRACE)
++++def+result+=+closure.call()
++++sqlLogger.setLevel(currentLevel)
++++result+}

executeBlockAndGenerateSqlLogs{DomainClazz.findByPropertyName("property+value")}|code-block|syntax|javascript|1072178|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

For a particular Block of code we can also create a method that accept a closure. eg.

<pre><code> static def executeBlockAndGenerateSqlLogs(Closure closure) {
 Logger sqlLogger = Logger.getLogger("org.hibernate.SQL");
 Level currentLevel = sqlLogger.level
 sqlLogger.setLevel(Level.TRACE)
 def result = closure.call()
 sqlLogger.setLevel(currentLevel)
 result }

executeBlockAndGenerateSqlLogs{DomainClazz.findByPropertyName("property value")}
</code></pre>

I want to log in the console or in a file, all the queries that Grails does, to check performance.

I had configured <a href="http://www.grails.org/FAQ#Q:%20How%20can%20I%20turn%20on%20logging%20for%20hibernate%20in%20order%20to%20see%20SQL%20statements,%20input%20parameters%20and%20output%20results?" rel="noreferrer">this</a> without success.

Any idea would help.

How to log SQL statements in Grails

日志文件

集成测试

数据库

服务器

我想要在控制台或文件中记录Grails执行的所有查询，以检查性能。我配置了，但没有成功。任何想法都会有所帮助。

问如何在Grails中记录SQL语句
EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在Grails中记录SQL语句EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在Grails中记录SQL语句
EN