How can I do a subquery in a JPA Criteria Builder Select statment?

Question

I'm trying to do a select statement using CritierBuilder/CrtieriaQuery to select certain fields from table A and then a boolean flag if that record exists in another table.

Basically, I have a list of "officers", and a list of users. Users are people using the system and are able to bookmark/save an officer. When a user queries the officers I want to be able to show which ones they have bookmarked.

SELECT o.FIRST_NAME, o.LAST_NAME,
 (select CAST(1 AS BIT) from OFFICER_BOOKMARK b where b.OFFICER_ID=o.OFFICER_ID AND USER_ID=123456789) as BOOKMARKED 
from OFFICER o;

So this query, I ran in my h2 database console and it (pretty) much works. It returns true if the officer is bookmarked by user 123456789 and otherwise null for the bookmarked column.

But I'm having trouble translating that into jpa criteria queries...

public List<OfficerDTO> getOfficersDto() {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<OfficerDTO> cq = cb.createQuery(OfficerDTO.class);
    Root<OfficerEntity> root = cq.from(OfficerEntity.class);
    Root<OfficerBookmarkEntity> subRoot = cq.from(OfficerBookmarkEntity.class);

    Subquery<Boolean> subquery = cq.subquery(Boolean.class);
    subRoot.alias("bookmarked");
    subquery.select(cb.isNotNull(subRoot.get("id")));
    subquery.where(cb.equal(subRoot.get("officer").get("officerId"), root.get("officerId")));
    subquery.where(cb.equal(subRoot.get("user").get("userId"), "123456789"));

    cq.multiselect(
            cb.construct(
                OfficerDTO.class,
                root.get("firstName"),
                root.get("lastName"),
                subquery.getSelection().as(Boolean.class)
            )
    );
    TypedQuery<OfficerDTO> q = em.createQuery(cq);
    return q.getResultList();
}

I think I'm pretty close but I can't figure out the subquery part for the select statment and how to just get a boolean value back.

Oleksii Valuiskyi · Accepted Answer · 2020-02-05T09:40:11.867

The problem is that cb.isNotNull(subRoot.get("id")) works only if subquery result is exist (returns true only). Otherwise you have got null. So you have to check subquery result on a higher lever.

This should work

  public List<OfficerDTO> getOfficersDto() {
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<OfficerDTO> cq = cb.createQuery(OfficerDTO.class);
        Root<OfficerEntity> root = cq.from(OfficerEntity.class);

        Subquery<Long> subquery = cq.subquery(Long.class); // or Integer (depends on id class)
        Root<OfficerBookmarkEntity> subRoot = 
            subquery.from(OfficerBookmarkEntity.class);

        Predicate officerPredicate = cb.equal(
            subRoot.get("officer").get("officerId"), 
            root.get("officerId")
        );

        Predicate userPredicate = cb.equal(
            subRoot.get("user").get("userId"), 
            "123456789"
        );

        subquery.select(subRoot.get("id"))                 // select subRoot id
            .where(officerPredicate, userPredicate);       // if you execute `.where` twice 
                                                           // it replaces the previously added restrictions     
        cq.multiselect(
            cb.construct(
                OfficerDTO.class,
                root.get("firstName"),
                root.get("lastName"),
                subquery.getSelection().isNotNull()        // check if subquery result is present
            )
        );

        return em.createQuery(cq).getResultList();
}

Thank you! That worked! I also had to change to use hibernate instead of eclipselink... Do you know if eclipselink supports sub selects in the select statement? This so question and the docs kind of make it seem like it does and doesnt, https://stackoverflow.com/questions/36077435/subqueries-in-select-in-eclipselink — John, Feb 06 '20 at 14:54

How can I do a subquery in a JPA Criteria Builder Select statment?

1 Answers1