Mapping collection value types all from the same table

mikeottinger · **Joined:** Tue Jun 27, 2006 2:51 pm **Posts:** 14

Hi Guys,

I've been given a large, heavily de-normalized table that details car models, their trims and the packages for their trims. A typical row looks like this:

Code:

modelid|modelname|modelprice|trimid|trimname|trimprice|pkgid|pkgname|pkgprice
001    | gallant | 26000    | 001   | es      | 1500 | 001 | sun&surf | 2500
001    | gallant | 26000    | 001   | es      | 1500 | 002 | home ent | 3500
001    | gallant | 26000    | 002   | gs      | 1200 | 001 | sun&surf | 2500
001    | gallant | 26000    | 002   | es      | 1500 | 001 | home ent | 3500
002    | eclipse | 24500    | 004   | qs      | 1700 | 001 | sun&surf | 2500

You get the point... ;)

I'm trying to establishing collections of value types for each model. A model will have a collection of value types for trims. Trims in turn will then have a collection of value types for packages. And as you can tell, all of this will be from the same table. Is any of this possible? I've read the chapter from Hibernate In Action on Advanced Mapping Concepts, it gives me great information on creating collections for value types where the collection data is in a separate table, but no examples where both the parent entity and its collection of value types are all in the same table. I've read that composite collections cannot contain collections themselves. Is this what I'm attempting to do? Any ideas on how to do this would help out greatly. Or should I go and have a long talk with the dba? ;)

Hibernate version:
Hibernate 3.1

Mapping documents:

Code:

<hibernate-mapping package="com.organic.mitsu.hib">
   <class name="Model" table="mmsaBPCarInfo">
   
      <cache usage="read-write" region="standardCache"/>
      
      <id name="modelId" column="ModelId" type="integer">
         <generator class="native"/>
      </id>
      <property name="modelYear" column="ModelYear" type="string"/>
      <property name="modelType" column="ModelType" type="string"/>
      <property name="modelDestPrice" column="ModelDestPrice" type="float"/>
      <property name="modelDestPriceAK" column="ModelDestPriceAK" type="float"/>
      
      <property name="modelDisplaySeq" column="ModelDispSeq" type="integer"/>
      <property name="modelNameKey" column="ModelNameKey" type="string"/>
      <property name="modelImgKey" column="ModelImgKey" type="string"/>
      <property name="modelDisclaimerKey" column="ModelDisclaimerKey" type="string"/>
      
      <set name="trims" table="mmsaBPCarInfo">
         <key column="ModelId"/>
         <composite-element class="Trim">
            <property name="trimCode" column="TrimCode" type="string"/>
            <property name="trimPrice" column="TrimPrice" type="float"/>
         </composite-element>
      </set>
      
   </class>
</hibernate-mapping>

Name and version of the database you are using:
Sql Server

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

The DBA-talk is highly recommended :)

The first problem that you have to solve, and it's not the most immediately obvious problem, is that every row of a mapped class must have a unique id for hibernate to be able to handle it. In the table you've given, the unique id would be modelid & trimid & pkgid. Which means that the only class you can actually map would be ModelAndTrimAndPkg. Not very useful :)

You could probably write a clever Tuplizer to do this for you, and use custom SQL to load the object, but that's not something I've ever done, so I can't help. You may even be able to present a read-only window over this, using custom SQL to load. But there's no way to make it editable if you do that, because of write locks, etc.

You'll have to convince your DBA that the table you've provided should be a view, and proper, normalized tables should be behind the view.

mikeottinger · **Joined:** Tue Jun 27, 2006 2:51 pm **Posts:** 14

Thanks for the info on this. I just learned this table is the result of a stored procedure that queries a set of normalized tables to come up with this uber-view. I'll have to convince them that this is where I need to point Hibernate. It seems like with the Hibernate tricks you described, I'd just be better off querying it with standard JDBC and working with it that way. Thanks again for the help.