puts Blog.new(”nonsense”)

Podcast Discusses Legacy DB Support in Grails »

Hoisting Grails to Your Legacy DB

Posted by Jason Rudolph on June 20th, 2006

Step 3 - Mastering Your Domain

Recall that Grails favors convention over configuration. Well, the Grails convention gives precedence to your domain model over the database schema. In a greenfield application, we could start with no tables at all and just define our domain classes in Grails. Grails would then generate the database tables for us based on our domain model.Since we're working with a legacy database, we don't fit quite as nicely into the standard Grails convention. So this is where some configuration comes into play.

  1. If you're application is still running, hit Ctrl-C in the Command Prompt window to stop your application.
  2. Now let's generate the domain class that will correspond to our employees table. From within your application directory (C:\grailsapps\grailstutorial), type grails create-domain-class and hit Enter.
  3. When prompted for the name of your domain class, type Employee and hit Enter.

    4. 200606182158

  4. Grails generated the following class for us, but we need to tweak it to work with our legacy database structure.

    5. class Employee {
        @Property Long id
        @Property Long version
        String toString() { "${this.class.name} : $id" }
    }

    It's important to note that the next two steps (steps 6 and 7) are only required because the employees table doesn't conform to
    the expected convention. Even though these steps are fairly quick to get through, know that if our table followed the standard convention, Grails would automatically infer all the information that we'll explicitly state in these next two steps.
  5. Update the class to match the code shown below.
    1. Note that we changed the data type of the id attribute from Long to String. Recall from earlier that the standard Grails convention expects the unique identifier for your domain objects to be a database-generated numeric value (i.e. a surrogate key). However, our legacy database uses a primary key that has actual business meaning (i.e. the employee's textual user ID).
    2. Note also that we removed the version attribute. Grails also prefers that your table have a column for capturing the version number for each object. (This preference is actually a product of Hibernate, which provides the underlying persistence framework for Grails.) Our legacy table does not have this attribute, so we must remove it from our model.

      3. class Employee {
          @Property String id
          @Property String firstName
          @Property String lastName
          @Property Date startDate
          String toString() { "${this.class.name} : $id" }
      }

  6. There's just one more bit of configuration we have to face in order to get Grails to cooperate with our database model.

    7. Because we deviate from the default convention, we need to tell Grails how to map the database columns to our domain model (and vice versa).

    1. Save this file as hibernate.cfg.xml and place it in the hibernate folder (C:\grailsapps\grailstutorial\hibernate) of your application. Most of its parameters mimic the default values you'd get with Grails out-of-the-box. The important entry to note is the mapping entry. This particular mapping entry tells Grails (well, Hibernate) that it needs to look at our custom mapping configuration in Employee.hbm.xml.
    2. Save this file as Employee.hbm.xml and place it in the hibernate folder (C:\grailsapps\grailstutorial\hibernate) of your application. Again, most of its parameters mimic the default values that Grails provides out-of-the-box.  Lets have a closer look at this file.

      3. The Grails convention expects our primary key to be named “id”. If our primary key was named “id”., then none of this XML configuration would be necessary.

      Since our actual primary key is named “employeeid”, we need to explicitly map the database columns to our domain model.

      Also note that since our primary key has business meaning (i.e. it's not an ID generated by the database), we need to inform Grails that the application logic will bear responsibility for assigning the value to the id column. We do so with the generator tag (<generator class="assigned"/>).

  7. We've got our domain model taken care of, and we need to tell Grails to generate its UI scaffolding. From within your application directory (C:\grailsapps\grailstutorial), type grails generate-all and hit Enter.
  8. When prompted for the name of your domain class, type Employee and hit Enter.

    9. 200606182218

  9. Grails has now created the UI components to provide all CRUD (i.e. Create, Read, Update, and Delete) functionality for the employees table. It's time to test again. From within your application directory (C:\grailsapps\grailstutorial), type grails run-app and hit Enter. You should once again see the application start successfully.
  10. Open a browser and navigate to http://localhost:8080/grailstutorial/.

    11. 200606182220

  11. Grails conveniently displays all controllers associated with the application. (Of course you'd replace this page with your own starting page before deploying your production application, but this feature is nevertheless great for getting started.) Click on the link to the EmployeeController.
  12. We're first presented with a list of all employees. (Try not to get too hung up on the look-and-feel of the application at this point. What's important to notice is just how quickly we've built to a functioning application. This look-and-feel can easily be and is frankly intended to be replaced/modified to fit your needs. If you need to sit down with a client to quickly prototype an application, this scaffolding can ease that process greatly. Best of all, you can then grow your prototype into your real application…instead of having to throw your prototype away and start the real application from scratch.)

    13. 200606182222

  13. For starters, let's click on one of the Show links to see the details for a particular employee.

    14. 200606182225

  14. From here, let's edit this entry.

    15. 200606182226

  15. So here's at least one thing we'll want to customize. Our Start Date field also allows the user to edit the time (i.e. hour and minute), but we really only want to capture the date. Since the startDate member in the Employee class is of type java.util.Date (which of course contains a full timestamp down to the millisecond), Grails has know no way of knowing that we only care about the year, month, and day. So, let's make a note that this is something we'll want to tweak.

  16. To complete our test of the edit functionality, let's change this employee's first name from ‘Jon' to ‘Bob' and click Update.

    17. 200606182228

  17. Now let's try out the delete functionality. Go ahead and click Delete.

    18. 200606182230

  18. Well, that was easy. Perhaps a bit too easy, don't you think? We should probably ask the user first before actually deleting good ol' Bob Smith. Let's make a note to come back here and add a simple ‘Are You Sure' prompt before deleting an employee.
  19. We have one bit of CRUD left to try out. Click on New Employee and let's add back our buddy Bob.

    20. 200606182231

  20. Fill in Bob's data and click Create.

  21. Whoa! That's not what we wanted. What happened? Did you notice that we weren't asked to specify an employee ID for the new employee. When we were configuring the Hibernate mapping files, we mentioned that the application logic (not the database) will bear responsibility for assigning a unique ID to each employee. The Grails convention prefers that the unique ID be generated behind the scenes, but since our ID has business meaning (perhaps as a user's LAN ID), we need to allow the user to explicitly specify the employee ID.

    22. Because our database design deviates from the standard convention, we need to make a slight adjustment to the UI that Grails generated for adding a new employee. Open up the create.gsp file in your editor of choice and add a field for the employee ID. You'll find this file in C:\grailsapps\grailstutorial\grails-app\views\employee\create.gsp. 

    Here's the content we need to add, and the file should look like this when you're done.

      <tr class='prop'>
        <td valign='top' style='text-align:left;' width='20%'>
          <label for='id'>ID:</label>        
        </td>
        <td valign='top' style='text-align:left;' width='80%'
          class='${hasErrors(bean:employee,field:'id','errors')}'>
          <input type='text' name='id' value='${employee?.id}' />
        </td>
      </tr>
  22. Save the changes to create.gsp, and let's try to add an employee again.
  23. Point your browser to http://localhost:8080/grailstutorial/employee/create. We're now prompted for an employee ID as well.

    24. 200606182239

  24. Fill in Bob's data and click Create.

    25. 200606182241

  25. Success!

Summary

So, let's review what we had to do over and above the standard Grails steps in order to get our non-conforming schema to work with Grails. We had to…

  1. Remove the default attributes from our domain class (since our table did not include a database-generated id column or a version column).
  2. Add Hibernate mapping files to map the attributes of our Employee class to the columns of our employees table.
  3. Modify create.gsp to allow the user to specify the employee ID when adding a new employee.

Next Steps

We can now manage our employees table, but we still need to add support for the computers table. And since there's a relationship between employees and computers, we'll want to make sure our UI can manage that relationship as well. We'll add that functionality in a future session. In the mean time, there were a few things we noted that we wanted to clean up. Shall we consider this an extra credit assignment?

  1. We can make the delete functionality a bit more friendly by providing a simple “Are You Sure” prompt to the user before deleting an employee.
  2. In the UI for creating and editing an employee, the Start Date field includes input for the hour and minute. We only want the year, month, and day.

Pretty straightforward, right?  For even more usability enhancements, we should also…

  1. Add some validation to make sure we have data for all required fields, and that the data is of the appropriate type and size to be safely persisted to the database. (For example, we need to make sure that an employee ID does not exceed the maximum length allowed by the employeeid column.)
  2. Add some simple confirmation messages after we successfully create, update, or delete an employee. (Hint: if you know how this is accomplished in Ruby on Rails, then you're almost done with this enhancement already.)

Useful Links

Pages: 1 2 3

Print This Post Print This Post

This entry was posted on Tuesday, June 20th, 2006 at 10:58 pm and is tagged with with , , . 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”

  1. 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

  2. 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. […]

  3. ben Says:
    July 3rd, 2006 at 3:05 pm

    thank you very much. It’s excactly what I was looking for!

  4. zjg_robin Says:
    July 21st, 2006 at 8:27 am

    very useful for me, I am eager for your next step!!

  5. 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

  6. 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

  7. 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

  8. 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!

  9. 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.

  10. 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

  11. 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

  12. 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

  13. 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

  14. 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

  15. 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

  16. 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

  17. 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

  18. 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

  19. 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

  20. 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

  21. 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 »