Setting and Tuning of JPA Queries

The Queryjavax.persistence.Query - JPA Interface Interface used to control query execution. and TypedQueryjavax.persistence.TypedQuery - JPA Interface Interface 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 Method Execute a SELECT query and return the query results as a typed List. or getSingleResultTypedQuery.getSingleResult() - JPA Method Execute a SELECT query that returns a single result..

Result Range (setFirstResult, setMaxResults)

The setFirstResultTypedQuery.setFirstResult(startPosition) - JPA Method Set the position of the first result to retrieve. and setMaxResultsTypedQuery.setMaxResults(maxResult) - JPA Method 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 setFirstResultTypedQuery.setFirstResult(startPosition) - JPA Method 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 setMaxResultsTypedQuery.setMaxResults(maxResult) - JPA Method 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.setFirstResultTypedQuery.setFirstResult(startPosition) - JPA Method
 Set the position of the first result to retrieve.(pageIx * pageSize)
           .setMaxResultsTypedQuery.setMaxResults(maxResult) - JPA Method
 Set the maximum number of results to retrieve.(pageSize)
           .getResultListTypedQuery.getResultList() - JPA Method
 Execute 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 Method Execute a SELECT query and return the query results as a typed List. since the setter methods in Queryjavax.persistence.Query - JPA Interface Interface used to control query execution. and TypedQueryjavax.persistence.TypedQuery - JPA Interface Interface 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 Interface Interface 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 Method 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 FlushModeTypejavax.persistence.FlushModeType - JPA Enum Flush 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 Constant Flushing 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 Interface Interface used to interact with the persistence context. level as a default for all the queries in that EntityManagerjavax.persistence.EntityManager - JPA Interface Interface used to interact with the persistence context. or at the level of a specific query, by overriding the default EntityManagerjavax.persistence.EntityManager - JPA Interface Interface used to interact with the persistence context. setting:

  // Enable query time flush at the EntityManager level:
  em.setFlushModeEntityManager.setFlushMode(flushMode) - JPA Method
 Set the flush mode that applies to all objects contained
 in the persistence context.(FlushModeTypejavax.persistence.FlushModeType - JPA Enum
 Flush 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 Method
 Set the flush mode type to be used for the query execution.(FlushModeTypejavax.persistence.FlushModeType - JPA Enum
 Flush 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 Method 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.setLockModeTypedQuery.setLockMode(lockMode) - JPA Method
 Set the lock mode type to be used for the query execution.(LockModeTypejavax.persistence.LockModeType - JPA Enum
 Lock 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 Query.setLockMode() or
 TypedQuery#setLockMode TypedQuery.setLockMode() method..PESSIMISTIC_WRITEjavax.persistence.LockModeType.PESSIMISTIC_WRITE - JPA Enum Constant
 Pessimistic write lock.)
           .getResultListTypedQuery.getResultList() - JPA Method
 Execute 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 Exception Thrown 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 Exception Thrown 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 Interface Interface used to interact with the entity manager factory for the persistence unit. - using the createEntityManagerFacotoryPersistence.createEntityManagerFactory(persistenceUnitName,properties) - JPA Static Method 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.createEntityManagerFactoryPersistence.createEntityManagerFactory(persistenceUnitName,properties) - JPA Static Method
 Create and return an EntityManagerFactory for the named persistence unit
 using the given properties.("pu", properties);

For an EntityManagerjavax.persistence.EntityManager - JPA Interface Interface used to interact with the persistence context. - using the createEntityManagerEntityManagerFactory.createEntityManager(map) - JPA Method Create 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 Method
 Create a new application-managed EntityManager with the
 specified Map of properties.(properties);

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

  em.setPropertyEntityManager.setProperty(propertyName,value) - JPA Method
 Set 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 Annotation
 Specifies 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 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 Annotation
 Used 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 setHintTypedQuery.setHint(hintName,value) - JPA Method Set a query property or hint. method (before query execution):

  query.setHintTypedQuery.setHint(hintName,value) - JPA Method
 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).