关键字 | 方法命名 | sql where字句 |
And | findByNameAndPwd | where name= ? and pwd =? |
Or | findByNameOrSex | where name= ? or sex=? |
Is,Equals | findById,findByIdEquals | where id= ? |
Between | findByIdBetween | where id between ? and ? |
LessThan | findByIdLessThan | where id < ? |
LessThanEquals | findByIdLessThanEquals | where id <= ? |
GreaterThan | findByIdGreaterThan | where id > ? |
GreaterThanEquals | findByIdGreaterThanEquals | where id > = ? |
After | findByIdAfter | where id > ? |
Before | findByIdBefore | where id < ? |
IsNull | findByNameIsNull | where name is null |
isNotNull,NotNull | findByNameNotNull | where name is not null |
Like | findByNameLike | where name like ? |
NotLike | findByNameNotLike | where name not like ? |
StartingWith | findByNameStartingWith | where name like '?%' |
EndingWith | findByNameEndingWith | where name like '%?' |
Containing | findByNameContaining | where name like '%?%' |
OrderBy | findByIdOrderByXDesc | where id=? order by x desc |
Not | findByNameNot | where name <> ? |
In | findByIdIn(Collection<?> c) | where id in (?) |
NotIn | findByIdNotIn(Collection<?> c) | where id not in (?) |
True | findByAaaTue | where aaa = true |
False | findByAaaFalse | where aaa = false |
IgnoreCase | findByNameIgnoreCase | where UPPER(name)=UPPER(?) |
//where name in(?,?...) and age<? public List<Employee> findByNameInAndAgeLessThan(List<String> names,Integer age);
在方法上加@Query就不需要遵守上面的规则了,可以进行自定义sql。
有两种方式传参:
@Query("select o from Employee o where o.name=?1 and o.age=?2") public List<Employee> queryParams1(String name,Integer age); @Query("select o from Employee o where o.name=:name and o.age=:age") public List<Employee> queryParams2(@Param("name") String name, @Param("age") Integer age);
修改数据的时候需要使用到三个注解
@Modifying @Query("update Employee o set o.age=:age where o.id=:id") public void update(@Param("id") Integer id,@Param("age") Integer age);首先是query这里需要加上@Modifying
但是仅仅加上这个会报错,需要打开事务
所以还需要在service层方法上开启事务,(service层开启事务,事务会作用于整个查询部分的方法)
@Transactional public void update(Integer id,Integer age){ employeeRepository.update(id,age); }