通过具有ManyToOne关系的实体上的Hibernate投影，在SQL查询中使用更少的列 [英] Use fewer columns on SQL query through Hibernate Projections on Entity with ManyToOne relation

问题描述

我试图构建一个更小的SQL，以避免为hibernate Criteria默认构建的select * from A。

如果我使用简单的字段（无关），通过变形金刚，我可以设法得到这个SQL：

  select description，weight from狗; 
  

我有这个实体：

  @Entity 
 public class Dog 
 {
 Long id; 
字符串描述; 
双重重量; 
 @ManyToOne（fetch = FetchType.LAZY）
 @JoinColumn（name =person_id，nullable = false）
 Person owner; 
} 
 
 @实体
公共类Person 
 {
 Long id; 
字符串名称; 
双倍身高; 
日期birthDate; 
} 
  

我的目标是拥有这个：

 从Dog 
选择description，weight，owner.name  

我用Criteria（和subcriteria）试了这个：

 条件dogCriteria = sess.createCriteria（Dog.class） ; 
 ProjectionList proList = Projections.projectionList（）; 
 proList.add（Projections.property（description），description）; 
 proList.add（Projections.property（weight），weigth）; 
 dogCriteria.setProjection（proList）; 
 
条件personCriteria = dogCriteria.createCriteria（owner）; 
 ProjectionList ownerProList = Projections.projectionList（）; 
 ownerProList.add（Projections.property（name），description）; 
 dogCriteria.setProjection（ownerProList）; //在这一行之后，调试器显示dogCriteria上的
投影被覆盖
 //并且查询失败，因为name是
 //不是Dog实体的字段。 
  

我应该如何使用投影来获得更小的SQL，更少的列？
在此先感谢。

解决方案

首先，

< pre $ 选择描述，权重，owner.name from Dog


不是有效的SQL。它必须是类似于

 选择描述，权重，Person.name 
从Dog加入Person到Dog。 person_id = Person.id 
  

改为。其次，为什么？虽然可以做你想做的事情（见下文），但通过Criteria API这样做非常冗长，你无法为其展示任何东西。除非所说的列是巨大的斑点或者您正在选择数十万条记录，否则几个列的数据传输节省可以忽略不计。在任何一种情况下，都有更好的方法来处理这个问题。

任何人，为了达到标准的目的，您需要通过别名加入链接表（Person）使用所述别名在 main 条件中指定投影：

  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））
）; 
  

在标准预测文档。请记住，执行时，上述条件将返回对象数组的列表。您需要指定 ResultTransformer 以便将结果转换为实际的对象。

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.

解决方案

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.

这篇关于通过具有ManyToOne关系的实体上的Hibernate投影，在SQL查询中使用更少的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！