Spring jpa "where param is null" not working-CodePudding

Reproduced code can be found here (Github).

Sorry for my bad English.

I'm using spring boot web, jpa, mysql to create a simple restful endpoint.

Spring boot parent version: 2.7.0
Java version: 11 Oracle

My repo:

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

My api:

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

The CustomPageable model:

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

My problem is, if the pageable.getKeyword() is null (http://localhost:8181/ab?size=3&sort=name,DESC&page=0), I expect it will return a page contains 3 elements. Actually it return an empty page.

Log:

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;

Please take a look at the query ...where '%org.hibernate.jpa.TypedParameterValue@d41192d%' is null or test0_.name like '%org.hibernate.jpa.TypedParameterValue@d41192d%'.... I have no idea about '%org.hibernate.jpa.TypedParameterValue@d41192d%'.

Please explain to me why my HQL does not work in case keyword is null? Is it my bug or JPA bug? How can I fix this problem?

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

NOTE:

If I send the keyword (http://localhost:8181/ab?size=3&sort=name,DESC&page=0&keyword=a), then it return correctly.

Log:

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%';

Using @Query with HQL is a must, please don't suggest another ways like CriteriaBuilder, etc

CodePudding user response：

Rather than using positional parameters use named parameters

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

CodePudding user response：

You need to pass value by using :#{#keyword} parameter in query. You also need to configure @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);

}