CODEWITHSUNDEEP
sqlhibernatejpaaggregate-functionsjpa-2.1
Ysak
Ysak

Reputation: 2755

Best way to get aggregate function result inside the entity object

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

Upvotes: 1

Views: 1408

Answers (1)

Alan Hay
Alan Hay

Reputation: 23226

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

Upvotes: 1

Related Questions