Using H2 with Grails and Tomcat

I’m working on a relatively small application for a some folks without an IT staff. In an effort to simplify the deployment, I’m planning on using H2 as the production database. It will work just fine for five users, and will prevent them from having to install and maintain MySQL or something else.

H2 is the default database in Grails, but you’ll need to make some changes to the production configuration for using a file-based H2 database with Tomcat. Here is the production section of my DataSource.groovy file:

production {
    dataSource {
        dbdir = "${System.properties['catalina.base']}/db/sample"

        dbCreate = "update"
        url = "jdbc:h2:file:${dbdir};MVCC=TRUE;LOCK_TIMEOUT=10000"
        pooled = true
        username = "sa"
        password = "db!admin"

        properties {
            maxActive = -1
            minEvictableIdleTimeMillis=1800000
            timeBetweenEvictionRunsMillis=1800000
            numTestsPerEvictionRun=3
            testOnBorrow=true
            testWhileIdle=true
            testOnReturn=true
            validationQuery="SELECT 1"
        }
    }
}

Line 3 sets the location and name of the database file. In this case, I’m reading the system property set by Tomcat to determine the location of running instance (catalina.base) and specifying the db will be in a directory called sample in the db directory. You want to create the db directory first. For example, here’s what my Tomcat 7 directory looks like:

On line 5, you’ll want to use update or validate as the dbCreate option so as not to blow away the database each time when you start the application. I prefer update so that Grails can keep simple fast-forward changes in sync.

Finally, on line 6, we build the full datasource URL, substituting in the file location we built above on line 3. I’m setting a password for the database on line 9, but that is optional and really depends on how secure you want the database to be.

Now, when you deploy the WAR file of the Grails application to Tomcat, it will use the embedded H2 database for production, saving the file under the Tomcat location. You can also put the DB file anywhere you want, but my goal was to keep the whole application (Tomcat + Grails) together so I could provide it as a preconfigured Zip file.

4 thoughts on “Using H2 with Grails and Tomcat”

  1. Hi. Thanks for your lovely article on setting up Grails with Tomcat for H2.

    However, I would really like to have JNDI working for my Grails 2.0 app on Tomcat, using H2 as the production database. I’m getting strange errors in the localhost.log file in Tomcat: “Cannot create JDBC driver of class ‘org.h2.Driver’ for connect URL ‘jdbc/qcamDb'”
    1. I’ve placed the h2 driver in the catalina_home/lib directory
    2. I’ve created a context.xml file with the path to the db and placed this file in META-INF directory under my application

    I’m not sure what to do. Please help

    regards,
    Roger

  2. Hi,

    I followed your guide but ran into errors when deploying this.
    In tomcat log tomcat7-stdout.2016-02-09.log I get:
    2016-02-09 11:59:35,797 [localhost-startStop-1] ERROR pool.ConnectionPool – Unable to create initial connections of pool.
    java.sql.SQLException: Driver:org.h2.Driver@3871b7bc returned null for URL:”jdbc:h2:file:E:/MyTool/db/MyTool;MVCC=TRUE;LOCK_TIMEOUT=10000″

    The same works fine on my development machine.

    Any idea?

  3. кто оплачивает сделку купли продажи у нотариуса

    вернуть деньги без расписки это

    Delete this!
    Удали это!

Leave a Reply

Your email address will not be published. Required fields are marked *


*