Spring Boot Mysql query to get all records based on the @query value

Question

I am trying to retreive all records from a MySQL table using spring boot JPA. Following is the repositry definition

MovieRepository class

@Query("From MovieEntity m, TheaterMovieShowEntity tms, TheaterEntity t where t.city=?1 and t.theaterid=tms.theaterid and m.movieid=tms.movieid and t.seatsavailable>=?2")
    List<Movie> getMoviesDetails(String cityName, int noOfTickets)throws MovieException;

MovieService Class

public List<Movie> getMoviesDetails(String cityName, int noOfTickets)throws MovieException{
        List<Movie>moviesIntheCityList = new ArrayList<Movie>();
        **moviesIntheCityList = (List<Movie>) movieRepository.getMoviesDetails(cityName, noOfTickets);**
        System.out.println("in the getMoviesDetails after querying"+moviesIntheCityList); // This is null
        return moviesIntheCityList;
    }

What am i missing?

score 0 · Answer 1 · answered Dec 10 '19 at 06:35

0

In your Repository File use this way

List<Movie> findByCityNameAndNoOfTickets(String cityName, int noOfTickets)

refer Query Creation

answered Dec 10 '19 at 06:35

dasunse

2,839
1
14
32

score 0 · Answer 2 · answered Dec 10 '19 at 06:49

0

Just use

List<Movie> movieRepository.findByCityNameAndNoOfTickets(String cityName, int noOfTickets);

in your MovieRepository. And then call it. In Spring Data JPA if you follow proper naming conventions then you got to only declare the name of method in Repository and JPA will implement it for you.

answered Dec 10 '19 at 06:49

dxjuv

879
1
8
28

Here the 2 query parameters are in 2 different tables. cityName is in table Movie while noOfTickets is in table Theater. How to handle this tpe of query which needs to filter results based on values across 2 different tables. – mack Dec 10 '19 at 22:56

Spring Boot Mysql query to get all records based on the @query value

2 Answers2