Reputation: 37
I have such Query where from which I want to extract movie title and its rating
@Query(value ="SELECT m.title, avg(r.rating) " +
"FROM movies m " +
"INNER JOIN " +
"ratings r " +
"ON m.movieid = r.movieid " +
"WHERE m.genres LIKE %:genre% " +
"GROUP BY m.title " +
"ORDER BY avg(r.rating) DESC " +
"LIMIT 10",
nativeQuery = true)
List<Movies> topTenMoviesByGenreLikeIgnoreCase(@Param("genre") String genre);
But this list obviously cannot store the resultSet with 2 values
After learning about it I've found e.g. this link Spring Data: JPA repository findAll() to return *Map instead of List? but their implementation seems unclear to me.
I don't understand where do they get the Long value from in the first link
default Map<Long, TransactionModel> findByClientIdMap(Long id) {
return findByClientId(id).stream().collect(Collectors.toMap(TransactionModel::getId, v -> v));
}
How do I create my map?
default Map<Movies, Float> topTenMoviesByGenreLikeIgnoreCaseMap(@Param("genre") String genre) {
return topTenMoviesByGenreLikeIgnoreCase(genre).stream().collect(Collectors.toMap());
}
Movies class:
@Entity
public class Movies {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Integer movieid;
private String title;
private String genres;
@OneToMany(mappedBy = "movieid")
private Set<Ratings> rates = new HashSet<>();
public Movies() {
}
public Movies(String title, String genres) {
this.title = title;
this.genres = genres;
}
/*Getters and Setters for variables*/
public Set<Ratings> getRates() {
return rates;
}
public void setRates(Set<Ratings> rates) {
this.rates = rates;
}
}
Ratings class:
@Entity
public class Ratings {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Integer rateid;
@ManyToOne
@JoinColumn(name = "movieid")
private Movies movieid;
@ManyToOne
@JoinColumn(name = "userid")
private Usrs userid;
private float rating;
private Integer timestamp;
public Ratings() {
}
public Ratings(Movies movieid, Usrs userid, float rating, Integer timestamp)
{
this.movieid = movieid;
this.userid = userid;
this.rating = rating;
this.timestamp = timestamp;
}
/*Getters and Setters for variables*/
}
Upvotes: 0
Views: 3379
Reputation: 2851
You can use Object[]
as a query result this way
@Query(value ="SELECT m.title, avg(r.rating) " +
"FROM movies m " +
"INNER JOIN " +
"ratings r " +
"ON m.movieid = r.movieid " +
"WHERE m.genres LIKE %:genre% " +
"GROUP BY m.title " +
"ORDER BY avg(r.rating) DESC " +
"LIMIT 10",
nativeQuery = true)
List<Object[]> topTenMoviesByGenreLikeIgnoreCase(@Param("genre") String genre);
So then
default Map<String, Float> topTenMoviesByGenreLikeIgnoreCaseMap(@Param("genre") String genre) {
return topTenMoviesByGenreLikeIgnoreCase(genre)
.stream()
.collect(Collectors.toMap((arr->arr[0].toString()), (arr->Float.valueOf(arr[1].toString()))));
}
As you can see topTenMoviesByGenreLikeIgnoreCaseMap
returns Map
where the movie title is the key. To make a Movie
entity the key you should use a JPQL
query instead of a native one.
Upvotes: 1
Reputation: 207
Try out this:
@Query(value ="SELECT m.title as title, r.avg(rating) as rates " +
"FROM movies m " +
"INNER JOIN " +
"ratings r " +
"ON m.movieid = r.movieid " +
"WHERE m.genre = LIKE %:genre% " +
"GROUP BY r.avg(rating) " +
"ORDER BY r.avg(rating) DESC " +
"LIMIT 10",
nativeQuery = true)
List<Movies> topTenMoviesByGenreLikeIgnoreCase(@Param("genre") String genre);
default Map<Movies, Set<Ratings>> topTenMoviesByGenreLikeIgnoreCaseMap(@Param("genre") String genre) {
return topTenMoviesByGenreLikeIgnoreCase(genre).stream().collect(
Collectors.toMap(
tuple -> (tuple.getTitle(),
tuple -> (tuple.getRates())
));
}
Upvotes: 0