Use the Hibernate Projections on Entity With Manytoone relationship, use fewer columns on SQL query

select description, weight from Dog;

@Entity
public class Dog
{
 Long id;
 String description;< br /> 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;
}

select description, weight, owner.name from Dog

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.

select description, weight, owner.name from Dog

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

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"))
 );

I am trying to build a smaller SQL to avoid the “select * from A” built for hibernate Criteria by default.

If I use simple fields (no relation), through “Transformers”, I can manage to have This SQL:

select description, weight from Dog;

Hey, I have this entity:

< pre>@Entity
public class Dog
{
Long id;
String description;
Double weight;
@ManyToOne(fetch = FetchType.LAZY)
@J oinColumn(name = “person_id”, nullable = false)
Person owner;
}

@Entity
public class Person
{
Long id;
String name;
Double height;
Date birthDate;
}

My goal is to have:

< /p>

select description, weight, owner.name from Dog

I tried this with Criteria (and sub-criteria):

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 dog Criteria gets overriden
 //and the query fails, because "name" is
 //not a field of Dog entity.

How should I use Projections to get smaller SQL, fewer columns?
Thanks in advance.

First of all,

select description, weight, owner. name from Dog

is invalid SQL. It must be like this

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

Replace. Secondly, why? Although you can do what you want (see below), doing so through the Criteria API is very verbose, and you don’t need to pay any price for it. Unless the column is a huge blob or you have selected hundreds of thousands of records, it’s very tedious. The data transfer savings of the column are negligible. In either case, there is a better way to deal with this problem.

Anyone, in order to make the standard you want, you need to join by alias Link the table (Person) and use the alias to specify the projection of the main criteria:

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 are the above descriptions and examples in the Criteria Projections documentation. Remember, when executed, the above conditions will return a list of object arrays. You need to specify ResultTransformer to convert the result into an actual object.