上一篇文章：spring-boot：使用JPA访问数据源（一）
一、使用where条件
上一篇我们使用JPA进行了数据源的访问，默认JPA已经实现了好几个接口可以调用。但是，在实际的业务中，查询语句不可避免地需要使用where、order by等语句。
我们用商品数据来做例子，添加一个价格字段price，按价格范围查询，看看怎么来实现。
方式一：通过方法名称来实现
public interface GoodsRepository extends JpaRepository<Goods, Long> {
List<Goods> findByPriceBetween(Double startPrice, Double endPrice);
}Spring Data JPA 查询方法支持的关键字（可参考：https://docs.spring.io/spring-data/jpa/docs/2.2.x/reference/html/#repositories.query-methods）
Table 2.2. Supported keywords inside method names
| Keyword | Sample | JPQL snippet |
|---|---|---|
And | findByLastnameAndFirstname | … where x.lastname = ?1 and x.firstname = ?2 |
Or | findByLastnameOrFirstname | … where x.lastname = ?1 or x.firstname = ?2 |
Between | findByStartDateBetween | … where x.startDate between 1? and ?2 |
LessThan | findByAgeLessThan | … where x.age < ?1 |
GreaterThan | findByAgeGreaterThan | … 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 |
方式二：通过自定义SQL来实现
在现实中，可能会含有非常复杂的SQL语句，或者为了性能优化，我们需要自定义sql。JPA提供注解和XML配置这2中方式。
public interface GoodsRepository extends JpaRepository<Goods, Long> {
@Query(value = "select * from goods g where g.price between :startPrice and :endPrice", nativeQuery = true)
List<Goods> findByPriceBetween(Double startPrice, Double endPrice);
}
二、order by查询
使用order by也可以编写在方法名上，可以把以上例子改为
public interface GoodsRepository extends JpaRepository<Goods, Long> {
List<Goods> findByPriceBetweenOrderByPriceAsc(Double startPrice, Double endPrice);
}至于自定义sql这里就省略了
版权声明：本文为loophome原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。