在实体对象内部获取聚合函数结果的最佳方法 [英] Best way to get aggregate function result inside the entity object

问题描述

很多时候，我必须在实体对象本身内部获取SQL聚合查询结果.到目前为止，我可以通过以下代码达到相同的目的

Many time I have to get the SQL aggregate query result inside the Entity Object itself. As of now I could able to achive the same by the following code

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Tuple> q = cb.createTupleQuery();
    Root<Test> c = q.from(Test.class);
    q.multiselect(c, cb.count(c));
    q.groupBy(c.get("type"));

    TypedQuery<Tuple> t = em.createQuery(q);
    List<Tuple> resultList = t.getResultList();
    List<Test> list = new ArrayList<>();

    for(Tuple tuple : resultList){
        Test te = (Test) tuple.get(0);
        te.setQuantity((long)tuple.get(1));
        list.add(te);
    }

但是我想知道什么是最好的方法.我的测试实体为

But I want to know what could be the best way. My Test Entity is as

@Entity
@Table(name = "test")
public class Test {
    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    @Column(name = "name")
    private String name;

    @Column(name = "type")
    private Integer type = 0;

    @Transient
    private long quantity;
}

推荐答案

如果您不能使用@Formula，那么我建议您根据自己的选择创建一个数据库视图，并将另一个实体映射到该视图.然后，您可以使用@OneToOne或@SecondaryTable将其映射到现有实体.

If you cannot use @Formula then I'd suggest create a database view basic on your select and mapping an additional entity to that. You can then map this to your existing entity using either as a @OneToOne or by using @SecondaryTable.

这具有符合JPA的附加优势(即不使用Hibernate的专有@Formula)，并且看起来像这样:

This has the added advantage of being JPA compliant (i.e. not using Hibernate's propreitary @Formula) and would look something like:

@Entity
@Table(name = "test")
public class Test {
    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;

    @Column(name = "name")
    private String name;

    @Column(name = "type")
    private Integer type = 0;

    @OneToOne//or via secondary table
    private TestSummaryInfo summaryInfo;

    public long getQuantity(){
        return summaryInfo.getQuantity();
    }
}

摘要映射到视图:

@Entity
@Table(name = "vw_test_summary")
public class TestSummaryInfo {
    @Id
    @Column(name = "id")
    private Integer id;

    @Column(name = "quantity")
    private Long quantity;
}

这篇关于在实体对象内部获取聚合函数结果的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！