creating Spring Repository Select using multiple "like" and Pageable

Question

I'm using H2-Database and Spring-Data PagingRepository. I like to use paging and select all articles from database that field html contains '@' or '[at]' or '(at)' or 'mail' ...

So the native query without paging looks like

SELECT * FROM article  WHERE (html LIKE '%@%' or html LIKE '%(at)%' or html LIKE '%[at]%' or ...)

I tried to solve it like this:

Page<Article> findByHtmlContainingOrderByDateDesc(String[] string,  Pageable pageable);

But there is no/empty result.

Second way I tried is using a native query:

@Query(value = "SELECT * FROM article  WHERE (html LIKE '%@%' or html LIKE '%(at)%') ORDER BY DATE desc \n#pageable\n" , 
nativeQuery = true,
countQuery = "SELECT count(*) FROM article  WHERE (html LIKE '%@%' or html LIKE '%(at)%')")
Page<Article> findAll(@Param("pageable") Pageable pageable);

But I'm getting the exception:

nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement] with root cause

Thanks a lot for help!

have you looked at http://stackoverflow.com/questions/38349930/spring-data-and-native-query-with-pagination there are db dependent variations of the #pageable magic. — Jens Schauder, May 03 '17 at 09:27

score 1 · Accepted Answer · answered May 07 '17 at 08:55

The solution, that worked for me was adding \n-- #pageable\n to the custom query. So I ended up at

@Query(value = "SELECT * FROM article  WHERE (html LIKE '%@%' or html LIKE '%(at)%') ORDER BY DATE desc \n-- #pageable\n" , nativeQuery = true,
    countQuery = "SELECT count(*) FROM article  WHERE (html LIKE '%@%' or html LIKE '%(at)%')")
Page<Article> findAll(@Param("pageable") Pageable pageable);

creating Spring Repository Select using multiple "like" and Pageable

1 Answers1