Issue
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.
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
Solution
I have examined your GitHub project and can say, that, actually, it is a known issue of the spring-data-jpa framework of version 2.7.0 and higher. There are registered Issues on GitHub, e.g., this or this. It had been closed as resolved, but it seems to be not completely fixed, IMHO. So I've started another one issue there. I have also created a pull request with a quick-fix, that makes the framework to behave like before.
So at the moment to overcome this problem you can just downgrade the spring boot version to 2.6.x, or go with hot fixes, built by yourself.
Answered By - Yuriy Tsarkov
Answer Checked By - Candace Johnson (JavaFixing Volunteer)
