JPA Criteria API

//hibernate 3.5.1
// //Simple Query
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
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
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
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
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
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)
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)
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();
// //Simple Join query
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
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
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);