Setting and Tuning of JPA Queries

The Queryjakarta.persistence.Query - JPA Interface Interface used to control query execution. and TypedQueryjakarta.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 List<X>. 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 List<X>.();

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 List<X>. since the setter methods in Queryjakarta.persistence.Query - JPA Interface Interface used to control query execution. and TypedQueryjakarta.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 EntityManagerjakarta.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 FlushModeTypejakarta.persistence.FlushModeType - JPA Enum Enumerates flush modes recognized by the EntityManager. enum, which has two values:

• AUTOjakarta.persistence.FlushModeType.AUTO - JPA Enum Constant (Default) Flushing to occur at query execution. - changes are flushed before query execution and on commit/flush.
• COMMITjakarta.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 EntityManagerjakarta.persistence.EntityManager - JPA Interface Interface used to interact with the persistence context. level as a default for all the queries in that EntityManagerjakarta.persistence.EntityManager - JPA Interface Interface used to interact with the persistence context. or at the level of a specific query, by overriding the default EntityManagerjakarta.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 {@linkplain FlushModeType flush mode} that applies to
 all objects contained in the persistence context.(FlushModeTypejakarta.persistence.FlushModeType - JPA Enum
 Enumerates flush modes recognized by the EntityManager..AUTOjakarta.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.(FlushModeTypejakarta.persistence.FlushModeType - JPA Enum
 Enumerates flush modes recognized by the EntityManager..AUTOjakarta.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.(LockModeTypejakarta.persistence.LockModeType - JPA Enum
 Enumerates the kinds of optimistic or pessimistic lock which
 may be obtained on an entity instance..PESSIMISTIC_WRITEjakarta.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 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.LockTimeoutException - JPA Exception Thrown 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.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 EntityManagerFactoryjakarta.persistence.EntityManagerFactory - JPA Interface Interface used to interact with the persistence unit, and to create new instances of EntityManager. - using the createEntityManagerFacotoryPersistence.createEntityManagerFactory(persistenceUnitName,properties) - JPA Static Method Create and return an EntityManagerFactory for the named persistence unit using the given properties.Persistence.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.Persistence.createEntityManagerFactory(persistenceUnitName,properties) - JPA Static Method
 Create and return an EntityManagerFactory for the named
 persistence unit, using the given properties.("pu", properties);

For an EntityManagerjakarta.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.EntityManagerFactory.createEntityManager(map) - JPA Method Create a new application-managed EntityManager with the given Map specifying property settings. 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.EntityManagerFactory.createEntityManager(map) - JPA Method
 Create a new application-managed EntityManager with
 the given Map specifying property settings.(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 hintsjakarta.persistence.NamedQuery.hints - JPA Annotation Attribute (Optional) Query properties and hints. element:

@NamedQueryjakarta.persistence.NamedQuery - JPA Annotation
 Declares a named query written in the Jakarta Persistence
 query language.(namejakarta.persistence.NamedQuery.name - JPA Annotation Attribute
 (Required) The name used to identify the query in calls to
 EntityManager#createNamedQuery.="Country.findAll", queryjakarta.persistence.NamedQuery.query - JPA Annotation Attribute
 (Required) The query string in the Jakarta Persistence
 query language.="SELECT c FROM Country c",
    hintsjakarta.persistence.NamedQuery.hints - JPA Annotation Attribute
 (Optional) Query properties and hints.={@QueryHintjakarta.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).