Announcement Announcement Module
Collapse
No announcement yet.
How to Map a Simple Oracle "Legacy" Table into a Grails Domain Class? Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to Map a Simple Oracle "Legacy" Table into a Grails Domain Class?

    Hi, everyone. I am a very experienced java developer, and a somewhat experienced Spring developer, but am new to Grails.

    I'm trying to create a simple one-table one-class grails prototype app that reads from a simple pre-existing Oracle table.

    I can see that the grails app is connecting to the database ok, but when I code the Datasource.groovy as dbCreate="update", it wants to add a new hibernate_sequence to the table, and gets this exception:

    Code:
    C:\opt\grailsWorkspace\myapp>grails run-app
    Welcome to Grails 1.3.7 - http://grails.org/
    Licensed under Apache Standard License 2.0
    Grails home is set to: C:\opt\grails
    
    Base Directory: C:\opt\grailsWorkspace\myapp
    Resolving dependencies...
    Dependencies resolved in 1154ms.
    Running script C:\opt\grails\scripts\RunApp.groovy
    Environment set to development
      [groovyc] Compiling 2 source files to C:\opt\grailsWorkspace\myapp\tar
    get\classes
       [delete] Deleting directory C:\Users\bethridge\.grails\1.3.7\projects\myapp\tomcat
    Running Grails application..
    2013-04-25 14:41:10,410 [main] ERROR hbm2ddl.SchemaUpdate  - Unsuccessful: creat
    e sequence hibernate_sequence
    2013-04-25 14:41:10,410 [main] ERROR hbm2ddl.SchemaUpdate  - ORA-01031: insuffic
    ient privileges
    Thank goodness I didn't have the Oracle privilege to do such. Otherwise, I imagine I would have just messed up a perfectly good Oracle table for our whole team! (The Grails documentation in my Grails book was not very clear on that point.)

    If I change to dbCreate="validate", then I get this exception:
    Code:
    org.springframework.beans.factory.BeanCreationException: Error creating bean wit
    h name 'messageSource': Initialization of bean failed; nested exception is org.s
    pringframework.beans.factory.BeanCreationException: Error creating bean with nam
    e 'transactionManager': Cannot resolve reference to bean 'sessionFactory' while
    setting bean property 'sessionFactory'; nested exception is org.springframework.
    beans.factory.BeanCreationException: Error creating bean with name 'sessionFacto
    ry': Invocation of init method failed; nested exception is org.hibernate.Hiberna
    teException: Missing sequence or table: hibernate_sequence
            at org.grails.tomcat.TomcatServer.start(TomcatServer.groovy:212)
            at grails.web.container.EmbeddableServer$start.call(Unknown Source)
            at _GrailsRun_groovy$_run_closure5_closure12.doCall(_GrailsRun_groovy:15
    8)
            at _GrailsRun_groovy$_run_closure5_closure12.doCall(_GrailsRun_groovy)
            at _GrailsSettings_groovy$_run_closure10.doCall(_GrailsSettings_groovy:2
    80)
            at _GrailsSettings_groovy$_run_closure10.call(_GrailsSettings_groovy)
            at _GrailsRun_groovy$_run_closure5.doCall(_GrailsRun_groovy:149)
            at _GrailsRun_groovy$_run_closure5.call(_GrailsRun_groovy)
            at _GrailsRun_groovy.runInline(_GrailsRun_groovy:116)
            at _GrailsRun_groovy.this$4$runInline(_GrailsRun_groovy)
            at _GrailsRun_groovy$_run_closure1.doCall(_GrailsRun_groovy:59)
            at RunApp$_run_closure1.doCall(RunApp.groovy:33)
            at gant.Gant$_dispatch_closure5.doCall(Gant.groovy:381)
            at gant.Gant$_dispatch_closure7.doCall(Gant.groovy:415)
            at gant.Gant$_dispatch_closure7.doCall(Gant.groovy)
            at gant.Gant.withBuildListeners(Gant.groovy:427)
            at gant.Gant.this$2$withBuildListeners(Gant.groovy)
            at gant.Gant$this$2$withBuildListeners.callCurrent(Unknown Source)
            at gant.Gant.dispatch(Gant.groovy:415)
            at gant.Gant.this$2$dispatch(Gant.groovy)
            at gant.Gant.invokeMethod(Gant.groovy)
            at gant.Gant.executeTargets(Gant.groovy:590)
            at gant.Gant.executeTargets(Gant.groovy:589)
    Caused by: org.springframework.beans.factory.BeanCreationException: Error creati
    ng bean with name 'transactionManager': Cannot resolve reference to bean 'sessio
    nFactory' while setting bean property 'sessionFactory'; nested exception is org.
    springframework.beans.factory.BeanCreationException: Error creating bean with na
    me 'sessionFactory': Invocation of init method failed; nested exception is org.h
    ibernate.HibernateException: Missing sequence or table: hibernate_sequence
            ... 23 more
    Caused by: org.springframework.beans.factory.BeanCreationException: Error creati
    ng bean with name 'sessionFactory': Invocation of init method failed; nested exc
    eption is org.hibernate.HibernateException: Missing sequence or table: hibernate
    _sequence
            ... 23 more
    Caused by: org.hibernate.HibernateException: Missing sequence or table: hibernat
    e_sequence
    ....and the error makes sense, but the bottom line is that this is a PRE-EXISTING and in-production table, so I don't want Grails/Hibernate to create a new hibernate_sequence at all. The table already has a perfectly good primary key. I just want grails/hibernate to map to the existing primary key.

    I've poked around my Grails books, the internet and this forum, but I don't see how to do this yet.

    In general, how am I supposed to configure for a simple legacy database scenario in Grails?

    Ben Ethridge

  • #2
    This is a Hibernate thing, not Grails. Hibernate defaults to using a single sequence for all tables for PK generation since Oracle doesn't support auto-increment PKs. Is this going to be read-only, or do you intend to add new records through the Grails app? If you want to assign the values directory, you can configure that with "id generator: 'assigned'", see http://grails.org/doc/latest/ref/Dat...apping/id.html

    Btw you'll be better off using the User mailing list, this forum doesn't get much traffic - see http://grails.org/Mailing+Lists

    Comment


    • #3
      I just noticed you're using Grails 1.3.7. If this is a new app you should consider using 2.1.x or 2.2.x. It won't affect this issue, but there are many significant benefits to using the more recent releases.

      Comment


      • #4
        Originally posted by burtbeckwith View Post
        Is this going to be read-only, or do you intend to add new records through the Grails app?
        I intend to add new records.

        The DBAs have all gone for the day, but I'm pretty sure they handle it with a trigger or some such. Whenever I add new rows in TOAD or Oracle SQL Developer, it auto-adds an appropriate and auto-incremented primary key. I never have to supply one, so bottom line is that the database is handling the key creation internally. Which hibernate value would I use for that design pattern?

        Thanks for the heads-up about the mailing list. I'll ask there next question.

        Regarding Grails version: I'm not free to move up to that. This is a fairly big shop :-)

        Comment


        • #5
          I confirmed with the DBA just now: an Oracle sequence and a trigger. Now I just need to know which hibernate value applies for that design pattern. Anyone know?

          Comment


          • #6
            @BusinessKey equivalent?

            Also, I looked over some of our hibernate code. Apparently we use @BusinessKey, as in:

            @Entity
            ...
            @Table(name = "CONTACT")
            public class Contact ...{

            ...
            /** The id */
            @BusinessKey
            private Long id;
            /** The email */
            private Email email;
            ...
            }

            So what would be the Grails equivalent syntax?

            Comment


            • #7
              Ah here is more:

              @Column(name = "CONTACT_ID", unique = true, nullable = false, precision = 22, scale = 0)
              @SequenceGenerator(name = "SEQ_GEN", sequenceName = "CONTACT_SEQ", allocationSize = 1)
              @Id
              @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SEQ_GEN")
              public Long getId() {
              return this.id;
              }


              Again, just trying to come up with the Grails equivalent syntax.

              Comment


              • #8
                I think maybe I see it:

                http://grails.org/doc/latest/ref/Dat...apping/id.html

                I will attempt this.

                Comment


                • #9
                  Ok. Looking over the Grails syntax, the Hibernate general syntax and our specific syntax, this is what I come up with:

                  Code:
                  ...
                  static mapping = {
                        tablePerHierarchy false
                        table 'CONTACT'
                        version false
                        id generator: 'sequence',
                          params: [name:'SEQ_GEN', sequenceName:'CONTACT_SEQ', allocationSize:1],
                          generatedValue:[strategy:GenerationType.SEQUENCE, generator:'SEQ_GEN'],
                          column:[name:'CONTACT_ID', unique:true, nullable:false, precision:22, scale:0]
                  ...
                  I don't see an example of the sequence syntax in the Grails documentation link above, so I'm taking a lot of guesses on the proper syntax. Given that the above (in hibernate syntax) compiles fine in our java/hibernate environment, but the Groovy/Grails syntax throws up with this exception in my Grails environment:

                  Code:
                  C:\opt\grailsWorkspace\myapp>grails run-app
                  Welcome to Grails 1.3.7 - http://grails.org/
                  Licensed under Apache Standard License 2.0
                  Grails home is set to: C:\opt\grails
                  
                  Base Directory: C:\opt\grailsWorkspace\myapp
                  Resolving dependencies...
                  Dependencies resolved in 1262ms.
                  Running script C:\opt\grails\scripts\RunApp.groovy
                  Environment set to development
                    [groovyc] Compiling 2 source files to C:\opt\grailsWorkspace\myapp\tar
                  get\classes
                     [delete] Deleting directory C:\Users\bethridge\.grails\1.3.7\projects\myapp\tomcat
                  Running Grails application..
                  2013-04-26 11:31:23,359 [main] ERROR context.GrailsContextLoader  - Error execut
                  
                  ...
                  
                  Caused by: org.codehaus.groovy.grails.exceptions.GrailsDomainException: Error ev
                  aluating ORM mappings block for domain [intervaladmin.Contact]:  No such propert
                  y: GenerationType for class: org.codehaus.groovy.grails.orm.hibernate.cfg.Hibern
                  ateMappingBuilder
                          ... 23 more
                  Caused by: groovy.lang.MissingPropertyException: No such property: GenerationTyp
                  e for class: org.codehaus.groovy.grails.orm.hibernate.cfg.HibernateMappingBuilde
                  r
                          at intervaladmin.Contact$__clinit__closure2.doCall(Contact.groovy:28)
                          at intervaladmin.Contact$__clinit__closure2.doCall(Contact.groovy)
                          ... 23 more
                  C:\opt\grailsWorkspace\IntervalAdmin>
                  ... I'm starting to think that maybe this is more of a Grails syntax or functionality issue than a pure hibernate issue.

                  Is this even close to being correct? If so, what am I missing?

                  Ben

                  Comment


                  • #10
                    Moving to mailing list

                    I'm going to copy all this over to the mailing list now, per burtbeckwith suggestion.

                    Comment


                    • #11
                      I'm not sure if that's correct, but the error is just telling you that you're missing an import for javax.persistence.GenerationType

                      Comment

                      Working...
                      X