"where param is null" not working #2650
Description
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