5.2 Query Criteria

Create QueryCriteria
Entity Join Graph
Entity Fetch Graph
Property Query Elements
Query Result Transformer
Query String
Row Count Query

QueryCriteria is a wrapper over JPA query string and criteria API. The type of query string can be native SQL, named query, or JPQL. A QueryCriteria can have a list of CriteriaQueryBuilder(s) for building a CriteriaQuery. It is detached and does not need an EntityManager.

QueryCriteria supports a list of QueryResultTransformer(s), so it has query result type from persistence and the transformed result type after transformers are applied to the query results.

Create QueryCriteria

Create a QueryCriteria with a list of CriteriaQueryBuilder(s). For example, build query for retrieving employee counts of departments.


	final List<Department> departments = ;
	CriteriaQueryBuilder<Object[]> builder1 = new CriteriaQueryBuilder<Object[]>() {
		@Override
		public void build(CriteriaBuilder criteriaBuilder, CriteriaQuery<Object[]> criteriaQuery) {
			Root<Employee> root = criteriaQuery.from(Employee.class);			
			Path<Department> deptPath = root.get(Employee.PROPERTY_DEPARTMENT);
			criteriaQuery.where(deptPath.in(departments));			
		}		
	};

	CriteriaQueryBuilder<Object[]> builder2 = new CriteriaQueryBuilder<Object[]>() {
		@Override
		public void build(CriteriaBuilder criteriaBuilder, CriteriaQuery<Object[]> criteriaQuery) {
			Root<Employee> root = criteriaQuery.getRoots().iterator().next();
			Path<Department> deptPath = root.get(Employee.PROPERTY_DEPARTMENT);			
			Path<Long> deptIdPath = deptPath.get(Department.PROPERTY_ID);
			Path<Long> empIdPath = root.get(Employee.PROPERTY_ID);
			criteriaQuery.groupBy(deptIdPath);
			criteriaQuery.multiselect(deptIdPath, criteriaBuilder.countDistinct(empIdPath));
		}		
	};
	
	QueryCriteria<Object[], Object[]> criteria = new QueryCriteria<Object[], Object[]>(
		Object[].class, Object[].class, builder1, builder2);
		
	BackingBeanContext context = BackingBeanContext.getInstance();
	PersistenceEntityManager peManager = context.getPersistenceEntityManager();
	List<Object[]> employeeCountResults = peManager.searchResults(criteria, null, null);

The QueryCriteria above can be constructed using a list of CriteriaElement(s).


	CriteriaElement[] pqeList = new CriteriaElement[] {
		DetachedCriteria.in(Employee.PROPERTY_DEPARTMENT, departments),
		DetachedCriteria.groupSelect(Employee.PROPERTY_DEPARTMENT+".id"),
		DetachedCriteria.select(DetachedCriteria.countDistinct("id"))
	};

	QueryCriteria<Object[], Object[]> criteria = new QueryCriteria<Object[], Object[]>(
		Employee.class, pqeList, Object[].class, Object[].class);
		
	BackingBeanContext context = BackingBeanContext.getInstance();
	PersistenceEntityManager peManager = context.getPersistenceEntityManager();
	List<Object[]> employeeCountResults = peManager.searchResults(criteria, null, null);

Entity Join Graph

An entity join graph describes join types between entities for a query(from clause). Join types are used only when related entities are joined in a query, and they will not be added in query otherwise.

Example: JPQL:

	select distinct e from ExpenseClaim e left join e.expenseClaimItems item
	inner join e.employee emp left join emp.address addr
	where addr.state != 'NY' and item.expense < 10000;

The join graph is:


	EntityJoinGraph joinGraph = new EntityJoinGraph(ExpenseClaim.class, false);
	joinGraph.addProperties(JoinType.LEFT, "expenseClaimItems");
	EntityJoinGraph employeeSubgraph = joinGraph.addSugraph("employee", Employee.class, JoinType.INNER);
	employeeSubgraph.addProperties(JoinType.LEFT, "address");

A convenient way to create an EntityJoinGraph using property name path with join types:


	EntityJoinGraph joinGraph = new EntityJoinGraph(ExpenseClaim.class,
		new Object[]{"employee.address", JoinType.LEFT, "expenseClaimItems", JoinType.LEFT});

If the join type of a property in a path is not specified, default to INNER join. ExpenseClaim inner joins its employee for the example above.

Entity Fetch Graph

An entity fetch graph describes what entities and properties to be fetched during a query. It can be inclusive or exclusive. If it is exclusive, only those properties and entities in the fetch graph will be retrieved from persistence. Otherwise, non-lazy properties and entities will be fetched too. An entity fetch graph can fetch both entities and properties.

EntityFetchGraph is a subclass of EntityJoinGraph. For fetch graph, joinType can be null. If joinType is not null, it is a join fetch. If it is null, it is up to Persistence Provider to decide whether it is a join fetch or fetch by additional queries.

Example: retrieve a list of ExpenseClaim(s), fetching its employee by inner join, fetching its expenseClaimItems by left join, and fetching ExpenseClaimItem properties: date, name and total.


	EntityFetchGraph fetchGraph = new EntityFetchGraph(ExpenseClaim.class, false);
	EntityFetchGraph employeeSubgraph = fetchGragh.addSugraph("employee", Employee.class, JoinType.INNER);
	employeeSubgraph.addProperties(JoinType.LEFT, "address");
	EntityFetchGraph itemsSubgraph = fetchGragh.addSugraph("expenseClaimItems", ExpenseClaimItem.class, JoinType.LEFT);
	itemsSubgraph.addProperties("date", "name", "total");
	
	queryCriteria.setEntityFetchGraph(fetchGraph);

A convenient way to create an EntityFetchGraph using property name path with join types:


	EntityFetchGraph fetchGraph = new EntityFetchGraph(ExpenseClaim.class,
		new Object[]{"employee.address", JoinType.LEFT, "expenseClaimItems", JoinType.LEFT}, false);
	
	EntityFetchGraph itemsSubgraph = fetchGraph.getSubgraph("expenseClaimItems");
	itemsSubgraph.addProperties("date", "name", "total");

If the join type of a property in a join path is not specified, default to INNER join. ExpenseClaim inner joins its employee for the example above.

Property Query Elements

A CriteriaElement describes a query restriction, selection, order, group, group restriction or hints. Equal type CriteriaElements can be used to initialize entity properties for query or creation when used in TypeDescriptor. Aggregate functions can be applied to properties in query restrictions, orders and selections.

Take the entities in the example HR module as examples.

Example 1: Select all part-time employees, order by hiredDate descending. Root entity type is Employee.


	CriteriaElement[] pqeList = new CriteriaElement[]{
		DetachedCriteria.eq("type", Employee.Type.PART_TIME)
		DetachedCriteria.desc("hiredDate")};

Example 2: Sum employee expenses of departments, group by employee and expense code. Root entity type is ExpenseClaimItem.


	CriteriaElement[] pqeList = new CriteriaElement[] {
		DetachedCriteria.in("expenseClaim.employee.department", departments),
		DetachedCriteria.groupSelect("expenseClaim.employee"),
		DetachedCriteria.groupSelect("code"),
		DetachedCriteria.select(DetachedCriteria.sum("expense"))
	};

Example 3: Join graph hint

Default join type is inner join. Use joinGraph hint to override default join types.


	EntityJoinGraph joinGraph = new EntityJoinGraph(ExpenseClaimItem.class,
		new Object[]{"expenseClaim.employee.department", JoinType.LEFT});
	
	CriteriaElement[] pqeList = new CriteriaElement[] {
		DetachedCriteria.in("expenseClaim.employee.department", departments),
		DetachedCriteria.joinGraph(joinGraph)
	};

Example 4: Fetch graph hint

select ExpenseClaimItem inner join fetch expenseClaim inner join fetch employee left join fetch department.


	EntityFetchGraph fetchGraph = new EntityFetchGraph(ExpenseClaimItem.class,
		new Object[]{"expenseClaim.employee.department", JoinType.LEFT}, false);
	
	CriteriaElement[] pqeList = new CriteriaElement[] {
		DetachedCriteria.le("date", aDate),
		DetachedCriteria.fetchGraph(fetchGraph)
	};

Query Result Transformer

A QueryCriteria can have a list of QueryResultTransformer(s). The results retrieved from persistence will go through the list of QueryResultTransformer(s) before the final results are returned to the caller.

The transformed results of previous QueryResultTransformer will be the input of next QueryResultTransformer.


	public class MyQueryResultTransformer implements QueryResultTransformer<Object[], Employee> {
		@Override
  		public List<Employee> transform(List<Object[]> resultList, PersistenceEntityManager peManager) throws SystemException {
  			// create Employee object by properties
  			List<Employee> employeeList = new ArrayList<Employee>(resultList.size());
  			for (Object[] row : resultList) {
  				String nid = (String)row[0];
  				String name = (String)row[1];
  				String hiredDate = (Date)row[2];
  				Employee employee = new Employee(nid, name, hiredDate);
  				employeeList.add(employee);
  			}
  			return employeeList;
  		}
	}
	
	MyQueryResultTransformer transformer = new MyQueryResultTransformer();
	queryCriteria.addQueryResultTransformer(transformer);

Query String

A query string can be native, named, or JPQL.


	String query = "select nid, name from Employee where hiredDate>=:hiredDate";
	Map<String, Object> parameterMap = new HashMap<String, Object>();
	parameterMap.put("hiredDate", aDate);
	QueryCriteria queryCriteria = new QueryCriteria(Object[].class, new DataDescriptor(Object[].class, true, false),
		query, QueryType.JPQL, parameterMap);
	BackingBeanContext context = BackingBeanContext.getInstance();
	PersistenceEntityManager peManager = context.getPersistenceEntityManager();
	List<Object[]> results = peManager.searchResults(queryCriteria, null, null);

Native query is not recommended for database portability.

Row Count Query

In a pageable entity list, the total number of rows will be fetched to calculate page count using a row count query. By default, row counting query is built from existing query as


	select count(*) from (existing query) as subquery.

It can be overridden by setting a row count QueryCriteria.


	QueryCriteria<Integer, Integer> rowCountQueryCriteria = new QueryCriteria<Integer, Integer>(...);
	queryCriteria.setRowCountQueryCriteria(rowCountQueryCriteria);

← Entity Annotation→ Entity ManagerFrames / No Frames