I have the following structure:

table1: Network

table2:SpecialNet1 extends Network

table3:SpecialNet2 extends Network

table4:Customer

Custumer has a property called host and it is type of Network

because, as a choice, can be either SpecialNet1 or SpecialNet2.

Both special networks have same property called name.

The problem is, when I try to make a query, using Criteria and Expression classes, I'm not able to search by this property "name".

I tried named queries as well, using casting, but I had always parsing errors.

I found couple of examples in mail-list archive, but nothing helped.

Is there any solution for this?

Thanks in advance

show us these queries, and mapping files and config files (the abridged versions, like the top and bottom stuff and key fields, not every attribute )

Here they are:

As it can be seen, the produced SQL, using named query, is not good, because WHERE clause should look like
where network1_1_.SHORT_NAME like ? or
network1_2_.SHORT_NAME like ?

Although, both outer joins are correct, but the same alias is used in where clause.

The same SQL query is generated using Criteria and Expression.


############ NETWORK table ##################

<class name="Network" table="networks">
<id name="id" type="int" column="id" unsaved-value="null">
<generator class="native"/>
</id>
<property name="name" type="string">
<column name="name" not-null="true" sql-type="varchar(100)" />
</property>
<property name="code" type="string">
<column name="code" not-null="true" sql-type="char(3)" />
</property>
</class>

############ SPEC NET 1 table ##################

<joined-subclass name="SpecialNet1" table="net1" extends="Network">
<key column="id" foreign-key="FK_NET1_ID"/>
<property name="shortName" type="string">
<column name="SHORT_NAME" not-null="true" sql-type="varchar(255)" />
</property>
More properties...
</joined-subclass>

############ SPEC NET 2 table ##################

<joined-subclass name="SpecialNet2" table="net2" extends="Network">
<key column="id" foreign-key="FK_NET2_ID"/>
<property name="shortName"
type="string">
<column name="SHORT_NAME"
not-null="true"
sql-type="varchar(255)" />
</property>
More properties...
</joined-subclass>

############ CUSOTMER table ##################

<class name="Custormer" table="customer">
More properties...
<many-to-one class="Network" name="host"
not-null="false" column="host_id"/>
</class>

######### CustomerDAO class - search method ############

...
Criteria host = criteria.createCriteria("host");
String _shortName = (String) map.get("shortName");

if(_shortName != null) {
host.add(Expression.like("shortName", "%" + _shortName+ "'%"));
}

...

############ Named query ##############

<query name="find.network">
from Customer as c
left join c.host as net1
left join c.host as net2
where net1.shortName like ? or net2.shortName like ?
</query>

### Generated SQL query (from JBOSS console) #######

10:00:08,578 INFO [STDOUT] Hibernate:
select *[bunch of fields] from customer customer0_
left outer join networks network1_
on customer0_.host_id=network1_.id
left outer join net1 network1_1_
on network1_.id=network1_1_.id
left outer join net2 network1_2_
on network1_.id=network1_2_.id

where network1_1_.SHORT_NAME like ? or
network1_1_.SHORT_NAME like ?