Categories
Uncategorized

JPA and more complex conditions of dynamic SQL query page

Outline

ORM mapping has brought us convenience, but also lost a greater flexibility, if more complex SQL to perform dynamic query, it must be a headache (may be lz have not found a good way), record under their own complex queries with three ways.

surroundings

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.0modelVersion>
    <parent>
        <groupId>org.springframework.bootgroupId>
        <artifactId>spring-boot-starter-parentartifactId>
        <version>2.1.6.RELEASEversion>
        <relativePath/> 
    parent>
    <groupId>com.xmlxygroupId>
    <artifactId>seasgameartifactId>
    <version>0.0.1-SNAPSHOTversion>
    <name>seasgamename>
    <description>Demo project for Spring Bootdescription>

    <properties>
        <java.version>1.8java.version>
    properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-webartifactId>
        dependency>

        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-testartifactId>
            <scope>testscope>
        dependency>

        
        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-data-jpaartifactId>
        dependency>
        <dependency>
            <groupId>mysqlgroupId>
            <artifactId>mysql-connector-javaartifactId>
            <scope>runtimescope>
        dependency>

        
        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-devtoolsartifactId>
            <scope>runtimescope>
            <optional>trueoptional>
        dependency>

        
        <dependency>
            <groupId>org.projectlombokgroupId>
            <artifactId>lombokartifactId>
            <optional>trueoptional>
        dependency>

        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-data-jpaartifactId>
        dependency>

        
        <dependency>
            <groupId>io.springfoxgroupId>
            <artifactId>springfox-swagger2artifactId>
            <version>2.8.0version>
        dependency>
        <dependency>
            <groupId>io.springfoxgroupId>
            <artifactId>springfox-swagger-uiartifactId>
            <version>2.8.0version>
        dependency>

        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-configuration-processorartifactId>
            <optional>trueoptional>
        dependency>
        
        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-securityartifactId>
        dependency>
        <dependency>
            <groupId>net.sf.json-libgroupId>
            <artifactId>json-libartifactId>
            <version>2.2.2version>
            <classifier>jdk15classifier>
        dependency>
        
        <dependency>
            <groupId>com.belerwebgroupId>
            <artifactId>pinyin4jartifactId>
            <version>2.5.1version>
        dependency>
        
        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-thymeleafartifactId>
        dependency>
        

        <dependency>
            <groupId>javax.servletgroupId>
            <artifactId>javax.servlet-apiartifactId>
            <version>3.1.0version>
            <scope>providedscope>
        dependency>
    dependencies>
    <packaging>warpackaging>
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.bootgroupId>
                <artifactId>spring-boot-maven-pluginartifactId>
            plugin>
            <plugin>
                <groupId>org.apache.maven.pluginsgroupId>
                <artifactId>maven-compiler-pluginartifactId>
                <configuration>
                    <source>1.8source>
                    <target>1.8target>
                configuration>
            plugin>
        plugins>

        <finalName>seasgamefinalName>
        <pluginManagement>
            <plugins>
                <plugin>
                    <groupId>org.apache.maven.pluginsgroupId>
                    <artifactId>maven-compiler-pluginartifactId>
                    <version>2.3.2version>
                    <configuration>
                        <encoding>${project.build.sourceEncoding}encoding>
                        <source>1.7source>
                        <target>1.7target>
                    configuration>
                plugin>
                <plugin>
                    <groupId>org.apache.maven.pluginsgroupId>
                    <artifactId>maven-surefire-pluginartifactId>
                    <configuration>
                        <testFailureIgnore>truetestFailureIgnore>
                    configuration>
                plugin>
            plugins>
        pluginManagement>
    build>

project>

@Query

When a SQL more complex, the first thought is the native SQL statements. If only a simple query, that the situation has not so 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 the need for dynamic queries, or change, then this value becomes complicated.

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 query JPQL within Java code, we need to use to respond to the method of the EntityManager. Perform the following general procedure

    Gets a EntityManager instance

    Call an instance method createQuery, create a Query instance, if there is a need to specify the maximum number and retrieve the starting position

    GetResultList use Query method to execute the query, of course, update, and delete operations was executed using executeUpdate

Carried out a complex dynamic SQL queries

   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;
    }

Note: If you do not have to redefine Pageable then pageNumber minus one, because it is starting from zero.

Criteria

This is a normative concept is based on the query element model based on this meta-model may be tired entities, embedded class, or parent class map, which used briefly a few interfaces.

CriteraQuery is a particular top-level query object, which contains the various parts of select, from, where, order by, etc., but he only entity class or an embedded class standard query function.

Root root object standard query root defines the entity type that you want to get results, you can also add query conditions, combined with the object management entity resulting object query.

CriteriaBuilder used to build the interface builder 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:学生对象
 * @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 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: The 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(predicates.toArray(new Predicate[predicates.size()])); } }; /*

studentId must be an entity class attributes and the corresponding database, otherwise reported abnormal ropertyReferenceException

*/ PageRequest page = new PageRequest(pageNumber,pageSize,Sort.Direction.ASC,"studentId"); Page pages = studentDao.findAll(specification,page); return pages; }

Because this project application is relatively simple, it is only one condition, if the conditions are more, or even define a special class to receive the stitching parameters, and then judge, the establishment of add inside.

Reproduced a well-written article: https: //blog.csdn.net/u010775025/article/details/80497986

 

Leave a Reply