京公网安备11010502063487号方法名里的 findByAgeBetween、Top3、IgnoreCase 到底生成什么 SQL?Containing 里的 % 会不会被当通配符?deleteBy 是一条 delete 吗?方法名写错了什么时候才发现?这篇在 Spring Boot 4.0.8、Hibernate 7、MySQL 上把 20 多个方法名都跑一遍并抓出 SQL。
findByLastName("Lee") ⇒ 返回 3 行 [Ann Lee, Bob Lee, Fay lee]
select … from person p1_0 where p1_0.last_name=?
findByFirstNameAndLastName("Ann","Lee") ⇒ 返回 1 行 [Ann Lee]
select … from person p1_0 where p1_0.first_name=? and p1_0.last_name=?
findByFirstNameOrLastName("Ann","Ray") ⇒ 返回 3 行 [Ann Lee, Cid Ray, Dee Ray]
select … from person p1_0 where p1_0.first_name=? or p1_0.last_name=?
findByAgeGreaterThanEqual(40) ⇒ 返回 4 行 [Cid Ray, 50%off Sale, 500off Sale, Fay lee]
select … from person p1_0 where p1_0.age>=?
findByAgeBetween(20,30) ⇒ 返回 3 行 [Ann Lee, Bob Lee, Eve Poe]
select … from person p1_0 where p1_0.age between ? and ?
findByAgeIn([19,22,25]) ⇒ 返回 3 行 [Bob Lee, Dee Ray, Eve Poe]
select … from person p1_0 where p1_0.age in (?,?,?)
findByCityIsNull() ⇒ 返回 1 行 [Dee Ray]
select … from person p1_0 where p1_0.city is null
findByActiveTrue() ⇒ 返回 6 行 [Ann Lee, Cid Ray, Dee Ray, 50%off Sale, 500off Sale, Eve Poe]
select … from person p1_0 where p1_0.active=true
findByCityNot("Rome") ⇒ 返回 4 行 [Cid Ray, 50%off Sale, 500off Sale, Fay lee]
select … from person p1_0 where p1_0.city<>?
findByCityOrderByAgeDesc("Oslo") ⇒ 返回 4 行 [Fay lee, 500off Sale, 50%off Sale, Cid Ray]
select … from person p1_0 where p1_0.city=? order by p1_0.age desc
findTop3ByOrderByAgeDesc() ⇒ 返回 3 行 [Fay lee, 500off Sale, 50%off Sale]
select … from person p1_0 order by p1_0.age desc limit ?
findFirstByOrderByAgeAsc() ⇒ 返回 Dee Ray
select … from person p1_0 order by p1_0.age limit ?
countByCity("Oslo") ⇒ 返回 4
select … from person p1_0 where p1_0.city=?
existsByLastName("Lee") ⇒ 返回 true
select … from person p1_0 where p1_0.last_name=? limit ?
findDistinctLastNameByCity("Oslo") ⇒ 返回 抛 ConversionFailedException:Failed to convert from type [java.util.ArrayList<?>] to type [java.util.List<java.lang.String>] for value [[...]]
select … from person p1_0 where p1_0.city=?
findOptionalByFirstName("Ann") ⇒ 返回 Optional[Ann Lee]
select … from person p1_0 where p1_0.first_name=?前缀决定返回形态,By 之后按 And/Or/比较关键字拆成条件;Top 和 First 加 order by 与 limit。
findByFirstNameLike("50%")(自己写通配符) ⇒ 返回 2 行 [50%off Sale, 500off Sale]
select … from person p1_0 where p1_0.first_name like ? escape '\\'
findByFirstNameStartingWith("50%") ⇒ 返回 1 行 [50%off Sale]
select … from person p1_0 where p1_0.first_name like ? escape '\\'
findByFirstNameContaining("0%o") ⇒ 返回 1 行 [50%off Sale]
select … from person p1_0 where p1_0.first_name like ? escape '\\'
findByLastNameIgnoreCase("LEE") ⇒ 返回 3 行 [Ann Lee, Bob Lee, Fay lee]
select … from person p1_0 where upper(p1_0.last_name)=upper(?)
findByLastName("LEE")(MySQL 默认排序规则) ⇒ 返回 3 行 [Ann Lee, Bob Lee, Fay lee]
select … from person p1_0 where p1_0.last_name=?StartingWith 与 Containing 会转义 % 和 _;IgnoreCase 变成 upper();区分大小写取决于数据库。
findByAddressZipCode("10001") ⇒ 返回 3 行 [Ann Lee, Bob Lee, Eve Poe]
select … from person p1_0 where p1_0.zip_code=?
findByAddress_ZipCode("10001") ⇒ 返回 3 行 [Ann Lee, Bob Lee, Eve Poe]
select … from person p1_0 where p1_0.zip_code=?AddressZipCode 自动拆成 address.zipCode;有歧义时用下划线。
deleteByCity("Rome")(@Transactional) ⇒ 返回 3
select … from person p1_0 where p1_0.city=?
delete from person where id=?
delete from person where id=?
delete from person where id=?先 select 再逐行 delete,是 1 + N 条 SQL;要自己加 @Transactional。
退出码 1;有 APPLICATION FAILED TO START 报告:False;输出共 71 行,其中堆栈帧 66 行
异常链(外 → 里):
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'personRepo' defined in demo.PersonRepo defined in @EnableJpaRepositories declared on DataJpaRepositoriesRegistrar.EnableJpaRepositoriesConfiguration: No property 'lastNme' found for type 'Person'; Did you mean 'l
Caused by: org.springframework.data.repository.query.QueryCreationException: Cannot create query for method [PersonRepo.findByLastNme(java.lang.String)]; No property 'lastNme' found for type 'Person'; Did you mean 'lastName'
Caused by: org.springframework.data.core.PropertyReferenceException: No property 'lastNme' found for type 'Person'; Did you mean 'lastName'
nickname 也是错的,但报错里提到了它吗:False应用启动时失败,只报第一个错误,没有整理过的报告。