Difference Between findBy and findAllBy in Spring Data JPA

1. Introduction

In this tutorial, we’ll investigate the differences between findBy and findAllBy method naming conventions when using the derived queries API in Spring Data JPA.

2. What Are Derived Queries in JPA

Spring Data JPA supports derived queries based on the method name. That means we don’t need to manually specify the query if we use specific keywords in the method’s name.

The find and By keywords work together to generate a query that searches for a collection of results using a rule. Notice that these two keywords return all results in the form of a collection, which might create confusion in the usage of findAllBy. There’s no All keyword defined in the Spring Data documentation.

In the following sections, we’ll verify that there are no differences between the findBy and findAllBy keywords in Spring Data JPA, and provide an alternative to search for only one result instead of a collection.

2.1. Sample Application

Let’s first define a sample Spring Data application. Then, let’s create the Player entity class:

@Entity
public class Player {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;

    private Integer score;

    //all-arg and no-arg constructors
    //overriden equals method
    //getters and setters
}

Let’s also create the PlayerRepository interface that extends the JpaRepository interface:

@Repository
public interface PlayerRepository extends JpaRepository<Player, Long> {
}

2.2. Example of findBy Query

As previously mentioned, the findBy keywords return a collection of the results using a rule. That rule comes after the By keyword. Let’s create a method in the PlayerRepository class to derive a query that finds all players with a score higher than a given input:

List<Player> findByScoreGreaterThan(Integer target);

Spring Data JPA parses the method name syntax into SQL statements to derive queries. Let’s look at what each keyword does:

• find is translated to a select statement.
• By is parsed to a where clause.
• Score is the table column name and should be the same name defined in the Player class.
• GreaterThan adds the > operator in the query to compare the score field with the target method argument.

2.3. Example of findAllBy Query

Similarly to findBy, let’s create a method with the All keyword in the PlayerRepository class:

List<Player> findAllByScoreGreaterThan(Integer target);

The method works like the findByScoreGreaterThan() method — the only difference is the All keyword. That keyword is just a naming convention and doesn’t add any functionality to the derived query, as we’ll see in the next section.

3. findBy vs. findAllBy

Now, let’s verify that there are only naming convention differences between the findBy and findAllBy keywords but prove that they are functionally the same.

3.1. Functional Differences

To analyze if there’s a functional difference between the two methods, let’s write an integration test:

@RunWith(SpringRunner.class)
@SpringBootTest(classes = FindByVsFindAllByApplication.class)
public class FindByVsFindAllByIntegrationTest {
    @Autowired
    private PlayerRepository playerRepository;

    @Before
    public void setup() {
        Player player1 = new Player(600);
        Player player2 = new Player(500);
        Player player3 = new Player(300);
        playerRepository.saveAll(Arrays.asList(player1, player2, player3));
    }

    @Test
    public void givenSavedPlayer_whenUseFindByOrFindAllBy_thenReturnSameResult() {
        List<Player> findByPlayers = playerRepository.findByScoreGreaterThan(400);
        List<Player> findAllByPlayers = playerRepository.findAllByScoreGreaterThan(400);
        assertEquals(findByPlayers, findAllByPlayers);
    }
}

Note: For that test to pass, the Player entity must have an overridden equals() method that compares both the id and score fields.

Both methods return the same results as shown in the assertEquals(). Therefore, they don’t differ from a functional perspective.

3.2. Query Syntax Differences

To be complete, let’s compare the syntax of the queries generated by both methods. To do that, we need to first add the following line to our application.properties file:

spring.jpa.show-sql=true

If we rerun the integration test, both queries should appear in the console. Here’s the derived query for findByScoreGreaterThan():

select
    player0_.id as id1_0_, player0_.score as score2_0_ 
from
    player player0_ 
where
    player0_.score>?

And the derived query for findAllByScoreGreaterThan():

select
    player0_.id as id1_0_, player0_.score as score2_0_
from
    player player0_
where
    player0_.score>?

As we can see, there are no differences in the syntax of the generated queries. Therefore, the code style we want to adopt is the only difference between using the findBy and findAllBy keywords. We can use either of them and expect the same results.

4. Returning a Single Result

We’ve clarified that there’s no difference between findBy and findAllBy, and both return a collection of results. If we changed our interface to return a single result from these generated queries that we know can return more than one result, we risk getting a NonUniqueResultException.

In this section, we’ll look at the findFirst and findTop keywords to derive a query that returns a single result.

The First and Top keywords should be inserted between the find and By keywords to find the first element stored. They can also be used together with a criteria keyword like IsGreaterThan. Let’s look at one example to find the first Player stored with a score greater than 400. First, let’s create our query method in the PlayerRepository class:

Optional<Player> findFirstByScoreGreaterThan(Integer target);

The Top keyword is functionally equal to First. The only difference between them is the naming convention. Thus, we can achieve the same result with a method named findTopByScoreGreaterThan().

Then, we verify that we only get a single result with this test:

@Test
public void givenSavedPlayer_whenUsefindFirst_thenReturnSingleResult() {
    Optional<Player> player = playerRepository.findFirstByScoreGreaterThan(400);
    assertTrue(player.isPresent());
    assertEquals(600, player.get().getScore());
}

The findFirstBy query uses a limit SQL operator to return the first element stored that matches our criteria, in that case, the Player with id=1 and score=600.

Finally, let’s take a look at the query generated by our method:

select
    player0_.id as id1_0_, player0_.score as score2_0_
from
    player player0_
where
    player0_.score>?
limit ?

The query is almost the same as findBy and findAllBy, except for the limit operator at the end.

5. Conclusion

In this article, we explored the similarities between the findBy and findAllBy keywords in Spring Data JPA. We’ve also looked at how to return a single result with the findFirstBy keywords.