JPA – JpaRepository 中使用的查询方法

参考链接：Spring Data JPA - Reference Documentation

默认方法

User user=new User();
userRepository.findAll();
userRepository.findOne(1l);
userRepository.save(user);
userRepository.delete(user);
userRepository.count();
userRepository.exists(1l);

自定义查询

User findByUserName(String userName);

也使用一些加一些关键字And、 Or

User findByUserNameOrEmail(String username, String email);

修改、删除、统计也是类似语法

Long deleteById(Long id);
Long countByUserName(String userName);

基本上 SQL 体系中的关键词都可以使用，例如：LIKE、 IgnoreCase、 OrderBy。

List findByEmailLike(String email);
User findByUserNameIgnoreCase(String userName);
List findByUserNameOrderByEmailDesc(String email);

Query 自定义查询

@Query 书写时，数据库名和字段名要写成代码中的大小写格式，而不是数据库中的字段格式
  使用 nativeQuery ，数据库名和字段名要写成数据库中的字段大小写 

一、本地语法直接查询（Native SQL Query)

@Query(value = "select * from Book b where b.name=?1", nativeQuery = true) 
List<Book> findByName(String name);

二、模糊查询

方法1：使用 “%”

Repository

List<Team> findByNameLike(String name); 

Controller

teamRepository.findByNameLike("%"+name+"%"); 

方法2：使用 Query 自定义 sql 查询

Repository

@Query(value = "select t from Team t where t.name like %?1%")
List<Team> findByNameLike(String name); 

Controller

teamRepository.findByNameLike(name); 

三、范围查询

@Query(value = "select name,author,price from Book b where b.price>?1 and b.price<?2")
List<Book> findByPriceRange(long price1, long price2);

四、@Param 注解注入参数

@Query(value = "select name,author,price from Book b where b.name = :name AND b.author=:author AND b.price=:price")
List<Book> findByNamedParam(@Param("name") String name, @Param("author") String author, @Param("price") long price);

五、分页查询

Page<User> findALL(Pageable pageable); 
Page<User> findByUserName(String userName,Pageable pageable); 

在查询的方法中，需要传入参数Pageable ,当查询中有多个参数的时候Pageable建议做为最后一个参数传入 。
Pageable 是 Spring 封装的分页实现类，使用的时候需要传入页数、每页条数和排序规则。

int page=1,size=10;
Sort sort = new Sort(Direction.DESC, "id");
Pageable pageable = new PageRequest(page, size, sort);
userRepository.findALL(pageable);
userRepository.findByUserName("testName", pageable); 

六、限制查询

查询前 N 个元素

User findFirstByOrderByLastnameAsc(); 
User findTopByOrderByAgeDesc(); 
Page<User> queryFirst10ByLastname(String lastname, Pageable pageable); 
List<User> findFirst10ByLastname(String lastname, Sort sort); 
List<User> findTop10ByLastname(String lastname, Pageable pageable); 

七、多表查询

@Query("select h.city as city, h.name as name, avg(r.rating) as averageRating " - "from Hotel h left outer join h.reviews r where h.city = ?1 group by h")  
Page<HotelSummary> findByCity(City city, Pageable pageable);

@Query("select h.name as name, avg(r.rating) as averageRating " - "from Hotel h left outer join h.reviews r  group by h")  
Page<HotelSummary> findByCity(Pageable pageable); 

基础语法

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, 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 (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> ages)	… 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)