CODEWITHSUNDEEP
hibernaterestspring-bootspring-data-jpa
Lily
Lily

Reputation: 707

SQL statement not working while using with JPA Repository

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%')

Upvotes: 2

Views: 2581

Answers (2)

Mohammed Jaffer Ali
Mohammed Jaffer Ali

Reputation: 29

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

Upvotes: 0

Lily
Lily

Reputation: 707

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%)

Upvotes: 1

Related Questions