在Hibernate中向列表添加投影 [英] Adding a Projection to a List in Hibernate

问题描述

我有一个名为Order的@Entity，我有一个名为orderEmails的字段或成员变量，如下所示。

  @Entity 
 @Table（name =order）
 public class Order {
 
 @Id 
 @Column（name =order_int）
私人长ID; 
 
 @OneToMany（cascade = CascadeType.ALL，fetch = FetchType.LAZY）
 @JoinColumn（name =order_int，insertable = false，updatable = false）
 private List< ; OrderEmail> orderEmails; 
  

...
}

我试图在这个订单上创建Projections，这意味着我要从Order实体和OrderEmail实体中选择一些特定的列

但是，当我在orderEmails字段上创建一个投影。我没有收到完整的电子邮件列表。这是我想要的。这里是我尝试的代码

  ProjectionList columnList = Projections.projectionList（）; 
 ... 
 columnList.add（Projections.property（id）。as（id））; 
 ... 
 columnList.add（Projections.property（orderemails.EmailAddress）。as（email））; 
  

请注意，我也尝试了
columnList.add（Projections.property（orderemails ）。如（ 电子邮件））;
并将电子邮件（as）更改为List，但没有帮助

是否有可能在Hibernate中的List上创建投影？

解决方案

我猜hibernate没有提供这样的功能。要做到这一点，您将不得不使用数据库特定的功能，如Oracle的 LISTAGG 或<来自MySQL的一个href =http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat =nofollow> GROUP_CONCAT 。它会将所有电子邮件（字符串）分组到一个列表中，结果如下：

  ORDER_ID电子邮件
 1 nemo @ email，dory @ email，whale @ email 
 2 spongebob @ email，squarepants @ email 
  

<

  public List< Map>< p> ; emailsByOrder（）{
 Criteria c = session.createCriteria（Order.class，order）; 
 
 Criteria critEmail = c.createCriteria（orderEmails，emails）; 
 String listAgg =LISTAGG（{别名} .EMAIL_ADDRESS_COLUMN_NAME，'，'）WITHIN GROUP（ORDER BY {别名} .EMAIL_ADDRESS_COLUMN_NAME ASC）AS EMAILS; 
 critEmail.setProjection（Projections.projectionList（）。add（Projections.groupProperty（order.idOrder）。as（ORDER_ID））
 .add（Projections.sqlProjection（listAgg，new String [ ] {EMAILS}，new Type [] {new StringType（）}）））; 
 c.setResultTransformer（Transformers.ALIAS_TO_ENTITY_MAP）; 
 
 return（List< Map>）c.list（）; 
} 
  

I have a @Entity called Order in this I have a field or a member variable called orderEmails as show below.

@Entity
@Table(name = "order")
public class Order {

@Id
@Column(name = "order_int")
private Long id;

@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
@JoinColumn(name = "order_int", insertable = false, updatable = false)
private List<OrderEmail> orderEmails;

... }

I am trying to create Projections on this Order , meaning I want to select some specific columns from the Order entity and a column from the OrderEmail entity

But, when I create a projection on the orderEmails field. I don't get the complete list of emails. Which is what I want. Here is the code that I am trying

ProjectionList columnList = Projections.projectionList();
...
columnList.add(Projections.property("id").as("id"));
...
columnList.add(Projections.property("orderemails.EmailAddress").as("email"));

Note, I have also tried columnList.add(Projections.property("orderemails").as("email")); and changed the email (in as) to be a List, but didn't help

Is it possible to create a projection on a List in Hibernate ?

解决方案

I guess hibernate does not provide such a function. To do this you will have to use database specific functions like LISTAGG from Oracle or GROUP_CONCAT from MySQL. It will group all emails (the String) into one colum, so the result would be:

ORDER_ID     EMAILS
1            nemo@email, dory@email, whale@email
2            spongebob@email, squarepants@email

You can use database specific functions in Hibernate with sqlProjection, so the code will look something like this:

public List<Map> emailsByOrder(){
    Criteria c = session.createCriteria(Order.class,"order");

    Criteria critEmail = c.createCriteria("orderEmails", "emails");
    String listAgg = "LISTAGG({alias}.EMAIL_ADDRESS_COLUMN_NAME, ', ') WITHIN GROUP(ORDER BY {alias}.EMAIL_ADDRESS_COLUMN_NAME ASC) AS EMAILS";
    critEmail.setProjection(Projections.projectionList().add(Projections.groupProperty("order.idOrder").as("ORDER_ID"))
                                                        .add(Projections.sqlProjection(listAgg, new String[]{"EMAILS"}, new Type[]{new StringType()}))); 
    c.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);

    return (List<Map>) c.list();
}

这篇关于在Hibernate中向列表添加投影的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！