spring data logo

Time to Create Custom Queries in Spring Data

Hi again everyone,

So, in our last post we talked about how to handle your standard CRUD operations with Spring Data's JpaRepository. It's a super easy way to handle all your basic CRUD needs.

But what happens when we wander away from the “basic CRUD” operations, and we start to have a need for more customized queries outside of the findAll() and findOne() scenarios?

Well this is where we embark on the journey to creating custom queries using a handle annotation called @Query

The @Query Annotation in Spring Data

First off, we need to go back and grab some of our previous code where we defined our own repository… the FoodDiaryRepository.

Here's what the FoodDiaryRepository looked like:

public interface FoodDiaryRepository extends JpaRepository<FoodDiary, Long> 
  // it can be empty inside if you want, or later, you can add any custom queries if the standard set of CRUD operations aren't enough. 

Now like I mentioned above, this will give us access to the default CRUD functionality within the FoodDiaryRepository, so we'll need to dig a bit deeper if we want to get more from our repository.

Let's assume that we also want to have a way to find all items based on the meal type (i.e. Give me all the entries that are for breakfast, lunch, dinner or snack.

That seems like it would be a reasonable thing to want. But it doesn't come as a query that we can run out of the box, so we'll need to create a custom query using the @Query annotation.

Using @Query Without Writing any SQL/HQL

Let's take a look at what our custom query would look like on its own.

public List<FoodDiary> findByMealType(String mealType);

The beauty of using Spring Data is that you can get away with creating queries without actually having to write any queries.

Sound confusing?

Well it isn't… It just means that you can create SQL queries without having to write any actual SQL.

You see, Spring Data uses clever conventions to create the queries for us based on how we name our methods in our repository interfaces.

In my example above I started the name of the method with findBy. This signals Spring Data that I'd like to create a query with this method and that I'd like Spring Data to generate the SQL for me.

Next, I give it the column (field) I'd like to find by… So in this case it's findByMealType.

So, Spring Data will know to look for the mealType field inside of my FoodDiary entity.

Lazy programmers rejoice!

So what Spring Data will generate behind the scenes with our method is this:

select * from food_diary where meal_type = ?

Now, we're almost done here, but the last remaining thing to mention is that question mark.

How does it know what to put in there?

Well, don't forget that we declared our method with a parameter… Specifically a String mealType parameter.

So this means that whenever this method is to be called, someone has to pass in a mealType that they'd like to “find by”.

Okay, so now that I've explained what all that code does, let's take a look a the whole picture:

public interface FoodDiaryRepository extends JpaRepository<FoodDiary, Long> 
  public List<FoodDiary> findByMealType(String mealType);

There's our repository in all its glory!

Now whenever we'd like to query our database by meal type, all we need to do is call this new method and we'll receive a list of FoodDiary items that are filtered by the given meal type.

Searching Between Ranges with @Query In Spring Data

That's all well and good, but what about when we want to search for a range of data, for example: find all the meals that cost between $10 and $20?

That's entirely possible with Spring Data too!

Let's create some methods to handle these range type searches…

public List<FoodDiary> findByCostGreaterThan(Double cost);

public List<FoodDiary> findByCostLessThan(Double cost);

public List<FoodDiary> findByCostBetween(Double minCost, Double maxCost);

The key here is that we combine what we already know about how Spring Data works with the “findBy” convention.

So we tell Spring Data to “findByCost”, and then we add additional information to this query by appending “LessThan”, “GreaterThan” or “Between”.

Let's put our queries into action.

In order to do this in the simplest way possible, I've added some URL mappings to our RootController.

Specifically, we'll add three different methods that handle the three new queries in our FoodDiaryRepository.

Here's the code we'll add to our controller:

  @RequestMapping(value="/food-diaries/cost-less-than/{cost}", method=RequestMethod.GET)
  public String findByCostLessThan (@PathVariable Double cost, ModelMap model)
    List<FoodDiary> findByMealType = foodDiaryRepository.findByCostLessThan(cost);
    model.put("foodDiaries", findByMealType);
    return "foodDiary";
  @RequestMapping(value="/food-diaries/cost-greater-than/{cost}", method=RequestMethod.GET)
  public String findByCostGreaterThan (@PathVariable Double cost, ModelMap model)
    List<FoodDiary> findByMealType = foodDiaryRepository.findByCostGreaterThan(cost);
    model.put("foodDiaries", findByMealType);
    return "foodDiary";
  @RequestMapping(value="/food-diaries/cost-between/{minCost}-{maxCost}", method=RequestMethod.GET)
  public String findByCostBetween(@PathVariable Double minCost, @PathVariable Double maxCost, ModelMap model)
    List<FoodDiary> findByMealType = foodDiaryRepository.findByCostBetween(minCost, maxCost);
    model.put("foodDiaries", findByMealType);
    return "foodDiary";

Okay, so what this new code means is that when we visit certain URLs, we'll trigger our new queries and get the appropriate results returned.

So if we navigate to http://localhost:8080/food-diaries/cost-less-than/10 that means we'll essentially be running the following code: foodDiaryRepository.findByCostLessThan(10)

Another example is if we visit http://localhost:8080/food-diaries/cost-between/10-15, this will execute the following method: foodDiaryRepository.findByCostBetween(10, 15)

Note: in the real world, we likely wouldn't create URLs like we did for these queries. We'd most likely be taking input from an HTML page and passing that along via a different URL structure. But the purpose of this tutorial is to teach you about Spring Data, not proper ways to use MVC and URL structures.

Using HQL with Spring Data

Alright, so sometimes you just need to write your own queries.

That's cool, I get it, we all want to be gangsta coders.

Let's say that we're in a situation where we need to find out all the times we had pizza for lunch or something along those lines.

Now, I'm pretty sure we'd be able to use the Spring Data method naming convention to create this query for us, but throw me a bone here, I can't think of any other queries to write off the top of my head that Spring Data doesn't already support.

What would this look like?

Well, we can actually pass in HQL as a String to the @Query annotation, like so:

@Query("from FoodDiary where food = :foodParam and mealType = :mealTypeParam")
public List findByFoodAndMealType(@Param("foodParam") String food, @Param("mealTypeParam") String mealType);

There are a few things going on here.

First thing to understand is that we needed to make use of parameters in our query. We used two to be exact, one for the food column called foodParam and another for the mealType column called meanTypeParam.

This isn't unique to HQL, as using named parameters in our queries is just something that we need to do to make them function dynamically.

What is slightly different is that we need to tell Spring Data how to map our method parameters to the HQL query params. To do this, we used the @Param annotation, which comes from Spring Data. With this, Spring Data is able to understand that the foodParam should map to the String food parameter (and same for the mealTypeParam).

Outside of that, there isn't much else new or interesting going on here.

Note: it doesn't matter what you name the actual method, as Spring Data will now ignore the method name in favor of the actual HQL query you specified in the @Query annotation.

And finally, to wrap things up, here's the code I used to call our new findByFoodAndMealType method:

  @RequestMapping(value="/food-diaries/search/{food}-{mealType}", method=RequestMethod.GET)
  public String findByFoodAndMealType (@PathVariable String food, @PathVariable String mealType, ModelMap model)
    List<FoodDiary> findByMealType = foodDiaryRepository.findByFoodAndMealType(food, mealType);
    model.put("foodDiaries", findByMealType);
    return "foodDiary";

Where to go from here?

I've shown you a couple of the most common uses of Spring Data, but if you want to see even more examples, please check out the Spring Data JPA Reference Documentation.

There you'll see examples like how to order your data, how to search while ignoring case, using distinct queries and more.

Also, if you're curious to see how all this code we talked about today works, feel free to check out my GitHub page and peruse the code. Or feel free to clone this project via this URL: https://github.com/tp02ga/FoodDiary.git

Free Java Roadmap

Discover exactly what you need to learn and where to start in order to become a professional coder.