使用 QueryDSL 简化 JPA 查询

代码仓库地址：https://gitee.com/fangjunjie/querydsl-jpa-demo

一、SpringBoot工程搭建

项目依赖

在JPA项目的基础上，增加QueryDSL支持，添加QueryDSL插件。

<?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 https://maven.apache.org/xsd/maven-4.0.0.xsd">

    <modelVersion>4.0.0</modelVersion>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.15</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>

    <groupId>cn.junki</groupId>
    <artifactId>querydsl-jpa-demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>querydsl-jpa-demo</name>
    <description>querydsl-jpa-demo</description>

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

    <dependencies>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <scope>runtime</scope>
        </dependency>

        <!-- QueryDSL支持 -->
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-apt</artifactId>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>com.querydsl</groupId>
            <artifactId>querydsl-sql-spring</artifactId>
            <version>${querydsl.version}</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
            <!-- querydsl查询类生成插件 -->
            <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>
            </plugin>
        </plugins>
    </build>

</project>

项目配置

在 src/main/resources/application.yml 中添加JPA和数据源配置。

spring:
  # JPA配置
  jpa:
    hibernate:
      # 如果数据库没有对应实体类的表，则创建表；如果数据库的表没有实体类对应的字段，则增加字段。
      ddl-auto: update
    show-sql: true

  # mysql配置
  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://127.0.0.1:3306/querydsl_jpa_demo?useSSL=true&serverTimezone=GMT%2B8
    driver-class-name: com.mysql.cj.jdbc.Driver

配置QueryDSL查询工厂

package cn.junki.querydsljpademo.config;

import com.querydsl.jpa.impl.JPAQueryFactory;
import com.querydsl.sql.MySQLTemplates;
import com.querydsl.sql.SQLQueryFactory;
import com.querydsl.sql.SQLTemplates;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.persistence.EntityManager;
import javax.sql.DataSource;

/**
 * JPA+QueryDSL查询工厂
 *
 * @author Junki
 * @date 2023-09-05 10:30:21
 */
@Configuration
public class QueryFactoryConfig {

    @Bean
    public JPAQueryFactory jpaQueryFactory(EntityManager entityManager) {
        return new JPAQueryFactory(entityManager);
    }

    @Bean
    public SQLQueryFactory sqlQueryFactory(DataSource dataSource){
        SQLTemplates sqlTemplates = MySQLTemplates.builder().build();
        return new SQLQueryFactory(new com.querydsl.sql.Configuration(sqlTemplates), dataSource);
    }
}

二、创建实体类，并自动生成QueryDSL查询类

创建实体类

创建两个简单有关联的实体类。这里没有使用JPA的表关联注解，如有需要可以增加，QueryDSL均可兼容使用。

package cn.junki.querydsljpademo.entity;

import cn.junki.querydsljpademo.enums.GenderEnum;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.*;

/**
 * 学生类
 * @author Junki
 * @date 2023-09-05 10:40:36
 */
@Entity
@Table(name = "demo_student")
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class Student {

    @Id // 主键
    @GeneratedValue(strategy = GenerationType.IDENTITY) // 自增
    private Long id;

    private Long schoolId;

    private String name;

    @Enumerated(EnumType.ORDINAL) // 使用枚举索引
    private GenderEnum gender;

}

package cn.junki.querydsljpademo.entity;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import javax.persistence.*;

/**
 * 学校类
 * @author Junki
 * @date 2023-09-05 10:41:23
 */
@Entity
@Table(name = "demo_school")
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class School {

    @Id // 主键
    @GeneratedValue(strategy = GenerationType.IDENTITY) // 自增
    private Long id;

    private String name;

}

建表并初始化数据

启动项目后，会自动创建数据表。

初始化一些数据：

INSERT INTO `demo_school` (`id`, `name`) VALUES (1, '一中');
INSERT INTO `demo_school` (`id`, `name`) VALUES (2, '二中');
INSERT INTO `demo_school` (`id`, `name`) VALUES (3, '三中');

INSERT INTO `demo_student` (`id`, `gender`, `name`, `school_id`) VALUES (1, 1, '大壮', 1);
INSERT INTO `demo_student` (`id`, `gender`, `name`, `school_id`) VALUES (2, 0, '小美', 2);
INSERT INTO `demo_student` (`id`, `gender`, `name`, `school_id`) VALUES (3, 0, '小兰', 3);
INSERT INTO `demo_student` (`id`, `gender`, `name`, `school_id`) VALUES (4, 1, '大胖', 2);

生成Q类

运行 mvn compile 后，QueryDSL插件会自动运行，并在 target/classes/cn/junki/querydsljpademo/entity 目录下生成Q类。

//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by FernFlower decompiler)
//

package cn.junki.querydsljpademo.entity;

import cn.junki.querydsljpademo.enums.GenderEnum;
import com.querydsl.core.types.Path;
import com.querydsl.core.types.PathMetadata;
import com.querydsl.core.types.PathMetadataFactory;
import com.querydsl.core.types.dsl.EntityPathBase;
import com.querydsl.core.types.dsl.EnumPath;
import com.querydsl.core.types.dsl.NumberPath;
import com.querydsl.core.types.dsl.StringPath;

public class QStudent extends EntityPathBase<Student> {
    private static final long serialVersionUID = 576080645L;
    public static final QStudent student = new QStudent("student");
    public final EnumPath<GenderEnum> gender = this.createEnum("gender", GenderEnum.class);
    public final NumberPath<Long> id = this.createNumber("id", Long.class);
    public final StringPath name = this.createString("name");
    public final NumberPath<Long> schoolId = this.createNumber("schoolId", Long.class);

    public QStudent(String variable) {
        super(Student.class, PathMetadataFactory.forVariable(variable));
    }

    public QStudent(Path<? extends Student> path) {
        super(path.getType(), path.getMetadata());
    }

    public QStudent(PathMetadata metadata) {
        super(Student.class, metadata);
    }
}

//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by FernFlower decompiler)
//

package cn.junki.querydsljpademo.entity;

import com.querydsl.core.types.Path;
import com.querydsl.core.types.PathMetadata;
import com.querydsl.core.types.PathMetadataFactory;
import com.querydsl.core.types.dsl.EntityPathBase;
import com.querydsl.core.types.dsl.NumberPath;
import com.querydsl.core.types.dsl.StringPath;

public class QSchool extends EntityPathBase<School> {
    private static final long serialVersionUID = 141054314L;
    public static final QSchool school = new QSchool("school");
    public final NumberPath<Long> id = this.createNumber("id", Long.class);
    public final StringPath name = this.createString("name");

    public QSchool(String variable) {
        super(School.class, PathMetadataFactory.forVariable(variable));
    }

    public QSchool(Path<? extends School> path) {
        super(path.getType(), path.getMetadata());
    }

    public QSchool(PathMetadata metadata) {
        super(School.class, metadata);
    }
}

三、创建数据操作接口并测试

创建数据操作接口

继承 JpaRepository 和 QuerydslPredicateExecutor 。

package cn.junki.querydsljpademo.repository;

import cn.junki.querydsljpademo.entity.Student;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;
import org.springframework.stereotype.Repository;

/**
 * @author Junki
 * @date 2023-09-05 10:58:13
 */
@Repository
public interface StudentRepository extends JpaRepository<Student, Long>, QuerydslPredicateExecutor<Student> {

}

数据操作测试

我们可以直接使用 StudentRepository 结合 Q类 进行简单的查询，也可以使用 JPAQueryFactory 结合 Q类 进行复杂的联合查询。

package cn.junki.querydsljpademo;

import cn.junki.querydsljpademo.entity.*;
import cn.junki.querydsljpademo.enums.GenderEnum;
import cn.junki.querydsljpademo.repository.StudentRepository;
import com.querydsl.core.group.GroupBy;
import com.querydsl.core.types.Projections;
import com.querydsl.core.types.QMap;
import com.querydsl.jpa.impl.JPAQueryFactory;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.domain.Page;
import org.springframework.data.querydsl.QPageRequest;

import javax.annotation.Resource;
import java.util.List;
import java.util.Map;

@SpringBootTest
class QuerydslJpaDemoApplicationTests {

    @Resource
    private StudentRepository studentRepository;

    @Resource
    private JPAQueryFactory jpaQueryFactory;

    @Test
    void test1() {

        // 查询所有学生
        List<Student> all = studentRepository.findAll();
        System.out.println(all);

        // 分页查询学生
        Page<Student> page = studentRepository.findAll(QPageRequest.of(0, 10));
        System.out.println(page.getContent());

        // 查询所有女生
        QStudent qStudent = QStudent.student;
        Iterable<Student> allFemale = studentRepository.findAll(qStudent.gender.eq(GenderEnum.FEMALE));
        System.out.println(allFemale);

        // 查询所有二中学生
        QSchool qSchool = QSchool.school;
        List<Student> schoolStudents = jpaQueryFactory.select(qStudent)
                .from(qStudent)
                .innerJoin(qSchool)
                .on(qStudent.schoolId.eq(qSchool.id))
                .where(qSchool.name.eq("二中"))
                .fetch();
        System.out.println(schoolStudents);

        // 查询所有学校以及学生数据
        Map<Long, SchoolVO> schoolVOMap = jpaQueryFactory.from(qSchool)
                .leftJoin(qStudent)
                .on(qStudent.schoolId.eq(qSchool.id))
                .transform(
                        GroupBy.groupBy(qSchool.id).as(
                                Projections.bean(
                                        SchoolVO.class,
                                        qSchool.id,
                                        qSchool.name,
                                        GroupBy.list(
                                                Projections.bean(
                                                        Student.class,
                                                        qStudent.id,
                                                        qStudent.schoolId,
                                                        qStudent.name,
                                                        qStudent.gender
                                                )
                                        ).as("students")
                                )
                        )
                );

        schoolVOMap.forEach((k, v) -> {
            System.out.println(k);
            System.out.println(v);
        });

    }

}