springboot系列08:jpa的高级使用
自定义高级查询
用一些加一些关键字And
、 Or
-
接口中定义
/** * 用户名或邮箱查询 */ List<User> findByUserNameOrEmail(String username, String email);
-
测试类
@Test public void testFindByUserNameOrEmail(){ Assert.assertEquals(2,userRepository.findByUserNameOrEmail("zhangl1", "1796969389@qq.com").size()); }
同理:修改、删除、统计语法类似
/** *根据主键删除 */ void deleteById(Long id);
-
测试结果
@Test public void testDeleteById(){ userRepository.deleteById(11L); Assert.assertFalse(userRepository.findById(11L).isPresent()); }
基本上 SQL 体系中的关键词都可以使用,例如: LIKE
、 IgnoreCase
、 OrderBy
/** * 邮箱模糊查询 */ List<User> findByEmailLike(String email);
@Test public void testFindByEmailLike(){ Assert.assertEquals(2,userRepository.findByEmailLike("%1796969389%").size()); }
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 | 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 ages) | … where x.age in ?1 |
NotIn | findByAgeNotIn(Collection 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) |
分页查询
SpringBoot Jpa 已经帮我们实现了分页的功能,在查询的方法中,需要传入参数Pageable
@Test public void testFindAllByPage(){ Pageable pageable = PageRequest.of(0, 10, new Sort(Sort.Direction.DESC, "id")); Assert.assertEquals(2,userRepository.findAll(pageable).getTotalElements()); }
限制查询
有时候我们只需要查询前N个元素,或者取前一个实体
/** * 取前10条数据 */ Page<User> queryFirst10ByUserName(String userName, Pageable pageable);
-
测试
@Test public void testQueryFirst10ByUserName(){ Pageable pageable = PageRequest.of(0, 10, new Sort(Sort.Direction.DESC, "id")); Page<User> userPage = userRepository.queryFirst10ByUserName("zhangl1", pageable); Assert.assertEquals(1,userRepository.queryFirst10ByUserName("zhangl1", pageable).getSize() ); }