How we can use MySQL mapping table in Groovy and Grails

Posted By : Amit Patel | 24-Sep-2018

grails

In this blog, we will learn about how we can execute custom SQL with Groovy SQL. We must create a new instance of groovy.sql.Sql in our groovy code to execute SQL code. In groovy, mapping tables can not access or execute directly. But when we create a new instance of groovy.sql.Sql then we execute mapping tables in our groovy code. The simplest way, it's to use a javax.sql.DataSource as a constructor argument for the groovy.sql.Sql class. The DataSource already available in a Grails application context and simply we can inject DataSource into our groovy code and use it. We simply define the name dataSource as a reference the default dataSource in a Grails application.

In this following example, we execute a custom query in groovy code using Groovy SQL. Note that we simply define a dataSource property in the Grails service package and Grails will automatically inject a DataSource instance.

Example:-

    package com.oodles.project
    import grails.transaction.Transactional
    import groovy.sql.GroovyRowResult
    import groovy.sql.Sql
    
    @Transactional
    class PortfolioService {

    // Reference to default datasource.
    def dataSource
    
       List<GroovyRowResult> sharedDedicatedResourceCount(Long userId) {
           final def query = '''\
               select count(*) from project_team_user where user_id = '''+userId+''';
               '''
    
           // Create new Groovy SQL instance with injected DataSource.
           final Sql sql = new Sql(dataSource)
    
           final results = sql.rows(query)
           results
       }
    }

In Grails application, we can also make the groovy.sql.Sql instance a Spring bean. And then we can inject the SQL instance in groovy code like Grails service package classes. In grails-app/conf/spring/UserResources.groovy we define the SQL bean:

Example:-

// File: grails-app/conf/spring/UserResources.groovy
beans = {
 
    // Create Spring bean for Groovy SQL.
    // groovySQL is the name of the bean and it can be used for injection.
    groovySQL(groovy.sql.Sql, ref('dataSource'))
 
}

Previous Example and use the bean groovySQL:

    package com.oodles.project
    import grails.transaction.Transactional
    import groovy.sql.GroovyRowResult
    import groovy.sql.Sql

     @Transactional
    class PortfolioService {
 
        // Reference to groovySQL defined in UserResources.groovy.
        def groovySQL
 
            List<GroovyRowResult> sharedDedicatedResourceCount(Long userId) {
 
                final def query = '''\
                   select count(*) from project_team_user where user_id = '''+userId+''';
                ''' 
                // Use groovySQL bean to execute the query.
                final results = groovySQL.rows(query)
                results
            }
    }