Persistence top

Get started with Spring Data JPA through the reference Learn Spring Data JPA course:


1. Introduction

Spring Data JPA provides a simple and consistent interface for accessing data stored in a variety of relational databases, making it easier for developers to write database-agnostic code. It also eliminates the need for a lot of boilerplate code, allowing developers to focus on building the business logic of their applications.

However, we still need to ensure the correct return types, or else an Exception is thrown. In this tutorial, we'll focus on the NonUniqueResultException. We'll learn what causes it and how to fix our code when we encounter it.

2. NonUniqueResultException

The Spring Data JPA framework throws a NonUniqueResultException runtime exception when a query method is expected to return a single result but more than one result is found. This can occur when a query is executed using one of Spring Data JPA's query methods, such as findById(), findOne(), or a custom-defined method that does not return a collection.

When the NonUniqueResultException is thrown, it means that the method being used was designed to return a single result, but it found more than one. This can be due to an incorrect query or inconsistent data in the database.

3. Example

Let's use this Entity we know from our article, Query Entities by Dates and Times with Spring Data JPA:

public class Article {

    private Integer id;

    private Date publicationDate;

    private Date publicationTime;

    private Date creationDateTime;

Now, let's create our ArticleRepository and add two methods:

public interface ArticleRepository extends JpaRepository<Article, Integer> {

    List<Article> findAllByPublicationTimeBetween(Date publicationTimeStart, Date publicationTimeEnd);

    Article findByPublicationTimeBetween(Date publicationTimeStart, Date publicationTimeEnd);

The only difference between the two methods is that findAllByPublicationTimeBetween() has a List<Article> as the return type and findByPublicationTimeBetween() has a single Article as the return type.

When we execute the first method, findAllByPublicationTimeBetween, we will always get a collection. Depending on the data in our database, we can get an empty List or a List with one or more Article instances.

The second method, findByPublicationTimeBetween, could technically also work, given the database contains exactly zero or one matching Article. In case there is no single entry for the given query, the method will return null. On the other hand, if there is one corresponding Article, it will return the single Article.

However, if there are multiple Articles matching the query for findByPublicationTimeBetween, the method will throw a NonUniqueResultException, which is then wrapped in an IncorrectResultSizeDataAccessException.

When exceptions like this can occur randomly at runtime, it's an indication that something is wrong with the database design or our method implementation. In the next section, we'll learn how we can avoid this error.

4. Tips for Avoiding NonUniqueResultException

To avoid the NonUniqueResultException, it's important to carefully design database queries and properly use Spring Data JPA's query methods. When designing a query, it is important to ensure that it will always return the expected number of results. We can achieve this by carefully specifying our query criteria, such as using unique keys or other identifying information.

We should follow a few basic rules to avoid NonUniqueResultExceptions when designing our query methods:

  • If more than one value may be returned, we should use a List or Set as the return type.
  • We only use a single return value if we can ensure that there is only one return value by database design. This is always the case when we're looking for a unique key like an Id, UUID, or, depending on the database design, it could also be an email or phone number that is guaranteed to be unique.
  • Another way to ensure that there is only one return value is to limit the return to a single element. This can be useful, for example, if we always want the newest Article.

5. Conclusion 

The NonUniqueResultException is an important exception to understand and avoid when using Spring Data JPA. It occurs when a query is expected to return a single result but more than one result is found. We can prevent this by making sure our JpaRepository methods return the correct number of elements and specify the correct return types accordingly.

By understanding and properly avoiding the NonUniqueResultException, we can ensure that our applications can access data from the database consistently and reliably.

As always, these examples can also be found over on GitHub.

Persistence bottom

Get started with Spring Data JPA through the reference Learn Spring Data JPA course:

Persistence footer banner
Inline Feedbacks
View all comments