Hi.
I need some help on this one. I'm trying to migrate an old Lotus Notes app into some java, and came across a problem. In general, I have this sql which I want to convert to a hbm... Should be a breeze, I thought. But the join tag is not as flexible as I first thought.
This is the sql:
Code:
SELECT s.idnumeric AS id,
S.SAMPLED_DATE AS sampleDate,
S.SAMPLING_POINT AS samplePoint,
sp.DESCRIPTION AS description,
S.CARGO AS cargo,
sp.POINT_LOCATION AS location,
R.NAME AS component,
R.TEXT, AS value,
R.UNITS AS unit,
vc.ORDER_NUMBER AS vcOrderNo,
T.ORDER_NUM AS testOrderNo
FROM SAMPLE S,
TEST T,
RESULT R,
SAMPLE_POINT sp,
VERSIONED_COMPONENT vc
WHERE S.ID_NUMERIC = T.SAMPLE
AND T.TEST_NUMBER = R.TEST_NUMBER
AND S.SAMPLING_POINT = sp.IDENTITY
AND T.ANALYSIS = vc.ANALYSIS
AND R.NAME = vc.NAME
AND R.TEXT <> ' '
and S.STATUS <> 'S'
Since this joins across multiple tables where only SAMPLEPOINT and TEST have references to the SAMPLE class, Hibernate seems have trouble joining correctly.
This is my attempt at a map:
Code:
<hibernate-mapping default-access="field">
<class name="no.gassco.track.domain.sampleManager.Sample" table="SAMPLE">
<id name="id" type="string" column="ID_NUMERIC" length="10" />
<property name="sampleDate" type="date" column="SAMPLED_DATE" length="7" />
<property name="status" type="string" column="STATUS" length="1" />
<property name="cargo" type="string" column="CARGO" length="8" />
<many-to-one name="location" cascade="none" lazy="false" column="LOCATION_ID" />
<many-to-one name="samplePoint" cascade="none" lazy="false" column="SAMPLING_POINT" />
<!-- Associations -->
<join table="TEST">
<key column="SAMPLE" property-ref="id" />
<property name="testId" type="string" column="TEST_NUMBER" length="10" />
<property name="testOrder" type="string" column="ORDER_NUM" length="10" />
<property name="analysis" type="string" column="ANALYSIS" length="10" />
</join>
<join table="RESULT">
<key column="TEST_NUMBER" property-ref="testId" />
<property name="componentName" type="string" column="NAME" length="40" />
<property name="unit" type="string" column="UNITS" length="10" />
<property name="numValue" type="double" column="VALUE" length="126" />
<property name="textValue" type="string" column="TEXT" length="234" />
</join>
<join table="VERSIONED_COMPONENT">
<key column="ANALYSIS" property-ref="analysis" />
<property name="componentOrder" type="string" column="ORDER_NUMBER" length="10" />
</join>
</class>
</hibernate-mapping>
But this map wants every join to use the SAMPLE class' primary key as reference. The resulting where clause is then:
Code:
FROM SAMPLE S,
TEST T,
RESULT R,
SAMPLE_POINT sp,
VERSIONED_COMPONENT vc
WHERE S.ID_NUMERIC = T.SAMPLE
AND S.ID_NUMERIC = R.TEST_NUMBER
AND S.ID_NUMERIC = vc.ANALYSIS
which is completelly wrong.
What can I do to make this map work as the sql statement presented at the top does?
Login
FAQ
Search