java - Spring Data JPA - case insensitive query w/ pattern matching -

suppose i'm implementing search functionality simple cms package (just example), , want match posts both title , content. presumably, have sort of post entity following underlying table structure:

+------------------------------------+ |               post                 | +---------+--------------+-----------+ | post_id | integer      | not null, | | title   | varchar(255) | not null, | | content | clob         | not null  | +---------+--------------+-----------+ 

next, extend spring's jparepository , add search method via @query annotation, (again, example):

public interface postrepository extends jparepository<post, integer> {      @query("select p post p lower(p.title) lower(%:searchterm%)"               + " or lower(p.content) lower(%:searchterm%) order p.title")     list<post> findbysearchterm(@param("searchterm") string searchterm); } 

the problem spring (or maybe it's underlying jpa provider, not sure) has hard time parsing query due lower(%:searchterm%) expression in where clause. i've tried other variations of syntax, e.g. %lower(:searchterm)%, none seem work far. have (and preferably clean) solution this?

p.s.: prefer use @query annotation consistency's sake. however, guess other solutions (such spring's method name -> query derivation or criteria api) welcome.

try this:

@query("select u.username user u u.username      concat('%',:username,'%')") list<string> finduserswithpartofname(@param("username") string username); 

notice: table name in jpql must start capital letter.