We already know that the EntityManager.find() method is used to retrieve entities by their primary key. However, there may be many situations where you need to search by other columns. The Query API allows us to create and execute custom queries to retrieve either one or a collection of entities.
In JPA 2, Queries are represented by two interfaces –
- javax.persistence.Query
- javax.persistence.TypedQuery
The Query interface should be used only when the query result type is unknown. So it returns an untyped result. But when the result type is known, you should use the TypedQuery interface.
Before discussing Query API, let’s first understand Java Persistence query language (JPQL) briefly.
JPQL
JPQL is a query language that allows you to write queries based on your entity model. It is quite similar to SQL query. In JPQL, instead of table and column names we use entity class names and property names. Please remember, in JPQL, class name and property name is case sensitive, but the rest part is case-insensitive. The main disadvantage of JPQL is – during code refactoring of your domains, if you rename the entity class, you have to change the name to each and every JPQL query.
Let’s understand the JPQL with an example. Consider we have following Entity –
In SQL, to get all rows from USER_DETAILS table where user name starts with “U”, you can write something like below –
select * from demo.user_details where user_name like 'U%';
Equivalent query in JPQL would be –
select e from User e where e.userName like 'U%'
Here e is just an identification variable to refer to the User entity. Often we call this identification variable an alias. We use the alias in the JPQL query to refer to the corresponding entity.
I hope you get a basic idea on JPQL. Let’s get back to our Query API discussion.
Create Query
The query API supports two types of queries –
- Dynamic query
- Named query
Dynamic query
We can use the createQuery() or createNativeQuery() method and pass the query string directly to build a query. In case of createQuery, we pass the JPQL statement.
Query query1 = entityManager.createQuery("select e from User e");
TypedQuery<User> query2 = entityManager.createQuery("select e from User e", User.class);
But in case of createNativeQuery, we pass the native SQL statement. The createNativeQuery() method returns an untyped Query, not the TypedQuery.
Query query1 = entityManager.createNativeQuery("select * from demo.user_details", User.class);
Named Query
In the case of named query, we define the queries on the Entity class itself and associate that query with a unique name. This can help us to easily locate all the queries related to an entity. We can use @NamedQuery and @NamedNativeQuery annotation to declare the name queries.
As the query name must be unique, you can use entity name as a prefix to avoid collision.
@Entity
@Table(name = "USER_DETAILS", schema = "demo")
@NamedQuery(name = "user.findall", query = "select e from User e")
public class User {
…
}
To create a Query object based on this query, you have to use createNamedQuery() method.
TypedQuery<User> query = entityManager.createNamedQuery("user.findall", User.class);
Similarly, you can use a named native query. As this is native SQL, please specify the result class.
@Entity
@Table(name = "USER_DETAILS", schema = "demo")
@NamedNativeQuery(name = "user.findall", query = "select * from demo.user_details", resultClass = User.class)
public class User {
…
}
TypedQuery<User> query = entityManager.createNamedQuery("user.findall", User.class);
Query parameter
In many cases you may want to execute the same query multiple times with different parameter values. For that purpose, you can use place holders (bind variable) in the query string and later during runtime, you can substitute the actual value using the setParameter() method. This process is called parameter binding.
There are two options available for parameter binding –
- named parameters
- positional parameters
In the case of named parameters, we use colon (:) followed by placeholder names.
TypedQuery<User> query = entityManager.createQuery("select e from User e where e.userName = :aName", User.class);
query.setParameter("aName", "User1");
But in the case of positional parameters, we use question mark (?) followed by a positive index number.
TypedQuery<User> query = entityManager.createQuery("select e from User e where e.userName = ?1", User.class);
query.setParameter(1, "User1");
Execute query
If you are running select query, you can use two methods provided by both Query and TypedQuery interface to get the result –
- getSingleResult() : when exactly one result object is expected.
- getResultList() : when one or multiple result objects are expected.
TypedQuery<User> query = entityManager.createQuery("select e from User e where e.userId = :id", User.class);
query.setParameter("id", 68L);
User user = query.getSingleResult();
List<User> userList = query.getResultList();
If you are running update or delete query, you can use executeUpdate() method. Please remember, this method must be executed within an active transaction.
EntityTransaction transaction = entityManager.getTransaction();
transaction.begin();
Query query = entityManager.createQuery("delete from User e where e.userId = :id");
query.setParameter("id", 44L);
query.executeUpdate();
transaction.commit();
Result Range
If you are dealing with large result sets, probably you may want to implement pagination. In case of pagination, you display a limited subset at a time and enable the user to navigate to the next or previous page. The Query and TypedQuery interface support this by providing two methods –
- setFirstResult() : specify how many rows you want to skip
- setMaxResults() : limit result set size
So, if you want to show 10 results after skipping first 20 rows, you can write –
TypedQuery<User> query = entityManager.createQuery("select e from User e order by userId", User.class);
query.setFirstResult(20);
query.setMaxResults(10);
List<User> userList = query.getResultList();
Criteria API
We have already seen how to write string based JPQL queries. Now, consider a website where you have multiple search fields and those fields are optional. Users may choose to fill in one or multiple fields to search.
If you use JPQL, you have to write complex logic to build a dynamic search query based on fields that a user fills at runtime. A better solution would be to write a criteria query to eliminate this complexity.
Also, Criteria queries are type-safe way to express a query. So, errors can be detected early at compile time.
Criteria based query
To write criteria queries, the very first thing you need is CriteriaBuilderCriteriaBuildergetCriteriaBuilder() method of the EntityManager.
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
The next step is to obtain a CriteriaQuery by calling createQuery() method of the CriteriaBuilder.
CriteriaQuery<User> query = builder.createQuery(User.class);
The type of CriteriaQuery object should be the same as the result type returned from the query. In our example, the expected result type is User entity.
Next, create a query root by calling the from() method on the CriteriaQuery instance. Root entity is nothing but the same entity that you specify after FROM clause of JPQL query.
Root<User> root = query.from(User.class);
To filter the criteria query Result, you can specify filter criteria using where() method.
query.where(builder.equal(root.get("userName"), "User1"));
In our search example, we have three fields and based on the fields that a user fills at runtime, we have to construct the filter. In such situations, we can follow below approach –
List<Predicate> predicates = new ArrayList<>();
if (userIdValue != null) {
predicates.add(builder.and(builder.equal(root.get("userId"), userIdValue)));
}
if (userNameValue != null) {
predicates.add(builder.and(builder.equal(root.get("userName"), userNameValue)));
}
if (email != null) {
predicates.add(builder.and(builder.equal(root.get("email"), email)));
}
query.where(predicates.toArray(new Predicate[0]));
If you want, you can also apply order by.
query.orderBy(builder.asc(root.get("userName")), builder.desc(root.get("email")));
Finally get the result based on the criteria query.
List<User> results = entityManager.createQuery(query).getResultList();
That’s it for now. Hope you have enjoyed this tutorial. If you have any doubt, please ask in the comment section. I will try to answer that as soon as possible. Till then, bye bye.