Skip to content

Derived Queries

Learning Objectives

  • Understand the benefits of using the database to filter data instead of using Java
  • Be able to write derived queries in the data access layer
  • Be able to join tables using derived queries
  • Be able to annotate methods with custom queries

Introduction

We've already seen the power that JPA and Hibernate can give us - using some basic annotations and extending specific interfaces could net us database tables being autogenerated and Repositories can create us methods to do some basic SQL CRUD operations. Luckily, even when we need something more complex, we can still rely on JPA being helpful!

First, however, let's see what problems we can solve with derived queries!

Each of these methods will require some data in the games table - remember to start a couple of games before trying to run any of them!

Filtering with Java

Imagine we are not looking for all Game objects back, instead we want only the ones that are completed. How could we achieve this?

First, let's try to write our method using Java with a little help from JPA.

Let's go to our GameService, and create a method named getAllCompletedGames(). We need to find all Game objects, then we can write some Java code to filter out the ones that are incomplete

services/GameService.java
// services/GameService.java

public List<Game> getAllCompletedGames(){
    List<Game> allGames = gameRepository.findAll();
    List<Game> completedGames = new ArrayList<>();
    for(Game game : allGames){
        if(game.isComplete()){
            completedGames.add(game);
        }
    }
    return completedGames;
}

We now have two options for getting game details - a specific game or all complete games. We don't yet have a method for getting all of our games, so we'll add one to our service to plug that gap.

services/GameService.java
// services/GameService.java

public List<Game> getAllGames(){
    return gameRepository.findAll();
}

Next, we need to have a way to access our methods. We need to create a new controller endpoint for it but we need to be careful about how we do it. Since all of the filtering tools will return us a list of Game objects they can all be considered Index actions, but adding a route for each will lead to lots of GET requests. By using a combination of @RequestParam and the Optional datatype we can deal with multiple scenarios on the same route:

controllers/GameController.java
// controllers/GameController.java

@GetMapping
public ResponseEntity<List<Game>> getAllGames(
    @RequestParam Optional<Boolean> isComplete     
){
    List<Game> games;                              
    if(isComplete.isPresent()){                    
        games = gameService.getAllCompletedGames();
    } else {                                       
        games = gameService.getAllGames();
    }
    return new ResponseEntity<>(games, HttpStatus.OK);
}

Note: we are using boolean so soon we can use this endpoint for filtering both complete and incomplete games.

Now we can test our route, and so long as we have finished a game we should be getting back at least one object when we check the filtered condition!

This is all well and good, but it raises some issues. Our filtering method written in Java is servicable, but will certainly be less optimized than asking our DB to only return filtered results to our Java side - we are asking Java to return every result, instantiate Game objects (there could be millions!) and then filter them. It would be much more efficient to ask it to filter the results before we get them loaded into our Java app!

Querying with JPA

There are a couple of ways we can achieve this.

We could write a couple of custom queries (we will in fact do that a little bit later!), which could include some raw SQL like this in our GameRepository:

```java title="repositories/GameRepository.java // repositories/GameRepository.java

@Query("SELECT * FROM games WHERE complete = t") List getAllCompletedGames();

While these are certainly useful, JPA actually gives us a way to write derived queries for simple operations like this. 

Derived queries are named as such because using the correct syntax, we can make it easy for JPA to derive the intention behind a method name - and it will autogenerate a method for us! It's not magic - JPA dissects the name of the method, and as long as the individual words are correct, and in order, it will be able to create us a method.

Derived method names have two main parts separated by the first `By` keyword. The first part is the introducer, and the rest is the criteria. Supported introducers include `find`, `read`, `query`, `count` and `get`, most of which are interchangeable. The criteria needs to follow some basic rules, but other than that, they rely on the column names and certain keywords. For example, if we are looking for games that are completed (`complete` column is `t`, or `true`), we could write the following in our `GameRepository`:

```java title="repositories/GameRepository.java"
// repositories/GameRepository.java

public interface GameRepository extends JpaRepository<Game, Long>{

    List<Game> findByCompleteTrue();

}

Complete needs to match with our column name complete, and True denotes what value we want for it. Let's change our GameService method to remove all that smelly looping logic!

services/GameService.java
// services/GameService.java

public List<Game> getAllCompletedGames(){
    return gameRepository.findByCompleteTrue();
}

Fantastic!

Let's write a query which takes in an argument - what if we were to look for games with specific words in them?

In our GameRepository:

repositories/GameRepository.java
// repositories/GameRepository.java

List<Game> findByWord(String word);

That's it! Now we just need to provide the String to it through our Controller and Service!

In GameService:

services/GameService.java
// services/GameService.java

public List<Game> getAllGamesByWord(String word){
    return gameRepository.findByWord(word);
}

Finally, in our GameController:

controllers/GameController.java
// controllers/GameController.java

@GetMapping
public ResponseEntity<List<Game>> getAllGames(
        @RequestParam Optional<Boolean> isComplete,
        @RequestParam Optional<String> word,        // ADDED
){
    List<Game> games;
    if(isComplete.isPresent()){
        games = gameService.getAllCompletedGames();
    } else if(word.isPresent()) {                   // ADDED
        games = gameService.getAllGamesByWord(word.get());
    } else {
        games = gameService.getAllGames();
    }
    return new ResponseEntity<>(games, HttpStatus.OK);
}

Let's test this through Postman, by making a GET request to localhost:8080/games?word=safari!

Optional: Ask the students to do the next one on their own, 15 minutes?

Task:

Add an extra method in our repository that is going to find games based on Player ids (JPA is able to traverse tables even in a one to many relationship) 1. Create the method in GameRepository 2. Create a method in GameService to be invoked by the controller 3. Modify our Index action in the GameController to invoke the method when appropriate

Possible solution:
repositories/GameRepository
// repositories/GameRepository.java

List<Game> findByPlayerId(long playerId);
services/GameService.java
// services/GameService.java

public List<Game> getAllGamesByPlayerId(long playerId){
    return gameRepository.findByPlayerId(id);
}
controllers/GameController.java
// controllers/GameController.java

@GetMapping
public ResponseEntity<List<Game>> getAllGames(
        @RequestParam Optional<Boolean> isComplete,
        @RequestParam Optional<String> word,
        @RequestParam Optional<Long> playerId
){
    List<Game> games;
    if(isComplete.isPresent()){
        games = gameService.getAllCompletedGames();
    } else if(word.isPresent()) {
        games = gameService.getAllGamesByWord(word.get());
    } else if(playerId.isPresent()){
        games = gameService.getAllGamesByPlayerId(playerId.get());
    } else {
        games = gameService.getAllGames();
    }
    return new ResponseEntity<>(games, HttpStatus.OK);
}

Nicely done! You can combine your derived queries with other specific criteria to create more specific queries, for example findByWordContaining(stringToCheckFor), findByWordStartingWithOrCompleteFalse, etc.

For more options, see here

Hopefully by now you can see where this can go horribly wrong - our controller keeps growing in size, until it becomes a big if/else mess. Not only that, but if we want to combine multiple parameters to search by together, we have to write derived queries for every permutation out there - findByWordStartingWithOrCompleteFalse() and findByWordStartingWithOrCompleteTrue and findByPlayerIdAndCompleteTrue, etc.

Surely there's a better way to handle this?

Custom Queries

There are extremely powerful tools at our disposal (things like QueryDslPredicateExecutor), but these tools usually are fairly complex, and they might not be readily available for every language out there.

Let's try to improve our code in case you find yourself with a controller that's dozens (let alone hundreds!) of lines long!

We can create custom Queries if we want to in our repositories, which enables us to write raw SQL queries! For this example, we will create an alternative Index route, which can handle optional params - it might seem a bit convoluted as an example, but hopefully you'll see the possibilities it opens up.

First, let's create the new Index route in the controller:

controllers/GameController.java
// controllers/GameController.java

//  Alternative Index Route
@GetMapping
public ResponseEntity<List<Game>> getAllGames(
        @RequestParam Map<String, String> parameters
){
    List<Game> games;
    if(!parameters.isEmpty()){
        games = gameService.getAllGamesMultiParam(parameters);
    } else {
        games = gameService.getAllGames();
    }
    return new ResponseEntity<>(games, HttpStatus.OK);
}

Make sure to comment out the original route!

By making an @RequestParam into a Map<String, String>, we can grab all of them!

In GameService:

services/GameService.java
// services/GameService.java

public List<Game> getAllGamesMultiParam(Map<String, String> parameters) {
    String word = parameters.get("word");
    Integer guesses;
    if(parameters.get("guesses") == null){
        guesses = null;
    } else {
        guesses = Integer.parseInt(parameters.get("guesses"));
    }
    return gameRepository.findByCompleteTrueAndOptionalWordAndOptionalGuessesLessThan(word, guesses);
}

Finally, the custom query!

With using the @Query annotation, we can write in something called JQL - it's JPA's own version of SQL, with minor differences only.

repositories/GameRepository.java
// repositories/GamerRepository.java

@Query("SELECT g FROM games g "
        + "WHERE g.complete = TRUE "
        + "AND (:word IS NULL OR g.word = :word) "
        + "AND (:guesses IS NULL OR g.guesses < :guesses)"
)
List<Game> findByCompleteTrueAndOptionalWordAndOptionalGuessesLessThan(
        @Param("word") String word,
        @Param("guesses") Integer guesses
);

@Param enables us to inject strings into a JQL query by giving them another word to be referred by - then by using the :, we can inject it

    games = gameService.getWeirdFilter(word.get(), guesses.get());


    @Query("SELECT g FROM games g "
            + "WHERE g.complete = TRUE "
            + "AND (:word IS NULL OR g.word = :word) "
            + "AND (:guesses IS NULL OR g.guesses < :guesses)"

    )
    List<Game> findByCompleteTrueAndOptionalWordAndOptionalGuessesLessThan(
            @Param("word") String word,
            @Param("guesses") Integer guesses
    );

        public List<Game> getWeirdFilter(String word, int guesses){
        return gameRepository.findByCompleteTrueAndOptionalWordAndOptionalGuessesLessThan(word, guesses);
    }