一、关键字
1.1 And
findByNameAndPwd
where name= ? and pwd =?
1.2 Or
findByNameOrSex
where name= ? or sex=?
1.3 Is,Equals
findById,findByIdEquals
where id= ?
1.4 Between
findByIdBetween
where id between ? and ?
1.5 LessThan
findByIdLessThan
where id < ?
1.6 LessThanEquals
findByIdLessThanEquals
where id <= ?
1.7 GreaterThan
findByIdGreaterThan
where id > ?
1.8 GreaterThanEquals
findByIdGreaterThanEquals
where id > = ?
1.9 After
findByIdAfter
where id > ?
1.10 Before
findByIdBefore
where id < ?
1.11 IsNull
findByNameIsNull
where name is null
1.12 isNotNull,NotNull
findByNameNotNull
where name is not null
1.13 Like
findByNameLike
where name like ?
1.14 NotLike
findByNameNotLike
where name not like ?
1.15 StartingWith
findByNameStartingWith
where name like '?%'
1.16 EndingWith
findByNameEndingWith
where name like '%?'
1.17 Containing
findByNameContaining
where name like '%?%'
1.18 OrderBy
findByIdOrderByXDesc
where id=? order by x desc
1.19 Not
findByNameNot
where name <> ?
1.20 In
findByIdIn(Collection<?> c)
where id in (?)
1.21 NotIn
findByIdNotIn(Collection<?> c)
where id not in (?)
1.22 True
findByAaaTue
where aaa = true
1.23 False
findByAaaFalse
where aaa = false
1.24 IgnoreCase
findByNameIgnoreCase
where UPPER(name)=UPPER(?)
1.25 where name in(?,?...) and age<?
public List<Employee> findByNameInAndAgeLessThan(List<String> names,Integer age);
在方法上加@Query就不需要遵守上面的规则了,可以进行自定义sql。
二、传参方式 :
2.1 ?1方式
@Query("select o from Employee o where o.name=?1 and o.age=?2")
public List<Employee> queryParams1(String name,Integer age);
2.2 @Param方式
@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);
修改数据的时候需要使用到三个注解
2.3 update or delete操作时需要加上@Modifying注解以告诉spring data 是update or dalete操作,并且需要在service上开启事务 @Transactional
例如:仅仅加上@Modifying这个会报错,需要打开事务,需要在service层方法上开启事务
dao层:
@Modifying
@Query("update Employee o set o.age=:age where o.id=:id")
public void update(@Param("id") Integer id,@Param("age") Integer age);
service层:
@Transactional
public void update(Integer id,Integer age){
employeeRepository.update(id,age);
}
三、查询
3.1.使用JPA命名查询
@Entity
@NamedQuery(name=User.findByCoursePackId,query="select u from User u where u.id = ?1")
public class User(){}
3.2.实现声明接口方法
List<CoursePackAndCourse> findByCoursePackId(String coursePackId);
3.3.使用Query代替
select操作
@Query("select q from Answer q where q.id = :id")
void selectAnswerByIdAndState(@Param("id") String id);
DELETE和UPDATE操作时必须在query方法上增加@Modifying,以通知Spring Data这是一个DELETE或UPDATE操作。如果UPDATE或DELETE操作需要使用事务,应在Service层的方法上添加事务操作。
@Modifying
@Query("update Answer q set q.state = :state where q.id = :id")
void updateState(@Param("id") String id, @Param("state") Integer state);
使用Like查询
@Query("select q from Answer q where q.id like %:id")
void updateState(@Param("id") String id);
3.4.实现设置nativeQuery=true的方法代码
update or delete
@Modifying
@Query(value = "DELETE FROM q_course_group WHERE course_id IN (:courseIds)", nativeQuery = true)
void deleteAll(@Param("courseIds") Set<String> courseIds);
select
@Query(value = "select q FROM q_course_group q WHERE course_id IN (:courseIds)", nativeQuery = true)
void selectTest(@Param("courseIds") Set<String> courseIds);
四、分页
public interface UserRepository extends JpaRepository<User, Long> { @Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1", countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1", nativeQuery = true) Page<User> findByLastname(String lastname, Pageable pageable); }