Hi,
We have used the Hibernate 3.0.5 mapping tag "discriminator" to map multiple hibernate entities to one single database table which
is residing in an Oracle 9.2.0.8.0 database.
Unfortunately, there is now a need to make a query spanning multiple hibernate entities in this table. Every HQL query we tried generated
an SQL which used one instance of this table per hibernate entity type used in the HQL query.
However, performance analysis has shown that this query is only usable when this table is only used once within the native sql query.
At this point there seem to be only two options:
A) use native SQL
B) define a new global, read-only hibernate mapping spanning the whole table
Before delving in any of these options, we'd like to exhaust all native HQL options which is the reason for this post.
Any pointer in the right direction would be greatly appreciated!
The tables in question are defined as:
Code:
CREATE TABLE SG (
OID NUMBER(18) NOT NULL,
VERSION NUMBER(18) NOT NULL,
TYPE VARCHAR2(2),
NV_TEILENUMMER VARCHAR2(255),
NV_VERSION VARCHAR2(255),
ZS_TEILENUMMER VARCHAR2(255),
ZS_VERSION VARCHAR2(255)
) ;
and
Code:
CREATE TABLE ABB_SG (
OID NUMBER(18) NOT NULL,
VERSION NUMBER(18) NOT NULL,
POSITION NUMBER(15),
OR_SG NUMBER(18)
) ;
The hibernate mappings are defined as:
Code:
<hibernate-mapping>
<class name="common.type.Version" table="SG" discriminator-value="SI">
<id name="oid" type="long" column="OID">
<generator class="seqhilo">
<param name="sequence">hi_value</param>
<param name="max_lo">100</param>
</generator>
</id>
<discriminator column="TYPE" type="string" />
<version name="version" type="long" column="VERSION" />
<subclass name="common.type.NvVersion" discriminator-value="NV">
<property name="NvTeilenummer" column="Nv_TEILENUMMER" type="string" />
<property name="NvVersion" column="Nv_VERSION" type="string" />
</subclass>
<subclass name="common.type.ZsVersion" discriminator-value="ZS">
<property name="ZsTeilenummer" column="Zs_TEILENUMMER" type="string" />
<property name="ZsVersion" column="Zs_VERSION" type="string" />
</subclass>
</subclass>
</class>
</hibernate-mapping>
and
Code:
<hibernate-mapping>
<class name="server.AbbSgBO"
table="ABB_SG">
<id name="oid" type="long" column="OID">
<generator class="seqhilo">
<param name="sequence">hi_value</param>
<param name="max_lo">100</param>
</generator>
</id>
<version name="version" type="long" column="VERSION" />
<many-to-one name="Sg" column="OR_SG" unique="true" lazy="true">
</many-to-one>
</class>
</hibernate-mapping>
The HQL Query before SQL transformation:
Code:
SELECT
distinct abb
FROM
AbbSgBO as abb ,
NvVersion as nv ,
ZsVersion as zs
WHERE
(
abb.Sg = nv and
upper(nv.NvTeilenummer) like :nvTeilenummer and
upper(nv.NvVersion) like :nvVersion
)
or
(
abb.Sg = zs and
upper(zs.ZsTeilenummer) like :zsTeilenummer and
upper(zs.ZsVersion) like :zsVersion
)
The problematic, by Hibernate generated native SQL looks like this:
Code:
SELECT
distinct abbsgs_.OID as OID
FROM
ABB_SG abbsgs,
SG nvversion3_,
SG zsversion4_
WHERE
zsversion4_.TYPE='ZS' and
nvversion3_.TYPE='NV' and
(
abbsgs_.OR_SG=nvversion3_.OID and
(upper(nvversion3_.NV_TEILENUMMER)like 'ABC') and
(upper(nvversion3_.NV_VERSION)like '123')
)
or
(
abbsgs_.OR_SG=zsversion4_.OID and
(upper(zsversion4_.ZS_TEILENUMMER)like 'ABC') and
(upper(zsversion4_.ZS_VERSION)like '123')
)
The native SQL query we would like to achieve via HQL looks something like this:
Code:
SELECT
distinct abbsgs_.OID as OID,
FROM
ABB_SG abbsgs,
SG sg_
WHERE
abbsgs_.OR_SG=sg_.OID AND
(UPPER(sg_.NV_TEILENUMMER)LIKE 'ABC') AND
(UPPER(sg_.NV_VERSION)LIKE '123') AND
sg_.TYPE='NV'
OR
abbsgs_.OR_SG=sg_.OID AND
(UPPER(sg_.ZS_TEILENUMMER)LIKE 'ABC') AND
(UPPER(sg_.ZS_VERSION)LIKE '123') AND
sg_.TYPE='ZS')
Only when the table SG is used exactly once in the resultant SQL query, the response time is acceptable given the amount of data in the respective tables.
Thank you for your time!
Login
FAQ
Search