BCS Grails With A MySQL Backend

There are those times when it is advantageous to connect Grails applications to MySQL relational database management system (DBMS).

The default DataSource.Groovy is depicted below.

dataSource {
    pooled = true
    jmxExport = true
    driverClassName = "org.h2.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' // Hibernate 3
//    cache.region.factory_class = 'org.hibernate.cache.ehcache.EhCacheRegionFactory' // Hibernate 4
}
 
// environment specific settings
environments {
    development {
        dataSource {
            dbCreate = "create-drop" // one of 'create', 'create-drop', 'update', 'validate', ''
            url = "jdbc:h2:mem:devDb;MVCC=TRUE;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE"
        }
    }
    test {
        dataSource {
            dbCreate = "update"
            url = "jdbc:h2:mem:testDb;MVCC=TRUE;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE"
        }
    }
    production {
        dataSource {
            dbCreate = "update"
            url = "jdbc:h2:prodDb;MVCC=TRUE;LOCK_TIMEOUT=10000;DB_CLOSE_ON_EXIT=FALSE"
            properties {
               // Documentation for Tomcat JDBC Pool
               // http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html#Common_Attributes
               // https://tomcat.apache.org/tomcat-7.0-doc/api/org/apache/tomcat/jdbc/pool/PoolConfiguration.html
               jmxEnabled = true
               initialSize = 5
               maxActive = 50
               minIdle = 5
               maxIdle = 25
               maxWait = 10000
               maxAge = 10 * 60000
               timeBetweenEvictionRunsMillis = 5000
               minEvictableIdleTimeMillis = 60000
               validationQuery = "SELECT 1"
               validationQueryTimeout = 3
               validationInterval = 15000
               testOnBorrow = true
               testWhileIdle = true
               testOnReturn = false
               ignoreExceptionOnPreLoad = true
               // http://tomcat.apache.org/tomcat-7.0-doc/jdbc-pool.html#JDBC_interceptors
               jdbcInterceptors = "ConnectionState;StatementCache(max=200)"
               defaultTransactionIsolation = java.sql.5.0%2Fdocs%2Fapi%2F">Connection.TRANSACTION_READ_COMMITTED // safe default
               // controls for leaked connections 
               abandonWhenPercentageFull = 100 // settings are active only when pool is full
               removeAbandonedTimeout = 120000
               removeAbandoned = true
               // use JMX console to change this setting at runtime
               logAbandoned = false // causes stacktrace recording overhead, use only for debugging
               /*
               // JDBC driver properties
               // Mysql as example
               dbProperties {
                   // Mysql specific driver properties
                   // http://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html
                   // let Tomcat JDBC Pool handle reconnecting
                   autoReconnect=false
                   // truncation behaviour 
                   jdbcCompliantTruncation=false
                   // mysql 0-date conversion
                   zeroDateTimeBehavior='convertToNull'
                   // Tomcat JDBC Pool's StatementCache is used instead, so disable mysql driver's cache
                   cachePrepStmts=false
                   cacheCallableStmts=false
                   // Tomcat JDBC Pool's StatementFinalizer keeps track
                   dontTrackOpenResources=true
                   // performance optimization: reduce number of SQLExceptions thrown in mysql driver code
                   holdResultsOpenOverStatementClose=true
                   // enable MySQL query cache - using server prep stmts will disable query caching
                   useServerPrepStmts=false
                   // metadata caching
                   cacheServerConfiguration=true
                   cacheResultSetMetadata=true
                   metadataCacheSize=100
                   // timeouts for TCP/IP
                   connectTimeout=15000
                   socketTimeout=120000
                   // timer tuning (disable)
                   maintainTimeStats=false
                   enableQueryTimeouts=false
                   // misc tuning
                   noDatetimeStringSync=true
               }
               */
            }
        }
    }
}

A working model of the DataSource.Groovy is depicted as follows:

dataSource {
	pooled = true
	driverClassName = "com.mysql.jdbc.Driver"
	dialect = "org.hibernate.dialect.MySQL5InnoDBDialect"
}
hibernate {
	cache.use_second_level_cache = true
	cache.use_query_cache = false
	cache.region.factory_class = 'net.sf.ehcache.hibernate.EhCacheRegionFactory'
}
// environment specific settings
environments {
	development {
		dataSource {
			dbCreate = "update" // one of 'create', 'create-drop', 'update', 'validate', ''
			url = "jdbc:mysql://localhost:3306/ame01?useUnicode=yes&characterEncoding=UTF-8"
			username = "bcs"
			password = "XXXXXX"
		}
	}
	test {
		dataSource {
			dbCreate = "update"
			url = "jdbc:mysql://localhost:3306/ame01?useUnicode=yes&characterEncoding=UTF-8"
			username = "bcs"
			password = "XXXXXX"
		}
	}
	production {
		dataSource {
			dbCreate = "update"
			url = "jdbc:mysql://localhost:3306/ame01?useUnicode=yes&characterEncoding=UTF-8"
			username = "bcs"
			password = "XXXXXX"
			pooled = true			
			properties {
				maxActive = -1
				minEvictableIdleTimeMillis=1800000
				timeBetweenEvictionRunsMillis=1800000
				numTestsPerEvictionRun=3
				testOnBorrow=true
				testWhileIdle=true
				testOnReturn=true
				validationQuery="SELECT 1"
			}
		}
	}
}

The next consideration is the BuildConfig.Groovy where we need to tell Grails to use the MySQL RDBMS for the build.

grails.servlet.version = "3.0" // Change depending on target container compliance (2.5 or 3.0)
grails.project.class.dir = "target/classes"
grails.project.test.class.dir = "target/test-classes"
grails.project.test.reports.dir = "target/test-reports"
grails.project.work.dir = "target/work"
grails.project.target.level = 1.6
grails.project.source.level = 1.6
//grails.project.war.file = "target/${appName}-${appVersion}.war"
 
grails.project.fork = [
    // configure settings for compilation JVM, note that if you alter the Groovy version forked compilation is required
    //  compile: [maxMemory: 256, minMemory: 64, debug: false, maxPerm: 256, daemon:true],
 
    // configure settings for the test-app JVM, uses the daemon by default
    test: [maxMemory: 768, minMemory: 64, debug: false, maxPerm: 256, daemon:true],
    // configure settings for the run-app JVM
    run: [maxMemory: 768, minMemory: 64, debug: false, maxPerm: 256, forkReserve:false],
    // configure settings for the run-war JVM
    war: [maxMemory: 768, minMemory: 64, debug: false, maxPerm: 256, forkReserve:false],
    // configure settings for the Console UI JVM
    console: [maxMemory: 768, minMemory: 64, debug: false, maxPerm: 256]
]
 
grails.project.dependency.resolver = "maven" // or ivy
grails.project.dependency.resolution = {
    // inherit Grails' default dependencies
    inherits("global") {
        // specify dependency exclusions here; for example, uncomment this to disable ehcache:
        // excludes 'ehcache'
    }
    log "error" // log level of Ivy resolver, either 'error', 'warn', 'info', 'debug' or 'verbose'
    checksums true // Whether to verify checksums on resolve
    legacyResolve false // whether to do a secondary resolve on plugin installation, not advised and here for backwards compatibility
 
    repositories {
        inherits true // Whether to inherit repository definitions from plugins
 
        grailsPlugins()
        grailsHome()
        mavenLocal()
        grailsCentral()
        mavenCentral()
        // uncomment these (or add new ones) to enable remote dependency resolution from public Maven repositories
        //mavenRepo "http://repository.codehaus.org"
        //mavenRepo "http://download.java.net/maven/2/"
        //mavenRepo "http://repository.jboss.com/maven2/"
		mavenRepo 'http://repo.spring.io/milestone'
        mavenRepo "http://download.java.net/maven/2/"
    }
 
    dependencies {
        // specify dependencies here under either 'build', 'compile', 'runtime', 'test' or 'provided' scopes e.g.
        // runtime 'mysql:mysql-connector-java:5.1.27'
        // runtime 'org.postgresql:postgresql:9.3-1100-jdbc41'
        runtime 'mysql:mysql-connector-java:5.1.29'
    }
 
    plugins {
        // plugins for the build system only
        build ":tomcat:7.0.50.1"
 
        // plugins for the compile step
        compile ":scaffolding:2.0.2"
        compile ':cache:1.1.1'
		compile ':spring-security-core:2.0-RC2'
		compile ":spring-security-ui:1.0-RC1"
		compile ":spring-security-acl:2.0-RC1"
 
        // plugins needed at runtime but not for compilation
        runtime ":hibernate:3.6.10.8" // or ":hibernate4:4.3.1.1"
        runtime ":database-migration:1.3.8"
        runtime ":jquery:1.11.0"
        runtime ":resources:1.2.1"
        // Uncomment these (or add new ones) to enable additional resources capabilities
        //runtime ":zipped-resources:1.0.1"
        //runtime ":cached-resources:1.1"
        //runtime ":yui-minify-resources:0.1.5"
 
        // An alternative to the default resources plugin is the asset-pipeline plugin
        //compile ":asset-pipeline:1.5.0"
 
        // Uncomment these to enable additional asset-pipeline capabilities
        //compile ":sass-asset-pipeline:1.5.1"
        //compile ":less-asset-pipeline:1.5.0"
        //compile ":coffee-asset-pipeline:1.5.0"
        //compile ":handlebars-asset-pipeline:1.0.0.3"
    }
}

Now run your grails application.  If you notice no errors you application will now use MySQL as a back end RDBMS.

Mr. Arch Brooks, Software Engineer, Brooks Computing Systems, LLC authored this article.