Show TOC

Background documentationQueries Locate this document in the navigation structure

 

Basic Query Operations

The entity manager's find and getReference methods allow you to locate and manage entities by their primary key. This is convenient and frequently used, but it is not sufficient for most applications. Often you want to search for entities based on the their data. Thus you need a way to express your search criteria, and a mechanism to execute a request and retrieve results. This is what the “Java Persistence Query Language” (JPQL) and the query objects are for.

A query in JPA is implemented as a separate object of type javax.persistence.Query that you create via a factory method of the entity manager. You can choose between two types of JPQL queries:

  • Static queries (also called “named queries”)

  • Dynamic queries

Note Note

The JPQL queries concern only the application's object model. Dealing with the object-relational side, finding the database table name to which the entity is mapped, determining the names of the columns that correspond to the persistent fields, and translating the query into proper SQL, are tasks of the JPA runtime.

End of the note.
JPQL Examples

JPQL queries are similar to SQL queries. The main difference is that using JPQL, you do not express a query in terms of database tables and columns, but in terms of entities and attributes of the application model.

The following query selects all instances of type Employee:

Syntax Syntax

  1. SELECT e FROM Employee e
End of the code.

With the FROM clause, you state that you want to select from the entity Employee, and you give it the “identification (ID) variable” e. This ID variable must be used to refer to the Employee in the other parts of the JPQL query.

In the SELECT clause, you declare that you want to select all instances of type e, that is, Employee.

The result of this query is a list of all Employee instances.

If you want to limit your result list to entities that meet some search criteria, you add a WHERE clause to your query. If your entity Employee has a persistent field lastName, and if you want to select only those entity instances where lastName has a given value, you can use a parameterized query. Typically, you should specify a particular value for the parameter only upon query execution.

Syntax Syntax

  1. SELECT e FROM Employee e WHERE e.lastName = :name
End of the code.
Creating a Query Object for a Static Query — the createNamedQuery Method

Static queries are defined at design time of the application using the @NamedQuery annotation, which can be placed at the class definition of any entity within the application model. Thus, you give the query a name by which it can be referred to in the application and you specify the corresponding JPQL query string.

Syntax Syntax

  1. @NamedQuery(name="findAllEmployees", query="SELECT e FROM Employee e")
    @Entity
    public class Employee {
    	// ...
    }
End of the code.

To define multiple static queries on the same entity class, you use the @NamedQuery annotation inside a @NamedQueries annotation:

Syntax Syntax

  1. @NamedQueries({
    	@NamedQuery(name="findAllEmployees", query="SELECT e FROM Employee e"),
    	@NamedQuery(name="findEmployeeByLastName", query="SELECT e FROM Employee e WHERE e.lastName= :name")
    })
    @Entity
    public class Employee {
    	// ...
    }
End of the code.

Recommendation Recommendation

We recommend that you use named queries when you develop your application, as this allows the JPA runtime to process the queries more efficiently.

End of the recommendation.

You use the entity manager's createNamedQuery method to create an executable Query instance for a predefined named query. You execute the query using the query's getResultList and getSingleResult methods.

Syntax Syntax

  1. @PersistenceContext
    EntityManager em;
    // ...
    public Employee findEmployee(String lastName) {
    	Query query = em.createNamedQuery("findEmployeeByLastName");
    	query.setParameter("name", lastName);
    
    	return (Employee)getSingleResult();
    }
End of the code.
Creating a Query Object for a Dynamic Query — the createQuery Method

If a query cannot be specified statically at design time, it can be constructed at runtime using the entity manager's createQuery method.

Syntax Syntax

  1. @PersistenceContext
    EntityManager em;
    // ...
    public List loadAllEntitiesOfSomeKind(String EntityName) {
    	String jpql = "select from " + entityName + " e";
    
    	return em.createQuery(jpql).getResultList();
    }
End of the code.
Advanced Query Operations
Creating a Query Object for a Native Query — the createNativeQuery Method

Typically, a JPA-based application is portable across different database platforms. The JPQL offers only a limited feature set that is covered by most database platforms. However, not all applications can be portable. Often, an application is written for a particular database which may be given by an external constraint or chosen intentionally, and the application should make use of the functionality and performance of the particular database offers. With the createNativeQuery method, JPA allows you to create queries based on vendor-specific SQL strings in addition to the normal JPQL queries.

Depending on the return values you expect from the query, you can choose between the following options of the entity manager interface for that purpose:

  • Query createNativeQuery(String sqlString, Class resultClass) — you can use the default mapping of the result set when you return instances only of a single entity class, and if the names of the column aliases in your SELECT statement are the same as the names of the persistent fields or properties.

  • Query createNativeQuery(String sqlString, String resultSetMapping) — using the @SqlResultSetMapping annotation, and thus declaring the result set mapping explicitly, you give a name to the result set mapping by which you can refer to it when creating a native query.

  • Query createNativeQuery(String sqlString) — if you do not expect a result of the query, for example, with the INSERT and UPDATE statements, you can pass only the SQL string as an argument to the createNativeQuery method.

Note Note

The createNativeQuery methods allow you to create a query object for a dynamic native query. Static (or named) native queries are also available and are preferable to use. However, there is no dedicated API method to create a query object for a static native query. This limitation can be overcome by using the method createNamedQuery for both JPQL and native queries. To declare a static native query, you have to annotate your entity class with @NamedNativeQuery instead.

End of the note.
Automatic Database Synchronization before Query Execution — the Flush Mode

To make the changes performed on the entities managed in the persistence context visible in query results, by default, all changes are flushed to the database before query execution. This may cause unwanted locks to be held on the database. If the query execution does not rely on changes performed in the persistence context, the flush before the query can be suppressed using the query's setFlushMode method.

For more information, refer to the Java Persistence API specification.