How to Log SQL Statements in Grails

How to log SQL statements in Grails

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.

Logging SQL queries for specific files in Grails

Unfortunately, that is not possible. The grails loggers are turned on and off by class or package name of the code doing the logging. In this case, these are core Hibernate and / or Grails classes, so they either log all activity or no activity.

What you can do is add your own logging statements in your code before and after the operations you are interested in. Then you can use grep to find your marker statements in the log file. The SQL logging you are interested in will be in between your markers and you can ignore the rest of the very large log file.

How to log sql in grails 1.3.7

We did it in Config.groovy,

log4j = {
// ... whatever

debug 'org.hibernate.SQL',
'org.hibernate.transaction' // optionally
}

Log4j is configured differently since Grails 1.1.

Logging SQL in Grails 3.3.1 unit tests

I needed to use HibernateSpec instead of just Specification on the unit tests. Then using the above SQL logging will work.

class MyServiceSpec extends HibernateSpec implements ServiceUnitTest

Grails sql queries

yes, with grails you can do both plain sql and hql queries. HQL is 'hibernate query language' and allows you to write sql-like statements, but use your domain classes and properties instead of the table names and column names. To do an hql query, do something like

def UserList = ConferenceUser.executeQuery('from ConferenceUser cu where cu.user = ?', [user]),  

what you have here is a parameterized query -- executeQuery sees the ? in the hql string and substitutes the arguments in the array that is the second parameter to the method([user] in this case) for you.

See
http://grails.org/doc/latest/ref/Domain%20Classes/executeQuery.html

and you can see this on how to do sql queries with Grails

Sql query for insert in grails

Logging Hibernate SELECT statements into their own log files

I cannot configure it to create separate files for hibernate.SQL logs through @Daniel's answer. But since my team needs to record the queries starting tomorrow, I decided just manually extract the required logs from catalina.out instead.

First, enable logging of SQL statements of your Grails Application before creating a war

log4j.main = {
...
debug 'org.hibernate.SQL'
...
}

The application will log all SQL statements into catalina.out, which by the way, will cause the file to become unnecessarily large. Now we need to schedule a cron task every start of the day to extract the statements from the previous day and compile them into a separate daily file.

cat catalina.out | grep "$(date -d "yesterday 13:00" '+%Y-%m-%d').*DEBUG hibernate.SQL" | cut -d " " -f 8- | sort | uniq | sed 's/$/;\n/' > "hibernate.$(date -d "yesterday 13:00" '+%Y-%m-%d').log"

The beakdown of the bash script is as follow:

  1. cat catalina.out Fetch the source file.
  2. grep "$(date -d "yesterday 13:00" '+%Y-%m-%d').*DEBUG hibernate.SQL" Filter only lines that start with the date yesterday (that you get through this part: $(date -d "yesterday 13:00" '+%Y-%m-%d')) and that has the string DEBUG hibernate.SQL somewhere in between the string.
  3. cut -d " " -f 8- Remove the first part of the line YYYY-MM-dd HH:mm:ss,xxx [http-bio-xxx.xxx.xxx.xxx-xx-exec-x] DEBUG hibernate.SQL -. Our line should immediately start with SELECT, UPDATE, or DELETE.
  4. sort Sorts the line, alphabetically (optional).
  5. uniq Remove duplicate lines (optional).
  6. sed 's/$/;\n/' append a semicolon and a new line at the end of each line.
  7. > "hibernate.$(date -d "yesterday 13:00" '+%Y-%m-%d').log" Save the output to a new file hibernate.YYYY-MM-dd.log.

I prefer to not use this "hacky" solution if not for the urgency, we need a solution by tomorrow after all. If someone can still provide the proper way to configure this through log4j, Grails Config.groovy, or Tomcat, will really be appreciated.

Logging prepared sql query in groovy

I guess what you want to do is log the query string with the ? parameters replaced by their values? I don't know how to do this in the application logs, but your database probably has a query log that can show it.

If you use an overloaded version of execute, you can achieve something very close to what you're asking for

Map params [status: 1, name: "Bobby"    
def query = 'UPDATE my_tbl SET status=? WHERE name=?';
log.debug "query: $query, params: $params"
sql.execute(params, query)

How to pass sql query in grails

        Instead of using def sql = new Sql(dataSource) in domain class use 
def sql = Sql.newInstance(dataSource)


your Domain class can be modified as

import groovy.sql.Sql
class InsertData {

def dataSource

def show(){

def sql = Sql.newInstance(dataSource)
sql.eachRow("SELECT fullName from User")
{
println "the fullname is ${it.fullname}"
}

}



YOUR CONTROLLER AS

class InsertDataController {

def index() { }
def sample() {
InsertData insertData=new InsertData();
insertData.show();
}
}


Related Topics



Leave a reply



Submit