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

2023-08-15 03:14:04 164

概述

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
DemoprojectforSpringBoot

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="SELECTIFNULL(sum(right_num),0)sumRightFROMt_recordWHERErecord_owner_id=?1ANDresponder_no=?2",nativeQuery=true)
MapsumRightNum(intstudentId,intresponderNo);

但如果需要进行动态查询，或更改，那这个value就变得复杂了。

packagecom.xmlxy.seasgame.dao;
importcom.xmlxy.seasgame.entity.ScoreEntity;
importorg.springframework.data.jpa.repository.Modifying;
importorg.springframework.data.jpa.repository.Query;
importorg.springframework.data.repository.CrudRepository;
importorg.springframework.data.repository.query.Param;
importorg.springframework.transaction.annotation.Transactional;
importjava.util.List;
/**
*
*Description:
*@authorhwc
*@date2019/9/5
*@return
*/
publicinterfaceScoreDaoextendsCrudRepository
{
/**
*
*Description:
*@paramscoreEntity
*@authorhwc
*@date2019/9/6
*/
@Transactional(rollbackFor=Exception.class)
@Modifying
@Query(value="UPDATEt_scoretSET"+
"t.responder_no=CASEWHEN:#{#scoreEntity.responderNo}ISNULLTHENt.responder_noELSE:#{#scoreEntity.responderNo}END,"+
"t.max_level=CASEWHEN:#{#scoreEntity.maxLevel}ISNULLTHENt.max_levelELSE:#{#scoreEntity.maxLevel}END,"+
"t.right_num=CASEWHEN:#{#scoreEntity.rightNum}ISNULLTHENt.right_numELSE:#{#scoreEntity.rightNum}END,"+
"t.use_time=CASEWHEN:#{#scoreEntity.userTime}ISNULLTHENt.use_timeELSE:#{#scoreEntity.userTime}ENDWHEREstudent_id=:#{#scoreEntity.getStudentId()}",nativeQuery=true)
voidupdateScore(@Param("scoreEntity")ScoreEntityscoreEntity);
}

JPQL

如果Java代码内发出JPQL查询，就需要利用到EntityManager的响应方法了。一般执行以下流程

获取一个EntityManager实例

调用实例的方法createQuery，创建一个Query实例，如果有需要可以指定检索的最大数量和起始位置

使用Query方法getResultList执行查询，当然更新和删除操作得使用executeUpdate执行

进行一个复杂的动态SQL查询

publicPagegetScoreByRank(intgradeId,intclassId,Pageablepageable)
{
StringBuildercountSelectSql=newStringBuilder("");
countSelectSql.append("SELECTCOUNT(*)");
countSelectSql.append("FROM");
countSelectSql.append("t_scores,");
countSelectSql.append("t_studentst");
countSelectSql.append("WHERE");
countSelectSql.append("s.student_id=st.student_id");
StringBuilderselectSql=newStringBuilder();
selectSql.append("SELECTs.student_id,st.real_name,st.student_class,s.max_level,s.use_time,s.right_num");
selectSql.append("FROMt_scores");
selectSql.append("JOINt_studentstONs.student_id=st.student_id");
selectSql.append("WHERE1=1");
Mapparams=newHashMap<>();
StringBuilderwhereSql=newStringBuilder();
if(gradeId!=-1)
{
whereSql.append("ANDst.student_grade=:student_grade");
params.put("student_grade",gradeId);
}
/**班级ID*/
if(classId!=-1)
{
whereSql.append("ANDst.student_class=:classId");
params.put("classId",classId);
}
StringorderSql="ORDERBYs.max_levelDESC,s.use_time,s.right_numASC";
StringcountSql=newStringBuilder().append(countSelectSql).append(whereSql).toString();
QuerycountQuery=entityManager.createNativeQuery(countSql);
for(Map.Entryentry:params.entrySet())
{
countQuery.setParameter(entry.getKey(),entry.getValue());
}
BigIntegertotalCount=(BigInteger)countQuery.getSingleResult();
StringquerySql=newStringBuilder().append(selectSql).append(whereSql).append(orderSql).toString();
Queryquery=entityManager.createNativeQuery(querySql,RankEntity.class);
for(Map.Entryentry:params.entrySet())
{
query.setParameter(entry.getKey(),entry.getValue());
}
query.setFirstResult((int)pageable.getOffset());
query.setMaxResults(pageable.getPageSize());
ListrankEntities=query.getResultList();
Pagepage=newPageImpl<>(rankEntities,pageable,totalCount.longValue());
returnpage;
}

注意：如果没有重新定义Pageable那么pageNumber必须减1，因为是从0开始的。

Criteria

这是一种规范查询是以元模型的概念为基础的，这个元模型可以是实体累，嵌入类，或者映射的父类，简单介绍几个里面用到接口。

CriteraQuery是一个特定的顶层查询对象，里面包含select，from，where，orderby等各个部分，然而他只对实体类或嵌入类的标准查询起作用。

Root标准查询的根对象，根定义了实体类型，是你想要查询要获得的结果，也可以添加查询条件，结合实体管理对象得到查询的对象。

CriteriaBuilder接口用来构建CritiaQuery的构建器

StudentEntity类

packagecom.xmlxy.seasgame.entity;
importio.swagger.annotations.ApiModel;
importlombok.Data;
importjavax.persistence.*;
importjavax.print.attribute.standard.MediaSize;
importjava.io.Serializable;
/**
*
*Description:学生对象
*@param
*@authorhwc
*@date2019/8/8
*/
@Entity
@Table(name="t_base_student")
@ApiModel
@Data
publicclassStudentEntityimplementsSerializable
{
privatestaticfinallongserialVersionUID=546L;
@Id
@GeneratedValue(strategy=GenerationType.AUTO)
@Column(name="student_id")
privateIntegerstudentId;
@Column(name="student_grade")
privateIntegerstudentGrade;
@Column(name="student_class")
privateIntegerstudentClass;
@Column(name="address")
privateStringaddress;
@Column(name="telephone")
privateIntegertelephone;
@Column(name="real_name")
privateStringrealName;
@Column(name="id_number")
privateStringidNumber;
@Column(name="study_id")
privateStringstudyId;
@Column(name="is_delete")
privateintisDelete;
@Column(name="uuid")
privateStringuuid;
}

dao层

publicinterfaceStudentDaoextendsJpaRepository,JpaSpecificationExecutor
{
}

动态查询

publicPagegetTeacherClassStudent(intpageNumber,intpageSize,intgradeId,intclassId,Stringkeyword)
{
pageNumber=pageNumber<0?0:pageNumber;
pageSize=pageSize<0?10:pageSize;
Specificationspecification=newSpecification()
{
@Override
publicPredicatetoPredicate(Rootroot,CriteriaQuerycriteriaQuery,CriteriaBuildercriteriaBuilder)
{
//page:0开始,limit:默认为10
Listpredicates=newArrayList<>();
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+"%"));
}
returncriteriaBuilder.and(predicates.toArray(newPredicate[predicates.size()]));
}
};
/*studentId必须是实体类属性与数据库对应，否则报ropertyReferenceException异常*/
PageRequestpage=newPageRequest(pageNumber,pageSize,Sort.Direction.ASC,"studentId");
Pagepages=studentDao.findAll(specification,page);
returnpages;
}

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

断，成立就add进去。

总结

以上所述是小编给大家介绍的JPA多条件复杂SQL动态分页查询功能，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持！
如果你觉得本文对你有帮助，欢迎转载，烦请注明出处，谢谢！

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

热门推荐

随机推荐