9 Replies Latest reply on Jul 15, 2005 8:24 AM by Steve Rose

    Foreign keys form Composite key

    Steve Rose Newbie

      I have a Entity bean and the composite key for that bean is made up of two foreign keys. I tried using the @IdClass annotation and created a PK class with the getter and setter methods but jboss doesnt like this. Any ideas?

        • 1. Re: Foreign keys form Composite key
          Emmanuel Bernard Master

          you're quite vague but I think that to solve your problem you should need to:
          - map the properties as plain columns in your IdClass properties
          - add an extra @ManyToOne @JoinColumn(isertable=false, updatable=false) that bind to the same column

          • 2. Re: Foreign keys form Composite key
            Colin Goudie Newbie

            Sorry I can't help as I'm wondering much the same thing.

            I have a few tables where the foreign keys in that table are a subset of the composite primary key.

            I.e.
            Invoice Table
            PK: invoiceId

            Order Table
            PK: invoiceId, orderId

            OrderLineTable
            PK: invoiceId, orderId, lineId

            I'm trying to specifiy a bidirectional OnetoMany relationship from Invoice<->Order (Which I think is ok)
            Order<->OrderLine

            The problem I think comes when in my OrderBean I have a
            @OneToMany(cascade=CascadeType.ALL, fetch=FetchType.EAGER, mappedBy="????")
            relationship that returns a collection of OrderLineBean's. The problem is the mappedBy parameter? The spec says its a field, but what field is it when the join is actually on the invoiceId, orderId subset of the OrderLineTable's primary key??

            Thanks

            • 3. Re: Foreign keys form Composite key
              Steve Rose Newbie

               

              "epbernard" wrote:
              you're quite vague but I think that to solve your problem you should need to:
              - map the properties as plain columns in your IdClass properties
              - add an extra @ManyToOne @JoinColumn(isertable=false, updatable=false) that bind to the same column


              I apologise for being so vague but I am happy to say your recommendation worked. The only problem is , it created 2 new columns in my table. Is there anyway to prevent this?

              • 4. Re: Foreign keys form Composite key
                Steve Rose Newbie

                Ok, i will try to be more specific. I have 3 tables Role, Function and RoleFunction.
                Roles pk = role_name
                Functions pk = function_name
                RoleFunctions composite pk = fk_rolename + fk_functionname

                Role function has a PK class which has getter and setter methods for fk_rolename and fk_rolefunction.

                @ManyToOne
                @JoinColumn(name = "ROLE_NAME")
                public Role getRoleName() {
                return this.roleName;
                }

                @ManyToOne
                @JoinColumn(name = "FUNCTION_NAME")
                public Function getFunctionName() {
                return this.functionName;
                }

                I use these methods to enforce the constraints. I also did as you suggested and added two more plain columns to match those in my primary key class.

                @ManyToOne
                @JoinColumn(name="ROLE_NAME",updateable=false,insertable=false)
                public java.lang.String getfkRoleName() {
                return fkRoleName;
                }

                @ManyToOne
                @JoinColumn(name= "FUNCTION_NAME",updateable=false,insertable=false)
                public java.lang.String getfkFunctionName() {
                return fkFunctionName;
                }

                my PK class has the getters and setters for fkFunctionName and fkRoleName. The problem is when i insert a new row, it gets inserted but as soon as I deploy my ear file , 2 new columns are added namely fkFunctionName -- varchar (255) and fkRoleName -- varchar (255). If i remove these columns I start getting errors. Am i supposed to map the columns in my PK class also? or what am I doing incorrectly?

                • 5. Re: Foreign keys form Composite key
                  Steve Rose Newbie

                  I figured it out and it seems to be a bug in hibernate. Apparently when you have a composite key class , if the columns defined in the pkclass do not
                  match the variable names, hibernate tries to create a new column in
                  the table which matches what the variable is called and ignores the
                  @column binding. I got it to work by naming my variables with the same
                  name as the columns.

                  • 6. Re: Foreign keys form Composite key
                    Steve Rose Newbie

                     

                    "srose" wrote:
                    I figured it out and it seems to be a bug in hibernate. Apparently when you have a composite key class , if the columns defined in the pkclass do not
                    match the variable names, hibernate tries to create a new column in
                    the table which matches what the variable is called and ignores the
                    @column binding. I got it to work by naming my variables with the same
                    name as the columns.


                    Should read:
                    Apparently when you have a composite key class , if the columns defined in the table do not
                    match the variable names in th PK class, hibernate tries to create a new column in
                    the table which matches the variable name and ignores the
                    @column binding. I got it to work by naming my variables with the same
                    name as the columns.

                    • 7. Re: Foreign keys form Composite key
                      Emmanuel Bernard Master

                      Hum, this is wrong, I've just tested it.

                      @Embeddable
                      public class ParentPk implements Serializable {
                      
                       @Column(name="p_lname")
                       public String getLastName() {
                       return lastName;
                       }
                      


                      is working as expected.

                      • 8. Re: Foreign keys form Composite key
                        Steve Rose Newbie

                        What version of the application server are you using? I am using 4.0.3 beta 1 and i am connecting to an oracle database. My code looks exactly like yours but in my case, it would create a column named lastname in my table unless i renamed my variable to p_lastname. I was going to post a jira bug but until someone else can replicate this i am not sure if its a bug or just my setup.

                        • 9. Re: Foreign keys form Composite key
                          Steve Rose Newbie

                           

                          "epbernard" wrote:
                          Hum, this is wrong, I've just tested it.

                          @Embeddable
                          public class ParentPk implements Serializable {
                          
                           @Column(name="p_lname")
                           public String getLastName() {
                           return lastName;
                           }
                          


                          is working as expected.


                          I take that back. I tried putting my same code that wasnt working on another machine and it works. I guess the machine i was testing on was caching some old code I had written. So it is not a bug. Thanks for all the help and you guys keep up the good work.