Featured Image with Sidebar

How to Use Named Queries with Spring Data JPA

By Thorben Janssen

Jpql, Query, Spring Data JPA, Sql

Spring Data JPA provides various options to define and execute queries. All of them use JPA’s query capabilities but make them a lot easier to use. You can:

I already explained the 2nd and 3rd options in previous articles. In this tutorial, I want to focus on the first option: the referencing of a named native or JPQL query in a Spring Data JPA repository. It makes executing your query much easier because Spring Data takes care of all the boilerplate code required by JPA.

Defining a Named Query with JPA

Named queries are one of the core concepts in JPA. They enable you to declare a query in your persistence layer and reference it in your business code. That makes it easy to reuse an existing query. It also enables you to separate the definition of your query from your business code.

You can define a named query using a @NamedQuery annotation on an entity class or using a <named-query /> element in your XML mapping. In this article, I will show you the annotation-based mapping. It’s the by far the most common approach to creating a named query.

When you define a named query, you can provide a JPQL query or a native SQL query in very similar ways. Let’s take a look at both options.

Defining a Named JPL Query

The JPA specification defines its own query language. It’s called JPQL, and its syntax is similar to SQL. But there are 2 essential differences between these 2:

  1. You define your JPQL query based on your entity model. When you execute it, your persistence provider generates a SQL query based on your entity mappings and the provided JPQL statement. That enables you to define database-independent queries but also limits you to the features supported by your persistence provider.
  2. JPQL supports only a small subset of the SQL standard and almost no database-specific features.

The definition of a named JPQL query is pretty simple. You just have to annotate one of your entity classes with @NamedQuery and provide 2 Strings for the name and query attributes.

The name of your query has to be unique within your persistence context. You will use it in your business code or repository definition to reference the query.

The name doesn’t have to follow any conventions if you want to reference the query programmatically. But if you’re going to reference it in a Spring Data JPA repository, the name should start with the name of the entity class, followed by a “.” and the name of the repository method.

The value of the query attribute has to be a String that contains a valid JPQL statement. If your query returns an entity, you can define your projection implicitly, as you can see in the Author.findByFirstName query. The Author.findByFirstNameAndLastName query contains a SELECT clause to define the projection explicitly.

JPQL, of course, supports way more features than I use in these simple examples. You can learn more about it in my Ultimate Guide to JPQL Queries with JPA and Hibernate.

@Entity
@NamedQuery(name = "Author.findByFirstName", query = "FROM Author WHERE firstName = ?1")
@NamedQuery(name = "Author.findByFirstNameAndLastName", query = "SELECT a FROM Author a WHERE a.firstName = ?1 AND a.lastName = ?2")
public class Author { ... }

If you want to define multiple JPQL queries and use at least JPA 2.2 or Hibernate 5.2, you can annotate your class with multiple @NamedQuery annotations. If you are using an older JPA or Hibernate version, you need to wrap your @NamedQuery annotation within a @NamedQueries annotation.

Defining a Named Native Query

Watch it on YouTube
https://youtu.be/W0-3H4_luOA

Native SQL queries are more powerful and flexible than JPQL queries. Your persistence provider doesn’t parse these queries and sends them directly to the database. That enables you to use all SQL features supported by your database. But you also have to handle the different database dialects if you need to support multiple DBMS.

You can define a named native query in almost the same way as you specify a named JPQL query. The 3 main differences are:

  1. You need to use a @NamedNativeQuery instead of a @NamedQuery annotation.
  2. The value of the query attribute has to be an SQL statement instead of a JPQL statement.
  3. You can define an entity class or a reference to an @SqlResultSetMapping that will be used to map the result of your query. Spring Data JPA can provide a set of default mappings so that you often don’t need to specify it.

Here you can see the same queries as in the previous example. But this time, they are defined as native SQL queries instead of JPQL queries.

@Entity
@NamedNativeQuery(name = "Author.findByFirstName", query = "SELECT * FROM author WHERE first_name = ?", resultClass = Author.class)
@NamedNativeQuery(name = "Author.findByFirstNameAndLastName", query = "SELECT * FROM author WHERE first_name = ? AND last_name = ?", resultClass = Author.class)
public class Author { ... }

Executing a Named Query Programmatically with JPA

Using JPA’s EntityManager, you can run named native and named JPQL queries in the same way:

  1. You call the createNamedQuery method on the EntityManager with the name of the named query you want to execute. That gives you an instance of a Query or TypedQuery interface.
  2. You then call the setParameter method on the returned interface for each bind parameter used in your query.
  3. As a final step, you call the getSingleResult or getResultSet method on the Query or TypedQuery interface. That executes the query and returns 1 or multiple result set records.

Here you can see the required code to execute the Author.findByFirstName query that we defined in the 2 previous examples.

Query q = em.createNamedQuery("Author.findByFirstName");
q.setParameter(1, "Thorben");
List a = q.getResultList();

Before you run this code, you should activate the logging of SQL statements. You can then see the executed SQL statement and the used bind parameter values in your log file. In this example, I called the @NamedNativeQuery version of the previously shown Author.findByFirstName query.

2019-06-24 19:20:32.061 DEBUG 10596 --- [           main] org.hibernate.SQL                        : 
    SELECT
        * 
    FROM
        author 
    WHERE
        first_name = ?
2019-06-24 19:20:32.073 TRACE 10596 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [Thorben]
2019-06-24 19:20:32.116 TRACE 10596 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([id] : [NUMERIC]) - [1]
2019-06-24 19:20:32.118 TRACE 10596 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([first_name] : [VARCHAR]) - [Thorben]
2019-06-24 19:20:32.119 TRACE 10596 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([last_name] : [VARCHAR]) - [Janssen]
2019-06-24 19:20:32.121 TRACE 10596 --- [           main] o.h.type.descriptor.sql.BasicExtractor   : extracted value ([version] : [INTEGER]) - [0]

Referencing a Named Query in a Spring Data JPA repository

As you have seen in the previous example, executing a named query using JPA’s EntityManager isn’t complicated, but it requires multiple steps.

Spring Data JPA takes care of that if you reference a named query in your repository definition. Doing that is extremely simple if you follow Spring Data’s naming convention. The name of your query has to start with the name of your entity class, followed by “.” and the name of your repository method.

In the previous examples, I defined the named queries Author.findByFirstName and Author.findByFirstNameAndLastName as JPQL and native queries. You can reference both versions of these queries by adding the methods findByFirstName and findByFirstNameAndLastName to the AuthorRepository.

public interface AuthorRepository extends JpaRepository<Author, Long> {

    List<Author> findByFirstName(String firstName);

    List<Author> findByFirstNameAndLastName(String firstName, String lastName);

}

You can then inject an AuthorRepository instance in your business code and call the repository methods to execute the named queries.

As you can see in the following code snippet, you can use these repository methods in the same way as a repository method that executes a derived query or a declared query. Spring Data JPA handles the instantiation of the named query, sets the bind parameter values, executes the query, and maps the result.

List<Author> a = authorRepository.findByFirstName("Thorben");

Conclusion

Named queries are one of the various options to query data from your database that are defined by the JPA specification.

Spring Data JPA provides a very comfortable way to use named queries in your repository. It takes care of all the repetitive boilerplate code required by the JPA specification. By default, Spring Data JPA checks for a named JPQL or a named native query that follows the naming convention <entity class name>.<repository method name> before it tries to derive a query from the method name.

About the author

Thorben is an independent consultant, international speaker, and trainer specialized in solving Java persistence problems with JPA and Hibernate.
He is also the author of Amazon’s bestselling book Hibernate Tips - More than 70 solutions to common Hibernate problems.

Books and Courses

Coaching and Consulting

Leave a Repl​​​​​y

Your email address will not be published. Required fields are marked

This site uses Akismet to reduce spam. Learn how your comment data is processed.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}