vaadin-framework/documentation/jpacontainer/jpacontainer-filtering-criteria-api.asciidoc

---
title: Querying with the Criteria API
order: 7
layout: page
---

[[jpacontainer.filtering.criteria-api]]
= Querying with the Criteria API

When the [interfacename]#Filterable# API is not enough and you need to have more
control, you can make queries directly with the JPA Criteria API. You may also
need to customize sorting or joins, or otherwise modify the query in some way.
To do so, you need to implement a [interfacename]#QueryModifierDelegate# that
the JPAContainer entity provider calls when making a query. The easiest way to
do this is to extend [classname]#DefaultQueryModifierDelegate#, which has empty
implementations of all the methods so that you can only override the ones you
need.

The entity provider calls specific [interfacename]#QueryModifierDelegate#
methods at different stages while making a query. The stages are:

. Start building a query

. Add " [literal]#++ORDER BY++#" expression

. Add " [literal]#++WHERE++#" expression (filter)

. Finish building a query


Methods where you can modify the query are called before and after each stage as
listed in the following table:

[[table.jpacontainer.filtering.criteria-api.methods]]
.[classname]#QueryModifierDelegate# Methods

|===============
|[methodname]#queryWillBeBuilt()#
|[methodname]#orderByWillBeAdded()#
|[methodname]#orderByWasAdded()#
|[methodname]#filtersWillBeAdded()#
|[methodname]#filtersWereAdded()#
|[methodname]#queryHasBeenBuilt()#

|===============



All the methods get two parameters. The [interfacename]#CriteriaBuilder# is a
builder that you can use to build queries. The [interfacename]#CriteriaQuery# is
the query being built.

You can use the [methodname]#getRoots().iterator().next()# in
[interfacename]#CriteriaQuery# to get the "root" that is queried, for example,
the [literal]#++PERSON++# table, etc.

[[jpacontainer.filtering.criteria-api.filters]]
== Filtering the Query

Let us consider a case where we modify the query for a [classname]#Person#
container so that it includes only people over 116. This trivial example is
identical to the one given earlier using the [classname]#Filterable# interface.


----

persons.getEntityProvider().setQueryModifierDelegate(
        new DefaultQueryModifierDelegate () {
    @Override
    public void filtersWillBeAdded(
            CriteriaBuilder criteriaBuilder,
            CriteriaQuery<?> query,
            List<Predicate> predicates) {
        Root<?> fromPerson = query.getRoots().iterator().next();

        // Add a "WHERE age > 116" expression
        Path<Integer> age = fromPerson.<Integer>get("age");
        predicates.add(criteriaBuilder.gt(age, 116));
    }
});
----
See the http://demo.vaadin.com/book-examples-vaadin7/book#jpacontainer.criteria.querymodification[on-line example, window="_blank"].


[[jpacontainer.filtering.criteria-api.compatibility]]
== Compatibility

When building queries, you should consider the capabilities of the different JPA
implementations. Regarding Hibernate, see
<<dummy/../../../framework/jpacontainer/jpacontainer-hibernate#jpacontainer.hibernate.joins,"Joins
in Hibernate vs EclipseLink">>.