JPA Criteria API by samples – Part-I

JPA Criteria API by samples – Part-I

In this post I try to explain jpa criteria API by simple example on a single entity.

Although examples are pretty much simple it will help you with finalized version of JPA 2.0 API.

For Part II please read JPA Criteria API by samples – Part-II

and in this examples in my opinion building query by programming is not so easy and maintainable  but still possible.

PS: I tested it with hibernate 3.5.1

Simple Query

1 2 3 4 5 6 7 8 9 10

Query query = entityManager.createQuery("from SimpleBean s"); List<SimpleBean> list = query.getResultList(); CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery<Object> criteriaQuery = criteriaBuilder.createQuery(); Root<SimpleBean> from = criteriaQuery.from(SimpleBean.class); CriteriaQuery<Object> select = criteriaQuery.select(from); TypedQuery<Object> typedQuery = entityManager.createQuery(select); List<Object> resultList = typedQuery.getResultList(); assertEqualsList(list, resultList);

Simple Query with Order

1 2 3 4 5 6 7 8 9

List<SimpleBean> expected= entityManager.createQuery("from SimpleBean s order by s.pbyte asc ,s.pint desc")                                   .getResultList(); //... CriteriaQuery<Object> select = criteriaQuery.select(from);         select.orderBy(criteriaBuilder.asc(from.get("pbyte"))                         ,criteriaBuilder.desc(from.get("pint"))); TypedQuery<Object> typedQuery = entityManager.createQuery(select);        //...

Simple Query with selected fields

1 2 3 4

Query query = entityManager.createQuery("select s.id,s.pbyte from SimpleBean s "); List listExpected = query.getResultList();         //... CriteriaQuery<Object> select = criteriaQuery.multiselect(from.get("id"),from.get("pbyte"));

Query with single criteria

1 2 3 4 5 6 7 8 9 10 11

int arg1 = 20000; Query query = entityManager.createQuery("from SimpleBean s where s.pint>=:arg1"); query.setParameter("arg1", arg1); List<SimpleBean> list = query.getResultList(); //... CriteriaQuery<Object> select = criteriaQuery.select(from); Predicate predicate = criteriaBuilder.ge(from.get("pint"), arg1); criteriaQuery.where(predicate); //...

Query with multiple criterias

1 2 3 4 5 6 7 8 9 10 11 12

int arg1 = 20000; int arg2 = 50000; Query query = entityManager.createQuery("from SimpleBean s where s.pint>=:arg1 and s.pint<=:arg2"); query.setParameter("arg1", arg1); query.setParameter("arg2", arg2); List<SimpleBean> list = query.getResultList(); //.. Predicate predicate1 = criteriaBuilder.ge(from.get("pint"), arg1); Predicate predicate2 = criteriaBuilder.le(from.get("pint"), arg2); criteriaQuery.where(criteriaBuilder.and(predicate1, predicate2)); //..

Query with single literal

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

String arg1 = "name"; Query query = entityManager.createQuery("from SimpleBean s where upper(s.pstring) like upper(:arg1)"); query.setParameter("arg1", arg1); List<SimpleBean> list = query.getResultList(); CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery<Object> criteriaQuery = criteriaBuilder.createQuery(); Root from = criteriaQuery.from(SimpleBean.class); CriteriaQuery<Object> select = criteriaQuery.select(from); Expression<String> literal = criteriaBuilder.upper(criteriaBuilder.literal((String) arg1)); Predicate predicate = criteriaBuilder.like(criteriaBuilder.upper(from.get("pstring")), literal); criteriaQuery.where(predicate); TypedQuery<Object> typedQuery = entityManager.createQuery(select); List<Object> resultList = typedQuery.getResultList(); assertEqualsList(list, resultList);

Query with summary (min,max,avg)

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

Query query = entityManager.createQuery("select min(s.pint) from SimpleBean s"); Object minActual = query.getSingleResult(); CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery<Object> criteriaQuery = criteriaBuilder.createQuery(); Root from = criteriaQuery.from(SimpleBean.class); Expression minExpression = criteriaBuilder.min(from.get("pint")); CriteriaQuery<Object> select = criteriaQuery.select(minExpression); TypedQuery<Object> typedQuery = entityManager.createQuery(select); Object minExpected = typedQuery.getSingleResult(); assertEquals(minActual, minExpected);

Query with aggreation (group by)

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

Query query = entityManager.createQuery("select min(s.pint),s.pbyte from SimpleBean s group by s.pbyte"); List listExpected = query.getResultList(); CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery<Object> criteriaQuery = criteriaBuilder.createQuery(); Root from = criteriaQuery.from(SimpleBean.class); Expression minExpression = criteriaBuilder.min(from.get("pint")); Path pbytePath = from.get("pbyte"); CriteriaQuery<Object> select = criteriaQuery.multiselect(minExpression, pbytePath); CriteriaQuery<Object> groupBy = select.groupBy(pbytePath); TypedQuery<Object> typedQuery = entityManager.createQuery(select); List listActual = typedQuery.getResultList();