BCS Grails Using MySQL RDBMS

The Grails environment for this example is Grails 3.2.2, Groovy 2.4.7 and JVM 1.8.0_92.

To allow Grails to converse with MySQL these following steps should be followed.

First change the build.gradle file to include the MySQL driver as depicted below.

buildscript {
    repositories {
        mavenLocal()
        maven { url "https://repo.grails.org/grails/core" }
    }
    dependencies {
        classpath "org.grails:grails-gradle-plugin:$grailsVersion"
        classpath "com.bertramlabs.plugins:asset-pipeline-gradle:2.11.1"
        classpath "org.grails.plugins:hibernate5:6.0.2"
    }
}
 
version "0.1"
group "books"
 
apply plugin:"eclipse"
apply plugin:"idea"
apply plugin:"war"
apply plugin:"org.grails.grails-web"
apply plugin:"org.grails.grails-gsp"
apply plugin:"asset-pipeline"
 
repositories {
    mavenLocal()
    maven { url "https://repo.grails.org/grails/core" }
}
 
dependencyManagement {
    imports {
        mavenBom "org.grails:grails-bom:$grailsVersion"
    }
    applyMavenExclusions false
}
 
dependencies {
    compile "org.springframework.boot:spring-boot-starter-logging"
    compile "org.springframework.boot:spring-boot-autoconfigure"
    compile "org.grails:grails-core"
    compile "org.springframework.boot:spring-boot-starter-actuator"
    compile "org.springframework.boot:spring-boot-starter-tomcat"
    compile "org.grails:grails-dependencies"
    compile "org.grails:grails-web-boot"
    compile "org.grails.plugins:cache"
    compile "org.grails.plugins:scaffolding"
    compile "org.grails.plugins:hibernate5"
    compile "org.hibernate:hibernate-core:5.1.1.Final"
    compile "org.hibernate:hibernate-ehcache:5.1.1.Final"
    console "org.grails:grails-console"
    compile "org.grails.plugins:spring-security-core:3.1.1"
    profile "org.grails.profiles:web"
    runtime "com.bertramlabs.plugins:asset-pipeline-grails:2.11.1"
    runtime "com.h2database:h2"
    runtime "mysql:mysql-connector-java:5.1.40"
    testCompile "org.grails:grails-plugin-testing"
    testCompile "org.grails.plugins:geb"
    testRuntime "org.seleniumhq.selenium:selenium-htmlunit-driver:2.47.1"
    testRuntime "net.sourceforge.htmlunit:htmlunit:2.18"
}
 
 
assets {
    minifyJs = true
    minifyCss = true
}

Next change the application.yml file to describe the connection for the database.

---
grails:
    profile: web
    codegen:
        defaultPackage: books
    spring:
        transactionManagement:
            proxies: false
info:
    app:
        name: '@info.app.name@'
        version: '@info.app.version@'
        grailsVersion: '@info.app.grailsVersion@'
spring:
    groovy:
        template:
            check-template-location: false
# Spring Actuator Endpoints are Disabled by Default
endpoints:
    enabled: false
    jmx:
        enabled: true
 
---
grails:
    mime:
        disable:
            accept:
                header:
                    userAgents:
                        - Gecko
                        - WebKit
                        - Presto
                        - Trident
        types:
            all: '*/*'
            atom: application/atom+xml
            css: text/css
            csv: text/csv
            form: application/x-www-form-urlencoded
            html:
              - text/html
              - application/xhtml+xml
            js: text/javascript
            json:
              - application/json
              - text/json
            multipartForm: multipart/form-data
            pdf: application/pdf
            rss: application/rss+xml
            text: text/plain
            hal:
              - application/hal+json
              - application/hal+xml
            xml:
              - text/xml
              - application/xml
    urlmapping:
        cache:
            maxsize: 1000
    controllers:
        defaultScope: singleton
    converters:
        encoding: UTF-8
    views:
        default:
            codec: html
        gsp:
            encoding: UTF-8
            htmlcodec: xml
            codecs:
                expression: html
                scriptlets: html
                taglib: none
                staticparts: none
endpoints:
    jmx:
        unique-names: true
 
---
hibernate:
    cache:
        queries: false
        use_second_level_cache: true
        use_query_cache: false
        region.factory_class: org.hibernate.cache.ehcache.SingletonEhCacheRegionFactory
 
dataSource:
       pooled: true
       driverClassName: "com.mysql.jdbc.Driver"
       dialect: "org.hibernate.dialect.MySQL5InnoDBDialect"
environments:
    development:
        dataSource:
            dbCreate: "update" 
            url: "jdbc:mysql://localhost/database?useUnicode=yes&characterEncoding=UTF-8"
            username: "user"
            password:  "password"
    test:
        dataSource:
            dbCreate: update
            url: "jdbc:mysql://localhost/database?useUnicode=yes&characterEncoding=UTF-8"
            username: "user"
            password:  "password"
    production:
        dataSource:
            dbCreate: none
            url: "jdbc:mysql://localhost/database?useUnicode=yes&characterEncoding=UTF-8"
            username: "user"
            password:  "password"
            properties:
               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
               jdbcInterceptors: "ConnectionState;StatementCache(max=200)"
               defaultTransactionIsolation: java.sql.Connection.TRANSACTION_READ_COMMITTED

Now you should issue the Grails run-app command at the command line as depicted below.

If there are no errors Grails should be conversant with MySQL at this point.  The resulting successful output is depicted below.

The coast is clear for further utilization of MySQL in your grails app.

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

Leave a Reply