上次只简单说明了Spring data jpa（http://www.jianshu.com/p/f80d026e1de1）的基础使用，在实际开发过程中业务复杂、多样性基础的CRUD是无法满足的；本篇目的在上章的基础上深入进解JAP的使用。
多条件查询 AND
public interface UserRepository extends Repository<User, Long> {
List<User> findByEmailAddressAndLastname(String emailAddress, String lastname);
}
SQL
select u from User u where u.emailAddress = ?1 and u.lastname = ?2
多条件查询 OR
public interface UserRepository extends Repository<User, Long> {
List<User> findByEmailAddressOrLastname(String emailAddress, String lastname);
}
SQL
select u from User u where u.emailAddress = ?1 or u.lastname = ?2
用例列表
| 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) |
@Query
public interface UserRepository extends JpaRepository<User, Long> {
@Query("select u from User u where u.emailAddress = ?1")
User findByEmailAddress(String emailAddress);
}
多条件排序
Sort sort = new Sort(Sort.Direction.DESC, "createdate") .and(new Sort(Sort.Direction.AES, "id"));
Pageable pageable = new PageRequest(1, 10, sort)
QueryHints
public interface UserRepository extends Repository<User, Long> {
@QueryHints(value = { @QueryHint(name = "name", value = "value")},
forCounting = false)
Page<User> findByLastname(String lastname, Pageable pageable);
}
可以使用的仓库接口有：
Repository： 是 Spring Data的一个核心接口，它不提供任何方法，开发者需要在自己定义的接口中声明需要的方法。
CrudRepository： 继承Repository，提供增删改查方法，可以直接调用。
PagingAndSortingRepository： 继承CrudRepository，具有分页查询和排序功能
JpaRepository： 继承PagingAndSortingRepository，针对JPA技术提供的接口
JpaSpecificationExecutor： 可以执行原生SQL查询
参考资料：http://docs.spring.io/spring-data/jpa/docs/1.6.1.RELEASE/reference/html/jpa.repositories.html
