JPA Criteria API by samples – Part-II

JPA Criteria API by samples – Part-II

you can read first part from JPA Criteria API by samples – Part-I

some more examples with JPA criteria API

Simple Join query

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

long category=200L; Query query = entityManager.createQuery("select s from OrderItem s " +         "where s.product.category=:cat"); query.setParameter("cat", category); List<OrderItem> list = query.getResultList(); CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery<Object> criteriaQuery = criteriaBuilder.createQuery(); Root<OrderItem> from = criteriaQuery.from(OrderItem.class); Path<Object> path = from.join("product").get("category"); CriteriaQuery<Object> select = criteriaQuery.select(from); select.where(criteriaBuilder.equal(path, category)); TypedQuery<Object> typedQuery = entityManager.createQuery(select); List<Object> resultList = typedQuery.getResultList(); assertEqualsList(list, resultList);

simple fetch join query

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

long category=200L; Query query = entityManager.createQuery("select s from OrderItem s " +         "join fetch s.product where s.product.category=:cat"); query.setParameter("cat", category); List<OrderItem> list = query.getResultList(); CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery<Object> criteriaQuery = criteriaBuilder.createQuery(); Root<OrderItem> from = criteriaQuery.from(OrderItem.class); Path<Object> path = from.join("product").get("category"); from.fetch("product"); //FETCH product CriteriaQuery<Object> select = criteriaQuery.select(from); select.where(criteriaBuilder.equal(path, category)); TypedQuery<Object> typedQuery = entityManager.createQuery(select); List<Object> resultList = typedQuery.getResultList(); assertEqualsList(list, resultList);

subselect (subquery) join query

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

Query query = entityManager.createQuery(         "select s from OrderItem s join fetch s.product" +         " where s.product.category in" +         " (select sb.pbyte from SimpleBean sb where sb.pint>=30000)"); List<OrderItem> list = query.getResultList(); CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery<Object> criteriaQuery = criteriaBuilder.createQuery(); Root<OrderItem> from = criteriaQuery.from(OrderItem.class); Path<Object> path = from.join("product").get("category"); from.fetch("product"); CriteriaQuery<Object> select = criteriaQuery.select(from); Subquery<SimpleBean> subquery = criteriaQuery.subquery(SimpleBean.class); Root fromSimpleBean = subquery.from(SimpleBean.class); subquery.select(fromSimpleBean.get("pbyte")); subquery.where(criteriaBuilder.ge(fromSimpleBean.get("pint"),30000)); select.where(criteriaBuilder.in(path).value(subquery)); TypedQuery<Object> typedQuery = entityManager.createQuery(select); List<Object> resultList = typedQuery.getResultList(); assertEqualsList(list, resultList);