Blog

  • 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
                }
        }
    

Tags: grails

Mobile Applications

Video Content

Bigdata & NoSQL

SaaS Applications

Miscellaneous

Archives


Alexa Certified Site Stats for www.oodlestechnologies.com