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.
[codesyntax lang=”groovy”]
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.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
}
*/
}
}
}
}
[/codesyntax]
A working model of the DataSource.Groovy is depicted as follows:
[codesyntax lang=”groovy”]
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"
}
}
}
}
[/codesyntax]
The next consideration is the BuildConfig.Groovy where we need to tell Grails to use the MySQL RDBMS for the build.
[codesyntax lang=”groovy”]
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"
}
}
[/codesyntax]
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.