Given the following tables with no relation between them.
TableA (
a_key1, a_col2, a_col3, a_col4 )
TableB (
b_key1, a_key1, b_col3, b_col4 )
mapping files:
TableA.xml
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="TableA">
<id name="a_key1" column="a_key1"/>
<property name="a_col2" column="a_col2"/>
<property name="a_col3" column="a_col3"/>
<property name="a_col4" column="a_col4"/>
<set name="bSet"
inverse="true" lazy="true"
cascade="all-delete-orphan">
<key>
<column name="akey_1"/>
</key>
<one-to-many class="TableB"/>
</bag>
</class>
</hibernate-mapping>
Code:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
<class name="TableB">
<composite-id>
<key-property name="b_key1" column="b_key1"/>
<key-property name="a_key1" column="a_key1"/>
</composite-id>
<property name="b_col2" column="b_col2"/>
<property name="b_col3" column="b_col3"/>
<property name="b_col4" column="b_col4"/>
<many-to-one name="aObject" class="TableA" column="a_key1" insert="false" update="false" not-null="true"/>
</class>
</hibernate-mapping>
The Java POJO classes are as expected with TableB implementing Serializable interface as required by <composite-id> feature.
And the result I would like to have from a similar sql query as shown below
Code:
select distinct( a.a_key1 ), b.key1
from TableA a, TableB b
where b.b_key1 = 'Some_Value'
and b.a_key1 in ( select a2.a_key1 from TableA a2 );
is achieved by using the following Criteria code
Code:
Criteria c = session.createCriteria( TableB.class ).setFetchMode( "aObject", FetchMode.JOIN )
.createAlias( "aObject", "a_alias" );
c = c.add( Restrictions.eq( "b_key1", "Some_Value" ) );
c = c.add( Restrictions.eqProperty( "this.a_key1", "a_alias.a_key1" ) );
I am still not sure if the approach is correct, but atleast I have the result.
For information sake, the generated sql query from hibernate logs is as follows
Code:
select a.a_key1, a.a_col2, a.a_col3, a.a_col4
b.b_key1, b.a_key1, b.b_col3, b.b_col4
from TableB b
inner join TableA a on b.a_key1 = a.a_key1
where b.b_key1 = 'Some_Value'
and b.a_key1 = a.a_key1
---------------------
However, when I want to get the result from an sql query as like below
Code:
select distinct( a.a_key1 ), b.key1
from TableA a, TableB b
where b.b_key1 = 'Some_Value'
and b.a_key1 not in ( select a2.a_key1 from TableA a2 );
the following Criteria query is not giving me expected result
Code:
Criteria c = session.createCriteria( TableB.class ).setFetchMode( "aObject", FetchMode.JOIN )
.createAlias( "aObject", "a_alias" );
c = c.add( Restrictions.eq( "b_key1", "Some_Value" ) );
c = c.add( Restrictions.neProperty( "this.a_key1", "a_alias.a_key1" ) );
And the generates SQL query from the hibernate logs is as below
Code:
select a.a_key1, a.a_col2, a.a_col3, a.a_col4
b.b_key1, b.a_key1, b.b_col3, b.b_col4
from TableB b
inner join TableA a on b.a_key1 = a.a_key1
where b.b_key1 = 'Some_Value'
and b.a_key1 <> a.a_key1
Can anyone give me the solution from Criteria approach instead of HQL or SQL approach.
Thanks in advance and appreciate it much as it helps a lot.
Login
FAQ
Search
