Table lookup by composite key spanning multiple tables

ASavitsky · **Joined:** Mon Aug 15, 2005 2:00 pm **Posts:** 19

We have a legacy DB that our new application (Hibernate based, for now - if I get that thing to work) references for some data. Now, the data model is as follows:

Code:

-- The first two are from our app
CREATE TABLE a (id NUMBER PRIMARY KEY, foo VARCHAR2(6))
CREATE TABLE b (id NUMBER PRIMARY KEY, a_id NUMBER NOT NULL REFERENCES a(id), bar VARCHAR2(10))

-- and this is the legacy lookup table
CREATE TABLE c (foo VARCHAR2(6), bar VARCHAR2(10),
data1 number, data2 VARCHAR2(20), data3 DATE,
PRIMARY KEY(foo, bar))

As you can see, the data is being retrieved from C based on composite key, consisting of FOO (from table A) and BAR (from table B) - it makes perfect sense from the business standpoint, even though it looks horrid. Most of the queries go against table B, but with parent record A always being initialized (many-to-one lazy="false"). Ideally, I'd like to have data1, data2 and data3 to be either simple properties of class B, or a composite class C inside B. However, as much as I've tried, I can't even picture how this mapping would look like... any ideas?

kgignatyev · **Posted:** Wed Oct 26, 2005 7:00 pm

Ternary association
http://www.hibernate.org/hib_docs/v3/re ... ns-ternary
Collection of composite elements
http://www.hibernate.org/hib_docs/v3/re ... s-ofvalues

ASavitsky · **Joined:** Mon Aug 15, 2005 2:00 pm **Posts:** 19

Okay, thanks for pointing me in the right direction - I'm about halfway there now. That piece works:

Code:

<class name="Detention" table="DETENTION" lazy="true">
... skipped for brewity ...
   <property name="mbol" column="MBOL" length="25" not-null="true" />
   <map name="edi" table="EDI_SHIPMENT_TIMES_MV" lazy="true">
      <key column="MBOL" property-ref="mbol" />
      <map-key type="string" column="SCAC" />
      <composite-element class="DateBlock">
         <property name="appointmentDate" column="DELIVERY_STOP_APPT_TIME" />
         <property name="arrivalDate" column="DELIVERY_STOP_ARRIVE_TIME" />
         <property name="departureDate" column="DELIVERY_STOP_DEPART_TIME" />
      </composite-element>
   </map>
</class>
Using my previous example, 
B = DETENTION
C = EDI_SHIPMENT_TIMES_MV
foo = SCAC
bar = MBOL
dataN elements = appointmentDate, arrivalDate, departureDate

This retrieves a map of C records, with foo's as keys, using SQL like that:

Code:

select edi0_.mbol as mbol0_, edi0_.DELIVERY_STOP_APPT_TIME as DELIVERY2_0_, edi0_.DELIVERY_STOP_ARRIVE_TIME as DELIVERY3_0_, edi0_.DELIVERY_STOP_DEPART_TIME as DELIVERY4_0_, edi0_.scac as scac0_ from tstest.EDI_SHIPMENT_TIMES_MV edi0_ where edi0_.mbol=?

Not bad already, but it doesn't take advantage of the EDI_SHIPMENT_TIMES' unique key (SCAC + MBOL). Now, when I've tried to link it to SCAC, too, here's what I've tried:

Code:

<class name="Detention" table="DETENTION" lazy="true">
... skipped for brewity ...
   <property name="mbol" column="MBOL" length="25" not-null="true" />
   <map name="edi" table="EDI_SHIPMENT_TIMES_MV" lazy="true">
      <key column="MBOL" property-ref="mbol" />
      <map-key-many-to-many column="SCAC" class="DetentionInvoice" />
      <composite-element class="DateBlock">
         <property name="appointmentDate" column="DELIVERY_STOP_APPT_TIME" />
         <property name="arrivalDate" column="DELIVERY_STOP_ARRIVE_TIME" />
         <property name="departureDate" column="DELIVERY_STOP_DEPART_TIME" />
      </composite-element>
   </map>
</class>
<class name="DetentionInvoice" table="DETENTION_INVOICE" lazy="true">
... skipped for brewity ...
   <property name="scac" column="SCAC" length="6" />
</class>

and here's what I've got:

Code:

uncategorized SQLException for SQL [select edi0_.mbol as mbol0_, edi0_.DELIVERY_STOP_APPT_TIME as DELIVERY2_0_, edi0_.DELIVERY_STOP_ARRIVE_TIME as DELIVERY3_0_, edi0_.DELIVERY_STOP_DEPART_TIME as DELIVERY4_0_, edi0_.
scac as scac0_ from tstest.EDI_SHIPMENT_TIMES_MV edi0_ where edi0_.mbol=?]; SQL state [null]; error code [17059]; Fail to convert to internal representation;
   at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
   at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
   at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
   at oracle.jdbc.driver.OracleStatement.getIntValue(OracleStatement.java:4479)
   at oracle.jdbc.driver.OracleResultSetImpl.getInt(OracleResultSetImpl.java:536)
   at oracle.jdbc.driver.OracleResultSet.getInt(OracleResultSet.java:1528)
   at org.hibernate.type.IntegerType.get(IntegerType.java:26)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:77)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:68)
   at org.hibernate.type.ManyToOneType.hydrate(ManyToOneType.java:84)
   at org.hibernate.type.EntityType.nullSafeGet(EntityType.java:207)
   at org.hibernate.persister.collection.AbstractCollectionPersister.readIndex(AbstractCollectionPersister.java:616)
   at org.hibernate.collection.PersistentMap.readFrom(PersistentMap.java:197)
   at org.hibernate.loader.Loader.readCollectionElement(Loader.java:674)
   at org.hibernate.loader.Loader.readCollectionElements(Loader.java:370)
   at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:314)
   at org.hibernate.loader.Loader.doQuery(Loader.java:412)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
   at org.hibernate.loader.Loader.loadCollection(Loader.java:1434)
   at org.hibernate.loader.collection.CollectionLoader.initialize(CollectionLoader.java:99)
   at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(AbstractCollectionPersister.java:488)
   at org.hibernate.event.def.DefaultInitializeCollectionEventListener.onInitializeCollection(DefaultInitializeCollectionEventListener.java:60
)
   at org.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:1430)
   at org.hibernate.collection.AbstractPersistentCollection.forceInitialization(AbstractPersistentCollection.java:280)
   at org.hibernate.Hibernate.initialize(Hibernate.java:293)

I assume it's because it's trying to link to DETENTION_INVOICE's primary key and not to SCAC column (since it's trying to retrieve an Integer, and the primary id is the only Integer in my mapping) - however, I don't see how this could be specified in the mapping - any chance you (or someone else) know the answer?

Thanks,

Alex

kgignatyev · **Posted:** Thu Oct 27, 2005 11:12 am

It looks like H/JDBC need help to convert property type to SQL type, something similar to the issue:
http://www.thearcmind.com/confluence/di ... esentation