4 Replies Latest reply on May 8, 2007 4:38 PM by swjackson

    Map multiple rows to one object?

    texan

      I'm using EJB3 persistence under JBoss 5.0.5. (But I think this is a hibernate question)

      With the table structure below, I want to create a UserPreference class with a relationship to User and Preference, and a collection of String values. Note that (user_id,preference_id) is not unique in the value table, as users can have multiple values for some preferences.

      I want the User to have a collection of UserPreference objects, one for each preference_id, rather than one for each unique record in the user_pref_value table.

      That is, I'd like to map the UserPreference class to the user_pref_value table where user_id and preference_id are unique and it contains a String collection of values.

      Any ideas?

      Tables:

      user
      id (pk)

      preference
      id (pk)

      user_pref_value
      user_id (pk)
      preference_id (pk)
      value (pk)

        • 1. Re: Map multiple rows to one object?
          texan

          To clarify, I want the primary key of the UserPreference class to effectively be distinct(user_id,preference_id).

          • 2. Re: Map multiple rows to one object?
            swjackson

            If I am understanding your question you need to have the relationship in your User POJO that returns the collection.

            in your setter method add the annotation for @JoinTable to enforce the sql relationship you want

            you also may have to implement Comparable to enforce the collection you want on UserPreference.

            Another approach that may be more effective is to use a NamedQuery

            • 3. Re: Map multiple rows to one object?
              texan

              The problem I have is that, in the table, the combination of user_id and preference_id are not unique. I haven't found any way to have distinct(user_id,preference_id) as the primary key while also rolling up the multiple rows into a collection based on the value column.

              Frankly, I don't think Hibernate can support this, unless there's some sort of custom PK mechanism that could be provided.

              The simple (but ugly) solution is to create a User_Preference table with just (user_id,preference_id) columns. Then I can map the UserPreference object to that and do the collection mapping for the values.

              I was just trying to avoid having to create an extra table that adds no value.

              • 4. Re: Map multiple rows to one object?
                swjackson

                You can create a custom ID by using (Assuming you are using ejb3)
                @Embeddable and @EmbeddedId

                if you are not using ejb3 (assume you are using xdoclets)
                create a new class that has your columns you want to be apart of the id
                only add the xdoclets tags to the getter methods


                Then add that class as the id