Cambridge Technology PartnersCommunity Documentation

Chapter 4. Query Annotations

4.1. Using Query Annotations
4.2. Annotation Options
4.3. Query Options
4.4. Bulk Operations

While method expressions are fine for simple queries, they will often reach their limit once things get slightly more complex. Another aspect is the way you want to use JPA: The recommended approach using JPA for best performance is over named queries. To help incorporate those use cases, the CDI Query module supports also annotating methods for more control on the generated query.

The simples way to define a specific query is by annotating a method and providing the JPQL query string which has to be executed. In code, this looks like the following sample:

public interface PersonDao extends EntityDao<Person, Long> {

    @Query("select count(p) from Person p where p.age > ?1")
    Long countAllOlderThan(int minAge);
}    

The parameter binding in the query corresponds to the argument index in the method.

You can also refer to a named query which is constructed and executed automatically. The @Query annotation has a named attribute which corresponds to the query name:

@Entity

@NamedQueries({
    @NamedQuery(name = Person.BY_MIN_AGE,
                query = "select count(p) from Person p where p.age > ?1 order by p.age asc")
})
public class Person {
    public static final String BY_MIN_AGE = "person.byMinAge";
    ...
}
public interface PersonDao extends EntityDao<Person, Long> {
    @Query(named = Person.BY_MIN_AGE)
    Long countAllOlderThan(int minAge);
}    

Same as before, the parameter binding correspons to the argument index in the method. If the named query requires named parameters to be used, this can be done by annotating the arguments with the @QueryParam annotation.

@NamedQuery(name = Person.BY_MIN_AGE,

                query = "select count(p) from Person p where p.age > :minAge order by p.age asc")
public interface PersonDao extends EntityDao<Person, Long> {
    @Query(named = Person.BY_MIN_AGE)
    Long countAllOlderThan(@QueryParam("minAge") int minAge);
}    

It is also possible to set a native SQL query in the annotation. The @Query annotation has a sql attribute which corresponds to the SQL query to execute:

@Entity

@Table(name = "PERSON_TABLE")
public class Person {
    ...
}
public interface PersonDao extends EntityDao<Person, Long> {
    @Query(sql = "SELECT * FROM PERSON_TABLE p WHERE p.AGE > ?1")
    List<Person> findAllOlderThan(int minAge);
}    

Beside providing a query string or reference, the @Query annotation provides also two more attributes:

public interface PersonDao extends EntityDao<Person, Long> {

    @Query(named = Person.BY_MIN_AGE, max = 10, lock = LockModeType.PESSIMISTIC_WRITE)
    List<Person> findAllForUpdate(int minAge);
}    

NameDescription
max Limits the number of results.
lock Use a specific LockModeType to execute the query.

Note that these options can also be applied to Chapter 3, Query Method Expressions

All the query options you have seen so far are more or less static. But sometimes you might want to apply certain query options dynamically. For example, sorting criterias could come from a user selection so they cannot be know before. CDI query allows to apply query options at runtime by using the QueryResult result type:

public interface PersonDao extends EntityDao<Person, Long> {

    @Query("select p from Person p where p.age between ?1 and ?2")
    QueryResult<Person> findAllByAge(int minAge, int maxAge);
}    

Once you have obtained a QueryResult, you can apply further options and restrictions to the query:

List<Person> result = personDao.findAllByAge(18, 65)

    .sortAsc(Person_.lastName)
    .sortDesc(Person_.age).
    .firstResult(10)
    .maxResults(10)
    .getResultList();
}    

Note that the QueryResult return type can also be used with Chapter 3, Query Method Expressions.

While reading entities and updating them one by one might be fine for many use cases, applying bulk updates or deletes is also a common usage scenario for DAOs. CDI query supports this with a special marking annotation @Modifying:

public interface PersonDao extends EntityDao<Person, Long> {

    @Modifying
    @Query("update Person as p set p.classifyer = ?1 where p.classifyer = ?2")
    int updateClassifyer(Classifier current, Classifier next);
}    

Bulk operation query methods can either return void or int, which counts the number of entities affected by the bulk operation.