6

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?

passer
  • 123
  • 1
  • 2
  • 7

4 Answers4

7

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.

Dovmo
  • 8,121
  • 3
  • 30
  • 44
5

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.

Sujit
  • 404
  • 5
  • 14
  • Thanks for your answer. But what if I need some other information for points record? For example if I need maximum points and when user got that points? – passer Jan 13 '19 at 14:27
  • If you need more columns from `points` table, you can fetch the `points` object rather than using aggregate function. You can replace the select with `u, p` and remove the `GROUP BY` clause. Also modify the `Result` class to accept `points`. This way you have the all the data in the both the table. The drawback is you now have to find the maximum from the `List`. – Sujit Jan 13 '19 at 22:25
3

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 max(transactionId) FROM TransactionPayloadInfo")
int getMaxTransactionId();
Ali Behzadian Nejad
  • 8,804
  • 8
  • 56
  • 106
Sandeep Jain
  • 1,019
  • 9
  • 13
0

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();
Lokendra solanki
  • 51
  • 3
  • Thanks for your answer. But what if I need some other information for points record? For example if I need maximum points and when user got that points? – passer Jan 13 '19 at 14:39