spring-data-jpa 如何将一个对象作为set Params传输到Spring中的派生查询方法?

oxalkeyp  于 2022-11-10  发布在  Spring
关注(0)|答案(1)|浏览(194)

我有一个Pojo(TomDto),其中的参数是累积的,根据它编译本地查询。

@Query(value = """
            SELECT *
            FROM tom_schema.tom t
            WHERE t.title ILIKE LOWER(concat('%', :#{#tomDto.title}, '%'))
                AND
                  t.completed = :#{#tomDto.completed}
                AND
                  t.priority_id IS NULL
               OR t.priority_id = :#{#tomDto.priorityId}
                AND
                  t.category_id IS NULL
               OR t.category_id = :#{#tomDto.categoryId}
                AND
                  t.task_date IS NULL
               OR t.task_date BETWEEN cast(:#{#tomDto.dateFrom} as timestamp) 
                                         AND 
                                      cast(:#{#tomDto.dateTo} as timestamp)
                AND
                  t.user_id = :#{#tomDto.userId};
                            """, nativeQuery = true)
    Page<Tom> findWithMultiParam(TomDto tomDto, Pageable pageable);

但在请求过程中,我收到一个错误:

{
    "status": "INTERNAL_SERVER_ERROR",
    "message": "Could not locate named parameter [__$synthetic$__7], expecting one of [__$synthetic$__4, __$synthetic$__3, __$synthetic$__6, __$synthetic$__5, __$synthetic$__7;, __$synthetic$__2, __$synthetic$__1]; nested exception is java.lang.IllegalArgumentException: Could not locate named parameter [__$synthetic$__7], expecting one of [__$synthetic$__4, __$synthetic$__3, __$synthetic$__6, __$synthetic$__5, __$synthetic$__7;, __$synthetic$__2, __$synthetic$__1]"
}

也许有想法的问题可能是什么或者你可以重写查询,但只是在方法参数中不使用这些参数(5-6个参数在方法参数中指定是不方便的)?

oxcyiej7

oxcyiej71#

@Query(value = """
            SELECT *
            FROM tom_schema.tom t\040
            WHERE t.title ILIKE LOWER(concat('%', :#{#tomDto.title}, '%'))\040
                AND\040
                  t.completed = :#{#tomDto.completed}\040
                AND\040
                  t.priority_id IS NULL\040
               OR t.priority_id = :#{#tomDto.priorityId}\040
                AND
                  t.category_id IS NULL
               OR t.category_id = :#{#tomDto.categoryId}\040
                AND\040
                  t.task_date IS NULL\040
               OR t.task_date BETWEEN cast(:#{#tomDto.dateFrom} as timestamp)\040\040
                                         AND\040\040
                                      cast(:#{#tomDto.dateTo} as timestamp)\040
                AND\040
                  t.user_id = :#{#tomDto.userId}\040
                            """, nativeQuery = true)
    Page<Tom> findWithMultiParam(TomDto tomDto, Pageable pageable);

它需要补充一点:\040(文本块中的尾随空格)
我添加了更多的空格,因为在传输行时,我可能忘记了在Sql语句之间放置空格。

相关问题