Spring-boot jpa how to find entity with max value

Question

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 1

I usually create a class to hold result such as

public class Result {

private User user;
private int votes;
// getters and setters 
}

And write a custom query in the repository to fetch the data

@Query(value = "SELECT new com.package.Result (u, MAX (p.points) ) 
FROM user u
JOIN points p
ON u.id = p.user_id 
GROUP BY u")
List<Result> getPointsPerUser();    

Replace com.package.Result with appropriate path to the Result class.