CODEWITHSUNDEEP
mysqlspringhibernate
Bavithran Mahendran
Bavithran Mahendran

Reputation: 13

Perform Complex Query in hibernate

SELECT * FROM reviews 
   WHERE id in (SELECT review_id FROM reviews_businesses_users_bridge WHERE user_id = 1)
Union 
SELECT * FROM reviews 
   WHERE id in (select review_id FROMreviews_drivers_users_bridge WHERE user_id = 1);

This query is perfectly working with Workbench, but when I use this query in my application its giving error..

@Query(value = "SELECT * FROM reviews WHERE id in (SELECT review_id FROM reviews_businesses_users_bridge WHERE user_id = ?) Union SELECT * FROM reviews WHERE id in (select review_id FROM reviews_drivers_users_bridge WHERE user_id = ?)",nativeQuery = true)
List<Review> getAllReceivedReviewsByUserId(Long userId);

and error in log is "Did not find handler method", this doesn't makes any sense for me, because if I let query like

@Query(value = "SELECT * FROM reviews WHERE id in (SELECT review_id FROM reviews_businesses_users_bridge WHERE user_id = ?)",nativeQuery = true)
List<Review> getAllReceivedReviewsByUserId(Long userId);

then its working perfectly, when I add some more complexity then it shows the error.

Upvotes: 1

Views: 48

Answers (1)

Alexey
Alexey

Reputation: 2488

Try using named params. You use two question marks, so hibernate expects second parameter to be filled also

@Query(value = "SELECT * FROM reviews WHERE id in (SELECT review_id FROM reviews_businesses_users_bridge WHERE user_id = :userId) Union SELECT * FROM reviews WHERE id in (select review_id FROM reviews_drivers_users_bridge WHERE user_id = :userId)",nativeQuery = true)
List<Review> getAllReceivedReviewsByUserId(@Param("userId") Long userId);

Upvotes: 2

Related Questions