Spring JPA“where param is NULL”不起作用

xtfmy6hx  于 2022-10-04  发布在  Spring
关注(0)|答案(3)|浏览(412)

复制的代码可以在here (Github)找到。

对不起,我的英语不好。

我正在使用Spring Boot Web、JPA、MySQL来创建一个简单的REST风格的端点。

  • Spring Boot父版本:2.7.0
  • Java版本:11 Oracle

我的回购:

public interface TestRepository extends JpaRepository<Test, Long> {

    @Query("SELECT t FROM Test t WHERE (?1 IS NULL OR t.name LIKE %?1%)")
    Page<Test> findAll(String keyword, Pageable pageable);
}

我的接口:

@GetMapping("/ab")
public Page<Test> ab(CustomPageable pageable) {
    //INSERT INTO test VALUES(1, 'aa', 1);
    //INSERT INTO test VALUES(2, 'a', 2);
    //INSERT INTO test VALUES(3, 'b', 3);
    return testRepository.findAll(pageable.getKeyword(), pageable);
}

可自定义分页模式:

public static class CustomPageable extends PageRequest {

    @Getter
    private final String keyword;

    protected CustomPageable(int page, int size, Sort sort) {
        super(page, size, sort);
        keyword = null;
    }

    public CustomPageable(int page, int size, Sort sort, String keyword) {
        super(page, size, sort);
        this.keyword = keyword;
    }
}

我的问题是,如果pageable.getKeyword()为空(http://localhost:8181/ab?size=3&sort=name,DESC&PAGE=0),我预计它将返回一个包含3个元素的页面。实际上,它返回一个空页。

日志:

2022-10-02 15:40:00.967  INFO 6560 --- [nio-8181-exec-6] p6spy                                    : #1664700000967 | took 2ms | statement | connection 4| url jdbc:mysql://localhost:3306/test
select test0_.id as id1_0_, test0_.name as name2_0_, test0_.status as status3_0_ from test test0_ where ? is null or test0_.name like ? order by test0_.name desc limit ?
select test0_.id as id1_0_, test0_.name as name2_0_, test0_.status as status3_0_ from test test0_ where '%org.hibernate.jpa.TypedParameterValue@d41192d%' is null or test0_.name like '%org.hibernate.jpa.TypedParameterValue@d41192d%' order by test0_.name desc limit 3;

请看查询...where '%org.hibernate.jpa.TypedParameterValue@d41192d%' is null or test0_.name like '%org.hibernate.jpa.TypedParameterValue@d41192d%'...。我对‘%org.hibernate.jpa.TypedParameterValue@d41192d%’.一无所知

请向我解释为什么我的HQL在关键字为空的情况下不起作用?是我的错误还是JPA的错误?我如何解决这个问题?

====================================================================================

注:

如果我发送关键字(http://localhost:8181/ab?size=3&sort=name,DESC&PAGE=0&Keyword=a),那么它将正确返回。

日志:

2022-10-02 15:42:00.659  INFO 6560 --- [io-8181-exec-10] p6spy                                    : #1664700120658 | took 3ms | statement | connection 6| url jdbc:mysql://localhost:3306/test
select test0_.id as id1_0_, test0_.name as name2_0_, test0_.status as status3_0_ from test test0_ where ? is null or test0_.name like ? order by test0_.name desc limit ?
select test0_.id as id1_0_, test0_.name as name2_0_, test0_.status as status3_0_ from test test0_ where '%a%' is null or test0_.name like '%a%' order by test0_.name desc limit 3;
2022-10-02 15:42:00.665  INFO 6560 --- [io-8181-exec-10] p6spy                                    : #1664700120665 | took 4ms | statement | connection 6| url jdbc:mysql://localhost:3306/test
select count(test0_.id) as col_0_0_ from test test0_ where ? is null or test0_.name like ?
select count(test0_.id) as col_0_0_ from test test0_ where '%a%' is null or test0_.name like '%a%';

在HQL中使用@Query是必须的,请不要建议使用其他方式,如CriteriaBuilder等

wr98u20j

wr98u20j1#

我已经检查了您的GitHub项目,可以说,实际上,这是2.7.0版和更高版本的spring-data-jpa框架的一个已知问题。GitHub上存在注册问题,例如thisthis。它已经被关闭了,但似乎并没有完全修复,IMHO。所以我开始了另一个issue there。我还创建了一个带有快速修复的pull request,它使框架的行为与以前一样。

因此,目前要克服这个问题,你可以将Spring Boot版本降级到2.6.x,或者使用自己开发的热修复程序。

fzsnzjdm

fzsnzjdm2#

使用命名参数而不是使用位置参数

@Query("SELECT t FROM Test t WHERE (:keyword IS NULL OR t.name LIKE %:keyword%)")
    Page<Test> findAll(@Param("keyword")String keyword, Pageable pageable);
lokaqttq

lokaqttq3#

需要在查询中使用:#{#keyword}参数传值。您还需要配置@Param("keyword")

public interface TestRepository extends JpaRepository<Test, Long> {

    @Query("SELECT t FROM TestEntity t WHERE (:#{#keyword} IS NULL OR t.name LIKE %:#{#keyword}%)")
    Page<Test> findAll(@Param("keyword") String keyword, Pageable pageable);

}

相关问题