Accessing DB Without Domain Classes With Grails

Question

I've ran into an issue while trying to put together a Grails app with an AS400/DB2 database. I cannot get most of the files mapped because they do not have a unique field to use as an id. And even if they do they are a text based field and not in a format that could be converted to a long type. (I don't get why the PK has to be a long data type? If you wanted to us a sequence or AI for the pk that would make sense but what if you just needed a unique key? Am I missing something here?)

I'm wondering if it is possible to keep the datasource that I have set up and just use it for straight SQL access to the DB without having to use domain objects?

Something I've seen was setting the domain object as transient. But I don't know if you could still do something like that without an id field. Anybody know how that works?

Any ideas?

Thanks, Jon

score 3 · Answer 1 · answered Dec 09 '10 at 04:45

There's no requirement that a primary key be long, it's just the standard for Hibernate and Grails. You can treat a varchar column that's unique as the primary key with a domain class like this:

class Person {

   String username
   String firstName
   String lastName

   static mapping = {
      id name: 'username', generator: 'assigned'
      version false
   }
}

This works for a table defined by this DDL:

create table person (
   username varchar(255) not null,
   first_name varchar(255) not null,
   last_name varchar(255) not null,
   primary key (username)
);

I added 'version false' since it's a legacy system and you probably don't have a 'version' optimistic locking column.

score 3 · Accepted Answer · answered Dec 09 '10 at 07:02

3

You can access the database quite easily, we are doing the same in certain cases for performance reasons:

class SomeService {
    def dataSource;

    def nativeAccessMethod = {
        def sql = new Sql(dataSource);
        def rows = sql.rows("select * from myTable");
        /* processing continues ...*/
    }
}

Groovy's native SQL support is also nice.

answered Dec 09 '10 at 07:02

Gregor Petrin

2,891
1
20
24

This worked for the most part. The dataSource was comming back null so I had to use def dataSource = AH.application.mainContext.dataSource to get the dataSource object. – jonsinfinity Dec 13 '10 at 18:05
And what is the import for Sql class? – user2427 Jan 26 '18 at 19:31
The import is groovy.sql.Sql – Gregor Petrin Jan 27 '18 at 05:22

Accessing DB Without Domain Classes With Grails

2 Answers2