Spring Data CrudRepository @Query 与 LIKE 和 IgnoreCase [英] Spring Data CrudRepository @Query With LIKE and IgnoreCase

问题描述

我需要从 CrudRepository 中检索一些实体字段:

公共类用户{私人字符串名称；//getter 和 setter}公共接口 UserRepository 扩展了 CrudRepository{@Query("SELECT U.name FROM User U WHERE LOWER(U.name) LIKE LOWER(?1)")列表<字符串>findByName(String matchPhrase);}

基本上，我想获得等效的 SQL 查询:

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

问题是@Query不起作用(返回空列表)，hibernate生成日志:

<块引用>

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

我实际上不知道如何指定附加了 % 的参数.

//在编译时也失败@Query("SELECT U.name FROM User U WHERE LOWER(U.name) LIKE LOWER(?1%)")

<块引用>

org.hibernate.hql.internal.ast.QuerySyntaxException:意外标记:空近线...

这工作正常，但返回整个实体，可能会产生长响应，因为我只需要检索特定字段:

ListfindByNameStartingWithIgnoreCase(字符串匹配);

解决方案

试试这个

@Query("SELECT U.name FROM User U WHERE LOWER(U.name) LIKE LOWER(concat(?1, '%'))")列表<字符串>findByName(String matchPhrase);

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);

解决方案

Try this

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

这篇关于Spring Data CrudRepository @Query 与 LIKE 和 IgnoreCase的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！