如何在 Grails 中记录 SQL 语句

我想在控制台或文件中登录 Grails 执行的所有查询，以检查性能。

我配置了 这个，但没有成功。

任何想法都会有帮助。

59285 次浏览
小开
最佳答案

Setting

datasource {
...
logSql = true
}

in DataSource.groovy (as per these instructions) 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.

小开

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 Config.groovy, add the following to your log4j block:

log4j = {


// Enable Hibernate SQL logging with param values
trace 'org.hibernate.type'
debug 'org.hibernate.SQL'
//the rest of your logging config
// ...
}
小开

Try this:

log4j = {
...
debug 'org.hibernate.SQL'
trace 'org.hibernate.type.descriptor.sql.BasicBinder'
}

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

小开

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.

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. 

tail -f path_to_log_file
小开

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.

Configuration:

In your BuildConfig.groovy: add below dependencies:

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 :

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/

King Regards

小开

For grails 3.*

Option #1 add the following to 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

environments:
local:
dataSource:
logSql: true
formatSql: true
小开

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")}
小开

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:

runtime 'p6spy:p6spy:3.0.0'

Change your datasource driver: 

driverClassName: com.p6spy.engine.spy.P6SpyDriver

And your jdbc url: 

url: jdbc:p6spy:mysql://

Configure it using spy.properties (in 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!

小开

If you have the console plugin installed, you can get sql logging with this little code snippet.

// grails 2.3
def logger=ctx.sessionFactory.settings.sqlStatementLogger


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


logger.logToStdout=true
try {
<code that will log sql queries>
}
finally {
logger.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 logToStdout 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:

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.

小开

Next works for me:

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

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

Source: http://sergiodelamo.es/log-sql-grails-3-app/

小开

logback.xml

Grails 5 and above only accepts logback.xml. Add the following inside the configuration tag:

<logger name="org.hibernate.SQL" level="DEBUG" additivity="false">
<appender-ref ref="STDOUT" />
</logger>
<logger name="org.hibernate.type.descriptor.sql.BasicBinder" level="TRACE" additivity="false">
<appender-ref ref="STDOUT" />
</logger>

application.yml

To better visualize SQL queries, add the following:

dataSource:
formatSql: true

If you want logback configuration for development only, you can add the following to the block environments > development with logging configuration in conf/logback-dev.xml:

logging:
config: classpath: logback-dev.xml

提交答案