Setting and Tuning of JPA Queries

The Queryjakarta.persistence.QueryInterface used to control query execution. and TypedQueryjakarta.persistence.TypedQueryInterface 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 getResultListjakarta.persistence.TypedQuery.getResultList()Execute a SELECT query and return the query results as a typed List<X>. or getSingleResultjakarta.persistence.TypedQuery.getSingleResult()Execute a SELECT query that returns a single result..

This page covers the following topics:

Result Range (setFirstResult, setMaxResults)Flush Mode (setFlushMode)Lock Mode (setLockMode)Query Hints

Result Range (setFirstResult, setMaxResults)

The setFirstResultjakarta.persistence.TypedQuery.setFirstResult(int)Set the position of the first result to retrieve. and setMaxResultsjakarta.persistence.TypedQuery.setMaxResults(int)Set 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 setFirstResultjakarta.persistence.TypedQuery.setFirstResult(int)Set 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 setMaxResultsjakarta.persistence.TypedQuery.setMaxResults(int)Set 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.setFirstResultjakarta.persistence.TypedQuery.setFirstResult(int)Set the position of the first result to retrieve.(pageIx * pageSize)
           .setMaxResultsjakarta.persistence.TypedQuery.setMaxResults(int)Set the maximum number of results to retrieve.(pageSize)
           .getResultListjakarta.persistence.TypedQuery.getResultList()Execute a SELECT query and return the query results as a typed List<X>.();

These methods can be invoked in a single expression with getResultListjakarta.persistence.TypedQuery.getResultList()Execute a SELECT query and return the query results as a typed List<X>. since the setter methods in Queryjakarta.persistence.QueryInterface used to control query execution. and TypedQueryjakarta.persistence.TypedQueryInterface 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 EntityManagerjakarta.persistence.EntityManagerInterface 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 findjakarta.persistence.EntityManager.find(Class,Object)Find 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 FlushModeTypejakarta.persistence.FlushModeTypeEnumerates flush modes recognized by the EntityManager. enum, which has two values:

AUTOjakarta.persistence.FlushModeType.AUTO(Default) Flushing to occur at query execution. - changes are flushed before query execution and on commit/flush.
COMMITjakarta.persistence.FlushModeType.COMMITFlushing 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 EntityManagerjakarta.persistence.EntityManagerInterface used to interact with the persistence context. level as a default for all the queries in that EntityManagerjakarta.persistence.EntityManagerInterface used to interact with the persistence context. or at the level of a specific query, by overriding the default EntityManagerjakarta.persistence.EntityManagerInterface used to interact with the persistence context. setting:

  // Enable query time flush at the EntityManager level:
  em.setFlushModejakarta.persistence.EntityManager.setFlushMode(FlushModeType)Set the flush mode that applies to all objects contained in the persistence context.(FlushModeTypejakarta.persistence.FlushModeTypeEnumerates flush modes recognized by the EntityManager..AUTOjakarta.persistence.FlushModeType.AUTO(Default) Flushing to occur at query execution.);

  // Enable query time flush at the level of a specific query:
  query.setFlushModejakarta.persistence.TypedQuery.setFlushMode(FlushModeType)Set the flush mode type to be used for the query execution.(FlushModeTypejakarta.persistence.FlushModeTypeEnumerates flush modes recognized by the EntityManager..AUTOjakarta.persistence.FlushModeType.AUTO(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 setLockModejakarta.persistence.TypedQuery.setLockMode(LockModeType)Set 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.setLockModejakarta.persistence.TypedQuery.setLockMode(LockModeType)Set the lock mode type to be used for the query execution.(LockModeTypejakarta.persistence.LockModeTypeEnumerates the kinds of optimistic or pessimistic lock which may be obtained on an entity instance..PESSIMISTIC_WRITEjakarta.persistence.LockModeType.PESSIMISTIC_WRITEPessimistic write lock.)
           .getResultListjakarta.persistence.TypedQuery.getResultList()Execute a SELECT query and return the query results as a typed List<X>.();

Notice that when a query is executed with a requested pessimistic lock mode it could fail if locking fails, throwing a LockTimeoutExceptionjakarta.persistence.LockTimeoutExceptionThrown by the persistence provider when a 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 QueryTimeoutExceptionjakarta.persistence.QueryTimeoutExceptionThrown 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 EntityManagerFactoryjakarta.persistence.EntityManagerFactoryInterface used to interact with the persistence unit, and to create new instances of EntityManager. - using the createEntityManagerFacotoryjakarta.persistence.Persistence.createEntityManagerFactory(String,Map)Create 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.createEntityManagerFactoryjakarta.persistence.Persistence.createEntityManagerFactory(String,Map)Create and return an EntityManagerFactory for the named persistence unit, using the given properties.("pu", properties);

For an EntityManagerjakarta.persistence.EntityManagerInterface used to interact with the persistence context. - using the createEntityManagerjakarta.persistence.EntityManagerFactory.createEntityManager(Map)Create a new application-managed EntityManager with the given Map<K,V> specifying property settings. method:

  Map<String,Object> properties = new HashMap();
  properties.put("javax.persistence.query.timeout", 5000);
  EntityManager em = emf.createEntityManagerjakarta.persistence.EntityManagerFactory.createEntityManager(Map)Create a new application-managed EntityManager with the given Map<K,V> specifying property settings.(properties);

or using the setPropertyjakarta.persistence.EntityManager.setProperty(String,Object)Set an entity manager property or hint. method:

  em.setPropertyjakarta.persistence.EntityManager.setProperty(String,Object)Set an entity manager property or hint.("javax.persistence.query.timeout", 6000);

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

@NamedQueryjakarta.persistence.NamedQueryDeclares a named query written in the Jakarta Persistence query language.(namejakarta.persistence.NamedQuery.name(Required) The name used to identify the query in calls to EntityManager.createNamedQuery.="Country.findAll", queryjakarta.persistence.NamedQuery.query(Required) The query string in the Jakarta Persistence query language.="SELECT c FROM Country c",
    hintsjakarta.persistence.NamedQuery.hints(Optional) Query properties and hints.={@QueryHintjakarta.persistence.QueryHintUsed to supply a query property or hint to the NamedQuery or NamedNativeQuery annotation.(name="javax.persistence.query.timeout",
    value="7000")})

For a specific query execution - using the setHintjakarta.persistence.TypedQuery.setHint(String,Object)Set a query property or hint. method (before query execution):

  query.setHintjakarta.persistence.TypedQuery.setHint(String,Object)Set 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 >