3.4.4.4. Executing JPQL Queries

The Query interface is designed to execute JPQL queries. 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 us have a look at the differences.

  • setParameter() – sets a value to a query parameter. If the value is an entity instance, implicitly converts the instance into its identifier. For example:

    Customer customer = ...;
    TypedQuery<Order> query = entityManager.createQuery(
        "select o from sales_Order o where o.customer.id = ?1", Order.class);
    query.setParameter(1, customer);

    Note that the entity is passed as a parameter while comparison in the query is done using identifier.

    A variant of the method with implicitConversions = false does not perform such conversion.

  • 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.

Query Hints

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.