Lauro Becker

Java, Grails and other great things

Category Archives: GORM

Dao Artefacts Plugin


That working with database using Grails is very trivial, everybody knows. You just have to write some domain mappings here, change some connections configurations there, and all works like a charm.

But, well, sometimes we have to do some additional coding around the conventions and we do need write some SQL instructions for a certain kind of database. This kind of thing commonly happens when you’re working with legacy systems. The best approach, with no doubt, would be to map everything as domain classes, and take all Grails’ advantages. But it’s not always possible.

The Problem

In my case, I’m in a project where we had to integrate an old hard coded Struts 1 app with Grails. I’m very lucky of getting everything working really fast with Struts 1 Plugin. Originally the application was developed over PostgreSQL, and now we’re running with Oracle too.  You should be thinking about DAO and DAO Factory patterns. And, I guess, it is the best solution for multiple datasource types.

Probably, before ORM’s era, you should have written tons of SQL instructions in hundreds of DAOs. Then, you always tried your very best for using SQL ANSI. So you’d never need to write specific SQLs. In the most of the situations you achieved it with no problems. But you always had to write some specific code for pagination purposes… Damn!

See how to do it with Oracle and PostgreSQL:

If you’re accustomed with Grails as I am, you may be thinking about how many lines you’ll have to write for achieving this. And, yes, it is a lot of code.

The Solution

I’m coming in this post to present you the new Dao Artefacts Plugin I’ve developed, for making my life easier – hope yours too. In fact, I hope you never need to write any DAO in your shinny Grails apps, but life is not always fair. 😉

Let’s say you have the above situation with Customer’s stuff – and have already installed the plugin. Create a new Groovy class inside grail-app/daos named CustomerDao. Put all your SQL ANSI inside it. Now create a subclass CustomerOracleDao and override all specific methods. Do the same for CustomerPostgresDao.

In your project’s Config.groovy, add the following lines: = "Oracle"
grails.plugins.daoartefacts.datasource.all = ["Oracle", "Postgres"]

The first is the actual database name. In this case, we are running over Oracle. The second line has a list of all known databases (for now, Oracle and Postgres).

When the application starts, the plugin looks for all classes inside grails-app/daos with *Dao.groovy pattern, and register them as Spring beans. If no per database class is created, the “ANSI” will be used. So, you don’t have to write factories. The magic happens by the Convention over Configuration paradigm.

Now you only need to inject your new DAO in your services. For example:

class CustomerService {
	def customerDao // Oracle or Postgres DAO, depending on the configuration

	void foo() {


I’ve been seeing people putting native SQLs inside Grails Services. The main goal of this great framework is its productivity, but it doesn’t mean that design patterns should be left behind.

If you were missing a solution for Data Access tier, perhaps this plugin can fit.

Grails: Personalizando nomes de chaves estrangeiras

É comum trabalharmos em projetos onde não temos total controle sobre a base de dados, e nossos sistemas devem se adaptar ao modelo. Também não é incomum encontrarmos chaves primárias compostas para as tabelas – vantagens e desvantagens dessa abordagem estão fora do escopo deste post.

Onde há chaves primárias compostas, presume-se que teremos chaves estrangeiras compostas. E, se você está trabalhando com Grails, isso não é tão simples de mapear quanto deveria ser. Não até a versão 1.3.7, ao menos.

Passando pelos problemas relatados em, resolvi criar um workaround, baseado na solução proposta pelo Burt Beckwith, enquanto a resolução definitiva não chega.

Adicione ao seu projeto o arquivo disponível em e altere o DataSource.groovy:


dataSource {
    pooled = true
    driverClassName = "…"
    configClass = OrganicoAnnotationConfiguration

Para o exemplo descrito no JIRA do Grails, seria feito algo assim:

class DeliveryInstruction {
    String description
    IntervalType intervalType
    IntervalQuantity intervalQuantity

    static constraints = {
        description(nullable:false, blank:false, size:3..250)

    static mapping = {
        table   'mail_delivery_instruction'
        version false
        id      generator:'sequence', params:[sequence:'DELIVERY_INSTR_SEQ']
        columns {
            id               column:'code'
            intervalType     column:'interval_type_code'
            //intervalQuantity column:'interval_quantity'
            description      column:'description'

    String toString() {
        return description

    static foreigners = [
        intervalQuantity : [intervalType : "interval_type",
                          quantity: "interval_quantity" ]

O atributo foreigners é um mapa. Cada item deste mapa possui uma chave String (intervalQuantity) e os valores são outro mapa ([interval_type : “interval_type”, quantity: “interval_quantity” ]).

A chave intervalQuantity é o nome da propriedade que define o relacionamento. intervalType é o nome da FK sugerida pelo Grails, e interval_type o novo nome. Ok, ok, nesse exemplo não foi nada alterado… mas observe o atributo quantity.

É algo experimental, mas já foi testado em produção. 😉