SpringData JPA的常用语法汇总

public interface JpaRepository<T, ID> extends PagingAndSortingRepository<T, ID>, QueryByExampleExecutor<T> {

     // 无条件，查询全部记录

     List<T> findAll();

     // 排序查询

     List<T> findAll(Sort var1);

     // 根据主键ID查询

     List<T> findAllById(Iterable<ID> var1);

     // 批量保存集合数据

     <S extends T> List<S> saveAll(Iterable<S> var1);

     void flush();

     <S extends T> S saveAndFlush(S var1);

     // 批量删除

     void deleteInBatch(Iterable<T> var1);

     // all in 全部删除

     void deleteAllInBatch();

     // 查询一条记录

     T getOne(ID var1);

     // 条件查询

     <S extends T> List<S> findAll(Example<S> var1);

     // 条件查询，带排序

     <S extends T> List<S> findAll(Example<S> var1, Sort var2);

}

// 构建user的Example对象

Example<User> example =Example.of(User);

List<User> users = userRepository.findAll(example);

@Retention (RetentionPolicy.RUNTIME)

@Target ({ElementType.METHOD, ElementType.ANNOTATION_TYPE})

@QueryAnnotation

@Documented

public @interface Query {

     String value() default "" ;

     String countQuery() default "" ;

     String countProjection() default "" ;

     boolean nativeQuery() default false ;

     String name() default "" ;

     String countName() default "" ;

}

@Query (value = "select task_id from task group by task_id" , nativeQuery = true )

List<Task> queryByGroup();

@Query (value = "select task_id,task_date from task order by task_id" , nativeQuery = true )

List<Task> queryOrder();

@Query (value = "select task_id,task_date from task where task_date >=? and task_date <=? and deleted=0 " , nativeQuery = true )

List<ApptTask> queryDate( @Param ( "startDate" ) String startDate, @Param ( "endDate" ) String endDate);

@Transactional (rollbackOn = Exception. class )

@Modifying

@Query (value = "update task set remark = ? where task_id=?" , nativeQuery = true )

void updateRemark( @Param ( "remark" ) String remark, @Param ( "taskId" ) String taskId);

@Query (value =

       " select a.task_name, a.task_id,u.user_name" +

       " from task a " +

       " LEFT JOIN usert u" +

       " ON a.user_id = u.user_id" +

       " where  a.deleted=0  " +

       " AND if(:taskName!='',a.task_name =:taskName,1=1)" +

       " AND if(:startDate!='',a.task_date >=:startDate,1=1)" +

       " AND if(:endDate!='',a.task_date <=:endDate,1=1)" +

       " order by a.task_date desc"

       ,

       nativeQuery = true

       ,

       countQuery =

       " select count(*)" +

       " from task a " +

       " LEFT JOIN usert u" +

       " ON a.user_id = u.user_id" +

       " where  a.deleted=0  " +

       " AND if(:taskName!='',a.task_name =:taskName,1=1)" +

       " AND if(:startDate!='',a.task_date >=:startDate,1=1)" +

       " AND if(:endDate!='',a.task_date <=:endDate,1=1)" +

       " order by a.task_date desc" )

Page<Map<String,Object>> queryUserTaskPage( @Param ( "taskName" ) String taskName, @Param ( "startDate" ) LocalDate startDate, @Param ( "endDate" ) LocalDate endDate, @Param ( "pageable" ) Pageable pageable);