CODEWITHSUNDEEP
springparametersspring-data-jpajpql
tobi
tobi

Reputation: 863

JPA, why named parameter in native query is not replaced?

I tried both, indexed and named parameters, but it doesn't work:

public interface CharacterRepository extends JpaRepository<Character, Long> {
    @Query(nativeQuery=true, value="SELECT * FROM Character WHERE pinyin like '%:keyword%'")
    List<Character> findByKeyword(@Param("keyword") String keyword);
}

The outcoming sql is:

Hibernate: 

   SELECT
        * 
    FROM
        Character 
    WHERE
        pinyin like '%:keyword%'

Why is the keyword-placeholder not replaced by the parameter I actually pass?

Upvotes: 5

Views: 5684

Answers (3)

Cepr0
Cepr0

Reputation: 30309

Try this:

JPQL/HQL

select c from Character c where c.pinyin like %:keyword%
select c from Character c where c.pinyin like concat('%', :keyword, '%')

Native (PostrgeSQL/MySQL)

select * from character c where c.pinyin like concat('%', :keyword, '%')

Native (PostrgeSQL)

select * from character c where c.pinyin like '%' || :keyword || '%'

Alternative variant with Spring Data JPA query method

List<Character> findByPinyinContaining(String pinyin);

Additional info:

Upvotes: 0

Sai prateek
Sai prateek

Reputation: 11896

Your query should be like this -

@Query(nativeQuery=true, value="SELECT * FROM Character c WHERE c.pinyin like %:keyword%")
 List<Character> findByKeyword(@Param("keyword") String keyword);

Hope it help.

Upvotes: 3

Brian Vosburgh
Brian Vosburgh

Reputation: 3276

You might try this:

public interface CharacterRepository extends JpaRepository<Character, Long> {
    @Query(nativeQuery=true, value="SELECT * FROM Character WHERE pinyin like ':keyword'")
    List<Character> findByKeyword(@Param("keyword") String keyword);
}

and call the method like this:

repository.findByKeyword("%myKeyword%");

Upvotes: 0

Related Questions