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 1 - Install Grails

Follow these steps to download and install grails. (As of this writing, Grails 0.1 is the current stable release. So we'll stick with 0.1 for now.)

Step 2 - Hello, Grails!

  1. Now that we have Grails installed, let's create a directory for our Grails applications. You can call it whatever you like, and place it wherever you prefer.

    2. 200606182044

  2. From within the directory we just created, let's generate our project structure. Enter grails create-app and hit Enter.
  3. When prompted for an application name, type grailstutorial and hit Enter.

    4. 200606182049

  4. Grails should declare a successful build, and we're ready to configure our database properties. (If you're curious, grails.org offers a good overview of the project structure that Grails uses for its applications.)

    5. 200606182049-1

  5. Grails comes pre-packaged with HSQLDB, but since we're using MySQL, we have a few quick steps to tell Grails how to talk to our database. First, download the Java MySQL driver from http://www.mysql.com/products/connector/j/. I opted for the current production-ready version which, as of this writing, is 3.1.13.
  6. Open the zip file and extract the mysql-connector-java-3.1.13-bin.jar file into the lib directory of your Grails application. (Please note that the exact name of the JAR file may differ based on the version of the driver you downloaded. No worries.)

    7. 200606182051

  7. Now we're ready to tell Grails where to find our database. Open ApplicationDataSource.groovy in your editor of choice and modify it to match the settings below. You'll find this file in C:\grailsapps\grailstutorial\grails-app\conf.

    8. class ApplicationDataSource {
        @Property boolean pooled = true
        //@Property String dbCreate = "create-drop"
        @Property String url = "jdbc:mysql://localhost/grailstutorial"
        @Property String driverClassName = "com.mysql.jdbc.Driver"
        @Property String username = "root"
        @Property String password = "password"
    }

    Note that we commented out the dbCreate property. Valid values for this optional property are "create", "create-drop", or "update". These values all instruct Grails to alter your database schema (in one way or another) at runtime to synchronize it with your domain classes. Since we're dealing with a legacy database here, we'll assume that we're forbidden from modifying the table structure. So, by commenting out this property, we're instructing Grails to leave the schema as is.

  8. Alright, let's test. From within your application directory (C:\grailsapps\grailstutorial), type grails run-app and hit Enter.
  9. The application is now waiting to respond to your requests at http://localhost:8080/grailstutorial/.

    10. 200606271915

    [UPDATED: June 27th, 2006: This screen shot has been corrected to reflect what you should see at this point.  Thanks to Rob Kainz for pointing out the problem with the screen shot formerly shown here.  That screen shot incorrectly included a link to the EmployeeController, which will not be present until we generate the controller in an upcoming step.]

    OK. So, admittedly this isn't particularly exciting. Before we see any real functionality, we first need to create our domain
    model and generate UI components to manage our model.

    We just wanted to make sure everything's working so far. (You didn't want to build the application end-to-end before you tested it, did you?)

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 »