Best way to get aggregate function result inside the entity object

Question

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

Possible duplicate of [Calculated property with JPA / Hibernate](http://stackoverflow.com/questions/2986318/calculated-property-with-jpa-hibernate) — Alan Hay, Nov 18 '15 at 12:37
How do I can achieve dynamic select criteria on a annotation. Apart from group by , I will be having conditional select. So I dont think its a good option to use the Formula, which is usually used for Derived values not for aggregate functions. — Ysak, Nov 18 '15 at 12:49
If I can use dynamic select conditions as well group by in Formula let me know how can I do it. Can you give me an example for the exact requirment where i need to add dynamic predicates on it. — Ysak, Nov 18 '15 at 12:51
You should either add a new question or edit your existing question if you think that your original question did not hit the point. It is difficult to understand what you mean with "conditional select". What kind of condition do you mean? — Tobias Liefke, Nov 18 '15 at 13:02

score 1 · Answer 1 · answered Nov 18 '15 at 14:13

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.

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();
    }
}

Summary mapped to a view:

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

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

Best way to get aggregate function result inside the entity object

1 Answers1

Linked