溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要為大家展示了“如何實現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
@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進去。
以上是“如何實現JPA多條件復雜SQL動態分頁查詢功能”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
