12 Replies Latest reply on Apr 5, 2007 2:10 PM by fhh

    Need some help with mapping

    mikedougherty

      OK, I would appreciate a little help with a mapping issue I am running into. I have what I will call a Type table. This table looks something like this:

      +------------+--------------+
      | Field | Type |
      +------------+--------------+
      | TABLE_NAME | varchar(25) |
      | ITEM_ID | varchar(10) |
      | ITEM_FIELD | varchar(25) |
      | ITEM_VALUE | varchar(255) |
      +------------+--------------+
      


      The TABLE_NAME, ITEM_ID, and ITEM_FIELD columns make up the primary key.

      I have this table mapped to a Type object, with a TypePK @IdClass. This works fine.

      However, what I need to do is map certain rows in this table to a field in another object. For example, if my STORE table looks like:

      +------------+--------------+
      | Field | Type |
      +------------+--------------+
      | STORE_NAME | varchar(255) |
      | STORE_ID | varchar(10) |
      | STORE_TYPE_ID | varchar(25) |
      +------------+--------------+
      


      The STORE_TYPE_ID value needs to be mapped to the ITEM_ID field in the TYPE table where TABLE_NAME = "STORE" and an ITEM_FIELD = "STORE_TYPE".

      I've tried subclassing Type (with @Inheritance, and @DiscriminatorValue annotations) but that does not seem to be giving me the desired result.

      @Entity
      @Inheritance(strategy = InheritanceType.SINGLE_TABLE)
      @DiscriminatorColumn(name="LIST_FIELD", discriminatorType = DiscriminatorType.STRING)
      @DiscriminatorValue("STORE_TYPE")
      public class StoreType extends Type {
      }
      


       entityManager.createQuery("select storeType from StoreType storeType");
      


      Generates the following error:

      Caused by: java.sql.SQLException: ORA-00942: table or view does not exist
      


      Any ideas how I might be able to annotate these objects in order to get the result I am looking for?

      @Entity
      @Table(name = "STORE")
      public class Store implements Serializable {
       @Id
       @Column(name = "STORE_ID")
       public Long getId() {
       return id;
       }
      
       public void setId(Long id) {
       this.id = id;
       }
      
       @OneToOne(mappedBy="id")
       public StoreType getType() {
       return type;
       }
      
       public void setType(StoreType type) {
       this.type = type;
       }
      
       @Column(name = "STORE_NAME")
       public String getName() {
       return name;
       }
      
       public void setName(String name) {
       this.name = name;
       }
      }
      


      Thanks.

        • 1. Re: Need some help with mapping

          Well, I think your ERM is broken. I think you will need three classes:

          -Item, to represent the information about the item. This contains an @EmbeddedId property of the ItemPK.

          -ItemPK, the primary key of the Item class. Should be annotateed @Embeddable. (BTW: Why do you store a TABLE_NAME. Does this refer to a table in the db? This sounds fishy.). Make sure you implement equals and hashcode-methods.

          - Create a Store Class which holds a Collection of Items (Set) as one of its properties and use @ManyToMany with an adequat @JoinTable.

          I cannot see why a store should extend an item. Do they share any properties except the pk of the item?

          Regards

          Felix

          • 2. Re: Need some help with mapping
            mikedougherty

            Thanks for your response Felix. But I'm not sure you are understanding the problem. I am not mapping Items in a store. I am mapping attributes *of* a store. If you look in the example code I posted, there is a Type of store. What is not in the post are other attributes like, Status, Region, etc. The possible values of these "types" is stored in the TYPE_TABLE.

            Think of them as enumerated types, but rather than being hard coded in a class file, the types are kept in a database.

            Also, note that I am mapping to a legacy system, so I have no control over the data model.

            So to answer your questions:

            * Yes, the TABLE_NAME is the name of the table that my Store object is mapped to. The only thing I am using it for at present is in the TypePK object to ensure uniqueness.

            * Store does not extend Item. What I had was a StoreType extending Type. Not because I thought I needed a StoreType, but because it seemed the only way to map the Type object to the appropriate sub elements in the Type table.

            * Also, I'm not sure @EmbeddedId and @Embeddable will work in this case. But feel free to correct me if I am wrong. The STORE table only has storage for one field in the PK (ITEM_ID). So it can not store the entire PK object.

            Thanks.

            • 3. Re: Need some help with mapping

               


              But I'm not sure you are understanding the problem.


              I perfectly agree: I don't understand your problem.

              That's why I said I think your ERM is broken. Your ERM should have some explanatory structure to tell people what you are doing.

              If you have just stores and no items (Why is there an ITEM_ID then?) in your ERM then just map everthing to a plain simple table.

              I'm not saying that there might not be perfectly good reasons to distribute things over different tables - especially if you do have to support some legacy application. But unless you describe what exactly it is you are trying to achieve, everthing is just guessing...

              Regards

              Felix

              • 4. Re: Need some help with mapping
                mikedougherty

                OK. I thought my original post was pretty clear, but apparently not. So for the purposes of this post, forget that there was ever mention of ITEM_ anything.


                TYPE_TABLE:

                +------------+--------------+
                | Field | Type |
                +------------+--------------+
                | TABLE_NAME | varchar(25) |
                | TYPE_ID | varchar(10) |
                | TYPE_FIELD | varchar(25) |
                | TYPE_VALUE | varchar(255) |
                +------------+--------------+
                



                STORE:
                +---------------+--------------+
                | Field | Type |
                +---------------+--------------+
                | STORE_NAME | varchar(255) |
                | STORE_ID | varchar(10) |
                | STORE_TYPE_ID | varchar(25) |
                | STATUS_ID | varchar(25) |
                +------------+-----------------+
                



                The "STORE_TYPE_ID" value in the STORE table relates to a value in the TYPE_ID column of the TYPE_TABLE where TABLE_NAME is equal to "STORE" and TYPE_FIELD is equal to "STORE_TYPE".

                The "STATUS_ID" value in the STORE table relates to a value in the TYPE_ID column of the TYPE_TABLE where TABLE_NAME is equal to "STORE" and TYPE_FIELD is equal to "STORE_STATUS".

                Anyone have any thoughts on how to annotate a Store object to map the correct values? I can't seem to find anything like this in the examples, or via Google.

                Thanks.

                • 5. Re: Need some help with mapping

                   


                  Anyone have any thoughts on how to annotate a Store object to map the correct values?


                  Sorry, I really don't wnat to annoy you: Nobody will be able to help you because you have not described your problem.

                  The point of an object relational mapping is to map the relationship of java objects to the tables in a relational database. There a dozens of ways how to "map" the values to the tables the way you describe. And most of them will not be what you want.

                  There are basically three types in which two java objects might be related:

                  Class A may


                  * hold a reference to an object of class B

                  * hold a collection with references to objects of class B

                  * extend class B.

                  The first one is either 1:1 or n:1 (in SQL terminology) and is represented as either a foreign key relationship or by shared primary keys. The second is an 1:n or m:n relationship and is represented as a foreign key or via a join table. The last one uses either a union, shared keys or everthing is dumped into a single table.

                  So the question boils down to this: In which of the three relationships do Store and Type stand to each other?

                  Regards

                  Felix

                  • 6. Re: Need some help with mapping
                    mikedougherty

                    You are not annoying me. I apparently do not have the correct vocabulary to describe my situation adequately enough. So let me try again...

                    Store has a one-to-one relationship with Type. However, simply using the following annotation is not adequate:

                     @OneToOne
                     @JoinColumn(name = "STORE_TYPE", referencedColumnName = "TYPE_ID")
                     public Type getStoreType() {
                     return storeType;
                     }
                    


                    Because it produces the following error:

                     State: FAILED
                     Reason: org.hibernate.AnnotationException: Column name TYPE_FIELD of com.foo.entities.Type not found in JoinColumns.referencedColumnName
                    



                    • 7. Re: Need some help with mapping

                      Okay. I think I'm starting to grasp what you want.

                      You have a store and each store is of a certain type? So you have stores of the type "department store", "hardware store" etc.

                      I guess there are fewer types of stores than stores. If that is so you do not have a @OneToOne relationship but @ManyToOne: Many stores belong to one store type. In SQL terminology this would be a 1:n relationship between types and stores.

                      If that is the case you can simply do this (leaving aside your table_name problem for a moment):

                      public class Store {
                       private Type storeType;
                       @ManyToOne
                       @JoinColumn(name = "STORE_TYPE_ID")
                       public Type getStoreType() {
                       return storeType;
                       }
                      //
                      }
                      


                      If you set up the Type class correctly the persistence layer will automatically determine that the join column must refer to TYPE.TYPE_ID and add a constraint to enforce this foreign key relationship if necessary.

                      Just to make this complete: I would strongly recommend that you drop your requirement of having the table_name in your type table. (I assume this is not really a table_name for use in dynamic sql but a string value to identify the "type of type" :-)) If you want this it would require you to use composite keys which is IMHO painful.

                      But THEORETICALLY you could do it like this: You create a class TypePK for the primary key of Type with a field for TYPE_ID (Integer) and a String for TABLE_NAME and annotate the class with @Embeddable, then you replace the type_id and table_name properties with a single property of the TypePk class and annotate it with @EmbeddedId.
                      Then you change your @ManyToOne relationship to something like:

                      @ManyTo
                      @JoinColumns({
                      @JoinColumn(name="STORE_TYPE_ID", referencedColumnName="TYPE_ID"),
                      @JoinColumn(name="TABLE_NAME", referencedColumnName="TABLE_NAME")
                      })

                      Finally adjust the setter of your Store to prevent setting types that do not have "STORE" as the table_name property of the type's typePK.

                      Sounds complicated? It is - and in my opinion not worth the effort.

                      Regards

                      Felix

                      P.S.: BTW: If the relationship between store and type is really 1:1 then you could solve this more elegantly by using the inheritance strategy joined.


                      • 8. Re: Need some help with mapping

                        Not sure if I made myself clear:

                        You CAN have a column in the database table_name which tells you that this type is a type that usually refers to stores. There are just two limitations:

                        1.) Nothing will prevent you from assigning a type that has not "Store" in the table_name column to the a store.

                        2.) The type_id must be uniquie over the whole table (and not just the tuple of type_id and table_name).

                        Regards

                        Felix

                        • 9. Re: Need some help with mapping
                          mikedougherty

                          Yeah, you're right it is a ManyToOne relationship. I was thinking a Store has one (and only one) "type" Type associated with it at any given time. Completely spaced looking at it from the Type's perspective.

                          I think using a @ManyToOne annotation will give me what I need. After playing around with @PostLoad and @PrePersist in another part of the app. I think I can use this to ensure that the right Type's are being set in the right fields.

                          BTW, yes, I do agree having a "table_name" column is a bit much, and in the case of this app not used at all (except as part of the PK). In fact, it would have likely been quicker and easier to just use a enum. But alas the tables are what they are, and I do what I am told (most of the time anyway).

                          Thanks again for letting me work through this with you.

                          • 10. Re: Need some help with mapping

                            The key question is: Is the table name really part of the primary key?

                            Because if TYPE.TYPE_ID is already (or can be made) unique it would be bad design to use the combination of table_name and type_id as primary key because this would violate the requirements of the 3rd NF. TABLE_NAME would be fully dependend on TYPE_ID and therefore it does not have to be saved in the store table again.

                            Just mentioning this to give you some arguments so you don't have to do what you are told :-)

                            Regards

                            Felix

                            • 11. Re: Need some help with mapping
                              mikedougherty

                              Yeah, you are right. I think the primary key in this case is breaking a few rules. I think one column in this case would be sufficient, but being an application developer and not a database developer, what do I know. I will make some effort to push back (if even possible). But for now I need to keep trudging ahead.

                              So.... changing the mapping to @ManyToOne did not seem to have the desired effect.

                               State: FAILED
                               Reason: org.hibernate.AnnotationException: A Foreign key refering com.foo.entities.Type from com.foo.entities.Store has the wrong number of column. should be 3
                              


                               @ManyToOne
                               @JoinColumn(name="STORE_TYPE", referencedColumnName = "TYPE_ID")
                               public Type getStoreType() {
                               return storeType;
                               }
                              


                              You had multiple JoinColumns in your earlier post. But the STORE table doesn't have a TABLE_NAME column in it. So how do I map that column of the TypePK class to a static value? I would like to try something like the following, but I don't think this will work the way it is.

                               @ManyToOne
                               @JoinColumn(name="STORE_TYPE", referencedColumnName = "TYPE_ID")
                               @DiscriminatorColumn(name="TABLE_NAME", discriminatorType=STRING,length=20)
                               @DiscriminatorValue("STORE")
                               @DiscriminatorColumn(name="FIELD_NAME", discriminatorType=STRING,length=20)
                               @DiscriminatorValue("STORE_TYPE")
                               public Type getStoreType() {
                               return storeType;
                               }
                              


                              But I believe @DiscriminatorColumn and @DiscriminatorValue are class level annotations, not method level. Is that a correct assumption? If so, is there a way to get the results of those annotations another way?

                              Thanks.

                              • 12. Re: Need some help with mapping

                                Your ERM is broken :-)

                                If the primary key in the type table has three columns, then any foreign key referencing the type table must also have three columns. This is not a requirement of java persistence or hibernate but of any underlying sql database.

                                Ask whoever created this mess to create a foreign key constraint between the two tables. That is the easiest way to convince them they made a mistake.

                                Regards

                                Felix