JPA多条件复杂SQL动态分页查询

2019 年 10 月 3 日
筆記

概述

　　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  @Data  public 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进去。

转载一篇写得不错的文章：https://blog.csdn.net/u010775025/article/details/80497986