【Spring Data JPA】模糊搜索时@Query lik

使用@Query的例子很简单

@Query(value = "select t form table t where t.id = ?1")
Table getEntityById(String id);

模糊搜索like语法也不难

@Query(value = "select t form table t where t.name like %?1%")
Table getEntityByName(String name);

可是，换个用法
测试数据如下：
表T-TABLE

表T-PERMISSION

我们希望查询出T-TABLE中，当前用户创建(creater)的记录，或者有权限的记录（根据depId和userId从T-PERMISSION中查询）
原生的SQL是：

select * from T-TABLE t, T-PERMISSION p where 1=1 and (t.creater like '%211000-02%' or (t.depId = p.depId and p.user = '211000-02'))

用JPA来实现
�用法一：

@Query(value = "select t from Table t, Permission p where 1=1 and (t.creater like %?1% or (t.depId = p.depId and p.user = ?1))")
List<Table> getTableList(String userId);

当userId =211000-02时，理想输出结果：table1,table2两条数据，实际输出：空
当userId = 211000-01时，理想输出结果：table1,table2两条数据，实际输出：�table1,table2两条数据

结果和以下SQL一致：

select * from T-TABLE t, T-PERMISSION p where 1=1 and (t.creater like '%211000-02%' or (t.depId = p.depId and p.user = '%211000-02%'))

用法二：

@Query(value = "select t from Table t, Permission p where 1=1 and ((t.depId = p.depId and p.user = ?1) or t.creater like %?1% )")
List<Table> getTableList(String userId);

当userId =211000-02时，理想输出结果：table1,table2两条数据，实际输出：�table1,table2两条数据
当userId = 211000-01时，理想输出结果：table1,table2两条数据，实际输出：table1一条数据

结果和以下SQL一致：

select * from T-TABLE t, T-PERMISSION p where 1=1 and ((t.depId = p.depId and p.user = '211000-02') or t.creater like '211000-02')

用法三：

@Query(value = "select t from Table t, Permission p where 1=1 and (t.creater like %?1% or (t.depId = p.depId and p.user like ?1))")
List<Table> getTableList(String userId);

结果编译报错：

Caused by: java.lang.IllegalArgumentException: Already found parameter binding with same index / parameter name but differing binding type! 
Already have: LikeBinding [name: null, position: 1, type: CONTAINING (1): [IsContaining, Containing, Contains]], found LikeBinding [name: null, position: 1, type: LIKE (1):                                                                                 [IsLike, Like]]! If you bind a parameter multiple times make sure they use the same binding.                                                                            

问题发生原因：
未明确，求补充〜〜

最后处理方法：使用concat()

@Query(value = "select t from Table t, Permission p where 1=1 and (t.creater like concat(concat('%',?1),'%') or (t.depId = p.depId and p.user = ?1))")
List<Table> getTableList(String userId);

参考链接:
Spring Data JPA参考文档
ProgramCreek.com
�https://jira.spring.io/browse/DATAJPA-362