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 methods for setting and tuning query execution. To take effect, you must invoke these methods before running a query with 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..

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 let you define a result window, which is a portion of a large query result list. The setFirstResultjakarta.persistence.TypedQuery.setFirstResult(int)Set the position of the first result to retrieve. method specifies the starting point of the result window by defining how many results to skip from the beginning of the complete result list. The setMaxResultsjakarta.persistence.TypedQuery.setMaxResults(int)Set the maximum number of results to retrieve. method specifies the size of the result window. Any results beyond this maximum are ignored.

These methods are useful for implementing efficient result paging. For example, if each page displays pageSize results and pageId represents the page number (starting from 0 for the first page), the following code retrieves the results for a specific 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>  .();

You can invoke these methods in a single expression with getResultListjakarta.persistence.TypedQuery.getResultList()Execute a SELECT query and return the query results as a typed List<X> . because 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 by using an EntityManagerjakarta.persistence.EntityManagerInterface used to interact with the persistence context. instance, em, are visible to any code that uses em, even before the transaction is committed. However, these changes are not visible to users of other EntityManager instances. JPA implementations can easily make uncommitted changes visible for simple 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, any uncommitted database changes must be flushed to the database to be visible to the query.

The 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. - Flushes changes before query execution and on commit or flush.
• COMMITjakarta.persistence.FlushModeType.COMMITFlushing to occur at transaction commit. - Flushes changes only on an explicit commit or flush.

In most JPA implementations, the default is AUTO. In ObjectDB, the default is COMMIT, which is more efficient. The application can change the default mode at the EntityManagerjakarta.persistence.EntityManagerInterface used to interact with the persistence context. level, which applies to all queries in that EntityManagerjakarta.persistence.EntityManagerInterface used to interact with the persistence context., or at the individual query level, which overrides the 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 can significantly affect performance. Therefore, you should consider this issue when performance is important.

Lock mode (setLockMode)

ObjectDB uses automatic optimistic locking to prevent concurrent changes to entities by multiple users. JPA 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 to apply to all result objects that the query retrieves. For example, the following code 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>  .();

Note that when a query is executed with a pessimistic lock mode, it can fail and throw a LockTimeoutExceptionjakarta.persistence.LockTimeoutExceptionThrown by the persistence provider when a pessimistic locking conflict occurs that does not result in transaction rollback. if the lock cannot be acquired.

Query hints

Additional

You can apply additional settings to queries by using hints.

Supported query hints

ObjectDB supports the following query hints:

• "jakarta.persistence.query.timeout" - Sets the 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 the timeout is exceeded.
• "jakarta.persistence.lock.timeout" - Sets the maximum wait time for pessimistic locks when pessimistic locking of query results is enabled. For more information, see the Lock Timeout section.
• "objectdb.query-language" - Sets the query language to one of the following: "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 the language to "JPQL" is useful for enforcing portable JPA code with ObjectDB.
• "objectdb.result-fetch" - Sets the fetch mode for the query result to either "EAGER" (the default) or "LAZY". When LAZY is used, result entities are returned as references with no content. This mode can be useful when the shared L2 cache is enabled and entities might already be available in the cache.

Setting query hints (Scopes)

You can set query hints in the following scopes, from most global to most local:

For the entire persistence unit, use a persistence.xml property:

    <properties>
       <property name="jakarta.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 ., use the createEntityManagerFactoryjakarta.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("jakarta.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., use 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("jakarta.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, use 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, use 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="jakarta.persistence.query.timeout",
    value="7000")})

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

  query.setHintjakarta.persistence.TypedQuery.setHint(String,Object)Set a query property or hint.("jakarta.persistence.query.timeout", 8000);

A hint that is set in a global scope affects all queries in that scope unless it is overridden in a more local scope. For example, setting a query hint on an EntityManager affects all queries that are created by that EntityManager, except for queries that have an explicit setting for the same hint.