京公网安备11010502063487号动态查询为什么用 Specification?条件可能没有的时候怎么写?join 一对多之后分页为什么少数据?拼 like 的时候用户输入会不会有问题?这篇在 Spring Boot 4.0.8、Spring Data JPA 4、Hibernate 7 上逐个实测。
name like 'lee' ⇒ 2 行 [Ann Lee, Bob Lee]
select … from person p1_0 where lower(p1_0.name) like ? escape ''
minAge 30 and city Rome(Specification.where(…).and(…)) ⇒ 2 行 [Ann Lee, x' or '1'='1]
select … from person p1_0 where p1_0.age>=? and p1_0.city=?
city Rome or city Oslo ⇒ 8 行 [Ann Lee, Bob Lee, Cid Ray, Dee Ray, 50%off, 500off, Eve Poe, x' or '1'='1]
select … from person p1_0 where p1_0.city=? or p1_0.city=?
not(city Rome) ⇒ 4 行 [Cid Ray, Dee Ray, 50%off, 500off]
select … from person p1_0 where p1_0.city<>?
Specification.where(null) ⇒ 抛 IllegalArgumentException:Specification must not be null
spec.and(null) ⇒ 抛 IllegalArgumentException:Other specification must not be null
Specification.unrestricted() ⇒ 8 行 [Ann Lee, Bob Lee, Cid Ray, Dee Ray, 50%off, 500off, Eve Poe, x' or '1'='1]
select … from person p1_0
所有条件都不成立(三个辅助方法都返回 Specification.unrestricted()) ⇒ 8 行 [Ann Lee, Bob Lee, Cid Ray, Dee Ray, 50%off, 500off, Eve Poe, x' or '1'='1]
select … from person p1_0
-- 动态查询:三个可选条件 name / minAge / city,8 种组合
name=null minAge=null city=null → 8 行;SQL 条数 1,(没有 where)
name=e minAge=null city=null → 4 行;SQL 条数 1, where lower(p1_0.name) like ? escape ''
name=null minAge=25 city=null → 6 行;SQL 条数 1, where p1_0.age>=?
name=e minAge=25 city=null → 2 行;SQL 条数 1, where lower(p1_0.name) like ? escape '' and p1_0.age>=?
name=null minAge=null city=Rome → 4 行;SQL 条数 1, where p1_0.city=?
name=e minAge=null city=Rome → 3 行;SQL 条数 1, where lower(p1_0.name) like ? escape '' and p1_0.city=?
name=null minAge=25 city=Rome → 3 行;SQL 条数 1, where p1_0.age>=? and p1_0.city=?
name=e minAge=25 city=Rome → 2 行;SQL 条数 1, where lower(p1_0.name) like ? escape '' and p1_0.age>=? and p1_0.city=?and、or、not 分别生成 and、or、<>;没有条件用 Specification.unrestricted()。
findAll(minAge 20, PageRequest.of(0, 3, Sort.by("age"))) ⇒ Page(内容 3 行,总数 7)
select … from person p1_0 where p1_0.age>=? order by p1_0.age limit ?,?
select count(p1_0.id) from person p1_0 where p1_0.age>=?
findAll(city Oslo, PageRequest.of(1, 2)) ⇒ Page(内容 2 行,总数 4)
select … from person p1_0 where p1_0.city=? limit ?,?
select count(p1_0.id) from person p1_0 where p1_0.city=?
count(city Oslo) ⇒ 4
select count(p1_0.id) from person p1_0 where p1_0.city=?
exists(city Mars) ⇒ false
select … from person p1_0 where p1_0.city=? limit ?
findOne(name like 'cid') ⇒ Optional[Cid Ray]
select … from person p1_0 where lower(p1_0.name) like ? escape '' limit ?数据与 count 用同一份 Specification;exists 与 findOne 加 limit。
phone like '555' 不 distinct ⇒ 2 行 [Ann Lee, Cid Ray]
select … from person p1_0 join phone p2_0 on p1_0.id=p2_0.owner_id where p2_0.number like ? escape ''
phone like '555' + query.distinct(true) ⇒ 2 行 [Ann Lee, Cid Ray]
select distinct … from person p1_0 join phone p2_0 on p1_0.id=p2_0.owner_id where p2_0.number like ? escape ''
Page:phone like '555' 不 distinct,每页 2 ⇒ Page(内容 1 行,总数 1)
select … from person p1_0 join phone p2_0 on p1_0.id=p2_0.owner_id where p2_0.number like ? escape '' limit ?,?
Page:phone like '555' + distinct,每页 2 ⇒ Page(内容 2 行,总数 2)
select distinct … from person p1_0 join phone p2_0 on p1_0.id=p2_0.owner_id where p2_0.number like ? escape '' limit ?,?
select distinct count(distinct p1_0.id) from person p1_0 join phone p2_0 on p1_0.id=p2_0.owner_id where p2_0.number like ? escape ''带集合 join 的分页要 distinct,否则 Page 少数据。
rawContains("50%")(自己拼 %) ⇒ 2 行 [50%off, 500off]
select … from person p1_0 where p1_0.name like ? escape ''
safeContains("50%")(转义 % 和 _ 并声明 escape) ⇒ 1 行 [50%off]
select … from person p1_0 where p1_0.name like ? escape '\\'
rawContains("x' or '1'='1")(想注入) ⇒ 1 行 [x' or '1'='1]
select … from person p1_0 where p1_0.name like ? escape ''参数绑定挡住注入,通配符要自己转义。