Hibernate version: 2.1.4

Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:

Name and version of the database you are using:DB2/NT 8.1.2

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:




Hi all,


My question is general so not providing you with mapping files or java code. I have an Entity which is having a set of children (bi-directional one-to-many association) and I need to write an HQL which returns set of parent filtering by some attributes of any (one atleast one child having all attrs) of its children. Is it possible in Hibernate 2.1.4 or in any version?

I can be acheived in SQL but we have an existing framework which uses only HQL and that to with JDBC style params only..so guys please help me in this regard.

1..*
say entity relation is Parent ----> Child [attr1, attr2..]

and my HQL may look as below...

Filter by one child attr....

SELECT
p
FROM
com.abc.Parent p
WHERE
p.parentId like 'searchCriteria%' AND
p.anyoneofthechild.attr1 = ?



Filter by more than one child attr...

SELECT
p
FROM
com.abc.Parent p
WHERE
p.parentId like 'searchCriteria%' AND
p.anyoneofthechild.attr1 = ? AND
p.anyoneofthechild.attr2 = ?


all attributes should match to any one of the same child...or can match more than one

Thank you all..



Regards,
Surya.

what about
select c.parent
from child c
where c.xxx
and c.yyy

Thanks alot for your Thunder response!!!
Seems simply great!
let me try in my use case..

Regards,
Surya

Hi Anthony,

Your HQL is working fine but returning duplicate records of parent as relation is many-to-one from child,
so I applied distinct over parent selection then it works fine.

but the query results should be paginated as it could be big. so when I applied "Query.setMaxResults" on the query generated SQL distincts over row number and again I got duplicates!
Is there any way to avoid duplicates?!



HQL

select distinct c.parent
from child c
where c.xxx
and c.yyy


apply qry.setMaxResults on above HQL

Generated SQL will be as below

select * from ( select distinct rownumber() over() as row_, parent1_.ID as ID from DB2ADMIN.Parent parent1_,
DB2ADMIN.Child child1_ ....) as temp_ where row_ <= ?




Thanks & Regards,
Surya