I am getting an information overload and cannot find the answer to this question:

I would like to use a criteria with restrictions on the columns of the bags the persistent class has collections of. Is this possible? If so, how can this be done?

Explanation:
A persistent class A has a primary key (A.getId()) and a collections of the persistent class B.
The persistent class B has a collection of the persistent class C.
The persistent class C has a property telling if it is deleted (C.isDeleted())

This is what I would like to achieve:
I would like to get the instance of A where A.getId() equals the primary key I am looking for.
I want to join the collections of B and C.
I do not want to receive any instances where C.isDeleted() == true

Anyone?

Criteria criteria = session().createCriteria(A.class);

criteria.add(Restrictions.eq("id", [your value]);

criteria.createAlias([name of the field of your collection of B in the A class], "colB");
criteria.createAlias("colB." + [name of the field of your collection of C in the B class], "colC");

criteria.add(Restrictions.eq("colC.deleted", Boolean.FALSE);

I am sorry... I cannot get this to work. I will give you my needs as it is a bit more complicated than my example...

I have a Customer. This customer has a collection of the entity Contract and a collection of CustomerData (more information on the customer). Each contract as a collection of the data for the contract (ContractData). The contract data in turn has a collection of the Entity Product which it represent.

What I want:
I want the customer for who I have the primary key. Then I want to eager fetch the customer data and I want to join the contracts on this customer. I also want to join the contract data on each contract (if the property historicDate is null). I finally want to join the products on the contract data if the property isDeleted is null or false.

This is my code which do not work :(


This criteria returns null and no customer.... Anyone?

Hi,

Can you post your required SQL along with your mapping files ?

Thanks
-Vishal

required sql? you want the generated sql?

generated sql:


I do not use mapping files, but annotations:


In other criteria I write, the restrictions on the collections do not restrict the result returned. Is there some "magic" I need to specify on the session factory or something?

this is very frustrating... please help...

try building your query step by step.

remove all criteria. do you get a result?
ad one, and check the result.

after done the above... can you tell where it goes wrong?

I have the following criteria:


which generates the following sql:


It generates an outer join on the Contract.customer relation.

How does the where clause restrict the result when there is one join and one outer join? The customer relationship is not specified in the criteria...

I get a result from this criteria, but the Contract.contractData.size should be one, but is two. the CustomerData.historicDate criteria is not regarded.

... it seems that the restrictions are disregarded when I am using Contract.getContractData()

... then a new sql is generated with no restrictions.

... so when I call getContractData(), this sql is generated:


all joins and restrictions are disregarded... I am now very frustrated...

The following HQL is doing what I try to achieve by criteria:


If I remove the fetch statement from the hql, it will behave as the criteria does. The javadoc of the FetchMode.JOIN says that it will do the same as an eager fetch.

Am I missing something?

I am stuck with the problem already mentioned and want to attack the problem from a different angle.

Can you take a relationship (customer.getContract().getContractData()) and refresh this dependency using some form of criteria?

A small part is resolved using hql (contract.getContractData()), but when I try writing the hql on the full object graph (customer.getContract().getContractData()), then hibernate returns an error saying it cannot fetch multiple bags.

So I hope I can fetch the entity which I am looking for and then refresh the dependencies with some sort of criteria. Is there any way I can achieve this?

It seems that I have misunderstood what Criteria is supposed to do.

With Criteria you can set restrictions on the result you search for, but it do not enforce those restrictions on the model which is returned by the criteria search. With hql one can restrict the search result.