User findByUserNameOrEmail(String username, String email);
修改、删除、统计也是类似语法
Long deleteById(Long id);
Long countByUserName(String userName)
基本上SQL 体系中的关键词都可以使用,
例如: LIKE 、 IgnoreCase、 OrderBy。
List<User> findByEmailLike(String email);
User findByUserNameIgnoreCase(String userName);
List<User> findByUserNameOrderByEmailDesc(String email);
具体的关键字,使用方法和生产成SQL如下表所示
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 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 ages) … where x.age in ?1
NotIn findByAgeNotIn(Collection 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)
@Modifying
@Query("update User u set u.userName = ?1 where u.id = ?2")
int modifyByIdAndUserId(String userName, Long id);
@Transactional
@Modifying
@Query("delete from User where id = ?1")
void deleteByUserId(Long id);
@Transactional(timeout = 10)
@Query("select u from User u where u.emailAddress = ?1")
User findByEmailAddress(String emailAddress);
多表查询
多表查询 Spring Boot Jpa 中有两种实现方式,第一种是利用 Hibernate 的级联查询来实现,第二种是创建一个结果集的接口来接收连表查询后的结果,这里主要第二种方式。
@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);
使用
Page<HotelSummary> hotels = this.hotelRepository.findByCity(new PageRequest(0, 10, Direction.ASC, "name"));
for(HotelSummary summay:hotels){
System.out.println("Name" +summay.getName());
}