HTTPS SSH

UWS-jOOQ Grails Plugin

Description

This plugin simplifies usage of jOOQ plugin and integrates it nicely with Grails lifecycle. I could be especially useful when dealing with legacy databases or introduce typesafe SQL.

More info about jOOQ: http://www.jooq.org/

More info about UWS: http://uws-software-service.com

Quickstart

Add plugin to plugins section in your BuildConfig.groovy

compile ':uws-jooq:0.1.1'

Add this to your Config.groovy

jooq.dataSource = ['dataSource']
jooq.xmlConfigDir = "src/resources/jooq/"
jooq.generatedClassOutputDirectory = 'src/java'
jooq.generatedClassPackageName.dataSource = 'ie.uws.example'

First you have to generate xml configuration file. Before running that command make sure that your database is there and it's accessible.

grails jooq-generate-config

Next you have to execute jooq classes generation. Please remember to execute this command each time your model changes:

grails jooq-init

Configuration

Grails configuration file

You can adjust configuration of plugin via regular Config.groovy file.

DataSource

jooq.dataSource - Specifies which dataSources should be used during generation of default jOOQ xml config file(s).

Using default dataSource:

jooq.dataSource = ['dataSource']

Using multiple dataSources:

jooq.dataSource = ['dataSource', 'dataSource_db2']

Target xml

jooq.xmlConfigDir - Specifies target directory of jOOQ xml configuration file. Used only during generation of default jOOQ xml config file(s).

Example:

jooq.xmlConfigDir = "src/resources/jooq/"

Target classes

jooq.generatedClassOutputDirectory - Specifies target directory for generated classes. Used only during generation of default jOOQ xml config file(s).

Example:

jooq.generatedClassOutputDirectory = 'src/java'

Target package

jooq.generatedClassPackageName.dataSource - Specifies package path for generated classes. Used only during generation of default jOOQ xml config file(s).

Example:

jooq.generatedClassPackageName.dataSource = 'ie.uws.example'

jOOQ configuration file

Documentation how to adjust jooq's xml configuration file can be found here.

Best practices

Keeping files in repository

You should commit generated classes and xml configuration to your code repository.

Plugin is compiling code to execute db migrations before generating classes, thanks to this approach all needed steps are performed in single jooq-init action. You might encounter situation when your code is not compiling and you cannot generate proper jooq classes. That's how jooq is notifying you that your code is out of date and should be fixed. You should fix compilation issues before you can execute jooq-init command.

Using jooqService

Connection is released by Grails when transaction is completed or action is completed. Each execution of jooqService.dataSource is trying to new connection from pool. In example below, new connection is created for each loop iteration. In short period application will hit connections limit and hang. You should not use this approach in your code, instead please see how to reuse dataSource or in worst case use explicit transaction.

class HangController {
    JooqService jooqService

    def willHang() {
        (1..10000).each { index ->
            BookRecord record = jooqService.dataSource.newRecord(Tables.BOOK)
            record.author = "John-${index}"
            record.name = "Hobbindex-${index}"
            record.version = 1
            record.store()
            println index
        }
    }

    def transaction() {
        (1..10000).each { index ->
            Book.withTransaction {
                BookRecord record = jooqService.dataSource.newRecord(Tables.BOOK)
                record.author = "John-${index}"
                record.name = "Hobbindex-${index}"
                record.version = 1
                record.store()
                println index
            }
        }
    }

    def reuse() {
        DSLContext dataSource = jooqService.dataSource
        (1..10000).each { index ->
            BookRecord record = dataSource.newRecord(Tables.BOOK)
            record.author = "John-${index}"
            record.name = "Hobbindex-${index}"
            record.version = 1
            record.store()
            println index
        }
    }
}

Plugin development

In order to build this plugin and use locally execute:

grails package-plugin

grails maven-install