How to write subquery using criteria builder

Question

Here I am using NativeQuery to perform selecting lookup type using subquery this is working right but I want to use Criteria Builder. How can I use it?

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

Answer 1

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