JPA相较Hibernate来说，比较优秀的一点就是实现了根据方法名的自动解析创建查询。今天看到团队的小伙伴无论查询复杂或简单还在吭哧吭哧的写@Query，于是决定把JPA的能自动解析的关键词扒出来。供大家参考。

简单示例

public interface UserRepository extends JPARepository<User, Long> {
  List<User> findByEmailAddressAndLastname(String emailAddress, String lastname);
}

以上就是一个简单的查询的示例。当继承了JPARepository接口之后，JPA就会尝试根据方法名去自动生成sql，上面的方法会被解析成

select u from User u where u.emailAddress = ?1 and u.lastname = ?2

JPARepository<User, Long> ，第一个参数是对应的实体类，第二个参数是这个实体类的主键的数据类型。根据需要修改。

所有关键词

以下列表列出了JPA目前支持的所有关键词，基本能hold住常规场景。

Keyword Sample JPQL snippet
And findByLastnameAndFirstname … where x.lastname = ?1 and x.firstname = ?2
Or findByLastnameOrFirstname … where x.lastname = ?1 or x.firstname = ?2
Is,Equals findByFirstname,findByFirstnameIs,findByFirstnameEquals … where x.firstname = ?1
Between findByStartDateBetween … where x.startDate between ?1 and ?2
LessThan findByAgeLessThan … where x.age < ?1
LessThanEqual findByAgeLessThanEqual … where x.age <= ?1
GreaterThan findByAgeGreaterThan … where x.age > ?1
GreaterThanEqual findByAgeGreaterThanEqual … where x.age >= ?1
After findByStartDateAfter … where x.startDate > ?1
Before findByStartDateBefore … where x.startDate < ?1
IsNull findByAgeIsNull … where x.age is null
IsNotNull,NotNull findByAge(Is)NotNull … where x.age not null
Like findByFirstnameLike … where x.firstname like ?1
NotLike findByFirstnameNotLike … where x.firstname not like ?1
StartingWith findByFirstnameStartingWith … where x.firstname like ?1(parameter bound with appended %)
EndingWith findByFirstnameEndingWith … where x.firstname like ?1(parameter bound with prepended %)
Containing findByFirstnameContaining … where x.firstname like ?1(parameter bound wrapped in %)
OrderBy findByAgeOrderByLastnameDesc … where x.age = ?1 order by x.lastname desc
Not findByLastnameNot … where x.lastname <> ?1
In findByAgeIn(Collection<Age> ages) … where x.age in ?1
NotIn findByAgeNotIn(Collection<Age> age) … where x.age not in ?1
True findByActiveTrue() … where x.active = true
False findByActiveFalse() … where x.active = false
IgnoreCase findByFirstnameIgnoreCase … where UPPER(x.firstame) = UPPER(?1)

关于多表关联查询

JPA的方法名解析也能支持多表关联，比如findByAgeAndUser_UserName，即通过下划线能指明这是另一个实体的字段。但这要求关联的实体存在主外键关系，并在实体类里声明出来。由于我们的实际情况是面向数据仓库的产品，在数据库模型层面基本不存在主外键关系。所以关联查询就不建议采用方法名解析，直接使用@Query解析去写，更便捷。

END