3.4.4.4. Executing JPQL Queries

This section describes the Query interface which is designed to execute JPQL queries at the ORM level. The reference to it may be obtained from the current EntityManager instance by calling createQuery() method. If the query is supposed to be used to load entities, we recommend calling createQuery() with the result type as a parameter. This will create a TypedQuery instance.

The methods of Query mainly correspond to the methods of the standard JPA javax.persistence.Query interface. Let’s have a look at the differences.

  • setView(), addView() – define a view which is used to load data.

  • getDelegate() – returns an instance of javax.persistence.Query, provided by the ORM implementation.

If a view is set for a query, then by default the query has FlushModeType.AUTO, which affects the case when the current persistence context contains changed entity instances: these instances will be saved to the database prior to the query execution. In other words, ORM first synchronizes the state of entities in the persistence context and in the database, and only after that runs the query. It guarantees that the query results contain all relevant instances, even if they have not been saved to the database explicitly yet. The downside of this is that you will have an implicit flush, i.e. execution of SQL update statements for all currently changed entity instances, which may affect performance.

If a query is executed without a view, then by default the query has FlushModeType.COMMIT, which means that the query will not cause a flush, and the query results will not respect the contents of the current persistence context.

In most cases ignoring the current persistence context is acceptable, and is a preferred behavior because it doesn’t lead to extra SQL updates. But there is the following issue when using views: if there is a changed entity instance in the persistence context, and you execute a query with a view and FlushModeType.COMMIT loading the same instance, the changes will be lost. That is why we use FlushModeType.AUTO by default when running queries with views.

You can also set flush mode explicitly using the setFlushMode() method of the Query interface. It will override the default settings described above.

Using DELETE FROM with soft-deleted entities

The JPQL DELETE FROM statement throws an exception if launched for the soft-deleted entity and the Soft Delete mode is on. Such statement is actually transformed to SQL which deletes all instances not marked for deletion. This confusing behavior is disabled by default and can be enabled using the cuba.enableDeleteStatementInSoftDeleteMode application property.

Query Hints
Since7.1

The Query.setHint() method allows you to add some hints to the generated SQL statements. The hints are usually used to specify how the query should use indexes or other database specifics. The framework defines the following constants which can be passed to this method as hint names:

  • QueryHints.SQL_HINT - the hint value is added after the generated SQL statement. Provide the full hint string here, including comment delimiters if any.

  • QueryHints.MSSQL_RECOMPILE_HINT - adds OPTION(RECOMPILE) SQL hint for MS SQL Server database. The hint value is ignored.

When working with DataManager, query hints can be provided to the query using LoadContext.setHint() method.