怎么實現JPA多條件復雜SQL動態分頁查詢功能

這篇文章給大家分享的是有關怎么實現JPA多條件復雜SQL動態分頁查詢功能的內容。小編覺得挺實用的，因此分享給大家做個參考，一起跟隨小編過來看看吧。

概述

　　ORM映射為我們帶來便利的同時，也失去了較大靈活性，如果SQL較復雜，要進行動態查詢，那必定是一件頭疼的事情（也可能是lz還沒發現好的方法），記錄下自己用的三種復雜查詢方式。

環境

springBoot

IDEA2017.3.4

JDK8

pom.xml

<?xml version="1.0" encoding="UTF-8"?><project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"     xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">  <modelVersion>4.0.0</modelVersion>  <parent>    <groupId>org.springframework.boot</groupId>    <artifactId>spring-boot-starter-parent</artifactId>    <version>2.1.6.RELEASE</version>    <relativePath/> <!-- lookup parent from repository -->  </parent>  <groupId>com.xmlxy</groupId>  <artifactId>seasgame</artifactId>  <version>0.0.1-SNAPSHOT</version>  <name>seasgame</name>  <description>Demo project for Spring Boot</description>  <properties>    <java.version>1.8</java.version>  </properties>  <dependencies>    <dependency>      <groupId>org.springframework.boot</groupId>      <artifactId>spring-boot-starter-web</artifactId>    </dependency>    <dependency>      <groupId>org.springframework.boot</groupId>      <artifactId>spring-boot-starter-test</artifactId>      <scope>test</scope>    </dependency>    <!--數據庫連接-->    <dependency>      <groupId>org.springframework.boot</groupId>      <artifactId>spring-boot-starter-data-jpa</artifactId>    </dependency>    <dependency>      <groupId>mysql</groupId>      <artifactId>mysql-connector-java</artifactId>      <scope>runtime</scope>    </dependency>    <!-- 熱啟動等 -->    <dependency>      <groupId>org.springframework.boot</groupId>      <artifactId>spring-boot-devtools</artifactId>      <scope>runtime</scope>      <optional>true</optional>    </dependency>    <!--Java bean 實體-->    <dependency>      <groupId>org.projectlombok</groupId>      <artifactId>lombok</artifactId>      <optional>true</optional>    </dependency>    <dependency>      <groupId>org.springframework.boot</groupId>      <artifactId>spring-boot-starter-data-jpa</artifactId>    </dependency>    <!--swagger2 API 測試工具 -->    <dependency>      <groupId>io.springfox</groupId>      <artifactId>springfox-swagger2</artifactId>      <version>2.8.0</version>    </dependency>    <dependency>      <groupId>io.springfox</groupId>      <artifactId>springfox-swagger-ui</artifactId>      <version>2.8.0</version>    </dependency>    <dependency>      <groupId>org.springframework.boot</groupId>      <artifactId>spring-boot-configuration-processor</artifactId>      <optional>true</optional>    </dependency>    <!--安全框架認證-->    <dependency>      <groupId>org.springframework.boot</groupId>      <artifactId>spring-boot-starter-security</artifactId>    </dependency>    <dependency>      <groupId>net.sf.json-lib</groupId>      <artifactId>json-lib</artifactId>      <version>2.2.2</version>      <classifier>jdk15</classifier>    </dependency>    <!--漢字轉拼音-->    <dependency>      <groupId>com.belerweb</groupId>      <artifactId>pinyin4j</artifactId>      <version>2.5.1</version>    </dependency>    <!-- thymeleaf模板 -->    <dependency>      <groupId>org.springframework.boot</groupId>      <artifactId>spring-boot-starter-thymeleaf</artifactId>    </dependency>    <!--    <dependency>      <groupId>org.springframework.boot</groupId>      <artifactId>spring-boot-starter-web</artifactId>    移除嵌入式tomcat插件    <exclusions>      <exclusion>        <groupId>org.springframework.boot</groupId>        <artifactId>spring-boot-starter-tomcat</artifactId>      </exclusion>    </exclusions>  </dependency>  -->    <dependency>      <groupId>javax.servlet</groupId>      <artifactId>javax.servlet-api</artifactId>      <version>3.1.0</version>      <scope>provided</scope>    </dependency>  </dependencies>  <packaging>war</packaging>  <build>    <plugins>      <plugin>        <groupId>org.springframework.boot</groupId>        <artifactId>spring-boot-maven-plugin</artifactId>      </plugin>      <plugin>        <groupId>org.apache.maven.plugins</groupId>        <artifactId>maven-compiler-plugin</artifactId>        <configuration>          <source>1.8</source>          <target>1.8</target>        </configuration>      </plugin>    </plugins>    <finalName>seasgame</finalName>    <pluginManagement>      <plugins>        <plugin>          <groupId>org.apache.maven.plugins</groupId>          <artifactId>maven-compiler-plugin</artifactId>          <version>2.3.2</version>          <configuration>            <encoding>${project.build.sourceEncoding}</encoding>            <source>1.7</source>            <target>1.7</target>          </configuration>        </plugin>        <plugin>          <groupId>org.apache.maven.plugins</groupId>          <artifactId>maven-surefire-plugin</artifactId>          <configuration>            <testFailureIgnore>true</testFailureIgnore>          </configuration>        </plugin>      </plugins>    </pluginManagement>  </build></project>

@Query

當一個SQL較為復雜時，第一個想到的就是原生的SQL語句。如果只是簡單的查詢，那情況還沒這么糟糕

@Query(value = " SELECT IFNULL(sum(right_num),0) sumRight FROM t_record WHERE record_owner_id = ?1 AND responder_no = ?2 ",nativeQuery = true) Map<String,Object> sumRightNum(int studentId,int responderNo);

但如果需要進行動態查詢，或更改，那這個value就變得復雜了。

package com.xmlxy.seasgame.dao;import com.xmlxy.seasgame.entity.ScoreEntity;import org.springframework.data.jpa.repository.Modifying;import org.springframework.data.jpa.repository.Query;import org.springframework.data.repository.CrudRepository;import org.springframework.data.repository.query.Param;import org.springframework.transaction.annotation.Transactional;import java.util.List;/** *  * Description:  * @author hwc * @date 2019/9/5 * @return*/ public interface ScoreDao extends CrudRepository<ScoreEntity,Integer>{  /**    *    * Description:   *@param scoreEntity   * @author hwc   * @date 2019/9/6  */  @Transactional(rollbackFor = Exception.class)  @Modifying  @Query(value = "UPDATE t_score t SET " +      "t.responder_no = CASE WHEN :#{#scoreEntity.responderNo} IS NULL THEN t.responder_no ELSE :#{#scoreEntity.responderNo} END," +      "t.max_level = CASE WHEN :#{#scoreEntity.maxLevel} IS NULL THEN t.max_level ELSE :#{#scoreEntity.maxLevel} END," +      "t.right_num = CASE WHEN :#{#scoreEntity.rightNum} IS NULL THEN t.right_num ELSE :#{#scoreEntity.rightNum} END," +      "t.use_time = CASE WHEN :#{#scoreEntity.userTime} IS NULL THEN t.use_time ELSE :#{#scoreEntity.userTime} END WHERE student_id = :#{#scoreEntity.getStudentId()}",nativeQuery = true)  void updateScore(@Param("scoreEntity") ScoreEntity scoreEntity);}

JPQL

如果Java代碼內發出JPQL查詢，就需要利用到EntityManager的響應方法了。一般執行以下流程

獲取一個EntityManager實例

調用實例的方法createQuery，創建一個Query實例，如果有需要可以指定檢索的最大數量和起始位置

使用Query方法getResultList執行查詢，當然更新和刪除操作得使用executeUpdate執行

進行一個復雜的動態SQL查詢

public Page<RankEntity> getScoreByRank(int gradeId,int classId,Pageable pageable) {   StringBuilder countSelectSql = new StringBuilder("");   countSelectSql.append(" SELECT COUNT(*) ");   countSelectSql.append(" FROM ");   countSelectSql.append(" t_score s, ");   countSelectSql.append(" t_student st  ");   countSelectSql.append(" WHERE ");   countSelectSql.append(" s.student_id = st.student_id ");   StringBuilder selectSql = new StringBuilder();   selectSql.append(" SELECT s.student_id,st.real_name,st.student_class,s.max_level,s.use_time,s.right_num ");   selectSql.append(" FROM t_score s ");   selectSql.append(" JOIN t_student st ON s.student_id = st.student_id ");   selectSql.append(" WHERE 1 = 1 ");   Map<String,Object> params = new HashMap<>();   StringBuilder whereSql = new StringBuilder();   if (gradeId != -1)   {     whereSql.append(" AND st.student_grade = :student_grade ");     params.put("student_grade",gradeId);   }   /**班級ID*/   if (classId != -1)   {     whereSql.append(" AND st.student_class = :classId ");     params.put("classId",classId);   }   String orderSql = " ORDER BY s.max_level DESC,s.use_time,s.right_num ASC ";   String countSql = new StringBuilder().append(countSelectSql).append(whereSql).toString();   Query countQuery = entityManager.createNativeQuery(countSql);   for (Map.Entry<String,Object> entry : params.entrySet())   {     countQuery.setParameter(entry.getKey(),entry.getValue());   }   BigInteger totalCount = (BigInteger)countQuery.getSingleResult();   String querySql = new StringBuilder().append(selectSql).append(whereSql).append(orderSql).toString();   Query query = entityManager.createNativeQuery(querySql,RankEntity.class);   for (Map.Entry<String,Object> entry:params.entrySet())   {     query.setParameter(entry.getKey(),entry.getValue());   }   query.setFirstResult((int) pageable.getOffset());   query.setMaxResults(pageable.getPageSize());   List<RankEntity> rankEntities = query.getResultList();   Page<RankEntity> page = new PageImpl<>(rankEntities,pageable,totalCount.longValue());   return page; }

注意：如果沒有重新定義Pageable那么pageNumber必須減1，因為是從0開始的。

Criteria

這是一種規范查詢是以元模型的概念為基礎的，這個元模型可以是實體累，嵌入類，或者映射的父類，簡單介紹幾個里面用到接口。

CriteraQuery是一個特定的頂層查詢對象，里面包含select，from，where，order by等各個部分，然而他只對實體類或嵌入類的標準查詢起作用。

Root標準查詢的根對象，根定義了實體類型，是你想要查詢要獲得的結果，也可以添加查詢條件，結合實體管理對象得到查詢的對象。

CriteriaBuilder接口用來構建CritiaQuery的構建器

StudentEntity類

package com.xmlxy.seasgame.entity;import io.swagger.annotations.ApiModel;import lombok.Data;import javax.persistence.*;import javax.print.attribute.standard.MediaSize;import java.io.Serializable;/** *  * Description:學生對象 * @param * @author hwc * @date 2019/8/8  */@Entity@Table(name = "t_base_student")@ApiModel@Datapublic class StudentEntity implements Serializable{  private static final long serialVersionUID = 546L;  @Id  @GeneratedValue(strategy = GenerationType.AUTO)  @Column(name = "student_id")  private Integer studentId;  @Column(name = "student_grade")  private Integer studentGrade;  @Column(name = "student_class")  private Integer studentClass;  @Column(name = "address")  private String address;  @Column(name = "telephone")  private Integer telephone;  @Column(name = "real_name")  private String realName;  @Column(name = "id_number")  private String idNumber;  @Column(name = "study_id")  private String studyId;  @Column(name = "is_delete")  private int isDelete;  @Column(name = "uuid")  private String uuid;}

dao層

public interface StudentDao extends JpaRepository<StudentEntity,Integer>,JpaSpecificationExecutor{}

動態查詢

public Page<StudentEntity> getTeacherClassStudent(int pageNumber,int pageSize,int gradeId, int classId,String keyword)  {    pageNumber = pageNumber < 0 ? 0 : pageNumber;    pageSize = pageSize < 0 ? 10 : pageSize;    Specification<StudentEntity> specification = new Specification<StudentEntity>()    {      @Override      public Predicate toPredicate(Root<StudentEntity> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder)      {        //page : 0 開始, limit : 默認為 10        List<Predicate> predicates = new ArrayList<>();        predicates.add(criteriaBuilder.equal(root.get("studentGrade"),gradeId));        predicates.add(criteriaBuilder.equal(root.get("studentClass"),classId));        if (!Constant.isEmptyString(keyword))        {          predicates.add(criteriaBuilder.like(root.get("realName").as(String.class),"%" + keyword + "%"));        }        return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));      }    };    /*studentId必須是實體類屬性與數據庫對應，否則報ropertyReferenceException異常*/    PageRequest page = new PageRequest(pageNumber,pageSize,Sort.Direction.ASC,"studentId");    Page<StudentEntity> pages = studentDao.findAll(specification,page);    return pages;  }

因為這個項目應用比較簡單，所以條件只有一個，如果條件較多，甚至可以定義一個專門的類去接收拼接參數，然后判

斷，成立就add進去。

感謝各位的閱讀！關于“怎么實現JPA多條件復雜SQL動態分頁查詢功能”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！