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

Question

I'm trying to build a smaller SQL, to avoid the "select * from A" that is being build by default for hibernate Criteria.

If I use simple fields (no relation), through "Transformers", I have can manage to have 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 to have this:

select description, weight, owner.name from Dog

I tried this with 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 should I use Projections, to get a smaller SQL, less columns ? Thanks in advance.

Answer 1

First of all,

select description, weight, owner.name from Dog

is not valid SQL. It would have to be something like

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

instead. Secondly, why? While it's possible to do what you want (see below), it's extremely verbose to do so via Criteria API and you gain nothing to show for it. Savings on data transfer for a couple of columns are negligible unless said columns are huge blobs or you're selecting hundreds of thousands of records. In either case there are better ways to deal with this issue.

Anywho, to do what you want for criteria, you need to join linked table (Person) via alias and specify projection on main criteria using said 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"))
 );

There's a description and an example of the above in Criteria Projections documentation. Keep in mind that, when executed, the above criteria would return a list of object arrays. You'll need to specify a ResultTransformer in order to have results converted into actual objects.