Spring Data JPA Tutorial: Creating Database Queries With Named Queries

The previous part of this tutorial described how we can create database queries with the @Query annotation.

This tutorial has now covered two of the three methods which we can use to create query methods with Spring Data JPA. This blog post describes the last method. We will learn to create database queries by using named queries.

We will also implement a search function that has two requirements:

• It must return todo entries whose title or description contains the given search term.
• The search must be case-insensitive.

Let’s get started.

Using Named Queries With Spring Data JPA

Before we can implement our search function, we have to understand how we can use named queries with Spring Data JPA. In other words, we have to find the answers to these questions:

1. How can we create named queries?
2. How can we create the query methods that invokes our named queries?

Let’s start by finding out the answer to the first question.

Creating Named Queries

We can specify named queries with Spring Data JPA by using a properties file, annotations, or the orm.xml file.

Before can take a closer look at these methods, we have to learn a few rules that we must follow when we are specifying the names of our named queries. These rules are:

• If we want to use the default naming strategy of Spring Data JPA, we have to specify the name of the named query by using this syntax: [entity class name].[name of the invoked query method].
• If we want to use some other syntax, we have to configure the name of named query when we create the query method that invokes it. We can do this by using the name attribute of the @Query annotation.

We are now ready to create named queries with Spring Data JPA. Let’s start by adding our named queries into a properties file.

Using a Properties File

We can declare named queries by adding them into the jpa-named-queries.properties file that is found from the META-INF folder of our classpath.

We can declare a new named query by following these steps:

1. Set the name of the named query as the name of the property.
2. Set the invoked query as the value of the property.

In other words, our properties file must use the following format:

1name=query

Example:

We want to create a named query whose name is ‘Todo.findByTitleIs’. It returns all todo entries whose title is ’title’.

If we want to use JPQL, we have to add the following line into our properties file:

1Todo.findByTitleIs=SELECT t FROM Todo t WHERE t.title = 'title'

If we want to use SQL, we have to add the following line into our properties file:

1Todo.findByTitleIs=SELECT * FROM todos t WHERE t.title = 'title'

Let’s move on and find out how we can declare named queries by using annotations.

Using Annotations

We can declare named queries by annotating our entities with the following annotations:

• If we want to create a JPQL query, we have to annotate our entity with the @NamedQuery annotation.
• If we want to create a SQL query, we have to annotate our entity with the @NamedNativeQuery annotation.

Example:

We want to create a named query whose name is ‘Todo.findByTitleIs’. It returns all todo entries whose title is ’title’.

If we want to create a JPQL query, we must follow these steps:

1. Annotate the entity with the @NamedQuery annotation.
2. Set the name of the named query (Todo.findByTitleIs) as the value of the @NamedQuery annotation’s name attribute.
3. Set the JPQL query (SELECT t FROM Todo t WHERE t.title = ’title’) as the value of the @NamedQuery annotation’s query attribute.

The relevant part of our entity looks as follows:

 1import javax.persistence.Entity;
 2import javax.persistence.NamedQuery;
 3import javax.persistence.Table;
 4
 5@Entity
 6@NamedQuery(name = "Todo.findByTitleIs",
 7        query = "SELECT t FROM Todo t WHERE t.title = 'title'"
 8)
 9@Table(name = "todos")
10final class Todo {
11
12}

If we want to create a SQL query, we must follow these steps:

1. Annotate the entity with the @NamedNativeQuery annotation.
2. Set the name of the named query (Todo.findByTitleIs) as the value of the @NamedNativeQuery annotation’s name attribute.
3. Set the SQL query (SELECT * FROM todos t WHERE t.title = ’title’) as the value of the @NamedNativeQuery annotation’s name attribute.
4. Set the returned entity class (Todo.class) as the value of the of the @NamedNativeQuery annotation’s resultClass attribute.

The relevant part of our entity looks as follows:

 1import javax.persistence.Entity;
 2import javax.persistence.NamedNativeQuery;
 3import javax.persistence.Table;
 4
 5@Entity
 6@NamedNativeQuery(name = "Todo.findByTitleIs”,
 7        query="SELECT * FROM todos t WHERE t.title = 'title'",
 8        resultClass = Todo.class
 9)
10@Table(name = "todos")
11final class Todo {
12
13}

Let’s find out how we can create named queries by using the orm.xml file.

Using the orm.xml File

We can declare named queries by adding them into the orm.xml file that is found from the META-INF folder of our classpath. We have to use one of these two XML elements:

• If we want to create a JPQL query, we have to use the named-query element.
• If we want to create a SQL query, we have to use the named-native-query element.

Example:

We want to create a named query whose name is ‘Todo.findByTitleIs’. It returns all todo entries whose title is ’title’.

If we want to create a JPQL query, we must follow these steps:

1. Add a named-query element into the orm.xml file.
2. Set the name of the named query (Todo.findByTitleIs) as the value of the named-query element’s name attribute.
3. Add a query element as the child of the named-query element and set the invoked JPQL query (SELECT t FROM Todo t WHERE t.title = ’title’) as the value of the query element.

The relevant part of the orm.xml file looks as follows:

 1<?xml version="1.0" encoding="UTF-8"?>
 2<entity-mappings
 3        xmlns="http://java.sun.com/xml/ns/persistence/orm"
 4        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 5        xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm http://java.sun.com/xml/ns/persistence/orm_2_0.xsd"
 6        version="2.0">
 7
 8    <named-query name="Todo.findByTitleIs">
 9        <query>SELECT t FROM Todo t WHERE t.title = 'title'</query>
10    </named-query>
11</entity-mappings>

If we want to create a SQL query, we must follow these steps:

1. Add a named-native-query element to the orm.xml file.
2. Set the name of the named query (Todo.findByTitleIs) as the value of the named-native-query element’s name attribute.
3. Set the type of the returned object (net.petrikainulainen.springdata.jpa.todo.Todo) as the value of the named-native-query element’s result-class attribute.
4. Add a query element as the child of the named-native-query element and set the invoked SQL query (SELECT * FROM todos t WHERE t.title = ’title’) as the value of the query element.

The relevant part of the orm.xml file looks as follows:

 1<?xml version="1.0" encoding="UTF-8"?>
 2<entity-mappings
 3        xmlns="http://java.sun.com/xml/ns/persistence/orm"
 4        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 5        xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm http://java.sun.com/xml/ns/persistence/orm_2_0.xsd"
 6        version="2.0">
 7
 8    <named-native-query name="Todo.findByTitleIs"
 9                        result-class="net.petrikainulainen.springdata.jpa.todo.Todo">
10        <query>SELECT * FROM todos t WHERE t.title = 'title'</query>
11    </named-native-query>
12</entity-mappings>

Let’s move on and find out how we can create the query methods that invoke our named queries.

Creating the Query Methods

We can create the query method that invokes a specific named query by following these steps:

1. Add a query method into our repository interface and follow these rules:

• If our named query uses the default naming strategy of Spring Data JPA, we must ensure that the name of the query method identifies the invoked named query.
• If our named query doesn’t use the default naming strategy, we have to annotate the query method with the @Query annotation and configure the name of invoked named query by using the name attribute of the @Query annotation.

2. If the invoked named query is a SQL query, we have to annotate the query method with the @Query annotation and set the value of its nativeQuery attribute to true.
3. Add the correct method parameters to the query method.
4. Specify the return type of the query method.

Example 1:

We want to create a query method that invokes the named query whose name is: Todo.findByTitleIs. Because this named query returns todo entries whose title is ’title’, it doesn’t have any parameters.

If the invoked named query is a JPQL query, we have to add the following query method into our repository interface:

1import org.springframework.data.repository.Repository;
2
3import java.util.List;
4
5interface TodoRepository extends Repository<Todo, Long> {
6
7	public List<Todo> findByTitleIs();
8}

If the invoked named query is a SQL query, we have to add the following query method into our repository interface:

 1import org.springframework.data.jpa.repository.Query;
 2import org.springframework.data.repository.Repository;
 3
 4import java.util.List;
 5
 6interface TodoRepository extends Repository<Todo, Long> {
 7
 8	@Query(nativeQuery = true)
 9	public List<Todo> findByTitleIs();
10}

Example 2:

We want to create the query method which invokes the named query whose name is: Todo.findByDesc. This named query has one named parameter called description.

If the invoked query is a JPQL query, we have to add the following query method into our repository interface:

1import org.springframework.data.repository.Repository;
2import org.springframework.data.repository.query.Param;
3
4import java.util.List;
5
6interface TodoRepository extends Repository<Todo, Long> {
7
8	public List<Todo> findByDesc(@Param("description") String description);
9}

If the invoked query is a SQL query, we have to add the following query method into our repository interface:

 1import org.springframework.data.jpa.repository.Query;
 2import org.springframework.data.repository.Repository;
 3import org.springframework.data.repository.query.Param;
 4
 5import java.util.List;
 6
 7interface TodoRepository extends Repository<Todo, Long> {
 8
 9	@Query(nativeQuery=true)
10	public List<Todo> findByDesc(@Param("description") String description);
11}

We are now ready to implement our search function. Let’s find out how we can do it.

Implementing the Search Function

We can implement our search function by following these steps:

1. Create the JPQL and SQL queries that are case-insensitive and return todo entries whose title or description contains the given search term.
2. Create the named queries that invoke the created JPQL and SQL queries.
3. Create the query methods that invoke our named queries.

Let’s get started.

Creating the Database Queries

Our search function must fulfil two requirements:

• It must return todo entries whose title or description contains the given search term.
• It must be case-insensitive.

This section describes the JPQL and SQL queries that fulfil these requirements.

First, the JPQL query that fulfils our requirements looks as follows:

1SELECT t FROM Todo t WHERE
2	LOWER(t.title) LIKE LOWER(CONCAT('%',:searchTerm, '%')) OR
3	LOWER(t.description) LIKE LOWER(CONCAT('%',:searchTerm, '%'))

Second, the SQL query that fulfils our requirements looks as follows:

1SELECT * FROM todos t WHERE
2	LOWER(t.title) LIKE LOWER(CONCAT('%',:searchTerm, '%')) OR
3	LOWER(t.description) LIKE LOWER(CONCAT('%',:searchTerm, '%'))

Let’s move on and create our named queries.

Creating the Named Queries

This section describes how we can specify the required named queries by using a properties file, annotations, and the orm.xml file. We need to create two named queries:

• The Todo.findBySearchTermNamed is a named query that uses JPQL.
• The Todo.findBySearchTermNamedNative is a named query that uses SQL.

Let’s get started.

Using a Properties File

After we have added both named queries (Todo.findBySearchTermNamed and Todo.findBySearchTermNamedNative) into the META-INF/jpa-named-queries.properties file, its content looks as follows:

1Todo.findBySearchTermNamed=SELECT t FROM Todo t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%', :searchTerm, '%')) OR LOWER(t.description) LIKE LOWER(CONCAT('%', :searchTerm, '%'))
2Todo.findBySearchTermNamedNative=SELECT * FROM todos t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%',:searchTerm, '%')) OR LOWER(t.description) LIKE LOWER(CONCAT('%',:searchTerm, '%'))

Let’s move on and declare these named queries by using annotations.

Using Annotations

After we have created both named queries (Todo.findBySearchTermNamed and Todo.findBySearchTermNamedNative) by using the @NamedQuery and the @NamedNativeQuery annotations, the relevant part of our entity class looks as follows:

 1import javax.persistence.Entity;
 2import javax.persistence.NamedNativeQuery;
 3import javax.persistence.NamedQuery;
 4import javax.persistence.Table;
 5
 6@Entity
 7@NamedNativeQuery(name = "Todo.findBySearchTermNamedNative",
 8        query="SELECT * FROM todos t WHERE " +
 9                "LOWER(t.title) LIKE LOWER(CONCAT('%',:searchTerm, '%')) OR " +
10                "LOWER(t.description) LIKE LOWER(CONCAT('%',:searchTerm, '%'))",
11        resultClass = Todo.class
12)
13@NamedQuery(name = "Todo.findBySearchTermNamed",
14        query = "SELECT t FROM Todo t WHERE " +
15                "LOWER(t.title) LIKE LOWER(CONCAT('%', :searchTerm, '%')) OR " +
16                "LOWER(t.description) LIKE LOWER(CONCAT('%', :searchTerm, '%'))"
17)
18@Table(name = "todos")
19final class Todo {
20
21}

Let’s find out how we can declare these named queries by using the orm.xml file.

Using the orm.xml File

After we have created both named queries (Todo.findBySearchTermNamed and Todo.findBySearchTermNamedNative) by using the named-query and the named-native-query elements, the META-INF/orm.xml file looks as follows:

 1<?xml version="1.0" encoding="UTF-8"?>
 2<entity-mappings
 3        xmlns="http://java.sun.com/xml/ns/persistence/orm"
 4        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 5        xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm http://java.sun.com/xml/ns/persistence/orm_2_0.xsd"
 6        version="2.0">
 7
 8    <named-query name="Todo.findBySearchTermNamedOrmXml">
 9        <query>SELECT t FROM Todo t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%', :searchTerm, '%')) OR LOWER(t.description) LIKE LOWER(CONCAT('%', :searchTerm, '%'))</query>
10    </named-query>
11
12    <named-native-query name="Todo.findBySearchTermNamedNativeOrmXml"
13                        result-class="net.petrikainulainen.springdata.jpa.todo.Todo">
14        <query>SELECT * FROM todos t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%',:searchTerm, '%')) OR LOWER(t.description) LIKE LOWER(CONCAT('%',:searchTerm, '%'))</query>
15    </named-native-query>
16</entity-mappings>

Let’s move on and create the query methods that invoke these named queries.

Creating the Query Methods

We can create the query methods that invoke our named queries by following these steps:

1. Create the query method that invokes the JPQL query (Todo.findBySearchTermNamed) by following these steps:
2. Create a query method called findBySearchTermNamed().
3. Set the return type of the query method to List<Todo>.
4. Add one method parameter to the query method and configure the name of the named parameter (searchTerm) by annotating the method parameter with the @Param annotation.
5. Create the query method that invokes the SQL query (Todo.findBySearchTermNamedNative) by following these steps:
6. Create a query method called findBySearchTermNamedNative().
7. Annotate the method with the @Query annotation and set value of its nativeQuery attribute to true.
8. Set the return type of the query method to List<Todo>.
9. Add one method parameter to the query method and configure the name of the named parameter (searchTerm) by annotating the method parameter with the @Param annotation.

The source code of our repository interface looks as follows:

 1import org.springframework.data.jpa.repository.Query;
 2import org.springframework.data.repository.Repository;
 3import org.springframework.data.repository.query.Param;
 4
 5import java.util.List;
 6
 7interface TodoRepository extends Repository<Todo, Long> {
 8
 9    List<Todo> findBySearchTermNamed(@Param("searchTerm") String searchTerm);
10
11    @Query(nativeQuery = true)
12    List<Todo> findBySearchTermNamedNative(@Param("searchTerm") String searchTerm);
13}

Let’s move on and find out when we should create our database queries by using named queries.

When Should We Use Named Queries?

Named queries have the following benefits:

• Named queries support both JPQL and SQL.
• If we have an existing application that uses named queries, it is easy to refactor it to use use Spring Data JPA (if we want to).
• Named queries provide support for SQL result set mapping. This means that we can write complex SQL queries and map the query results into objects.

Named queries have the following drawbacks:

• We cannot see the invoked database query from the repository interface.
• There is no support for dynamic queries.
• If we specify named queries by using annotations, they “litter” the source code of our entity classes.

If we think about the pros and cons of named queries, it becomes clear that the query methods which use named queries aren’t as easy to read or write as the query methods that use either the query generation from the method name strategy or the @Query annotation.

However, named queries have two advantages:

1. We can write complex SQL queries and map the query results into objects.
2. If we need to refactor an existing application to use Spring Data JPA, we don’t have to move its named queries to our repository interfaces.

Thus, I think that we should create our query methods by using the query generation from the method name strategy or the @Query annotation.

However, if we cannot create the query method by using these two methods, we can use named queries.

Also, if we are refactoring an existing application to use Spring Data JPA, using its existing named queries is a no-brainer because it can save us a lot of work.

Let’s move on and summarize what we learned from this blog post.

Summary

This blog post has taught us four things:

• We can create named queries by using a properties file, annotations, or the orm.xml file.
• Named queries support both JPQL and SQL.
• If we need to create complex SQL queries, we can map the query results into objects by using SQL result set mapping.
• We should use named queries only if we don’t have a choice OR we are refactoring an existing application to use Spring Data JPA.

The next part of this tutorial describes how we can create dynamic queries by using the JPA Criteria API.

P.S. You can get the example application of this blog post from Github.