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.

ChssPly76 · Accepted Answer · 2009-10-24 18:49:31Z

4

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.

answered Oct 24, 2009 at 18:49

ChssPly76

101k25 gold badges210 silver badges196 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Awi Over a year ago

Hi ChssPly76,thanks for your pointers.I've managed to get the query working. I'm not an SQL expert but my client is obsessed with "performance", I tried to convince him to let that for a later stage of optimization (if there's really a problem...). I'm trying to facilitate web search with criteria.Sorry for the sloppy query I'll try next time to submit better formed examples. I was able to get the query I wanted ,implementing my own ResultTransformer I was able to return a list of Dog instances(I could not use Hibernate's one because it does not support nested properties setter).

Christian Lischnig · Accepted Answer · 2018-09-06 12:54:38Z

0

I didn't tried it yet by myself, but I think you can also use another constructor in your Entity (Pojo) and pass the columns there. See https://www.thoughts-on-java.org/hibernate-best-practices/ chapter "1.2 Pojo" for a detailed instruction. Altough for me it's not yet clear if this also works for ManyToOne relationships too. I will have a try.

answered Sep 6, 2018 at 12:54

Christian Lischnig

3953 silver badges10 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related