Criteria query with projections not fetching one to many collection

Question

So I have 2 hibernate pojos as below

class Owner{

Integer id;
String name;
Integer age;
String address;

/* 
  Many more fields here
*/

Set<Cat> cats;

}

class Cat{
Owner owner; //referenced from Owner.id
String color;
}

I am querying Owner table using criteria as below -

Criteria criteria = session.createCriteria(Owner.class);
criteria.createAlias("cats", "cats");

ProjectionList projList = Projections.projectionList();
projList.add(Projections.property("id"), "id");
projList.add(Projections.property("name"), "name");
projList.add(Projections.property("cats"), "cats");

criteria.setProjection(projList);
criteria.setResultTransformer(Transformers.aliasToBean(Owner.class));
List<Owner> owners = (List<Owner>)criteria.list();

I need list of owners with their respective cats populated.

If I do not add projections ( which is equivalent to select * ) I am getting the cats populated for each owner. But select * would be very expensive as owner table has more than 60 columns with foreign key relation to many other tables as well. I want to use projections to select only the required columns so that the query executes faster. And If I add projections and/or alias, I get the owners but without cats (cats for each owner is null).

I have searched a lot for a solution to this, tried all ways to create an alias and projections in criteria, and I have tried even using custom ResultTransformers. None of those seem to work when I am using projections.

Anyone else faced similar issues ? Any ideas?

Answer 1

The question is not very clear. You say that if you do not add projections you will get the cats populated for each owner. But then in the projection list you add cats as a projected field, so basically you want the cats in the result, right? And if so the only reason to use projections is to not include the age and address of the Owner?

As a suggestion please try to think what is the SQL that you to obtain from the query? You want something like this:

select id, name from owner;

Or something like this:

select o.id, o.namer, c.color from owner o join cat c on o.id = c.owner_id; 

I think is important to thing first what do you want to obtain and then how to do it in Hibernate.

EDIT: Ok, I think I understand what do you want to accomplish now. But I don't think you will be able to do it like you want - to extract directly the Owner entity with cats also populated. That is because by default by applying the projections the result will not be a list of Owner entities, but a list of Object[].

For example let's say you want the owner id, the owner name and the cat color. Your projections will look like this:

projList.add(Projections.property("id"), "id");
projList.add(Projections.property("name"), "name");
projList.add(Projections.property("cats.color"), "catsColor");

and the sql generated will be similar with the one put above with cat join.

Then you can apply a result transformer. This are the methods from interface:

public Object transformTuple(Object[] tuple, String[] aliases);
public List transformList(List collection);

Now the first one will be called for each object in the result list, if you want to return map each row to an object, but if you want you can process the whole list and combine the results. Maybe you would like that because for example you will have an OwnerDto that has a list of cat colors or a list of catDtos and you can transform the result in this tree-like structure. Beware that because of the join your result will look like this (let's say you have 2 owners, each one with a cat):

tuple: [1, 'Owner1', 'white'] - aliases ['id', 'name', 'catsColor']
tuple: [1, 'Owner1', 'black'] - aliases ['id', 'name', 'catsColor']
tuple: [2, 'Owner2', 'yellow'] - aliases ['id', 'name', 'catsColor']
.. etc

That is why you would like the result transformer that combines this raw list to something more appropriate.

The idea is that projections are very useful to get only the data you need and for performance gains but you must now that the results returned will not be entities - you must prepare them and use like they are. It makes much more sense to transform the projected data in DTO's and use them like such because that is why you use the projections in the first place - to not retrieve the whole entities, right?

Let's hope this settles the problem, if you need more advice on this please leave a comment.