Spring-boot jpa how to find entity with max value

spring-boot jpa linked-tables

passer · Jan 10, 2019 · Viewed 22.6k times · Source

Lets tell I have two tables.

CREATE TABLE user (ID int AUTO_INCREMENT,PRIMARY KEY (ID));
CREATE TABLE points (ID int AUTO_INCREMENT, user_id int, points int,PRIMARY KEY (ID));

How can I use spring-boot jpa to request user and max points like this?

select u.ID,max(p.points) from user u, points p where u.id=p.user_id

Or any alternatives to solve this kind of problems?

Answer

Assuming you have a Repository of User:

public class User {
    private int id;
    private List<Point> points;
    ...
}

With a relationship to the Points object:

public class Point {
    private int id;
    private User User;
    private int points;
    ...
}

I haven't tested, but you should be able to do:

User findFirstByIdOrderByPointPointsDesc(int userId)

Similar to example 18 in the docs.

The only problem you have, regardless of the query or Spring Data, is if you have two users with the same point values. If you need more logic around tie-breaking, it might be more worth it to write a @Query (with your query, plus the extra tie-breaking logic) or a @NativeQuery.

Spring-boot jpa how to find entity with max value

Answer

Related questions