2

I am trying to get records from MYSQL while posting a GET request from POSTMEN. The SQL query works in MYSQL console but not working in JPA Repository.

@Repository
public interface RecipeRepository extends JpaRepository<Recipe, Long> {
@Query(value = "select * from Recipe where Recipe.id in (select Recipe_id  
    from Recipe_keywords where keywords like '%:keyword_rec%') ", nativeQuery = true)
List<Recipe> findByKeyword(@Param("keyword_rec") String keyword_rec);
}

Actual Result

No data selected

Expected Result

A row is available in database which is being returned as a result of query while used with MYSQL console which should be returned here as well.

Update by updating the query as suggested, no changes were made in the output.Here is the printed query

select * from Recipe r where r.id in (select rk.Recipe_id  from Recipe_keywords rk where rk.keywords like '%:keyword_rec%')
Lily
  • 605
  • 3
  • 15
  • 31
  • Set flag show sql to true and check the query. https://stackoverflow.com/questions/30118683/how-to-log-sql-statements-in-spring-boot – Lemmy Jul 16 '19 at 20:35

2 Answers2

1

Error was resolved changing my query to 

select * from Recipe r where r.id in (select rk.Recipe_id  from Recipe_keywords rk where rk.keywords like %:keyword_rec%)
Lily
  • 605
  • 3
  • 15
  • 31
0

Because the query you write on MySQL will be slightly different from the query you will write for JPA.

For example, SELECT * FROM Customer becomes "SELECT c FROM Customer c"

Have a look at the link: https://www.oracle.com/technetwork/articles/vasiliev-jpql-087123.html

Romil Patel
  • 12,879
  • 7
  • 47
  • 76
Mohammed Jaffer Ali
  • 29
  • 2