Use fewer columns in SQL query through Hibernate Projections on Entity with ManyToOne

Question

I am trying to create a smaller SQL to avoid the "select * from A" that is created by default for hibernate Criteria.

If I use simple fields (without relation) through Transformers, I can control this SQL:

select description, weight from Dog; 

Hi, I have this Entity:

 @Entity public class Dog { Long id; String description; Double weight; @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "person_id", nullable = false) Person owner; } @Entity public class Person { Long id; String name; Double height; Date birthDate; } 

My goal is:

 select description, weight, owner.name from Dog 

I tried this with criteria (and subcriteria):

 Criteria dogCriteria = sess.createCriteria(Dog.class); ProjectionList proList = Projections.projectionList(); proList.add(Projections.property("description"), description); proList.add(Projections.property("weight"), weigth); dogCriteria.setProjection(proList); Criteria personCriteria = dogCriteria.createCriteria("owner"); ProjectionList ownerProList = Projections.projectionList(); ownerProList.add(Projections.property("name"), description); dogCriteria.setProjection(ownerProList); //After this line, debugger shows that the //projection on dogCriteria gets overriden //and the query fails, because "name" is //not a field of Dog entity. 

How do I use Projections to get less SQL, fewer columns? Thanks in advance.

Answer 1

First of all,

 select description, weight, owner.name from Dog 

invalid SQL. It should be something like

 select description, weight, Person.name from Dog join Person on Dog.person_id = Person.id 

instead of this. Secondly, why? Although you can do what you want (see below), it is very difficult to do this through the criteria API and you will not get anything to display it. The savings in transferring data for a couple of columns are negligible, unless these columns are huge drops or you select hundreds of thousands of records. In any case, there are more effective ways to solve this problem.

Anywho, in order to do what you want for the criteria, you need to link the linked table (Person) using an alias and specify the forecast for the main criteria using the specified alias:

 Criteria criteria = session.createCriteria(Dog.class, "dog") .createAlias("owner", "own") .setProjection( Projections.projectionList() .add(Projections.property("dog.description")) .add(Projections.property("dog.weight")) .add(Projections.property("own.name")) ); 

Here is the description and example above in the Documentation for the Project Criteria . Keep in mind that when executed, the above criteria will return a list of arrays of objects. You will need to specify ResultTransformer so that the results are converted to real objects.