jparepository查询所有_JPA – JpaRepository 中使用的查询方法

默认方法

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。

ListfindByEmailLike(String email);

User findByUserNameIgnoreCase(String userName);

ListfindByUserNameOrderByEmailDesc(String email);

Query 自定义查询

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

使用 nativeQuery ，数据库名和字段名要写成数据库中的字段大小写

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

@Query(value = "select * from Book b where b.name=?1", nativeQuery = true)

List findByName(String name);

二、模糊查询

方法1：使用 “%”

Repository

List findByNameLike(String name);

Controller

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

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

Repository

@Query(value = "select t from Team t where t.name like %?1%")

List findByNameLike(String name);

Controller

teamRepository.findByNameLike(name);

三、范围查询

@Query(value = "select name,author,price from Book b where b.price>?1 and b.price2")

List 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 findByNamedParam(@Param("name") String name, @Param("author") String author, @Param("price") long price);

五、分页查询

Page findALL(Pageable pageable);

Page 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 queryFirst10ByLastname(String lastname, Pageable pageable);

List findFirst10ByLastname(String lastname, Sort sort);

List 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 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 findByCity(Pageable pageable);

基础语法

KeywordSampleJPQL snippetAndfindByLastnameAndFirstname… where x.lastname = ?1 and x.firstname = ?2

OrfindByLastnameOrFirstname… where x.lastname = ?1 or x.firstname = ?2

Is, EqualsfindByFirstname,findByFirstnameIs,findByFirstnameEquals… where x.firstname = ?1

BetweenfindByStartDateBetween… where x.startDate between ?1 and ?2

LessThanfindByAgeLessThan… where x.age < ?1

LessThanEqualfindByAgeLessThanEqual… where x.age <= ?1

GreaterThanfindByAgeGreaterThan… where x.age > ?1

GreaterThanEqualfindByAgeGreaterThanEqual… where x.age >= ?1

AfterfindByStartDateAfter… where x.startDate > ?1

BeforefindByStartDateBefore… where x.startDate < ?1

IsNull, NullfindByAge(Is)Null… where x.age is null

IsNotNull, NotNullfindByAge(Is)NotNull… where x.age not null

LikefindByFirstnameLike… where x.firstname like ?1

NotLikefindByFirstnameNotLike… where x.firstname not like ?1

StartingWithfindByFirstnameStartingWith… where x.firstname like ?1 (parameter bound with appended %)

EndingWithfindByFirstnameEndingWith… where x.firstname like ?1 (parameter bound with prepended %)

ContainingfindByFirstnameContaining… where x.firstname like ?1 (parameter bound wrapped in %)

OrderByfindByAgeOrderByLastnameDesc… where x.age = ?1 order by x.lastname desc

NotfindByLastnameNot… where x.lastname <> ?1

InfindByAgeIn(Collection ages)… where x.age in ?1

NotInfindByAgeNotIn(Collection ages)… where x.age not in ?1

TruefindByActiveTrue()… where x.active = true

FalsefindByActiveFalse()… where x.active = false

IgnoreCasefindByFirstnameIgnoreCase… where UPPER(x.firstame) = UPPER(?1)

参考：