CODEWITHSUNDEEP
spring-bootjpalinked-tables
passer
passer

Reputation: 163

Spring-boot jpa how to find entity with max value

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?

Upvotes: 8

Views: 71201

Answers (4)

Sandeep Jain
Sandeep Jain

Reputation: 1259

Below method can be written in Repo and used as Transaction as in dao layer, which will be accessible from service layer.

@Query(value = "SELECT COALESCE(MAX(transactionId), 0) FROM TransactionPayloadInfo")
int getMaxTransactionId();

Upvotes: 4

Lokendra solanki
Lokendra solanki

Reputation: 51

create a model of data.

public class Data {
 private int id;
  private int maxPoints;
 // getters and setters method  
 }

And write your query like this for getting model of Data.

 @Query(select packagename.Data(u.ID,max(p.points) ) from user u, points p where   u.id=p.user_id)
 Data findUserWithMaxVots();

Upvotes: 0

Sujit
Sujit

Reputation: 415

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.

Upvotes: 5

Dovmo
Dovmo

Reputation: 8749

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.

Upvotes: 10

Related Questions