CODEWITHSUNDEEP
javamysqlspring-data-jpa
xiao
xiao

Reputation: 552

Spring Data JPA how to pass a variable by using @repository

Thank U, guys! I have found the solution:

@Repository
public interface BookRepository extends JpaRepository<Book, Integer>{       
    @Query(value = "select * from Book where find_in_set(:market,market)", nativeQuery = true)
    public List<Book> findBooksByMarcket(@Param("market") String market);
}

Original question

I'm using the @Query annotation to create queries by using the JPA query language and to bind these queries directly to the methods of my repository interface.

My database is created correctly and I'm successful to create some queries except this one:

@Repository
public interface BookRepository extends JpaRepository<Book, Integer>{       
    @Query("select b from Book b where find_in_set(:market,b.market)")
    public List<Book> findBooksByMarcket(@Param("market") String market);
}

I can get the correct result by using the find_in_set function when I check it though MySql. But I cannot reach to pass a variable in java. I have searched though the internet but I cannot find the correct format for it.

please help and thank you guys!

Upvotes: 3

Views: 7787

Answers (3)

Radu Dumbrăveanu
Radu Dumbrăveanu

Reputation: 1416

A quick solution is to transform the JPQL query to a native query (by setting the nativeQuery flag to true): 

@Query(value = "select * from Book b where find_in_set(:market,b.market)", nativeQuery = true)
public List<Book> findBooksByMarcket(@Param("market") String market);

Upvotes: 3

Lokesh Kumar Gaurav
Lokesh Kumar Gaurav

Reputation: 726

Try this

@Repository
public interface BookRepository extends JpaRepository<Book, Integer>{       
    @Query("select b from Book b where find_in_set(?1,b.market)")
    public List<Book> findBooksByMarcket(String market);
}

Upvotes: 0

dimirsen Z
dimirsen Z

Reputation: 891

If you have a custom MySQL function and want to utilize it in a JPA repository, please take a look at tip 1 There is another way to do it using CriteriaBuilder (I used this mechanism along with JPA specification): tip 2 Key words for your search: custom db function, JPA specification, CriteriaBuilder, Criteria

Upvotes: 0

Related Questions