spring data JPA: How to execute aggregate functions

Question

I am using Spring Data JPA and I have a requirement where I need to execute the SQL query as below:

select key_1, sum(key_2), sum(key_3) from table_1 
where key_1 in ('1', '2') and {dynamic_columns} group by key_1;

For dynamic columns, I am using Spring Data JPA Specifications but I am not sure how can I write the aggregate functions like sum() on multiple columns.

TOlson05 · Accepted Answer · 2018-12-19 16:12:21Z

2

With Spring Data JPA you have a few options in a spring repository.

References: Baeldung - Spring data JPA Spring.io - Spring data JPA

Specify a native query

@Query(value="select key_1, sum(key_2) ...", nativeQuery=true)
Optional<Object[]> getAggregates;

Use the providers (HQL for hibernate etc. query language)

@Query(value="select e.key_1, sum(e.key_2) from entity e ...")
Optional<Object[]> getAggregates;

You'll use objects here because you are not returning a specific entity you are adding custom (aggregate) columns. If you were returning a specific entity with a JPA repository you could return that entity instead of Object[]. Each item inside the object array will correspond to a columnof data, if you had multiple rows here you would use:

Optional<List<Object[]>> getAggregates;

Finally, if you have not used optionals before you will get your object array by:

if(objectsOptional.isPresent()) {
    Objects[] objects = objectsOptional.get();
    ...
}

If this isn't what you were looking for i'll need more information to help you out.

answered Dec 19, 2018 at 16:12

TOlson05

412 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JDev Over a year ago

thanks for taking time to help. The above options are not valid in my case as the where clause is condition based. For generating the where clause, I am using Spring-Data-JPA Specification but seem's that doesn't support aggregate functions.

Frischling Over a year ago

the Object[] hint (or Any[] in kotlin) was the tip I needed.

Collectives™ on Stack Overflow

spring data JPA: How to execute aggregate functions

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related