复合条件多层级联查询QueryDSL之Predicate

背景

常见的复合多表联查需要写复杂的sql及业务逻辑，会带来以下几个问题：
1.校验复杂：大量条件判断中一个if写错就会导致数据错误
2.效率低下：代码完成后需要大量校验
3.不易维护：他人维护时，非常考验维护人及之前代码的水平，有可能过段时间自己都看不懂

QueryDSL

Querydsl是一个Java开源框架用于构建类型安全的SQL查询语句。它采用API代替拼凑字符串来构造查询语句。可跟 Hibernate 和 JPA 等框架结合使用。
官网
github

Predicate构建无sql复合多表联查

一、pom引用

1.dependency

		<!-- querydsl -->
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-apt</artifactId>
            <version>4.2.1</version>
            <scope>provided</scope>
        </dependency>

        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-jpa</artifactId>
            <version>4.2.1</version>
        </dependency>

2.plugin

			<plugin>
                <groupId>com.mysema.maven</groupId>
                <artifactId>apt-maven-plugin</artifactId>
                <version>1.1.3</version>
                <executions>
                    <execution>
                        <goals>
                            <goal>process</goal>
                        </goals>
                        <configuration>
                            <outputDirectory>target/generated-sources/java</outputDirectory>
                            <processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
                        </configuration>
                    </execution>
                </executions>
                <dependencies>
                    <dependency>
                        <groupId>com.querydsl</groupId>
                        <artifactId>querydsl-apt</artifactId>
                        <version>4.2.1</version>
                    </dependency>
                </dependencies>
            </plugin>

二、代码构建

1.model

1.继承java.io.Serializable，实体上用@Entity注解

public abstract class AbstractModel implements Serializable {
    ...
}

2.在关联字段上加入注解@QueryInit("*.*.*.*")

@Setter
@Getter
@Entity
@Table(name = "student")
public class Student extends AbstractModel{

    @QueryInit("*.*.*.*")
    @ManyToOne(fetch = FetchType.EAGER, optional = false)
    @JoinColumn(name = "grade_id")
    private Grade grade;
    
	...
}

2.Repository

import com.joe.querydsl.model.QStudent;
import com.joe.querydsl.model.Student;
import com.querydsl.core.types.dsl.StringPath;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;
import org.springframework.data.querydsl.binding.QuerydslBinderCustomizer;
import org.springframework.data.querydsl.binding.QuerydslBindings;
import org.springframework.data.repository.Repository;

/**
 * @author JoeToffler
 * @describe Student Repository
 * @date 2019-10-10 11:24
 */
@org.springframework.stereotype.Repository
public interface StudentRepository extends Repository<Student, Long>
        , JpaSpecificationExecutor<Student>
        , QuerydslPredicateExecutor<Student>
        , QuerydslBinderCustomizer<QStudent> {
    /**
     * Student查询入口统一自定义过滤
     */
    @Override
    default void customize(QuerydslBindings bindings, QStudent student) {
        /**
         * 通过name匹配name或nickname
         */
        bindings.bind(student.name).first((path, value) ->
                student.name.contains(value).or(student.nickname.contains(value))
        );

        /**
         * 查询字段黑名单
         * 如：禁止通过性别查询
         */
        bindings.excluding(student.sex);

        /**
         * 忽略大小写
         */
        bindings.bind(String.class).first((StringPath path, String value) -> path.containsIgnoreCase(value));
    }
}

3.Controller

import com.joe.querydsl.model.Student;
import com.joe.querydsl.repository.StudentRepository;
import com.querydsl.core.BooleanBuilder;
import com.querydsl.core.types.Predicate;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.querydsl.binding.QuerydslPredicate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;


/**
 * @author JoeToffler
 * @describe
 * @date 2019-10-10 14:56
 */
@RestController
@RequestMapping("/w/students")
@Api(tags = {"学生API"})
public class StudentController {

    @Autowired
    StudentRepository studentRepository;

    @ApiOperation(value = "获取分页数据")
    @GetMapping("/search")
    public ResultModel search(@QuerydslPredicate(root = Student.class) Predicate predicate, final Pageable pageable) {
        if (predicate == null) predicate = new BooleanBuilder();
        Page<Student> all = studentRepository.findAll(predicate, pageable);
        Pageable page = all.getPageable();
        List<Student> content = all.getContent();
        ResultModel resultModel = new ResultModel(content, page);
        return resultModel;
    }
}

4.请求

GET http://localhost:7777/w/students/search?page=0&size=20&grade.teacher.teachingGroup.school.name=北京四中

坑

1.每次修改model时需要mvn clean install生产新的querydsl model类，否则。

2.Predicate方式查询默认为三层(使用默认时可以不加@QueryInit())，最大支持到五层，如：国、省、市、区、街道、小区，从小区查国家是不支持的。

完整代码示例工程

QueryDSL Github

感谢您的阅读，如有帮助请来波真诚的点赞，博主会努力更新到肝爆