Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Spring Data CrudRepository @Query With LIKE and IgnoreCase

I need to retrieve some Entity fields from CrudRepository:

public class User {
    private String name;

    // getters and setters
}

public interface UserRepository extends CrudRepository<User, Long> { 

   @Query("SELECT U.name FROM User U WHERE LOWER(U.name) LIKE LOWER(?1)")
   List<String> findByName(String matchPhrase);
}

Basically, I want to get equivalent of SQL query:

SELECT u.name FROM user u WHERE LOWER(u.name) LIKE LOWER('match%')

The problem is that @Query doesn't works (empty list returned), hibernate generates log:

Hibernate: select user0_.name as col_0_0_ from user user0_ where lower(user0_.name) like lower(?)

I actually didn't get how to specify a parameter bound with appended %.

// also fails at compile-time
@Query("SELECT U.name FROM User U WHERE LOWER(U.name) LIKE LOWER(?1%)")

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: null near line ...

This works fine but returns whole Entity, what may produce long response because I need retrieve only specific fields:

List<User> findByNameStartingWithIgnoreCase(String match);
like image 762
J-Alex Avatar asked Nov 01 '16 08:11

J-Alex


People also ask

Is it possible to define a sort type in @query annotation using Spring JPA?

Spring Data JPA allows you to add a Sort parameter to your query method. The Sort class is only a specification that provides sorting options for database queries. With dynamic sorting, you can choose the sorting column and direction at runtime to sort the query results.

What is true about CrudRepository and JpaRepository in Spring data JPA?

Their main functions are: CrudRepository mainly provides CRUD functions. PagingAndSortingRepository provides methods to do pagination and sorting records. JpaRepository provides some JPA-related methods such as flushing the persistence context and deleting records in a batch.

What is @query used for spring data?

Spring Data JPA @Query The @Query annotation declares finder queries directly on repository methods. While similar @NamedQuery is used on domain classes, Spring Data JPA @Query annotation is used on Repository interface. This frees the domain classes from persistence specific information, which is a good thing.

Should I use JpaRepository or CrudRepository?

Crud Repository doesn't provide methods for implementing pagination and sorting. JpaRepository ties your repositories to the JPA persistence technology so it should be avoided. We should use CrudRepository or PagingAndSortingRepository depending on whether you need sorting and paging or not.


1 Answers

Try this

@Query("SELECT U.name FROM User U WHERE LOWER(U.name) LIKE LOWER(concat(?1, '%'))")
List<String> findByName(String matchPhrase);
like image 81
Abdullah Khan Avatar answered Sep 30 '22 16:09

Abdullah Khan