虽然接口预置的方法使用起来非常方便,但是如果我们需要进行条件查询等操作或是一些判断,就需要自定义一些方法来实现,同样的,我们不需要编写SQL语句,而是通过方法名称的拼接来实现条件判断,这里列出了所有支持的条件判断名称:
属性 | 拼接方法名称示例 | 执行的语句 |
---|---|---|
Distinct | findDistinctByLastnameAndFirstname | select distinct … where x.lastname = ?1 and x.firstname = ?2 |
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,Null | findByAge(Is)Null | … 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(参数与附加% 绑定) |
EndingWith | findByFirstnameEndingWith | … where x.firstname like ?1(参数与前缀% 绑定) |
Containing | findByFirstnameContaining | … where x.firstname like ?1(参数绑定以% 包装) |
OrderBy | findByAgeOrderByLastnameDesc | … where x.age = ?1 order by x.lastname desc |
Not | findByLastnameNot | … where x.lastname <> ?1 |
In | findByAgeIn(Collection ages) | … where x.age in ?1 |
NotIn | findByAgeNotIn(Collection ages) | … where x.age not in ?1 |
True | findByActiveTrue | … where x.active = true |
False | findByActiveFalse | … where x.active = false |
IgnoreCase | findByFirstnameIgnoreCase | … where UPPER(x.firstname) = UPPER(?1) |
比如我们想要实现根据用户名模糊匹配查找用户:
@Repository
public interface AccountRepository extends JpaRepository<Account, Integer> {
//按照表中的规则进行名称拼接,不用刻意去记,IDEA会有提示
List<Account> findAllByUsernameLike(String str);
}
我们来测试一下:
@Test
void contextLoads() {
repository.findAllByUsernameLike("%明%").forEach(System.out::println);
}