Setting and Tuning of JPA Queries

The Queryjavax.persistence.Query - JPA InterfaceInterface used to control query execution.javax.jdo.Query - JDO InterfaceThe Query interface allows applications to obtain persistent instances, values, and aggregate data from the data store. and TypedQueryjavax.persistence.TypedQuery - JPA InterfaceInterface used to control the execution of typed queries. interfaces define various setting and tuning methods that may affect query execution if invoked before a query is run using getResultListTypedQuery.getResultList() - JPA MethodExecute a SELECT query and return the query results as a typed List. or getSingleResultTypedQuery.getSingleResult() - JPA MethodExecute a SELECT query that returns a single result..

This page covers the following topics:

Result Range (setFirstResult, setMaxResults)

The setFirstResultTypedQuery.setFirstResult(startPosition) - JPA MethodSet the position of the first result to retrieve. and setMaxResultsTypedQuery.setMaxResults(maxResult) - JPA MethodSet the maximum number of results to retrieve. methods enable defining a result window that exposes a portion of a large query result list (hiding anything outside that window). The setFirstResultTypedQuery.setFirstResult(startPosition) - JPA MethodSet the position of the first result to retrieve. method is used to specify where the result window begins, i.e. how many results at the beginning of the complete result list should be skipped and ignored. The setMaxResultsTypedQuery.setMaxResults(maxResult) - JPA MethodSet the maximum number of results to retrieve. method is used to specify the result window size. Any result after hitting that specified maximum is ignored.

These methods support the implementation of efficient result paging. For example, if each result page should show exactly pageSize results, and pageId represents the result page number (0 for the first page), the following expression retrieves the results for a specified page:

  List<Country> results =
      query.setFirstResultTypedQuery.setFirstResult(startPosition) - JPA MethodSet the position of the first result to retrieve.(pageIx * pageSize)
           .setMaxResultsTypedQuery.setMaxResults(maxResult) - JPA MethodSet the maximum number of results to retrieve.(pageSize)
           .getResultListTypedQuery.getResultList() - JPA MethodExecute a SELECT query and return the query results
 as a typed List.();

These methods can be invoked in a single expression with getResultListTypedQuery.getResultList() - JPA MethodExecute a SELECT query and return the query results as a typed List. since the setter methods in Queryjavax.persistence.Query - JPA InterfaceInterface used to control query execution.javax.jdo.Query - JDO InterfaceThe Query interface allows applications to obtain persistent instances, values, and aggregate data from the data store. and TypedQueryjavax.persistence.TypedQuery - JPA InterfaceInterface used to control the execution of typed queries. support method chaining (by returning the query object on which they were invoked).

Flush Mode (setFlushMode)

Changes made to a database using an EntityManagerjavax.persistence.EntityManager - JPA InterfaceInterface used to interact with the persistence context. em can be visible to anyone who uses em, even before committing the transaction (but not to users of other EntityManager instances). JPA implementations can easily make uncommitted changes visible in simple JPA operations, such as findEntityManager.find(entityClass,primaryKey) - JPA MethodFind by primary key.. However, query execution is much more complex. Therefore, before a query is executed, uncommitted database changes (if any) have to be flushed to the database in order to be visible to the query.

Flush policy in JPA is represented by the FlushModeTypejavax.persistence.FlushModeType - JPA EnumFlush mode setting. enum, which has two values:

AUTOjavax.persistence.FlushModeType.AUTO - JPA Enum Constant(Default) Flushing to occur at query execution. - changes are flushed before query execution and on commit/flush.
COMMITjavax.persistence.FlushModeType.COMMIT - JPA Enum ConstantFlushing to occur at transaction commit. - changes are flushed only on explicit commit/flush.

In most JPA implementations the default is AUTO. In ObjectDB the default is COMMIT (which is more efficient). The default mode can be changed by the application, either at the EntityManagerjavax.persistence.EntityManager - JPA InterfaceInterface used to interact with the persistence context. level as a default for all the queries in that EntityManagerjavax.persistence.EntityManager - JPA InterfaceInterface used to interact with the persistence context. or at the level of a specific query, by overriding the default EntityManagerjavax.persistence.EntityManager - JPA InterfaceInterface used to interact with the persistence context. setting:

  // Enable query time flush at the EntityManager level:
  em.setFlushModeEntityManager.setFlushMode(flushMode) - JPA MethodSet the flush mode that applies to all objects contained
 in the persistence context.(FlushModeTypejavax.persistence.FlushModeType - JPA EnumFlush mode setting..AUTOjavax.persistence.FlushModeType.AUTO - JPA Enum Constant(Default) Flushing to occur at query execution.);

  // Enable query time flush at the level of a specific query:
  query.setFlushModeTypedQuery.setFlushMode(flushMode) - JPA MethodSet the flush mode type to be used for the query execution.(FlushModeTypejavax.persistence.FlushModeType - JPA EnumFlush mode setting..AUTOjavax.persistence.FlushModeType.AUTO - JPA Enum Constant(Default) Flushing to occur at query execution.);

Flushing changes to the database before every query execution affects performance significantly. Therefore, when performance is important, this issue has to be considered.

Lock Mode (setLockMode)

ObjectDB uses automatic optimistic locking to prevent concurrent changes to entity objects by multiple users. JPA 2 adds support for pessimistic locking. The setLockModeTypedQuery.setLockMode(lockMode) - JPA MethodSet the lock mode type to be used for the query execution. method sets a lock mode that has to be applied on all the result objects that the query retrieves. For example, the following query execution sets a pessimistic WRITE lock on all the result objects:

  List<Country> results =
      query.setLockModeTypedQuery.setLockMode(lockMode) - JPA MethodSet the lock mode type to be used for the query execution.(LockModeTypejavax.persistence.LockModeType - JPA EnumLock modes can be specified by means of passing a LockModeType
 argument to one of the {@link javax.persistence.EntityManager} methods that take locks
 (lock, find, or refresh) or
 to the {@link Query#setLockMode Query.setLockMode()} or
 {@link TypedQuery#setLockMode TypedQuery.setLockMode()} method..PESSIMISTIC_WRITEjavax.persistence.LockModeType.PESSIMISTIC_WRITE - JPA Enum ConstantPessimistic write lock.)
           .getResultListTypedQuery.getResultList() - JPA MethodExecute a SELECT query and return the query results
 as a typed List.();

Notice that when a query is executed with a requested pessimistic lock mode it could fail if locking fails, throwing a LockTimeoutExceptionjavax.persistence.LockTimeoutException - JPA ExceptionThrown by the persistence provider when an pessimistic locking conflict occurs that does not result in transaction rollback..

Query Hints

Additional settings can be applied to queries via hints.

Supported Query Hints

ObjectDB supports the following query hints:

"javax.persistence.query.timeout" - sets maximum query execution time in milliseconds. A QueryTimeoutExceptionjavax.persistence.QueryTimeoutException - JPA ExceptionThrown by the persistence provider when a query times out and only the statement is rolled back. is thrown if timeout is exceeded.
"javax.persistence.lock.timeout" - sets maximum waiting time for pessimistic locks, when pessimistic locking of query results is enabled. See the Lock Timeout section for more details about lock timeout.
"objectdb.query-language" - sets the query language, as one of "JPQL" (JPA query language), "JDOQL" (JDO query language) or "ODBQL" (ObjectDB query language). The default is ODBQL, which is a union of JPQL, JDOQL and ObjectDB extensions. Setting "JPQL" is useful to enforce portable JPA code by ObjectDB.
"objectdb.result-fetch" - sets fetch mode for query result as either "EAGER" (the default) or "LAZY". When LAZY is used result entity objects are returned as references (with no content). This could be useful when the shared L2 cache is enabled and entity objects may already be available in the cache.

Setting Query Hint (Scopes)

Query hints can be set in the following scopes (from global to local):

For the entire persistence unit - using a persistence.xml property:

    <properties>
       <property name="javax.persistence.query.timeout" value="3000"/>
    </properties>

For an EntityManagerFactoryjavax.persistence.EntityManagerFactory - JPA InterfaceInterface used to interact with the entity manager factory for the persistence unit. - using the createEntityManagerFacotoryPersistence.createEntityManagerFactory(persistenceUnitName,properties) - JPA Static MethodCreate and return an EntityManagerFactory for the named persistence unit using the given properties. method:

  Map<String,Object> properties = new HashMap();
  properties.put("javax.persistence.query.timeout", 4000);
  EntityManagerFactory emf =
      Persistence.createEntityManagerFactoryPersistence.createEntityManagerFactory(persistenceUnitName,properties) - JPA Static MethodCreate and return an EntityManagerFactory for the named persistence unit
 using the given properties.("pu", properties);

For an EntityManagerjavax.persistence.EntityManager - JPA InterfaceInterface used to interact with the persistence context. - using the createEntityManagerEntityManagerFactory.createEntityManager(map) - JPA MethodCreate a new application-managed EntityManager with the specified Map of properties. method:

  Map<String,Object> properties = new HashMap();
  properties.put("javax.persistence.query.timeout", 5000);
  EntityManager em = emf.createEntityManagerEntityManagerFactory.createEntityManager(map) - JPA MethodCreate a new application-managed EntityManager with the
 specified Map of properties.(properties);

or using the setPropertyEntityManager.setProperty(propertyName,value) - JPA MethodSet an entity manager property or hint. method:

  em.setPropertyEntityManager.setProperty(propertyName,value) - JPA MethodSet an entity manager property or hint.("javax.persistence.query.timeout", 6000);

For a named query definition - using the hintsjavax.persistence.NamedQuery.hints - JPA Annotation Attribute(Optional) Query properties and hints. element:

@NamedQueryjavax.persistence.NamedQuery - JPA AnnotationSpecifies a static, named query in the Java Persistence query language.(namejavax.persistence.NamedQuery.name - JPA Annotation Attribute(Required) The name used to refer to the query with the {@link EntityManager}
 methods that create query objects.="Country.findAll", queryjavax.persistence.NamedQuery.query - JPA Annotation Attribute(Required)
 The query string in the Java Persistence query language.="SELECT c FROM Country c",
    hintsjavax.persistence.NamedQuery.hints - JPA Annotation Attribute(Optional) Query properties and hints.={@QueryHintjavax.persistence.QueryHint - JPA AnnotationUsed to supply a query property or hint to the {@link NamedQuery} or {@link
 NamedNativeQuery} annotation.(name="javax.persistence.query.timeout",
    value="7000")})

For a specific query execution - using the setHintTypedQuery.setHint(hintName,value) - JPA MethodSet a query property or hint. method (before query execution):

  query.setHintTypedQuery.setHint(hintName,value) - JPA MethodSet a query property or hint.("javax.persistence.query.timeout", 8000);

A hint that is set in a global scope affects all the queries in that scope (unless it is overridden in a more local scope). For example, setting a query hint in an EntityManager affects all the queries that are created in that EntityManager (except queries with explicit setting of the same hint).

< Criteria Query API ^ Query API Query Structure >