puts Blog.new(”nonsense”)

Hoisting Grails to Your Legacy DB

Posted by Jason Rudolph on June 20th, 2006

[Update 2007-07-21] - To try out a working sample application using a legacy schema with a more recent version of Grails, check out this post from the "Grokking GORM" series.

–

So you've heard about Grails and its promise to bring the power of Ruby on Rails to the Java platform. If you're like me, you stand in pure awe of the innovation and power illustrated by Rails, but know that your organization just isn't going to stray from the safety of Java. So if we can achieve similar productivity with a more Java-centric solution, what's not to like? And the more conservative businesses should find additional comfort knowing that Grails applications play nicely with their existing Java code, because Grails applications are written in Groovy (a dynamically-typed scripting language that also happens to be a Java standard).

Grails is still in its infancy (with version 0.1 being the current stable release), but it's slam-packed with many more features than you might expect given its youth, and I expect it to mature rapidly over the next few months. The development team has established an aggressive road map to continue to progress Grails as a solid and rich web development framework.

That all sounds good, but will it work with your legacy databases? If you're familiar with Rails, you know that its convention over configuration approach (which Grails shares) is best suited to greenfield applications where the database schema can be defined to follow the preferred conventions. Shoehorning a legacy database into such a framework, while it can be done, takes away some of that feeling of elegance you get from working with a purely-conforming schema. That said, I was surprised by how little tweaking is required to wrap Grails around a non-conforming legacy schema. Care to see for yourself? Let's get started…

Step 0 - Dust off that Database

First we'll make sure you have a database server to host that aging schema of yours. While Grails works with most databases, we'll use MySQL for this example. (We can't really call this Step 1, can we? After all, this database has been sitting around gathering dust forever, or at least since before you heard about Grails.)

Grab the current version of the 5.0.x MySQL Windows Installer. (I'm using Windows Essentials(x86) 5.0.22.)
Run the installer, and continue to take all default settings until you're prompted to sign up for a MySQL.com account.

We can safely punt here. Select Skip Sign-Up and click Next.

Click Finish to complete the installation and bring up the configuration wizard.

Click Next until you get to the security section.

Enter the password you'll use to connect to the database, and click Next. (Keep this password handy, because we'll need it again in a moment.)

Continue through the last few screens to complete the configuration and start the MySQL server.

Open the MySQL Command Line Client. (You can find it in Start–>Programs–>MySQL MySQL Server 5.0–>MySQL Command Line Client.)

Paste the contents of this file into your console to create and populate your database.

Now let's check out our data model and the data we have to work with.

Enter the queries shown below to verify that your first table is intact and populated. As you can see, our simple (but non-conforming) legacy database houses a list of employees each uniquely identified by an employee ID.

How does this table deviate from what the Grails convention expects? It's all in a name. First, Grails expects each table to have a single column named "id" that represents the primary key. Secondly, Grails prefers that the primary key have no business meaning, whereas the data in this table's employeeid column most likely does have business meaning. We'll discuss the workaround shortly.

Then enter the queries shown below to verify that your other table is intact and populated. Notice that the database stores a list of computers and the employee to which the computer is assigned (if any). (For some reason, nobody wants the Tandy.)

And what's wrong with this table? Well, it suffers from the same problems discussed for the employees table, and then one more issue. Since this table stores computers, we likely want to define a domain model named Computer represent the data in this table. However, by default Grails tries to map each domain class to a table will the corresponding pluralized name - in this case, "computers", not "computermanagement". We'll handle this customization as well.

Pages: 1 2 3

Print This Post

This entry was posted on Tuesday, June 20th, 2006 at 10:58 pm and is tagged with with Grails, Groovy, Java. You can follow any responses to this entry through the RSS 2.0 feed. Responses are currently closed, but you can trackback from your own site.

21 Responses to “Hoisting Grails to Your Legacy DB”

Graeme Rocher Says:
June 21st, 2006 at 4:37 am
Fantastic article Jason! If you don’t mind I will be adding a link to it on the Grails tutorials page
Groovy Bar & Grill » Hoisting Grails to Your Legacy DB Says:
June 24th, 2006 at 7:32 pm
[…] A nice tutorial explains how to hook up a legacy db to Grails. […]
ben Says:
July 3rd, 2006 at 3:05 pm
thank you very much. It’s excactly what I was looking for!
zjg_robin Says:
July 21st, 2006 at 8:27 am
very useful for me, I am eager for your next step!!
Springett Says:
July 22nd, 2006 at 12:02 pm
Great article, and I’ve found it very useful, as I work with an Oracle designer who creates the database for me.

However, it seems I experience the same issues as you have done when moving away from the example MySql database and use my pre-built Oracle tables instead.

Have you had any feedback on this issue?

KRgds, M
Jason Rudolph Says:
July 22nd, 2006 at 1:13 pm
Thanks for your feedback. I’m glad to hear that you found this article useful.

Regarding the issue with grails generate-all hanging, I’ve been using a temporary workaround. If you first run grails generate-controller, it should generate the necessary files and hang just before outputing the “build successful” message. You’ll need to hit Control-C to terminate the hanging script. Then, run grails generate-views, and the script should generate all of the necessary view files, but it will hang before outputing the “build successful” message. Again, hit Control-C to terminate the hanging script.

These steps should yield the same results as a successful execution of grails generate-all.

If you’d like to vote for this issue, you may be able to influence how soon it gets addressed.

I hope this helps.

Cheers, Jason
springett Says:
July 23rd, 2006 at 3:18 am
Thanks Jason.

I noticed when running grails generate-all that the controller was created but the views were missing, so I was wary of what else was missing. Knowing that only the creation of the views hasn’t been completed, means I can take your guidance and run the grails generate-views after this hanging point. This seems to work fine, so many thanks for your help.

I’ve voted for the fix anyway, so hopefully this will be fixed in time.

KRgds, M
Ali Says:
August 17th, 2006 at 4:06 pm
Awesome tutorial. I was looking all over for a good tutorial on Grails, and this was exactly what I was looking for.

Thanks!
Dennis Says:
November 17th, 2006 at 12:08 am
Hi Jason,

Typo in step 15. “know” should be “no”.

“Grails has know way of knowing that we only care about the year, month, and day.”

Great tutorial. Thanks.
Christop Droste Says:
March 30th, 2007 at 11:01 am
Very useful tutorial. I really managed to connect to a fictious legacy data base in less than half a day.

Unfortunately it requires some revision, since some things seem to have changed when using version 0.4.2 of grails.

Here are the detected changes (perhaps more is necessary): 1.)Step 2.7.: It should be mentioned that in String url = “jdbc:mysql://localhost/grailstutorial” the identifier ‘grailstutorial’ refers to the database name and not to the application name when editing C:grailsappsgrailstutorialgrails-appconfApplicationDataSource.groovy 2.)Step 3.4.: The generated domain class doesn’t require to remove the version attribute, since there is no such attribute. I think it now is hidden behind the scenes as well as the id propterty. And no toString() function. Furthermore no Prefix ‘@Property’ is needed any more. 3.)Instead the id propterty has to be added to get a human editable one. 4.)Step 3.21: Instead of adding a section like that in the tutorial I used one, where was replaced by since this is what I found for the other properties. Nevertheless both might work (I didn’t try). 5.)The process of putting id and version behind the scene is evidently the reason for another Exception I got. Fortunately it points to the crucial source code in saying (for my case):

Message: ids for this class must be manually assigned before calling save(): Name; nested exception is org.hibernate.id.IdentifierGenerationException: ids for this class must be manually assigned before calling save(): Name Caused by: org.springframework.orm.hibernate3.HibernateSystemException: ids for this class must be manually assigned before calling save(): Name; nested exception is org.hibernate.id.IdentifierGenerationException: ids for this class must be manually assigned before calling save(): Name Class: NameController At Line: [72] Code Snippet: 72: if(name.save()) { 73: redirect(action:show,id:name.id)

So I guessed (almost knowing nothing about Groovy apart from a few snippet I read during the last days) that the source code (in create.gsp)
```
def save = {
    def name = new Name()
    name.properties = params
    // missing statement: name.id = params.id
    if(name.save()) {
        redirect(action:show,id:name.id)
    }
    else {
        render(view:'create',model:[name:name])
    }
}
```
didn’t assign the id to the new instance of Name. Thus I included name.id = params.id instead of the (manually added) comment line above.

And it worked!

I am very happy with this solution but other could really link to a legacy data base if the few changes could be included in the tutorial.

Thanks a lot

Christoph
Jason Rudolph Says:
March 31st, 2007 at 10:38 am
@Christoph,

Thanks for your comments. I'd like to post an updated version of this tutorial at some point. As you noticed, this tutorial was written in the days of Grails 0.1, and Grails has progressed greatly since then. Until I'm able to update the tutorial for the current Grails release, I hope folks will take the time to read your excellent comments for applying this tutorial to Grails 0.4.2.

Thanks again,

Jason
Hamdi Says:
April 13th, 2007 at 6:11 am
Hi!

Great article and great ebook! A pity it’s a bit out dated. Grails is really moving fast.

Some questions from a newbie: 1. Is there a way to generate the hbm files from Grails, much like in hibernate? 2. For normal Grails usage, nor using legacy db, is there a way to generate the db schema, also like hibernate?

Sorry if asking here is inappropriate. Not sure where to ask really.

Thanks,

Hamdi
Jason Rudolph Says:
April 14th, 2007 at 8:03 am
@Hamdi,

Great article and great ebook! A pity it’s a bit out dated. Grails is really moving fast.

Thanks for the kind words. Grails is indeed progressing quickly, and this post is admittedly due for an update.

However, as of now, you should still find the e-book to be quite relevant and far from outdated. Be sure to check out the FAQ and the latest source code to pick up any changes between Grails 0.3.1 (the current Grails release when the book was published) and Grails 0.4.2 (the current Grails release as of today).

1. Is there a way to generate the hbm files from Grails, much like in hibernate?

There was some discussion on this topic on the Grails mailing list that you may find helpful.

http://www.nabble.com/Re%3A-Hibernate-mappings%2C-sequences%2C-etc.-p8967184.html

On a similar note, the Grails roadmap includes future support for generating the domain model from the database.

2. For normal Grails usage, nor [sic] using legacy db, is there a way to generate the db schema, also like hibernate?

By default, Grails will indeed generate the database tables for you. See the dbCreate option in grails-app/conf/*DataSource.groovy.

I hope this helps.

Cheers,
Jason
Hamdi Says:
April 15th, 2007 at 9:03 pm
Jason,

Thanks for the fast reply!

My usage scenarios for wanting the db schema itself, instead of Grails generating the db tables directly into the DB are: 1. For me to do any manual changes once, instead of going to the DB everytime (Dev DB, Test DB, Production DB). I prefer to change, tune it, etc, once, and use the db schema for all stages. 2. For me to submit to my DBA.

One last question please. Where can I get details on creating Builders? I am extremely interested in DSL. I am experimenting with builders now but can’t find any details to it. My source for information so far are from the groovy website, books (more on using builders) and grails builders (source code for Hibernate Criterion Builder, etc).

Sorry for soo many questions for a groovy/grails newbie. Thanks again for your help.

Hamdi
Jason Rudolph Says:
April 15th, 2007 at 10:11 pm
@Hamdi,

My usage scenarios for wanting the db schema itself, instead of Grails generating the db tables directly into the DB are: 1. For me to do any manual changes once, instead of going to the DB everytime (Dev DB, Test DB, Production DB). I prefer to change, tune it, etc, once, and use the db schema for all stages. 2. For me to submit to my DBA.

Ah. I think see what you mean now. If you're wanting just to tweak the database schema that Grails creates, you can try the following steps.
1. Let Grails create the tables for you in your dev schema.
2. Go into the database and make your adjustments.
3. Comment out the dbCreate property in all of your grails-app/conf/*DataSource.groovy classes. (By doing so, Grails will no longer try to modify the schema in any way.)
4. Export the DDL for your schema, and you (or your DBA) can use the DDL to create the schemas for your other enviroments.
Where can I get details on creating Builders?

I recommend taking a look at Chapter 8 of Groovy in Action.

I hope this helps.

Cheers,
Jason
Hamdi Says:
April 15th, 2007 at 10:32 pm
Wow!

That was fast. Do you sleep?

I guess grails does not generate the schema by itself, like hibernate, does it? No problem though. Workarounds, like what you mentioned are available.

Yup, Chapter 8 of Groovy in Action is one of my source to create by own builder. Just wondering whether there are more detail information regarding it that you may have. No problem. I’ll continue with my experiment.

Thanks again. Very helpful.

Hamdi
Dennis Carroll Says:
May 3rd, 2007 at 3:44 pm
Jason,

Very nice work. Thankyou. Followed the tutorial with v0.5. Had to add // Next line added for assigned key (i.e ) employee.id = params.id to EmployeeController save().

Does that mean that id is excluded from “employee.properties = params”?

Dennis
Jason Rudolph Says:
May 5th, 2007 at 4:26 pm
@Dennis,
Thanks for the kind feedback.

Does that mean that id is excluded from “employee.properties = params”?

Yes indeed. As of Grails 0.4, the id is excluded from the binding. For more info please see the following thread from the mailing list.
http://www.nabble.com/Re%3A-properties-method-doesn%27t-work-well-with-self-created-map-p9709402.html
Cheers,
Jason
Dennis Carroll Says:
May 5th, 2007 at 7:40 pm
Thanks Jason,

The next thing that is driven me crazy is this: “Cannot get property: id on null object” whenever the id filed is left blank. This appears to be coming from : render(view:’create’,model:[employee:employee])

I tried various things… 1. Putting constraints in the Employee class: static constraints = { id(blank:false) id(matches:/[a-zA-Z]/) } 2. redisplay the view: if(params.id==”") { render(view:’create’) } Finally I just gave up and added: if (employee.id == “”) { redirect(action:create) } that has the effect of blanking out the data already entered but at least I avoid a stack trace. Obviously I am new at this so I’ll sign up on the nabble forum and post a question there.

Regards, Dennis
Horst Krause Says:
May 17th, 2007 at 4:13 pm
Hi Jason,

thanks for the tutorial. It really helped me to get started with a legacy db. But (working with grails 0.5) it still does not work properly for my mysql database. As soon as I use your hibernate.cfg.xml with Person.hbm.xml for my domain class Person (just id, name and surname for testing) I get the following exception:

Server failed to start: org.mortbay.util.MultiException[org.springframework.beans.factory.BeanCreationException: Error c reating bean with name ‘transactionManager’: Cannot resolve reference to bean ’sessionFactory’ while setting bean proper ty ’sessionFactory’; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean wi th name ’sessionFactory’: Invocation of init method failed; nested exception is org.hibernate.MappingException: invalid configuration, org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘transactionManage r’: Cannot resolve reference to bean ’sessionFactory’ while setting bean property ’sessionFactory’; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name ’sessionFactory’: Invocation of i nit method failed; nested exception is org.hibernate.MappingException: invalid configuration, org.springframework.beans. factory.BeanCreationException: Error creating bean with name ‘transactionManager’: Cannot resolve reference to bean ’ses sionFactory’ while setting bean property ’sessionFactory’; nested exception is org.springframework.beans.factory.BeanCre ationException: Error creating bean with name ’sessionFactory’: Invocation of init method failed; nested exception is or g.hibernate.MappingException: invalid configuration]

Do you have any idea, why this happens? It does not seem to have anything to do with my domain class…?

Anyway, the only reason to use the hibernate files is, that I have no version column in my domain class table and so always get an exeption because of the missing column:

Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column ‘this_.version’ in ‘field list’ at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2822) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1536) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1626) at com.mysql.jdbc.Connection.execSQL(Connection.java:3031) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:943) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1049) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186) at org.hibernate.loader.Loader.getResultSet(Loader.java:1778) at org.hibernate.loader.Loader.doQuery(Loader.java:662) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224) at org.hibernate.loader.Loader.doList(Loader.java:2211) … 77 more

So, is there any simple trick to tell grails/GORM/hibernate not to use the “version” column?

Thanks for any help!

Bye, Torsten
Jason Rudolph Says:
May 17th, 2007 at 7:39 pm
@Torsten,
Thanks for your comments. I’m sorry to see that you ran into some issues. As you probably noticed, this tutorial was written in the days of Grails 0.1, and Grails has progressed greatly since then. I’ll be posting an updated version of this tutorial at some point in the near future.

So, is there any simple trick to tell grails/GORM/hibernate not to use the “version” column?

There’s a proposal for this feature in the Grails sandbox, so it’s quite possible that we’ll see something along these lines implemented eventually.

Thanks again,
Jason

Podcast Discusses Legacy DB Support in Grails »

Subscribe

Archives

Meta

Recent Posts

Hoisting Grails to Your Legacy DB

Step 0 - Dust off that Database

21 Responses to “Hoisting Grails to Your Legacy DB”