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

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

概述

ORM映射为我们带来便利的同时，也失去了较大灵活性，如果SQL较复杂，要进行动态查询，那必定是一件头疼的事情（也可能是lz还没发现好的方法），记录下自己用的三种复杂查询方式。

环境

springBoot

IDEA2017.3.4

JDK8

pom.xml

xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

4.0.0

org.springframework.boot

spring-boot-starter-parent

2.1.6.RELEASE

com.xmlxy

seasgame

0.0.1-SNAPSHOT

seasgame

Demo project for Spring Boot

1.8

org.springframework.boot

spring-boot-starter-web

org.springframework.boot

spring-boot-starter-test

test

org.springframework.boot

spring-boot-starter-data-jpa

mysql

mysql-connector-java

runtime

org.springframework.boot

spring-boot-devtools

runtime

true

org.projectlombok

lombok

true

org.springframework.boot

spring-boot-starter-data-jpa

io.springfox

springfox-swagger2

2.8.0

io.springfox

springfox-swagger-ui

2.8.0

org.springframework.boot

spring-boot-configuration-processor

true

org.springframework.boot

spring-boot-starter-security

net.sf.json-lib

json-lib

2.2.2

jdk15

com.belerweb

pinyin4j

2.5.1

org.springframework.boot

spring-boot-starter-thymeleaf

javax.servlet

javax.servlet-api

3.1.0

provided

war

org.springframework.boot

spring-boot-maven-plugin

org.apache.maven.plugins

maven-compiler-plugin

1.8

1.8

seasgame

org.apache.maven.plugins

maven-compiler-plugin

2.3.2

${project.build.sourceEncoding}

1.7

1.7

org.apache.maven.plugins

maven-surefire-plugin

true

xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

4.0.0

org.springframework.boot

spring-boot-starter-parent

2.1.6.RELEASE

com.xmlxy

seasgame

0.0.1-SNAPSHOT

seasgame

Demo project for Spring Boot

1.8

org.springframework.boot

spring-boot-starter-web

org.springframework.boot

spring-boot-starter-test

test

org.springframework.boot

spring-boot-starter-data-jpa

mysql

mysql-connector-java

runtime

org.springframework.boot

spring-boot-devtools

runtime

true

org.projectlombok

lombok

true

org.springframework.boot

spring-boot-starter-data-jpa

io.springfox

springfox-swagger2

2.8.0

io.springfox

springfox-swagger-ui

2.8.0

org.springframework.boot

spring-boot-configuration-processor

true

org.springframework.boot

spring-boot-starter-security

net.sf.json-lib

json-lib

2.2.2

jdk15

com.belerweb

pinyin4j

2.5.1

org.springframework.boot

spring-boot-starter-thymeleaf

javax.servlet

javax.servlet-api

3.1.0

provided

war

org.springframework.boot

spring-boot-maven-plugin

org.apache.maven.plugins

maven-compiler-plugin

1.8

1.8

seasgame

org.apache.maven.plugins

maven-compiler-plugin

2.3.2

${project.build.sourceEncoding}

1.7

1.7

org.apache.maven.plugins

maven-surefire-plugin

true

@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 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

{

/**

*

* 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 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 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 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 entry:params.entrySet())

{

query.setParameter(entry.getKey(),entry.getValue());

}

query.setFirstResult((int) pageable.getOffset());

query.setMaxResults(pageable.getPageSize());

List rankEntities = query.getResultList();

Page 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

* @authorhttp:// 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,JpaSpecificationExecutor

{

}

动态查询

public Page getTeacherClassStudent(int pageNumber,int pageSize,int gradeId, int classId,String keyword)

{

pageNumber = pageNumber < 0 ? 0 : pageNumber;

pageSize = pageSize < 0 ? 10 : pageSize;

Specification specification = new Specification()

{

@Override

public Predicate toPredicate(Root root, CriteriaQuery> criteriaQuery, CriteriaBuilder criteriaBuilder)

{

//page : 0 开始, limit : 默认为 10

List 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 pages = studentDao.findAll(specification,page);

return pages;

}

因为这个项目应用比较简单，所以条件只有一个，如果条件较多，甚至可以定义一个专门的类去接收拼接参数，然后判

断，成立就add进去。

总结

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。