Grails SQL Queries

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

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();
}
}

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.

Translating SQL query to Grails

Todd. In grails you can use hibernate native sql queries as described in this post by mr hacki.

I share an example taken from code in production that uses the technique described in the publication and uses left join.

List<Map<String, Object>> resumeInMonth(final String monthName) {
    final session = sessionFactory.currentSession
    final String query = """
        SELECT
            t.id AS id,
            e.full_name AS fullName,
            t.subject AS issue,
            CASE t.status
                WHEN 'open' THEN 'open'
                WHEN 'pending' THEN 'In progress'
                WHEN 'closed' THEN 'closed'
            END AS status,
            CASE t.scheduled
                WHEN TRUE THEN 'scheduled'
                WHEN FALSE THEN 'non-scheduled'
            END AS scheduled,
            ifnull(d.name, '') AS device,
            DATE(t.date_created) AS dateCreated,
            DATE(t.last_updated) AS lastUpdated,
            IFNULL(total_tasks, 0) AS tasks
        FROM
            tickets t
                INNER JOIN
            employees e ON t.employee_id = e.id
                LEFT JOIN
            devices d ON d.id = t.device_id
                LEFT JOIN
            (SELECT
                ticket_id, COUNT(1) AS total_tasks
            FROM
                tasks
            GROUP BY ticket_id) ta ON t.id = ta.ticket_id
        WHERE
            MONTHNAME(t.date_created) = :monthName
        ORDER BY dateCreated DESC
    """
    final sqlQuery = session.createSQLQuery(query)
    final results = sqlQuery.with {
        resultTransformer = AliasToEntityMapResultTransformer.INSTANCE

        setString('monthName', monthName)

        list()
    }

    results
}

I hope it is useful for you

Grails: Raw SQL query in the current transaction

The above code would create a new connection, hence a new transaction. You can use the current Hibernate session(injecting sessionFactory) to execute a raw sql in the current transaction as below.

def session = sessionFactory.getCurrentSession()
def results = session.createSQLQuery(yourQueryString)

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.

---

Related Topics