I released five new sample lessons from my Test With Spring course: Introduction to Spock Framework

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.

Additional Reading:

If you are not familiar with Spring Data JPA, you should read the following blog posts before you continue reading this blog post:

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.

My "Test With Spring" course helps you to write unit, integration, and end-to-end tests for Spring and Spring Boot Web Apps:

CHECK IT OUT >>

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.

If we want to use a different properties file or move the jpa-named-queries.properties file to a different directory, we can configure its location by using these two options:

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:

name=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:

Todo.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:

Todo.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.
If we want to create more than one named query, we have to wrap our queries inside the @NamedQueries or the @NamedNativeQueries 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:

import javax.persistence.Entity;
import javax.persistence.NamedQuery;
import javax.persistence.Table;

@Entity
@NamedQuery(name = "Todo.findByTitleIs”,
        query = "SELECT t FROM Todo t WHERE t.title = 'title'" 
)
@Table(name = "todos")
final class Todo {
	
}

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:

import javax.persistence.Entity;
import javax.persistence.NamedNativeQuery;
import javax.persistence.Table;

@Entity
@NamedNativeQuery(name = "Todo.findByTitleIs”,
        query="SELECT * FROM todos t WHERE t.title = 'title'",
        resultClass = Todo.class
)
@Table(name = "todos")
final class Todo {
	
}
Additional Reading:

If you need to create complex SQL queries, you might have to map the results of your SQL query by using the @SqlResultSetMapping and the @SqlResultSetMappings annotations. If you want to get more information about this, check out the following webpages:

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:

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings
        xmlns="http://java.sun.com/xml/ns/persistence/orm"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm http://java.sun.com/xml/ns/persistence/orm_2_0.xsd"
        version="2.0">

    <named-query name="Todo.findByTitleIs">
        <query>SELECT t FROM Todo t WHERE t.title = 'title'</query>
    </named-query>
</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:

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings
        xmlns="http://java.sun.com/xml/ns/persistence/orm"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm http://java.sun.com/xml/ns/persistence/orm_2_0.xsd"
        version="2.0">

    <named-native-query name="Todo.findByTitleIs"
                        result-class="net.petrikainulainen.springdata.jpa.todo.Todo">
        <query>SELECT * FROM todos t WHERE t.title = 'title'</query>
    </named-native-query>
</entity-mappings>
If you need to create complex SQL queries, you might have to map the results of your SQL query by using the sql-result-set-mapping element. If you want to get more information about SQL result set mapping, you should read these blog posts:

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:

import org.springframework.data.repository.Repository;

import java.util.List;

interface TodoRepository extends Repository<Todo, Long> {

	public List<Todo> findByTitleIs();
}

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

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;

import java.util.List;

interface TodoRepository extends Repository<Todo, Long> {

	@Query(nativeQuery = true)
	public List<Todo> findByTitleIs();
}

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:

import org.springframework.data.repository.Repository;
import org.springframework.data.repository.query.Param;

import java.util.List;

interface TodoRepository extends Repository<Todo, Long> {

	public List<Todo> findByDesc(@Param("description") String description);
}

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

 
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;
import org.springframework.data.repository.query.Param;

import java.util.List;

interface TodoRepository extends Repository<Todo, Long> {

	@Query(nativeQuery=true)
	public List<Todo> findByDesc(@Param("description") String description);
}

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:

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

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

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 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:

Todo.findBySearchTermNamed=SELECT t FROM Todo t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%', :searchTerm, '%')) OR LOWER(t.description) LIKE LOWER(CONCAT('%', :searchTerm, '%'))
Todo.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:

import javax.persistence.Entity;
import javax.persistence.NamedNativeQuery;
import javax.persistence.NamedQuery;
import javax.persistence.Table;

@Entity
@NamedNativeQuery(name = "Todo.findBySearchTermNamedNative",
        query="SELECT * FROM todos t WHERE " +
                "LOWER(t.title) LIKE LOWER(CONCAT('%',:searchTerm, '%')) OR " +
                "LOWER(t.description) LIKE LOWER(CONCAT('%',:searchTerm, '%'))",
        resultClass = Todo.class
)
@NamedQuery(name = "Todo.findBySearchTermNamed",
        query = "SELECT t FROM Todo t WHERE " +
                "LOWER(t.title) LIKE LOWER(CONCAT('%', :searchTerm, '%')) OR " +
                "LOWER(t.description) LIKE LOWER(CONCAT('%', :searchTerm, '%'))"
)
@Table(name = "todos")
final class Todo {

}

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:

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings 
        xmlns="http://java.sun.com/xml/ns/persistence/orm" 
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
        xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm http://java.sun.com/xml/ns/persistence/orm_2_0.xsd" 
        version="2.0">

    <named-query name="Todo.findBySearchTermNamedOrmXml">
        <query>SELECT t FROM Todo t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%', :searchTerm, '%')) OR LOWER(t.description) LIKE LOWER(CONCAT('%', :searchTerm, '%'))</query>
    </named-query>

    <named-native-query name="Todo.findBySearchTermNamedNativeOrmXml"
                        result-class="net.petrikainulainen.springdata.jpa.todo.Todo">
        <query>SELECT * FROM todos t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%',:searchTerm, '%')) OR LOWER(t.description) LIKE LOWER(CONCAT('%',:searchTerm, '%'))</query>
    </named-native-query>
</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:
    1. Create a query method called findBySearchTermNamed().
    2. Set the return type of the query method to List<Todo>.
    3. 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.
  2. Create the query method that invokes the SQL query (Todo.findBySearchTermNamedNative) by following these steps:
    1. Create a query method called findBySearchTermNamedNative().
    2. Annotate the method with the @Query annotation and set value of its nativeQuery attribute to true.
    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.

The source code of our repository interface looks as follows:

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;
import org.springframework.data.repository.query.Param;

import java.util.List;

interface TodoRepository extends Repository<Todo, Long> {

    List<Todo> findBySearchTermNamed(@Param("searchTerm") String searchTerm);

    @Query(nativeQuery = true)
    List<Todo> findBySearchTermNamedNative(@Param("searchTerm") String searchTerm);
}

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.

My "Test With Spring" course helps you to write unit, integration, and end-to-end tests for Spring and Spring Boot Web Apps:

CHECK IT OUT >>

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.

If you want to learn how to use Spring Data JPA, you should read my Spring Data JPA tutorial.

About the Author

Petri Kainulainen is passionate about software development and continuous improvement. He is specialized in software development with the Spring Framework and is the author of Spring Data book.

About Petri Kainulainen →

12 comments… add one
  • Hi,

    I followed your example for @NamedNativeQuery with a parameter, using analogy of title = ‘title’ in the query.
    However, in my implementation, that did not work. What did work, was title = :title

    I am on Java 8, spring data jpa 1.8.0-release.
    Any ideas?

    Thanks,
    Jan

    Reply
    • Hi,

      Do you pass this parameter to your query method as a method parameter? If so, you need to specify the name of the parameter by using syntax: :name_of_the_parameter. You can specify the name of the parameter by using the @Param annotation.

      On the other hand, if you want to specify the parameter value in the query string, you should be able to add it to your SQL query (like I did in some of my examples). Is it possible to see your named query?

      Reply
      • Hi,
        I did try to answer your question a couple of times but the answer never appeared. Let’s try again …

        Reply
        • Can’t seem to get any code in the post. Send me an email if you want to see my code …

          Cheers,
          Jan

          Reply
          • Hi Jan,

            I sent you an email!

  • Hi

    can you please add some example for join query to fetch the data from two tables .

    Reply
  • Great content, Petri! Thank you for taking the time to write such helpful examples. Well done!

    Reply
    • Thank you for your kind words. I really appreciate them.

      Reply
  • Thank you for writing such a great and helpful Spring data Jpa tutorial. I was searching for long long time for this kind of content for Spring data JPA Concept.

    Thank you So much
    Prem Prakash
    Software developer

    Reply
    • Thank you for your kind words. I really appreciate them. Also, it was nice to hear that this blog post was useful for you.

      Reply

Leave a Comment