默认方法
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)
参考: