Query using Criteria

Question

How to write the following query using criteria

StringBuilder queryString = new StringBuilder("from BorrowerEvaluationDTO b where b.hasEvaluation = TRUE"+" ");
                if(sector != null && !sector.trim().equals("")){
                    queryString.append (" AND b.sector = '" + sector + "'");
                }
queryString.append(" AND b.id in (select needProfile.id from Investment where investor.id = '"+investorId + "')");

I think your problem is the subselect. Here is a example: http://stackoverflow.com/questions/4483576/jpa-2-0-criteria-api-subqueries-in-expressions — pL4Gu33, Feb 11 '14 at 11:27

score 0 · Answer 1 · answered Feb 11 '14 at 11:54

i'm not sure how your model exacly looks like, but i think your query will be more or less like that:

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Object> criteria = cb.createQuery(Object.class);
        Root<Object> root = criteria.from(Object.class);
        List<Predicate> predicates = new ArrayList<Predicate>();
        predicates.add(cb.equal(root.get("hasEvaluation"), cb.literal(true)));

        if (sector != null && !sector.trim().equals("")) {
            predicates.add(cb.equal(root.get("hasEvaluation"),
                    cb.literal(sector)));

        }
        Subquery<Object> sub = criteria.subquery(Object.class);
        Root<Object> subRoot = sub.from(Object.class);
        sub.select(subRoot.get("needProfile").get("id"));
        sub.where(cb.equal(subRoot.get("investor").get("id"), investorId));
        predicates.add(root.get("needProfile").in(sub));

        criteria.where(predicates.toArray(new Predicate[predicates.size()]));

Query using Criteria

1 Answers1