如何在 Grails 中记录 SQL 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2568507/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to log SQL statements in Grails
提问by user2427
回答by Tomislav Nakic-Alfirevic
Setting
环境
datasource {
...
logSql = true
}
in DataSource.groovy (as per theseinstructions) was enough to get it working in my environment. It seems that parts of the FAQ are out of date (e.g. "the many-to-many columns backwards" question) so this might also be something that changed in the meantime.
在 DataSource.groovy 中（按照这些说明）足以让它在我的环境中工作。似乎部分常见问题解答已经过时（例如“向后的多对多列”问题），因此这也可能在此期间发生了变化。
回答by Peter
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).
我发现执行以下操作更有用，即启用 Hibernate 的日志记录以记录 SQL 以及绑定变量（这样您就可以看到传递到调用中的值，并在编辑器或其他方式中轻松复制 SQL）。
In your Config.groovy, add the following to your log4j block:
在您的 中Config.groovy，将以下内容添加到您的 log4j 块中：
log4j = {
// Enable Hibernate SQL logging with param values
trace 'org.hibernate.type'
debug 'org.hibernate.SQL'
//the rest of your logging config
// ...
}
回答by Robert Hutto
For grails 3.*
对于 grails 3.*
Option #1 add the following to logback.groovy
选项 #1 将以下内容添加到 logback.groovy
logger("org.hibernate.SQL", DEBUG, ["STDOUT"], false)
logger("org.hibernate.type.descriptor.sql.BasicBinder", TRACE, ["STDOUT"], false)
or
或者
Option #2 add the following to dataSource in the application.yml. However this approach does not log the parameter values
选项 #2 将以下内容添加到 application.yml 中的 dataSource。但是这种方法不会记录参数值
environments:
local:
dataSource:
logSql: true
formatSql: true
回答by Jason
Try this:
尝试这个：
log4j = {
...
debug 'org.hibernate.SQL'
trace 'org.hibernate.type.descriptor.sql.BasicBinder'
}
It avoids the performance problems of trace logging the Hibernate typepackage. This works with Hibernate 3.6 and above. I got this from: https://burtbeckwith.com/blog/?p=1604
它避免了跟踪记录 Hibernatetype包的性能问题。这适用于 Hibernate 3.6 及更高版本。我从：https: //burtbeckwith.com/blog/?p=1604
回答by Wuestenfuchs
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.
A) 使用 log4jdbc 或 p6Spy 代理您的 jdbc 连接。
B) look at it on database level. For example really easy to do with mysql.
B) 在数据库级别查看它。例如，使用 mysql 真的很容易做到。
Find out where you general_log_file is. Active general log if no activated already.
找出您的 general_log_file 在哪里。如果尚未激活，则活动一般日志。
mysql command line> show variables like "%general_log%";
mysql command line> set global general_log = true;
Now everything is logged to you log file. Mac / linux example to show nice stream of your queries.
现在一切都记录到您的日志文件中。Mac / linux 示例来显示您的查询流。
tail -f path_to_log_file
回答by Dennie de Lange
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).
仅供参考，但我使用 p6spy 来记录 SQL 查询。它是一个小型的中间 jdbc 驱动程序。确切的查询将被记录，因为它将被发送到服务器（包括参数）。
include it in your project:
将其包含在您的项目中：
runtime 'p6spy:p6spy:3.0.0'
Change your datasource driver:
更改您的数据源驱动程序：
driverClassName: com.p6spy.engine.spy.P6SpyDriver
And your jdbc url:
还有你的 jdbc 网址：
url: jdbc:p6spy:mysql://
Configure it using spy.properties (in grails-app/conf).
使用 spy.properties（在 grails-app/conf 中）配置它。
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
Don't forget to disable this for production!
不要忘记在生产中禁用它！
回答by Madhu Bose
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.
我知道这个问题很久以前就有人问过并回答过。但我碰巧看到了这个问题，无法阻止自己回答或分享我们项目中的 sql 日志记录实现方法。希望能有所帮助。
Currently it is in development environment. We are using "log4jdbc Driver Spy " to log sql.
目前处于开发环境。我们使用“log4jdbc Driver Spy”来记录sql。
Configuration:
配置：
In your BuildConfig.groovy: add below dependencies:
在您的 BuildConfig.groovy: 添加以下依赖项：
dependencies {
.....
runtime 'org.lazyluke:log4jdbc-remix:0.2.7'
}
And in your DataSource or other config related :[wherever you have defined the data source related configuration] , Add :
并在您的 DataSource 或其他相关配置 :[where you have defined the data source related configuration] 中，添加：
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'
}
From my personal experience I found it quite useful and helpful while debugging. Also more information you can find in this site. https://code.google.com/p/log4jdbc-remix/
根据我的个人经验，我发现它在调试时非常有用和有用。您还可以在此站点中找到更多信息。https://code.google.com/p/log4jdbc-remix/
King Regards
国王问候
回答by Eduardo Cuomo
Next works for me:
接下来对我来说有效：
grails-app/conf/application.yml
grails-app/conf/application.yml
# ...
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:...
# ...
grails-app/conf/logback.groovy
grails-app/conf/logback.groovy
// ...
appender('STDOUT', ConsoleAppender) {
encoder(PatternLayoutEncoder) {
pattern = "%level %logger - %msg%n"
}
}
// >>>>>>> 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
// ...
回答by burns
If you have the consoleplugin installed, you can get sql logging with this little code snippet.
如果您安装了控制台插件，您可以使用这个小代码片段获取 sql 日志记录。
// 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 {
logToStdout = false
}
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 logToStdoutit only shows the queries and not the bind values.
这是上述许多解决方案的变体，但允许您在运行时调整值。就像处理logToStdout它的其他解决方案一样，只显示查询而不显示绑定值。
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.
这个想法是从几年前我读过的 burtbeckwith 帖子中窃取的，我现在找不到了。它已被编辑以与 grails 3.3 一起使用。
A similar technique can be used to turn on logging for specific integration tests:
可以使用类似的技术为特定的集成测试打开日志记录：
class SomeIntegrationSpec extends IntegrationSpec {
def sessionFactory
def setup() {
sessionFactory.settings.sqlStatementLogger.logToStdout = true
}
def cleanup() {
sessionFactory.settings.sqlStatementLogger.logToStdout = false
}
void "some test"() {
...
}
This will turn on sql logging for just the tests in this one file.
这将只为这个文件中的测试打开 sql 日志记录。
回答by akashsethi
For a particular Block of code we can also create a method that accept a closure. eg.
对于特定的代码块，我们还可以创建一个接受闭包的方法。例如。
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")}
