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:
cat catalina.out
Fetch the source file.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 stringDEBUG hibernate.SQL
somewhere in between the string.cut -d " " -f 8-
Remove the first part of the lineYYYY-MM-dd HH:mm:ss,xxx [http-bio-xxx.xxx.xxx.xxx-xx-exec-x] DEBUG hibernate.SQL -
. Our line should immediately start withSELECT
,UPDATE
, orDELETE
.sort
Sorts the line, alphabetically (optional).uniq
Remove duplicate lines (optional).sed 's/$/;\n/'
append a semicolon and a new line at the end of each line.> "hibernate.$(date -d "yesterday 13:00" '+%Y-%m-%d').log"
Save the output to a new filehibernate.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
Concatenate Multiple Result Rows of One Column into One, Group by Another Column
Is Having an 'Or' in an Inner Join Condition a Bad Idea
How to Declare Variable and Use It in the Same Oracle SQL Script
Escaping Ampersand Character in SQL String
Filter Table Before Applying Left Join
Group by Behavior When No Aggregate Functions Are Present in the Select Clause
How to Use on Delete Cascade in MySQL
How to Rewrite Is Distinct from and Is Not Distinct from in SQL Server 20008R2
Microsoft Jet Wildcards: Asterisk or Percentage Sign
Key Value Pairs in Relational Database
Declare Variable in Sqlite and Use It
Reset Auto Increment Counter in Postgres
How to Get Column Names from a Table in Oracle
SQL Server: Make All Upper Case to Proper Case/Title Case
Generate a Resultset of Incrementing Dates in Tsql
Stored Procedure or Function Expects Parameter Which Is Not Supplied