Spring Data JPA是什么？以及简单的使用(联表查询)

Spring Data学习网站（下载gitHub工程学习）

Spring Boot JPA 使用教程 - fishpro - 博客园

Spring Data JPA是什么？以及相关概述

• 首先Spring框架几乎是无所不能，无所不在。
• 其次Spring也想要做持久化相关工作，并且已有Spring-data-**这一系列包（Spring-data-jpa,Spring-data-template,Spring-data-mongodb等）。
• 其中Spring-data-jpa即代表着，Spring框架对JPA的整合。
• Spring Data JPA是在JPA规范的基础下提供了Repository层的实现。
• 在项目中的repository层中具体表现为：
  1.接口要继承JpaRepository接口，示例代码如下：

概念参考文档

JPA、Hibernate、Spring data jpa之间的关系，以及和springboot的整合 - 悦文 - 博客园

使用参考文档

Spring Data JPA实现分页Pageable的实例代码_shanshan_blog的博客-CSDN博客_jparepository pageable

完整 增，该，查

新增

save（X x）方法

RangeCoverageJsDO rangeCoverageJsDO = new RangeCoverageJsDO(rangeJobAppDO.getId(),appName,commitId,branch,sprintId,sprintName,v_task_id,coverAgeDataTfsKey,new Date(System.currentTimeMillis()),false,3,null,null,null);
rangeCoverageJsDO.setStatus(1);
rangeCoverageJsDO.setGmt_create(new Date(System.currentTimeMillis()));
rangeCoverageJsDO= rangeCoverageJsDao.save(rangeCoverageJsDO);

Jap文件：

package com.pajk.totoro.range.dao.range;



import com.pajk.totoro.range.domain.range.RangeRecordDO;

import org.springframework.data.domain.Page;

import org.springframework.data.domain.Pageable;

import org.springframework.data.jpa.repository.JpaRepository;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

import org.springframework.data.jpa.repository.Modifying;

import org.springframework.data.jpa.repository.Query;

import org.springframework.data.repository.query.Param;

import org.springframework.transaction.annotation.Transactional;



import java.util.List;



public interface RangeRecordDao extends JpaRepository<RangeRecordDO, Long>,

        JpaSpecificationExecutor<RangeRecordDO> {



    @Query(value = "SELECT * FROM totoro_range.range_record  where id =:recordId and is_deleted=0 limit 1 ; ", nativeQuery = true)

    RangeRecordDO findById(@Param("recordId") String recordId);



    @Query(value = "update totoro_range.range_record  set is_deleted=1  where id =:recordId ;", nativeQuery = true)

    @Modifying

    @Transactional

    void deleteById(@Param("recordId") String recordId);



    @Query(value = "SELECT * FROM totoro_range.range_record  where diff_job_id =:diffJobId and file_id =:fileId and is_deleted=0;", nativeQuery = true)

    List<RangeRecordDO> findByJobFileId(@Param("diffJobId") Long diffJobId, @Param("fileId") Long fileId);



    @Query(value = "SELECT *  FROM totoro_range.range_record where diff_job_id=:diffJobId and file_id=:fileId and is_deleted=0 and((start_line>=:start_line and start_line<=:end_line) or (end_line>=:start_line and end_line<=:end_line))", nativeQuery = true)

    List<RangeRecordDO> findIsLineHasUse(@Param("diffJobId") Long diffJobId, @Param("fileId") Long fileId, @Param("start_line") Long start_line, @Param("end_line") Long end_line);

     

    @Query(value = "SELECT * FROM totoro_range.range_record  where is_deleted =:isDeleted  ORDER BY ?#{#pageable} ", nativeQuery = true)

    Page<RangeRecordDO> findByIs_deleted(@Param ( "isDeleted" ) int isDeleted,Pageable pageable);

}

查询自定义Jap中的sql：

@Query(value = "SELECT * FROM totoro_range.range_record where id =:recordId and is_deleted=0 limit 1 ; ", nativeQuery = true)
    RangeRecordDO findById(@Param("recordId") String recordId);

也可以用原生的

findByxx

如：findById(1)

JPA实现链表查询

（chopper-server项目）

@Query("SELECT count(1) as num,tc.id , tc.caseName  FROM TestCase tc ,TaskLog tl where tc.id = tl.taskId and tl.taskId is not null and tl.taskType in ('CASESET','CASE') group by tc.id,tc.caseName " )
	List<Object[]> findAllCaseActivity();

更新操作：

1、方式1 ，通过注释自定义更新语句

@Modifying
  @Transactional

//可以通过自定义的JPQL 完成update和delete操作，注意：JPQL不支持Insert操作  ，直接用save（E e）方法保存

 //在@Query注解中编写JPQL语句，但必须使用@Modify进行修饰，以通知SpringData，这是一个Update或者Delete

//nativeQuery = true 表示的是支持原生sql，这段sql就可以直接在sql中运行，若nativeQuery = false 则 sql应该写成SELECT * FROM rangeRecord ;其中rangeRecord 不是数据库对应的真正的表名，而是对应的实体名，并且sql中的字段名也不是数据库中真正的字段名，而是实体的字段名

更新对应的jpa的sql：

public interface RangeRecordDao extends JpaRepository<RangeRecordDO, Long>,
        JpaSpecificationExecutor<RangeRecordDO> {

    @Query(value = "SELECT * FROM totoro_range.range_record  where id =:recordId and is_deleted=0 limit 1 ; ", nativeQuery = true)
    RangeRecordDO findById(@Param("recordId") String recordId);

    @Query(value = "update totoro_range.range_record  set is_deleted=1  where id =:recordId ;", nativeQuery = true)
    @Modifying
    @Transactional
    void deleteById(@Param("recordId") String recordId);

    @Query(value = "SELECT * FROM totoro_range.range_record  where diff_job_id =:diffJobId and file_id =:fileId and is_deleted=0;", nativeQuery = true)
    List<RangeRecordDO> findByJobFileId(@Param("diffJobId") Long diffJobId, @Param("fileId") Long fileId);

    @Query(value = "SELECT *  FROM totoro_range.range_record where diff_job_id=:diffJobId and file_id=:fileId and is_deleted=0 and((start_line>=:start_line and start_line<=:end_line) or (end_line>=:start_line and end_line<=:end_line))", nativeQuery = true)
    List<RangeRecordDO> findIsLineHasUse(@Param("diffJobId") Long diffJobId, @Param("fileId") Long fileId, @Param("start_line") Long start_line, @Param("end_line") Long end_line);
}

方式二：

通过save方法设置相同的唯一主键key如id，来实现覆盖存储，进行更新（不推荐）

查询

 带分页功能的查询业务代码：

Pagination<RangeRecordVO> result = new Pagination<>();
        try {
            QueryRangeRecord queryRangeRecord = JSON.parseObject(queryJson, QueryRangeRecord.class);
            result = rangeRecordRepository.queryList(queryRangeRecord, page, pageSize);
        } catch (Exception e) {
            logger.error("查询转化记录异常！", e);
     
        }

Pageable pageable = new PageRequest( 0 , 3 , Sort.Direction.DESC, "id" );

//新增

RangeRecordDO record1 = rangeRecordDao.save(record);

//分页查询

Page<RangeRecordDO> aa= rangeRecordDao.findByIs_deleted(0,pageable);

//更新

rangeRecordDao.deleteById(recordId);

  

分页查询对应的jpa的sql：

 @Query(value = "SELECT * FROM totoro_range.range_record where is_deleted =:isDeleted ORDER BY ?#{#pageable} ", nativeQuery = true)
    Page<RangeRecordDO> findByIs_deleted(@Param ( "isDeleted" ) int isDeleted,Pageable pageable);

删除：

用原生方法：

deleteById(X x);

或delete(X x)