how to write subquery using criteria builder

Question

Here Iam using NativeQuery to perform selecting lookuptype using subquery this is working right but I want to use Criteria Builder how i can use

Query query = em.createNativeQuery("SELECT * FROM LOOKUPMASTER WHERE PARENTLOOKUPTYPEID = (SELECT LOOKUPID FROM LOOKUPMASTER WHERE LOOKUPTYPE =? ) ", Lookupmaster.class); query.setParameter(1, lookUpType);

I tried to write the above query using criteria builder but i am getting different result here is my criteria query

`CriteriaBuilder cb =em.getCriteriaBuilder();
        CriteriaQuery cq=cb.createQuery(Lookupmaster.class);
        Root<Lookupmaster> rt=cq.from(Lookupmaster.class);
        Path<Object> path = rt.get("parentlookuptypeid");
        cq.select(rt);
        Subquery<Lookupmaster> subquery = cq.subquery(Lookupmaster.class);
        Root rt1 = subquery.from(Lookupmaster.class);
        subquery.select(rt1.get("lookupid"));
        subquery.where(cb.equal(rt.get("lookuptype"),lookUpType));

        cq.where(cb.in(path).value(subquery));
        Query qry =em.createQuery(cq)`

Why did you tag this with [tag:html]? – unor Dec 28 '13 at 12:58 — unor, Dec 28 '13 at 12:58

score 1 · Answer 1 · edited May 23 '17 at 12:28

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Object> criteriaQuery = criteriaBuilder.createQuery();
Root<EMPLOYEE> from = criteriaQuery.from(EMPLOYEE.class);
Path<Object> path = from.get("compare_field"); // field to map with sub-query
from.fetch("name");
from.fetch("id");
CriteriaQuery<Object> select = criteriaQuery.select(from);

Subquery<PROJECT> subquery = criteriaQuery.subquery(PROJECT.class);
Root fromProject = subquery.from(PROJECT.class);
subquery.select(fromProject.get("requiredColumnName")); // field to map with main-query
subquery.where(criteriaBuilder.equal("name",name_value));
subquery.where(criteriaBuilder.equal("id",id_value));

select.where(criteriaBuilder.in(path).value(subquery));

TypedQuery<Object> typedQuery = entityManager.createQuery(select);
List<Object> resultList = typedQuery.getResultList();

Here is a link

another article

how to write subquery using criteria builder

1 Answers1