Shulou(Shulou.com)06/03 Report--

This article is about how to implement the dynamic paging query function of JPA multi-conditional and complex SQL. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Overview

While ORM mapping brings us convenience, it also loses great flexibility. If SQL is more complex and dynamic query is required, it must be a headache (or maybe lz has not found a good way). Record the three complex query methods that you use.

Environment

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.81.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

When a SQL is more complex, the first thing that comes to mind is the native SQL statement. If it's just a simple query, it's not that bad.

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

But if you need to make dynamic queries or changes, then the value becomes complex.

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

If you issue a JPQL query in your Java code, you need to take advantage of EntityManager's response method. Generally perform the following process

Get an instance of EntityManager

Call the method createQuery of the instance to create a Query instance. If necessary, you can specify the maximum number of retrievals and the starting location.

Use the Query method getResultList to execute the query, and of course update and delete operations have to be performed using executeUpdate

Make a complex dynamic SQL query

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. Studentprincipidrect st.realrealtimeameparentiary st.studentclassstations.maxroomlevelgrades.usetimetimepages.rightroomnum"); 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);} / * * Class 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;}

Note: if Pageable is not redefined, then pageNumber must be minus 1 because it starts at 0.

Criteria

This is a canonical query based on the concept of a metamodel, which can be an entity tired, an embedded class, or a parent class of mapping, briefly introducing several interfaces used in it.

CriteraQuery is a specific top-level query object that contains various parts such as select,from,where,order by, but it only works on standard queries for entity classes or embedded classes.

The root object of Root standard query, which defines the entity type, is the result you want to query. You can also add query conditions and combine the entity management object to get the query object.

The CriteriaBuilder interface is used to build the builder of CritiaQuery.

StudentEntity class

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: student object * @ param * @ author hwc * @ date 2019-8-8 * / @ Entity@Table (name = "t_base_student") @ ApiModel@Datapublic 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 layer

Public interface StudentDao extends JpaRepository,JpaSpecificationExecutor {}

Dynamic query

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 start, limit: default is 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 (new Predicate [predicates.size ()]);}} / * studentId must be an entity class attribute that corresponds to the database, otherwise report ropertyReferenceException exception * / PageRequest page = new PageRequest (pageNumber,pageSize,Sort.Direction.ASC, "studentId"); Page pages = studentDao.findAll (specification,page); return pages;}

Because the application of this project is relatively simple, there is only one condition. If there are more conditions, you can even define a special class to receive splicing parameters, and then determine

Break, set up and add in.

Thank you for reading! On "how to achieve JPA multi-condition complex SQL dynamic paging query function" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, you can share it out for more people to see it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.