Tuesday, May 22, 2012

Keeping tidy table and columns in Oracle and Grails

Keeping tidy table and columns in Oracle and Grails...

Found I keep having to match your existing oracle schema (It's not case sensitive, but they create all of their tables and columns in all caps with underscored separating words). This doesn't jive with Grails defaults, where it makes tables and columns in lower case seperated by underscores. For example, of I have a domain object PizzaToppings, grails makes it 'pizza_toppings'.

You can fix it like this in your domain class:

static mapping = {
     table name: 'PIZZA_TOPPINGS'

But that gets very tedious, especially if you have to do the same for columns. So why not make it automatically do it for you?

So to fix this you can implement or extend the hibernate 'DefaultNamingStrategy' class:

class MyNamingStrategy extends DefaultNamingStrategy {
    String classToTableName(String className) {

        return covertFromCamelCase(super.classToTableName(className))

    String covertFromCamelCase(String input) {
        GrailsNameUtils.getNaturalName(input).replaceAll("\\s", "_").toUpperCase();

Now add this to your DataSource.groovy:

hibernate {
    naming_strategy = com.mycompany.database.oracle.MyNamingStrategy


Somehow I remember Oracle being better than it was 15 years ago(10g). Now I find it's obtuse error codes and 31 character column/table name limits highly annoying and antiquated. I think I prefer MySQL 5+ now - and the price, just perfect. I'm guessing 80%+ of people running Oracle don't even need it these days, on small to medium installations. And installing on Ubuntu 64bit current versions, Oracle is just a huge mess. The only give you an RPM for 11g, and for 10g just a 32bit deb file - come on Oracle, what are you doing. Please don't ruin Java like this, or maybe it's too late.

1 comment:

DB said...

I didn't follow what problem you encountered. I never have to translate for Oracle - GORM takes care of that automatically. Perhaps you need to use a different dialect?
dialect = 'org.hibernate.dialect.Oracle10gDialect'