Spring Data Aggregate Functions in a repository

A quick demo on how to use aggregate functions in your Spring Data Repository

In this tutorial, we are taking a look at a student’s question from my Spring Boot Introduction Course

Aggregate Functions

Hi Dan,
How can I use the @Query to specify an aggregation function in a select statement like…
“select post_date, sum(value) from post group by post_date”
retrieve it to a DTO and show on screen?

This is actually pretty easy to do using the @Query annotation. In this tutorial, I will show you how to do this using JQL and native SQL. 

Creating the application

The first thing we need to do is to create a very basic Spring Boot application with the following dependencies. If you want you can grab the source code for this demo here

Aggregate Functions Dependencies

I won’t walk you through every single step of this application because I don’t want to focus on the application itself. We are building a simple application that exposes some endpoints that call a service and a repository.  

Aggregate Functions Project Structure

This is what our domain looks like. 

And we will load some initial test data using a Command Line Runner

With those in place, we can now begin to add some new methods to our repositories. 

Spring Data Repositories

In the first example, I want to find out the average age of all the users in our system. We can do so by using the Aggregate function AVG in both JPQL and SQL. We can write these queries using the @Query annotation

In this example, we are using JPQL to write the SQL that will fetch us the data we need. JPQL stands for Java Persistence Query Language and if you have ever worked with Hibernate before you have probably seen this in action. It is important to understand that this is the default and if you want to write native SQL you can, but you need to add a flag, more on that in a bit. 

Now let’s say that we wanted to add a new method that would give us the max age of all the users but also allow us to exclude a single user. This is strictly a demo and you probably wouldn’t use this in a real application but it does show off a couple of things. First, it shows us how to include parameters in our SQL statement and it also shows us that we can write native SQL by using the native SQL flag. 

Screencast 

Conclusion

I think the one that confuses people is not understanding that the default query syntax for @Query annotation is JPQL and that it does support aggregate functions. If you are interested in the full source code for this demo you can grab it here

Question: Are you facing any issues with your Spring Data Repositories? 

Hi, I’m Dan Vega. I am a Software Engineer living just outside of one of my favorite places in the world, Cleveland, Ohio. I am a self-taught programmer who firmly believes that you can accomplish anything in this life if you put your mind to it, roll up your sleeves and are willing to put in the work.

I have been writing software for over 18+ years now and I use my knowledge, passion, and influence to help other developers achieve their goals. I teach online and currently, have over 30,000 students.

When I am not writing software, I have a number of things that really keep me busy. I love to read books, listen to podcasts and watch online courses. I have a real thirst for knowledge and it consumes me every single day of the week. I also love to run & lift heavy weights which keep me balanced throughout the week.

Please note: I reserve the right to delete comments that are offensive or off-topic.