How to Use Spring Data JPA Named Queries

Spring Data JPA provides multiple ways of writing and executing different queries to fetch data from the database.

In the last two articles, I have explained how to:

Create derived queries by referencing the method name.
Use the @Query annotation to declare JPQL and native SQL queries.

Both these approaches are good for small to medium applications that have only a small number of queries. As soon as your application grows with tens of hundreds of different kinds of queries scattered in many classes, it becomes harder to maintain such a large number of queries.

To make developers' life easier, Spring Data JPA provides the concept of named queries that can be defined using a properties file, annotations, or an XML file. You can group related queries in one place and refer them in your code by their names. Spring Data JPA will take care of all the boilerplate code required to execute these queries.

In this article, you will learn how to create and reference named JPQL and native SQL queries in a Spring Data JPA repository.

Create an Application

Let us extend our existing Spring Data JPA and H2 database application to add another entity and a repository interface.

First of all, add the following Book entity that holds book information:

Book.java

package com.attacomsian.jpa.named.domains;

import javax.persistence.*;

@Entity
public class Book {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    private String title;
    @Column(unique = true)
    private String isbn;
    private int pages;

    public Book() {
    }

    public Book(String title, String isbn, int pages) {
        this.title = title;
        this.isbn = isbn;
        this.pages = pages;
    }

    // getters and setters, equals(), toString(), ... (omitted for brevity)
}

Now create the BookRepository.java interface for storing and retrieving the data from the database:

BookRepository.java

package com.attacomsian.jpa.named.repositories;

import com.attacomsian.jpa.named.domains.Book;
import org.springframework.data.repository.CrudRepository;

public interface BookRepository extends CrudRepository<Book, Long> {

    // TODO: add named query methods
}

Creating Named Queries

We can create named queries using an external properties file, Java annotations, or an XML file. Spring Data JPA supports both JPQL and native SQL named queries.

The definition of a named query is pretty much straightforward. It has two parts: the name and the query.

The name must be unique within your application persistence context. The name should start with the name of the entity class, followed by a dot (.), and the name of the repository method.

Here is an example:

{EntityName}.{RepositoryMethodName}

// Examples

1. Book.findByIsbn
2. Book.findByPagesGreaterThan

Note: You do not need to follow these naming conventions if you want to execute named queries programmatically using EntityManager.

The query part of the named query must contain a valid JPQL or native SQL statement.

1. Using a Properties File

You can define named queries by using a properties file called jpa-named-queries.properties inside the META-INF folder of your classpath.

In a Spring Boot project, by default, this folder is not available. You need to first create a META-INF folder inside /src/main/resources/.

Here is how you can declare JPQL-named queries in the jpa-named-queries.properties file:

# find all books, order by title descending
Book.findAllNamedFile=SELECT b FROM Book b ORDER BY b.title DESC

# find books by title
Book.findByTitleNamedFile=SELECT b FROM Book b WHERE b.title = ?1

# find a book by isbn
Book.findByIsbnNamedFile=SELECT b FROM Book b WHERE b.isbn = ?1

As you can see above, we set the name of the named query as the property name and the query as the property value.

Similarly, you can define native SQL named queries inside the properties file:

# native SQL query to find books by title
Book.findByTitleNativeNamedFile=SELECT * FROM book b WHERE b.title = :title

2. Using the `orm.xml` File

If you don't want to use a properties file, you can create an orm.xml file inside the same META-INF folder for declaring named queries. Spring Data JPA supports the following two XML elements to enable automatic invocation of named queries:

<named-query /> — Use this XML element for defining a JPQL named query.
<named-native-query /> — This XML element is used to declare a native SQL named query. If your native name query returns an entity, you need specify the entity class using the result-class attribute.

Let us create the orm.xml file and add some named queries:

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings version="2.0" 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 ">

    <!--find all books ordered by pages descending-->
    <named-query name="Book.findAllXML">
        <query>SELECT b FROM Book b ORDER BY b.pages DESC</query>
    </named-query>

    <!--find books by title-->
    <named-query name="Book.findByTitleContainingXML">
        <query>SELECT b FROM Book b WHERE b.title LIKE ?1</query>
    </named-query>

    <!--native SQL query to find a book by isbn-->
    <named-native-query name="Book.findByIsbnNativeXML"
                        result-class="com.attacomsian.jpa.named.domains.Book">
        <query>SELECT * FROM book b WHERE b.isbn = :isbn</query>
    </named-native-query>

</entity-mappings>

3. Using Annotations

In addition to the above two methods, you can also declare named queries by annotating your entities with the following annotations:

@NamedQuery — This annotation defines a JPQL named query.
@NamedNativeQuery — Use this annotation to declare a native SQL named query.

Both @NamedQuery and @NamedNativeQuery annotations can be used separately or together on the entity class. These annotations define the query name as well as the query string. If your native SQL named query returns an entity, you need explicitly indicate it by using the resultClass element.

`@NamedQuery` Annotation

The @NamedQuery annotation accepts two elements, name and query, as follows:

@NamedQuery(name = "Book.findAllJPQL",
        query = "SELECT b FROM Book b ORDER BY b.title DESC")
public class Book {
    // ...
}

Multiple JPQL named queries can be defined together using the @NamedQueries annotation, as shown below:

@NamedQueries({
        @NamedQuery(name = "Book.findAllJPQL",
                query = "SELECT b FROM Book b ORDER BY b.title DESC"),
        @NamedQuery(name = "Book.findByTitleJPQL",
                query = "SELECT b FROM Book b WHERE b.title = ?1"),
        @NamedQuery(name = "Book.findByTitleAndPagesGreaterThanJPQL",
                query = "SELECT b FROM Book b WHERE b.title = :title AND b.pages > :pages")
})
public class Book {
    // ...
}

The @NamedQuery annotation is similar to the @Query annotation. The only difference is the former is used to define finder queries directly on the entity class, whereas the latter is used in the repository interface method.

`@NamedNativeQuery` Annotation

The @NamedNativeQuery annotation is used for defining native SQL named queries. A native SQL query is more powerful and flexible than a JPQL query. The underlying persistence provider, Hibernate for Spring Boot, doesn't parse these queries and sends them directly to the database. This allows you to use all SQL features supported by your database.

A native SQL-named query is declared in almost the same way as you define a named JPQL query.

However, there are two notable differences:

The value of the query attribute must be a valid SQL statement instead of a JPQL statement.
You need to indicate the return type of the query by using the resultClass attribute.

Here is an example of a native SQL named query defined using the NamedNativeQuery annotation:

@NamedNativeQuery(name = "Book.findAllNative",
        query = "SELECT * FROM book b ORDER BY b.title DESC",
        resultClass = Book.class)
public class Book {
    // ...
}

Multiple native SQL named queries can be declared using the @NamedNativeQueries annotation, as follows:

@NamedNativeQueries({
        @NamedNativeQuery(name = "Book.findAllNative",
                query = "SELECT * FROM book b ORDER BY b.title DESC",
                resultClass = Book.class),
        @NamedNativeQuery(name = "Book.findByIsbnNative",
                query = "SELECT * FROM book b WHERE b.isbn = :isbn",
                resultClass = Book.class)
})
public class Book {
    // ...
}

Executing Named Queries using `EntityManager`

The simplest way to execute a named query is using the JPA's EntityManager.createNamedQuery() method. You can run both JPQL and native SQL queries in the same way.

Just inject the EntityManager object to your controller or any other business class:

@PersistenceContext
private EntityManager em;

Now you call the createNamedQuery() method on the EntityManager with the name of the named query you want to execute as a parameter:

Query q = em.createNamedQuery("Book.findByTitleJPQL");

This gives you an instance of a Query interface. Now call the setParameter() method on the returned interface for each binding parameter used in your query:

q.setParameter(1, "Java 101");

After specifying bind parameters, call the getSingleResult() (for a single result) or getResultSet() (for multiple results) method on the Query interface to execute the query:

// execute query
List<Book> books = q.getResultList();

That's all for creating and executing a JPQL named query. You can also execute a native SQL named query in a similar way:

Query q = em.createNamedQuery("Book.findByIsbnNative");
q.setParameter("isbn", "145804");
// execute query
Book book = (Book) q.getSingleResult();

Referencing Named Queries in a Spring Data Repository

While creating and executing a named query using EntityManager is straightforward, it may not be the most efficient way. Spring Data JPA allows you to reference your named queries in a repository interface.

If you have followed the Spring Data JPA's naming conventions, I explained before in this article, referencing a named query is very simple. All you need to do is just create a method in the repository interface with the same name as the named query, pass the correct method parameters, and specify the return type of the query method.

If the named query is a native SQL query, you must annotate the query method with the @Query annotation and set the value of its nativeQuery attribute to true.

Let us reference all named queries, we have created in the above section, in our BookRepository interface:

// named queries declared in `jpa-named-queries.properties` file

List<Book> findAllNamedFile();

List<Book> findByTitleNamedFile(String title);

Book findByIsbnNamedFile(String isbn);

@Query(nativeQuery = true)
List<Book> findByTitleNativeNamedFile(@Param("title") String title);

// named queries declared in `orm.xml` file

List<Book> findAllXML();

List<Book> findByTitleContainingXML(String title);

@Query(nativeQuery = true)
List<Book> findByIsbnNativeXML(@Param("isbn") String isbn);

// named queries declared with `@NamedQuery`

List<Book> findAllJPQL();

List<Book> findByTitleJPQL(String title);

List<Book> findByTitleAndPagesGreaterThanJPQL(@Param("title") String title, @Param("pages") int pages);

// named queries declared with `@NamedNativeQuery`

@Query(nativeQuery = true)
List<Book> findAllNative();

@Query(nativeQuery = true)
List<Book> findByIsbnNative(@Param("isbn") String isbn);

You can now inject a BookRepository instance in your controller or any other class and call the repository methods to execute the named queries:

// create books
bookRepository.save(new Book("Java 101", "145804", 450));
bookRepository.save(new Book("Spring Bot", "48524", 289));

// list all books
List<Book> books = bookRepository.findAllXML();

// fetch a single book
Book book = bookRepository.findByIsbnNamedFile("145804");

// multiple parameters
List<Book> moreBooks = bookRepository.findByTitleAndPagesGreaterThanJPQL("Spring Bot", 150);

As you can see above, calling a named query repository method is the same as you call a derived query or a custom query defined by using @Query.

Sorting Named Queries Results

Named queries support static sorting to sort the query results. You can append the ORDER BY clause to any named JPQL or native SQL query.

We have already added static sorting to several named queries, which we have declared above. For instance, the Book.findAllJPQL() named query sorts all books by their title field in descending order.

Unfortunately, dynamic sorting is currently not supported by Spring Data JPA named queries.

Source Code: Download the complete source code from GitHub available under MIT license.

Conclusion

Named queries are one of the three options discussed in this Spring Data JPA tutorial series. The other two options are derived query and the @Query annotation. Spring Data JPA allows you to define both JPQL and native SQL named queries.

In this article, you have learned about three different ways of defining name queries in Spring Data JPA. You can use a properties file, JPA annotations, or an XML file for defining named queries.

The simplest and the most widely used approach is the JPA @NamedQuery and @NamedNativeQuery annotations. These annotations accept at least two parameters, the name and the query. The name must be unique within the application context and follow the {EntityName}.{RepositoryMethodName} naming convention. The query attribute must contain a valid JPQL or an SQL statement depending upon the type of the name query (JPQL vs. native SQL).

To execute a named query, you can either use JPA's EntityManager or reference it in your repository interface and then executes it by invoking the repository method.

How to Use Spring Data JPA Named Queries

Create an Application

Creating Named Queries

1. Using a Properties File

2. Using the `orm.xml` File

3. Using Annotations

`@NamedQuery` Annotation

`@NamedNativeQuery` Annotation

Executing Named Queries using `EntityManager`

Referencing Named Queries in a Spring Data Repository

Sorting Named Queries Results

Conclusion

Further Reading

You might also like...

Buy me a coffee ☕

✨ Learn to build modern web applications using JavaScript and Spring Boot

How to Use Spring Data JPA Named Queries

Create an Application

Creating Named Queries

1. Using a Properties File

2. Using the orm.xml File

3. Using Annotations

@NamedQuery Annotation

@NamedNativeQuery Annotation

Executing Named Queries using EntityManager

Referencing Named Queries in a Spring Data Repository

Sorting Named Queries Results

Conclusion

Further Reading

You might also like...

Buy me a coffee ☕

✨ Learn to build modern web applications using JavaScript and Spring Boot

2. Using the `orm.xml` File

`@NamedQuery` Annotation

`@NamedNativeQuery` Annotation

Executing Named Queries using `EntityManager`