Working with database : Grails

In Grails it is very easy to work with database. Grails framework is already supported with ORM called GORM (Grails Object relation mapping).

How to configure to use NHibernate with Mysql?

You just need to add some dependencies and plugins in BuildConfig.groovy

dependencies {
        runtime 'mysql:mysql-connector-java:5.1.22'
    }

 plugins {
    runtime ":hibernate:$grailsVersion"
   }

Next step is to configure datasource under DataSource.groovy file.

Database configuration for MySql Database

dataSource {
    pooled = true
    driverClassName = "com.mysql.jdbc.Driver"
    username = "sa"
    password = ""
}

hibernate {
    cache.use_second_level_cache = true
    cache.use_query_cache = false
    cache.region.factory_class = 'net.sf.ehcache.hibernate.EhCacheRegionFactory'
}

environments {
    development {
        dataSource {
            logSql = true
            url = "jdbc:mysql://localhost/mysqldbname"
            username = "root"
            password = "root"
        }
    }
    test {
        dataSource {
            dbCreate = "update"
            url = "jdbc:h2:mem:testDb;MVCC=TRUE;LOCK_TIMEOUT=10000"
        }
    }
    production {
        dataSource {
            url = "jdbc:mysql://productionmysqlserver/databasename"
            username = "root"
            password = "********"
            properties {
                maxActive = -1
                minEvictableIdleTimeMillis=1800000
                timeBetweenEvictionRunsMillis=1800000
                numTestsPerEvictionRun=3
                testOnBorrow=true
                testWhileIdle=true
                testOnReturn=true
                validationQuery="SELECT 1"
            }
        }
    }
}

Now you need to so some insert, update, delete and query data with mysql database from grails application. Domain classes are bases for developing application on grails. You create domain classes that should be persistent.

Here is the simple domain class.

class Product{

    String name
    String description
    boolean active = true

}

One you create the domain class, you are ready to do any database operation. Here is the example code to create new product and save to database.

def productInstance = new Product()
productInstance.name="MY name"
productInstance.description="Test test description"
productInstance.active=true
productInstance.save()

Querying the database from grails application

Groovy is the dynamic language, so once you create a domain classes, groovy does some wonders. It is smart enough to generate some dynamic static functions so you can simply call that method to query the database. For eg: If you have name property on the domain class, findByName is available to you on the fly.

In addition to dynamic query functions, you can use executeQuery and createCriteria to fetch data.

Below are some example queires

Find distinct records using inner join query

def list = MyDomainClass.executeQuery("select distinct a from ClassA a, ClasssB b  where a=b.parentProp and (g.status=:status1 or (g.assignedUser=:user and g.status=:status2))", [status1: 'Completed', user: user, status2: 'Open'])

Group by along with filter, sorting by grouping column

  def criteria = DomainClass.createCriteria()
        def list = criteria.list {
            projections {
                groupProperty 'field1'
                count 'id', 'count'
            }
            and {
                eq("field2", field2)

                or {
                    eq("field3",field3value)
                    eq("field4", field4value)
                }
            }
            order('count', 'desc')
        }