Grails and real databases – a bumpy road

von Silvia Rothen, rothen ecotronics, am 3.2.2012

Nearly since the beginnings I have observed Grails as a web framework and played around with it. I have developed database backed web applications connected to an ERP for the last 15 years and I have seen the technology stack for web applications evolve from CGI over ASP or Struts and JSP to ASP.Net, Spring and JSF, so I think I’m expert on this topic.

The fun

Some parts of Grails I find really awesome. Here are some of the features I really like

Groovy – I find it a really elegant language, it’s fun to work with it (at least most of the time)
Spring: While I was not able to convince my employer of the advantages of Grails, this is not the case for Spring. In the meantime we use it in all our web projects in combination with Oracle’s ADF business components. This combination works really well and has made our work much easier.
Convention over Configuration: I like well organized projects where the directories are always called the same and where you are encouraged to stick to name and other conventions
Some awesome tags and features in GSP: paging, combo boxes, validation are really helpful compared to JSP or ADF Faces
Easy configuration and deployment: Who ever had the “pleasures” to configure logging in an Oracle IAS as an application server for a J2EE application knows what I mean
AJAX: although I have had my bad experiences (who hasn’t?), sometimes it’s really necessary to use AJAX. Grails does a good job there, making it easy.

Real databases

Until now I never could convince my employer or one of our customers to use Grails as a platform for a web project. And I see indeed some weaknesses of Grails when it comes to working with real databases. Some call those legacy databases. But you can as well say that someone who uses the term “legacy database” has until now probably only played around with kindergarden databases. Maybe I have to specify what I mean with a “real database“:

the right to exist of such a database lies beyond any web application, it is for example the base of an ERP. Web applications are just (nice to have) additions to the main purpose
the database model existed long before any web application and it will probably survive most of the current web applications
the database has it’s own name conventions
tables like “article” or “order” have some 100 fields or more
there exist some 10’000 to 100’000 records in tables like “article”
data is not deleted, but flagged inactive
views of the data are highly customized, every customer sees for example a different set of articles with customer specific prices
native SQL is a “must” because of performance
there exist tree structures with self joins and a varying number of levels

But..

When trying to imitate with Grails what I normally do in our web applications with JSP, Spring and ADF Business Components I stumbled over the following weaknesses:

CRUD: Grails sticks too much to the CRUD model (Create, Read, Update, Delete). In my experience web applications are normally about 80% Read, 15% Update, 5% Create and 0% Delete. Imagine your customers editing your article prices! Viewing data is far more important than all the rest. It took several versions of Grails until you were able to make a domain object read-only.
When you have 10’000 records or more and a heavily customized application, filtering your data in the most efficient way is vital. Normally this means using native SQL. Grails is still very clumsy when working with native SQL. If you don’t believe me, just have a look at the chapter about Native SQL mit createSQLQuery in my german grails tutorial. Even if you don’t understand German, you will see how much code it takes to make a pageable list of domain objects. Grails has not yet solved the problem of mapping domain objects to read-only database views.
HQL with executeQuery is no substitute for native SQL, as it lacks support for native Oracle functions and elements like for example DECODE or START WITH

Real-life problems

Let’s show what I mean with a real task as I have solved it in our web shops many times:

1st Problem: Oracle self join

We have for a web shop a catalogue with several ten thousands of records. The catalogue is built as a tree with a self join and the records are fetched with a CONNECT BY. This part can be solved by using the above mentioned createSQLQuery. Feasible, but not really a pleasure.

2nd Problem: Filling a field of a domain object with a stored procedure

Next task: A list of Articles with customer specific prices has to be shown on a web page. I fetch a list of articles (once again native SQL) and then I fetch the customer specific prices for these articles. With ADF Business Components I have a ViewObject for the article with a transient field for the price and a method in the ViewRowImpl object to fetch and set the price for the current article and customer. The stored procedure was not the problem, one way to use it can be found in Stored Procedure einbinden (in German).

I’ve made several attempts to do this in grails:

a) Method in the domain object

First I tried to do it in ADF BC style by just integrating the method in the domain object. But for this I need a CallableStatement and this requires a data source. Unfortunately the dataSource object cannot be injected into a domain object. And also the solution to call the service from the domain obect is not feasible (for good reasons). A domain object can be injected into a service, but not the other way round. I did not find another way to get a data source for my CallableStatement or to solve this problem directly in the domain object.

b) Fill a transient with a method in a service

Second attempt: I made a transient field in my article object. By the way, I waited a long time for transient fields! Then I created a service for the stored procedure, as it is recommended. The method takes the article as an argument, fetches the price and fills it into the transient field of my article object. Ouch! In Grails a transient field is read-only. For the developeres of Grails this seems to be so evident that they not even mention it in the official documentation. If you come from ADF BC a transient field is just a field that is not persisted in the database. There is no reason why it should be read-only. Even with calculated fields a setter is often used for performance reasons. You calculate the field value once, set it and afterwards you fetch the set value. Thus you avoid to make the same calculation over and over again.

c) Service wraps the domain object

The next attempt works. If I cannot place the price field in the article, then let’s create an article service wrapping both, article object and price field. Don’t forget to change the scope, the default is singleton! This solution has several drawbacks:

so far it works only for one article, not for a list of articles
it’s pretty clumsy, as it introduces an object that’s not really necessary (not to speak of all the setters and getters you have to write manually)
from an architectural view point the price belongs to the domain object, it’s not really stringent to place it in a service (the nbusiness logic is in the stored procedure, not in my code)
in the GSP page you work no longer directly with the domain object, but with the artikelService

Just some code snippets to show you what I came up with:

First the Artikel domain class:

class Artikel {

  static final SQL_LIST = """
    -- here comes your native SQL
  """

  static mapping  = {
    table 'ARTIKEL'
    version false
    id column: 'artikel_key'
    //we don't want to write into the database!
    cache 'read-only'
  }

  //here come the article fields
  ...

  static constraints = {
    id (nullable: false, size: 1..10)
  }
}

Now comes the really interesting part, the ArtikelService:

import groovy.sql.Sql
import java.sql.*;
import oracle.jdbc.driver.OracleTypes

class ArtikelService {
  static scope = 'request'

  def dataSource

  Artikel artikel
  Long kundnr
  Double menge

  void setArtikel(Artikel parArtikel) {
    artikel = parArtikel
  }

  Artikel getArtikel() {
    return artikel
  }

  void setKundnr(Long parKundnr) {
    kundnr = parKundnr
  }

  void setMenge(Double parMenge) {
    menge = parMenge
  }

  Double getPreis() {
    Double result = 0.0
    if (artikel && artikel.id) {
      Sql sql = new Sql(dataSource)
      CallableStatement stmt =
         dataSource.connection.prepareCall(
         "BEGIN pack_webshop.get_artikelpreise(?, ?, ?, ?); END;")
      //take care, this is not 0 based!
      stmt.setLong(1, artikel.id)
      stmt.setLong(2, kundnr)
      stmt.setDouble(3, menge)
      stmt.registerOutParameter(4, OracleTypes.NUMBER)
      stmt.execute()
      result = stmt.getDouble(4)
    }
    return result
  }
}

Here is what the controller does with the domain object and the artikelService:

class ArtikelController {
  //inject the service
  ArtikelService artikelService

  def show = {
    def artikelInstance = Artikel.get(params.id)
    if (!artikelInstance) {
      flash.message = "${message(code: 'default.not.found.message', args: [message(code: 'artikel.label', default: 'Artikel'), params.id])}"
      redirect(action: "index")
    } else {
      artikelService.setArtikel(artikelInstance)
      artikelService.setMenge(1.0)
      artikelService.setKundnr(101011)
      [artikelService: artikelService]
    }
  }

And finally just a little snippet how you use this in the GSP page:

<td valign="top">${artikelService.artikel.id}</td>
<td valign="top">
  <g:formatNumber number="${artikelService.preis}" type="currency" currencyCode="CHF" />
</td>

3rd Problem: NumberTable in a stored procedure

Today I stumbled over a new problem: I want to call a stored procedure that takes a number table as an input parameter. The best solution I’ve found until now is the article “Calling a stored procedure from Grails using the Datasources plugin”. Unfortunately this plugin does not work anymore with the version 2.0.0 of Grails, because now the functionality of using several datasources is integrated in the framework itself. But in this article the plugin is used to get a native OracleConnection, which is necessary for an ArrayDescriptor. The tiny part that is missing in my code is how to get a native OracleConnection. I googled and read a lot and I tried every cast I could imagine, but until now I did not solve this problem. These are the situations where you start to hate Groovy’s dynamic typing: You have to know what type you’ve got before you can cast it to something else.

Conclusions

It seems to me that for large web projects based on an ERP Grails has still a long way to go until it can compete with a JSP/Spring/ADF BC technology stack. Unfortunately!