Setting and Tuning of JPA Queries

The Queryjavax.persistence.QueryJPA interfaceInterface used to control query execution.See JavaDoc Reference Page... and TypedQueryjavax.persistence.TypedQueryJPA interfaceInterface used to control the execution of typed queries.See JavaDoc Reference Page... interfaces define various setting and tuning methods that may affect query execution if invoked before a query is run using getResultListgetResultList()TypedQuery's methodExecute a SELECT query and return the query results as a typed List.See JavaDoc Reference Page... or getSingleResultgetSingleResult()TypedQuery's methodExecute a SELECT query that returns a single result.See JavaDoc Reference Page....

Result Range (setFirstResult, setMaxResults)

The setFirstResultsetFirstResult(startPosition)TypedQuery's methodSet the position of the first result to retrieve.See JavaDoc Reference Page... and setMaxResultssetMaxResults(maxResult)TypedQuery's methodSet the maximum number of results to retrieve.See JavaDoc Reference Page... methods enable defining a result window that exposes a portion of a large query result list (hiding anything outside that window). The setFirstResultsetFirstResult(startPosition)TypedQuery's methodSet the position of the first result to retrieve.See JavaDoc Reference Page... 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 setMaxResultssetMaxResults(maxResult)TypedQuery's methodSet the maximum number of results to retrieve.See JavaDoc Reference Page... 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.setFirstResultsetFirstResult(startPosition)TypedQuery's methodSet the position of the first result to retrieve.See JavaDoc Reference Page...(pageIx * pageSize)
           .setMaxResultssetMaxResults(maxResult)TypedQuery's methodSet the maximum number of results to retrieve.See JavaDoc Reference Page...(pageSize)
           .getResultListgetResultList()TypedQuery's methodExecute a SELECT query and return the query results
 as a typed List.See JavaDoc Reference Page...();

These methods can be invoked in a single expression with getResultListgetResultList()TypedQuery's methodExecute a SELECT query and return the query results as a typed List.See JavaDoc Reference Page... since the setter methods in Queryjavax.persistence.QueryJPA interfaceInterface used to control query execution.See JavaDoc Reference Page... and TypedQueryjavax.persistence.TypedQueryJPA interfaceInterface used to control the execution of typed queries.See JavaDoc Reference Page... 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.EntityManagerJPA interfaceInterface used to interact with the persistence context.See JavaDoc Reference Page... 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 findfind(entityClass, primaryKey)EntityManager's methodFind by primary key.See JavaDoc Reference Page.... 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.FlushModeTypeJPA enumFlush mode setting.See JavaDoc Reference Page... enum, which has two values:

• AUTOFlushModeType.AUTOenum constant(Default) Flushing to occur at query execution.See JavaDoc Reference Page... - changes are flushed before query execution and on commit/flush.
• COMMITFlushModeType.COMMITenum constantFlushing to occur at transaction commit.See JavaDoc Reference Page... - 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.EntityManagerJPA interfaceInterface used to interact with the persistence context.See JavaDoc Reference Page... level as a default for all the queries in that EntityManagerjavax.persistence.EntityManagerJPA interfaceInterface used to interact with the persistence context.See JavaDoc Reference Page... or at the level of a specific query, by overriding the default EntityManagerjavax.persistence.EntityManagerJPA interfaceInterface used to interact with the persistence context.See JavaDoc Reference Page... setting:

  // Enable query time flush at the EntityManager level:
  em.setFlushModesetFlushMode(flushMode)EntityManager's methodSet the flush mode that applies to all objects contained
 in the persistence context.See JavaDoc Reference Page...(FlushModeTypejavax.persistence.FlushModeTypeJPA enumFlush mode setting.See JavaDoc Reference Page....AUTOFlushModeType.AUTOenum constant(Default) Flushing to occur at query execution.See JavaDoc Reference Page...);

  // Enable query time flush at the level of a specific query:
  query.setFlushModesetFlushMode(flushMode)TypedQuery's methodSet the flush mode type to be used for the query execution.See JavaDoc Reference Page...(FlushModeTypejavax.persistence.FlushModeTypeJPA enumFlush mode setting.See JavaDoc Reference Page....AUTOFlushModeType.AUTOenum constant(Default) Flushing to occur at query execution.See JavaDoc Reference Page...);

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 setLockModesetLockMode(lockMode)TypedQuery's methodSet the lock mode type to be used for the query execution.See JavaDoc Reference Page... 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.setLockModesetLockMode(lockMode)TypedQuery's methodSet the lock mode type to be used for the query execution.See JavaDoc Reference Page...(LockModeTypejavax.persistence.LockModeTypeJPA enumLock modes can be specified by means of passing a LockModeType
 argument to one of the javax.persistence.EntityManager methods that take locks
 (lock, find, or refresh) or
 to the Query.setLockMode() or
 TypedQuery.setLockMode() method.See JavaDoc Reference Page....PESSIMISTIC_WRITELockModeType.PESSIMISTIC_WRITEenum constantPessimistic write lock.See JavaDoc Reference Page...)
           .getResultListgetResultList()TypedQuery's methodExecute a SELECT query and return the query results
 as a typed List.See JavaDoc Reference Page...();

Notice that when a query is executed with a requested pessimistic lock mode it could fail if locking fails, throwing a LockTimeoutExceptionjavax.persistence.LockTimeoutExceptionJPA exceptionThrown by the persistence provider when an pessimistic locking conflict occurs that does not result in transaction rollback.See JavaDoc Reference Page....

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.QueryTimeoutExceptionJPA exceptionThrown by the persistence provider when a query times out and only the statement is rolled back.See JavaDoc Reference Page... 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.EntityManagerFactoryJPA interfaceInterface used to interact with the entity manager factory for the persistence unit.See JavaDoc Reference Page... - using the createEntityManagerFacotorycreateEntityManagerFactory(persistenceUnitName, properties)Persistence's static methodCreate and return an EntityManagerFactory for the named persistence unit using the given properties.See JavaDoc Reference Page... method:

  Map<String,Object> properties = new HashMap();
  properties.put("javax.persistence.query.timeout", 4000);
  EntityManagerFactory emf =
      Persistence.createEntityManagerFactorycreateEntityManagerFactory(persistenceUnitName, properties)Persistence's static methodCreate and return an EntityManagerFactory for the named persistence unit
 using the given properties.See JavaDoc Reference Page...("pu", properties);

For an EntityManagerjavax.persistence.EntityManagerJPA interfaceInterface used to interact with the persistence context.See JavaDoc Reference Page... - using the createEntityManagercreateEntityManager(map)EntityManagerFactory's methodCreate a new application-managed EntityManager with the specified Map of properties.See JavaDoc Reference Page... method:

  Map<String,Object> properties = new HashMap();
  properties.put("javax.persistence.query.timeout", 5000);
  EntityManager em = emf.createEntityManagercreateEntityManager(map)EntityManagerFactory's methodCreate a new application-managed EntityManager with the 
 specified Map of properties.See JavaDoc Reference Page...(properties);

or using the setPropertysetProperty(propertyName, value)EntityManager's methodSet an entity manager property or hint.See JavaDoc Reference Page... method:

  em.setPropertysetProperty(propertyName, value)EntityManager's methodSet an entity manager property or hint.See JavaDoc Reference Page...("javax.persistence.query.timeout", 6000);

For a named query definition - using the hintsNamedQuery.hintsannotation element(Optional) Query properties and hints.See JavaDoc Reference Page... element:

@NamedQueryjavax.persistence.NamedQueryJPA annotationSpecifies a static, named query in the Java Persistence query language.See JavaDoc Reference Page...(nameNamedQuery.nameannotation element(Required) The name used to refer to the query with the EntityManager 
 methods that create query objects.See JavaDoc Reference Page...="Country.findAll", queryNamedQuery.queryannotation element(Required) 
 The query string in the Java Persistence query language.See JavaDoc Reference Page...="SELECT c FROM Country c",
    hintsNamedQuery.hintsannotation element(Optional) Query properties and hints.See JavaDoc Reference Page...={@QueryHintjavax.persistence.QueryHintJPA annotationUsed to supply a query property or hint to the NamedQuery or NamedNativeQuery annotation.See JavaDoc Reference Page...(name="javax.persistence.query.timeout", value="7000")})

For a specific query execution - using the setHintsetHint(hintName, value)TypedQuery's methodSet a query property or hint.See JavaDoc Reference Page... method (before query execution):

  query.setHintsetHint(hintName, value)TypedQuery's methodSet a query property or hint.See JavaDoc Reference Page...("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).