Query Criteria

QueryCriteria is a wrapper over query string and criteria API. The type of query string can be native SQL, named query, or JPQL. A QueryCriteria can have chained CriteriaQueryBuilders for building a CriteriaQuery.

QueryCriteria supports chained QueryResultTransformers, 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 chained CriteriaQueryBuilders. For example, build query for retrieving employee counts of the 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. Join types are used only when related entities are joined in a query, and they will not be added in query otherwise.

Example: retrieve a list of ExpenseClaim(s), inner join its employee, left join employee.address, left join its expenseClaimItems.


	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

EntityFetchGraph is a subclass of EntityJoinGraph. 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. Oherwise non-lazy properites and entities will be fetched too. An entity fetch graph can fetch both entities and properties.

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 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 ExampleHR 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 the 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

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


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

Query Result Transformer

A QueryCriteria can have a chain of QueryResultTransformer(s). The results retrieved from persistence will go through the list of QueryResultTransformers 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);		

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);