英文:
Type mismatch error in custom jpa query with 'like' operator
问题
我正在尝试编写这样的选择查询（在控制台中运行正常）
select * from orders o where (o.id like '%1%')
与以下代码：
@Query("SELECT o from Order o where o.id like %:searchQuery%")
Page<Order> findAllBySearchQuery(Long searchQuery, Pageable pageable);
但是IDEA显示o.id具有“类型不匹配：期望字符串类型”和当我尝试调用该方法时，我得到“java.lang.IllegalArgumentException: 参数值[%1%]与期望类型不匹配[java.lang.Long（n/a）]”，
我找不到解决方法。我只找到了如何使用“nativeQuery = true”来解决，但它不支持“Pageable”。
id的类型是bigint。MariaDB。
英文:
I'm trying to write such select (it works from console)
select * from orders o where (o.id like '%1%')
with
@Query("SELECT o from Order o where o.id like %:searchQuery%")
Page<Order> findAllBySearchQuery(Long searchQuery, Pageable pageable);
but IDEA shows that o.id has Type mismatch: string type expected and when I try to call the method I get java.lang.IllegalArgumentException: Parameter value [%1%] did not match expected type [java.lang.Long (n/a)],<br><br>
I can't find solution for this. I found only how to do it with nativeQuery = true, but it doesn't support Pageable.
Type of id is bigint. MariaDB.
答案1
得分: 1
正确的方法是将字符串绑定到已包含通配符的占位符：
@Query("SELECT o from Order o where o.id like :searchQuery")
Page<Order> findAllBySearchQuery(String searchQuery, Pageable pageable);
在使用 findAllBySearchQuery 时，您应该将 searchQuery 绑定为类似 %10% 的内容，假设您想查找包含数字 10 的任何 id。例如：
findAllbySearchQuery("%10%", somePageable);
这里还存在另一个潜在的问题，即 Order#id 的类型以及实际 SQL 表中的相应类型。理想情况下，它也应该是文本类型，否则尝试将文本与数字进行比较是没有意义的。
编辑： 鉴于您已经透露您的 id 列是一个整数，您可以尝试首先将其转换为文本，然后进行 like 比较：
@Query("SELECT o from Order o where cast(o.id as string) like :searchQuery")
Page<Order> findAllBySearchQuery(String searchQuery, Pageable pageable);
以上是您要翻译的内容。
英文:
The correct approach here to bind a string to the placeholder which already contains the wildcards:
@Query("SELECT o from Order o where o.id like :searchQuery")
Page<Order> findAllBySearchQuery(String searchQuery, Pageable pageable);
When using findAllBySearchQuery, you should bind to searchQuery something like %10%, assuming you wanted to find any id which contained the number 10. For example, call:
findAllbySearchQuery("%10%", somePageable);
There is another potential issue here, namely the type of Order#id, and the corresponding type in the actual SQL table. Ideally, it should be text as well, otherwise it does not make sense trying to compare text against a number.
Edit: Given that you have revealed your id column to be an integer, you may try first casting it to text before doing the like comparison:
@Query("SELECT o from Order o where cast(o.id as string) like :searchQuery")
Page<Order> findAllBySearchQuery(String searchQuery, Pageable pageable);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论