符合中小企业对网站设计、功能常规化式的企业展示型网站建设
本套餐主要针对企业品牌型网站、中高端设计、前端互动体验...
商城网站建设因基本功能的需求不同费用上面也有很大的差别...
手机微信网站开发、微信官网、微信商城网站...
这篇文章主要为大家展示了“如何实现JPA多条件复杂SQL动态分页查询功能”,内容简而易懂,条理清晰,希望能够帮助大家解决疑惑,下面让小编带领大家一起研究并学习一下“如何实现JPA多条件复杂SQL动态分页查询功能”这篇文章吧。
成都创新互联公司是一家集网站建设,永福企业网站建设,永福品牌网站建设,网站定制,永福网站建设报价,网络营销,网络优化,永福网站推广为一体的创新建站企业,帮助传统企业提升企业形象加强企业竞争力。可充分满足这一群体相比中小企业更为丰富、高端、多元的互联网需求。同时我们时刻保持专业、时尚、前沿,时刻以成就客户成长自我,坚持不断学习、思考、沉淀、净化自己,让我们为更多的企业打造出实用型网站。
概述
ORM映射为我们带来便利的同时,也失去了较大灵活性,如果SQL较复杂,要进行动态查询,那必定是一件头疼的事情(也可能是lz还没发现好的方法),记录下自己用的三种复杂查询方式。
环境
springBoot
IDEA2017.3.4
JDK8
pom.xml
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 seasgame org.apache.maven.plugins maven-compiler-plugin 2.3.2 ${project.build.sourceEncoding} 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) MapsumRightNum(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 PagegetScoreByRank(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 * @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,JpaSpecificationExecutor { }
动态查询
public PagegetTeacherClassStudent(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进去。
以上是“如何实现JPA多条件复杂SQL动态分页查询功能”这篇文章的所有内容,感谢各位的阅读!相信大家都有了一定的了解,希望分享的内容对大家有所帮助,如果还想学习更多知识,欢迎关注创新互联行业资讯频道!