How to write query with additional count column using JPA Criteria Builder

Question

I am struggling to write a JPA query that would return all P objects that are in database and next to them I want to have count of their S children that have propertyA = 1.

SQL query

Select p.*, (Select count(s.id) from s_table s WHERE p.id = s.p_id and s.propertyA = 1) from p_table p

The mapping:

@Entity
@Table(name = "t_table")
public class PTable{
    @Id
    private String id;

    @Version
    private Long version;

    private String subject;
   
    @OneToMany(cascade = CascadeType.ALL, orphanRemoval = true, fetch = FetchType.EAGER)
    @JoinColumn(name = "p_id", referencedColumnName = "id")
    private Set<STable> sSet = new HashSet<>();
}
 
@Entity
@Table(name = "s_table")
public class STable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "p_id")
    private String pId;
  
    private String propertyA;
}

Also, would you like to point any good tutorial to write complex queries in JPA.

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<PTable> q = cb.createQuery(PTable.class);
Root<PTable> c = q.from(PTable.class);

Have you mapped the join relationship in your entity definition? Show them. Why don't you use a plain sql query like SELECT s.*, count(p.id) FROM p_table p JOIN s_table s ON p.id = s.p_id WHERE p.propertyA = 1 GROUP BY s.id. Such a query is much easier to turn into JPQL and JPA CriteriaQuery — perissf
– perissf, Commented Aug 19, 2020 at 7:12
I added the mapping. This mapping already existed in code. I must create such query. — michealAtmi
– michealAtmi, Commented Aug 19, 2020 at 7:20
It's not clear, given the relationships you might want to select PTable with count(STable) instead? — perissf
– perissf, Commented Aug 19, 2020 at 7:39
Oh..sorry I corrected the description. In result I want to have all P objects that are in database and next to them I want to have count of their S children that have propertyA = 1. — michealAtmi
– michealAtmi, Commented Aug 19, 2020 at 7:47

JLazar0 · Accepted Answer · 2020-08-19 09:57:07Z

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<MyPojo> cq = cb.createQuery(MyPojo.class);

Root<PTable> rootPTable = cq.from(PTable.class);
Join<PTable, STable> joinSTable = rootPTable.join(PTable_.sSet);

Subquery<Long> sqCount = cq.subquery(Long.class);
Root<STable> sqRootSTable = sqCount.from(STable.class);
Join<STable, PTable> sqJoinPTable = sqRootSTable.join(STable_.pSet);

sqCount.where(cb.and(
    cb.equal(sqJoinPTable.get(PTable_.id),rootPTable.get(PTable_.id)),
    cb.equal(sqRootSTable.get(STable_.propertyA),"1")));

sqCount.select(cb.count(sqRootSTable));

cq.multiselect(
    rootPTable.get(PTable_.id),
    rootPTable.get(PTable_.version),
    rootPTable.get(PTable_.subject),
    joinSTable.get(STable_.id),
    sqCount.getSelection(),
);

You will need a Pojo to obtain the results that a constructor has that matches in order and type with the multiselect parameters as follows:

public MyPojo(String pId, Long version, String subject, Long sId, Long count){
    [...]
}

You will also have to change your entities to correctly map the relationship, being bidirectional and lazy to improve performance as follows:

PTable

@OneToMany(mappedBy="p",fetch = FetchType.LAZY)
private Set<STable> sSet;

STable

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name="id")
private PTable p;

Collectives™ on Stack Overflow

How to write query with additional count column using JPA Criteria Builder

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related