Using grails on an existing database

By Confusion on donderdag 12 februari 2009 22:12 - Comments (3)
Categories: Java, Software engineering, Views: 19.406

I've encountered the following problem several times: I needed to inspect or alter data in a database, but there is no interface to this data. Todays problem was that I needed to edit some html in a SQL server database, but the so called 'Enterprise Manager' did not display these fields, complaining about the 'grid buffer size'. Of course it turned out to be impossible to find out how to increase that value and using the Query Manager to edit large text fields isn't very convenient either.

Now I could write some custom Java to perform the specific actions that were required, but I would be hard pressed to provide full CRUD capabilities within a reasonable period of time and I certainly wouldn't have a GUI. After searching a bit for frameworks to generate CRUD with editing controls for an existing database, I decided to give grails, a Ruby on Rails type framework based on the JVM language Groovy, a go.

Now firstly, it is important to realize that such frameworks are usually bent on specifying a domain model, which is then used to generate the database. When used with an existing database the generator code will often try to modify your database. In case of grails, by adding an 'id' and 'version' column. If you generate the skeleton for a grails application (see the quick start, there will be a database configuration file in grails-app/conf/DataSource.groovy. When you modify this file to add your configuration, be sure to comment the lines saying
dbCreate = "update" // or "create" or ...

The order of operation is as follows: create domain objects, generate default views and controllers and modify them appropriately. There is a tool called GRAG that can help you to reverse engineer your domain objects from your database and although it produced some odd results in my case, it will certainly help you on your way. A domain object may look like this:
class Email {
    static mapping = {
         table 'emails'
         id column:'email_id'
         version false
    String content

The 'mapping' element is required because I'm not following the defaults: if the table was called 'email', the id column was called 'id' and the table would have a 'version' column, it would not have been necessary.

After having created domain objects, you need to run
grails generate-all Email

to generate a controller and a view. Follow with
grails run-app

and you've got a CRUD application running for this table and its two fields.

Of course, you'll often need a bit more than this, but for instance changing the input element for the html field to a large enough textarea and adding some functionality to properly (un)escape html was easy.

Volgende: Spring, Hibernate and leaky abstractions 02-'09 Spring, Hibernate and leaky abstractions
Volgende: Behulpzaam of bemoeizuchtig? 02-'09 Behulpzaam of bemoeizuchtig?


By Tweakers user mOrPhie, vrijdag 13 februari 2009 00:30

There are other (more practical) ways. At least, if you're not a Java Programmer. You can create linked tables in Microsoft Access to SQL Server. Now you can make a form that uses the table and add a multi-row text field linked to the column you wanted to edit. Run the form and your ready to edit the field.

This, of course, only works if you have Access installed. When you're a Java developer running linux, your solution is probably faster.

By Sonny Wu, vrijdag 8 mei 2009 10:04

Hi, came across your post and found it matches what I am doing... If you know the following question please email the answer to me. BTW, I am on my day 2 with Grails and Groovy.

In my case, there is a database view I'd like to be for my main domain class. The problem is it contains no less than 600000 records in an product environment. If I followed most of the online tutorials, the generated controller will try to return all the record at once, am I right? If yes how can I limit the number of rows by specifying a query in the grails? ie. How and where.

Typically I use SQL query to extract a subset of data this way:

select * from resolutions where product_version in ('1','2') and status in ('fixed','open')

thanks in advance!

By Dennis Carroll, woensdag 8 juli 2009 21:25

def dataSource
def index = {
Sql sql = new groovy.sql.Sql(dataSource)
query = 'SELECT distinct route_name FROM test_section where route_name is not null order by route_name'
sql.eachRow(query, { row ->
routes << row.route_name
} // end index

def list = {
// read .1% of the rows
params.max = MaterialSample.list().size() / 1000
[ materialSampleInstanceList: MaterialSample.list( params ) ]

Comments are closed