不废话，一个Demo上手QueryDSL
2025-01-22 08:19:30    2k 字    #Java
This post is also available in English and alternative languages.

最近的项目使用 QueryDSL 操作数据库，之前都是用 MyBatis，得闲记录下。

QueryDSL 是一个用于构建类型安全数据库查询的框架。它不依赖于特定的 ORM 框架，可以与多种持久化技术（包括JPA、Hibernate等）一起使用。它提供了一种DSL（领域特定语言），使得在 Java 代码中编写查询变得更加直观和类型安全。


1. test

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
@Slf4j
@SpringBootTest
public class ApplicationSpringBootExampleTest21 {

    @Resource
    private StudentService studentService;

    @Test
    public void save() {
        String data = "{\"no\": \"1\",\"name\": \"c\",\"age\": 10}";
        StudentSaveReqDTO student = JSONObject.toJavaObject(JSONObject.parseObject(data), StudentSaveReqDTO.class);

        LocalDateTime now = LocalDateTime.now();
        Student studentEntity = new Student();
        BeanUtils.copyProperties(student, studentEntity);
        studentEntity.setCreateBy("cyx");
        studentEntity.setCreateTime(now);
        studentEntity.setUpdateBy("cyx");
        studentEntity.setUpdateTime(now);
        studentService.save(studentEntity);
    }

    @Test
    public void updateStudent() {
        String data = "{\"id\": 27,\"no\": \"11\",\"name\": \"cc\",\"age\": 1010}";
        StudentUpdateReqDTO repStudent = JSONObject.toJavaObject(JSONObject.parseObject(data), StudentUpdateReqDTO.class);

        Student existStudent = studentService.checkExist(repStudent.getId());

        // 重名校验
        // 请求的name与exist的name一致,不处理。不一致,检查db。
        if (!repStudent.getName().equals(existStudent.getName())) {
            studentService.duplicateNameCheck(repStudent.getName());
        }

        // 新对象 -> 旧对象
        BeanUtils.copyProperties(repStudent, existStudent);
        existStudent.setUpdateBy("9527").setUpdateTime(LocalDateTime.now());

        studentService.update(existStudent);
    }

    @Test
    public void removeStudent() {
        studentService.delete(26L);
    }

    @Test
    public void pageQuery() {

        String data = "{\"page_number\": 0,\"page_size\": 2,\"no\": \"\",\"name\": \"\",\"orders\": [{\"property\": \"id\",\"direction\": \"asc\"}]}";
        StudentPageReqDTO pageReq = JSONObject.toJavaObject(JSONObject.parseObject(data), StudentPageReqDTO.class);

        QueryResults<Student> results = studentService.queryPage(pageReq);

        List<StudentPageResDTO> collect = results.getResults().stream().map(student -> {
            StudentPageResDTO studentPageResDTO = new StudentPageResDTO();
            BeanUtils.copyProperties(student, studentPageResDTO);
            return studentPageResDTO;
        }).collect(Collectors.toList());

        QueryResults<StudentPageResDTO> pageQueryResults = new QueryResults<>(
                collect, results.getLimit(), results.getOffset(), results.getTotal());

        log.info("pageQueryResults:{}", pageQueryResults);
    }

}

2. service

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
import com.querydsl.core.QueryResults;
import com.querydsl.core.types.Order;
import com.querydsl.core.types.OrderSpecifier;
import com.querydsl.core.types.dsl.PathBuilder;
import com.yxcheng.example21.common.Direction;
import com.yxcheng.example21.dao.StudentDAO;
import com.yxcheng.example21.dto.req.page.SortOrder;
import com.yxcheng.example21.dto.req.page.StudentPageReqDTO;
import com.yxcheng.example21.entity.Student;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;

import javax.annotation.Resource;
import java.util.Collections;
import java.util.List;
import java.util.Objects;

@Slf4j
@Service
public class StudentService {

    @Resource
    private StudentDAO studentDAO;

    /**
     * {@inheritDoc}
     * <p>
     * 使用 entityManager
     */
    @Transactional(rollbackFor = Exception.class, propagation = Propagation.REQUIRED)
    public void save(Student student) {
        // 重名校验
        duplicateNameCheck(student.getName());

        studentDAO.save(student);
    }

    public Student checkExist(Long id) {
        Student existStudent = studentDAO.readById(id);
        if (Objects.isNull(existStudent)) {
            log.error("更新学生信息失败,未找到学生信息,id:{}", id);
            throw new RuntimeException("未找到学生信息");
        }
        return existStudent;
    }

    /**
     * {@inheritDoc}
     * <p>
     * 使用 entityManager
     */
    @Transactional(rollbackFor = Exception.class, propagation = Propagation.REQUIRED)
    public void update(Student existStudent) {
        studentDAO.update(existStudent);
    }

    /**
     * 重名校验
     *
     * @param studentName 学生姓名
     */
    public void duplicateNameCheck(String studentName) {
        Student data = studentDAO.findByName(studentName);
        if (Objects.nonNull(data)) {
            throw new RuntimeException("学生姓名重复");
        }
    }

    /**
     * {@inheritDoc}
     * <p>
     * 使用 entityManager
     */
    @Transactional(rollbackFor = Exception.class, propagation = Propagation.REQUIRED)
    public void delete(Long id) {
        Student oldEntity = studentDAO.readById(id);
        studentDAO.remove(oldEntity);
        log.info("删除学生信息,studentId:{}", id);
    }

    public QueryResults<Student> queryPage(StudentPageReqDTO studentPageReqDTO) {
        // 请求参数兜底处理
        List<SortOrder> orders;
        if (Objects.isNull(studentPageReqDTO) || CollectionUtils.isEmpty(studentPageReqDTO.getOrders())) {
            orders = Collections.singletonList(new SortOrder().setProperty("id").setDirection(Direction.DESC.name()));
        } else {
            orders = studentPageReqDTO.getOrders();
        }
        Integer pageNumber = Objects.isNull(studentPageReqDTO.getPageNumber()) ? 0 : studentPageReqDTO.getPageNumber();
        Integer pageSize = Objects.isNull(studentPageReqDTO.getPageSize()) ? 200 : studentPageReqDTO.getPageSize();
        OrderSpecifier[] orderSpecifiers = getOrderSpecifiers(orders, Student.class);

        return studentDAO.queryPage(orderSpecifiers, pageNumber, pageSize);
    }

    /**
     * 根据排序指令生成相应的排序规则
     *
     * @param orders 排序指令
     * @param klass  待排序对象的类
     * @return 排序规则数组
     */
    private OrderSpecifier[] getOrderSpecifiers(List<SortOrder> orders, Class klass) {
        // 从类名中获取变量名的首字母小写形式
        String className = klass.getSimpleName();
        final String orderVariable = String.valueOf(Character.toLowerCase(className.charAt(0)))
                .concat(className.substring(1));

        // 将排序指令列表转换为排序规则数组
        return orders.stream().map(order -> new OrderSpecifier(
                        // 判断排序方向，若为 DESC，则使用降序排序，否则使用升序排序
                        order.getDirection().equalsIgnoreCase(Order.DESC.name()) ? Order.DESC : Order.ASC,
                        // 构建路径表达式，用于查询DSL
                        new PathBuilder(klass, orderVariable).get(order.getProperty())))
                .toArray(OrderSpecifier[]::new);
    }
}

3. dao

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
import com.alibaba.fastjson2.JSONObject;
import com.querydsl.core.QueryResults;
import com.querydsl.core.types.OrderSpecifier;
import com.querydsl.jpa.impl.JPAQuery;
import com.querydsl.jpa.impl.JPAQueryFactory;
import com.yxcheng.example21.entity.QStudent;
import com.yxcheng.example21.entity.Student;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import javax.persistence.EntityManager;

@Data
@Slf4j
@Repository
public class StudentDAO {

    @Resource
    private EntityManager entityManager;

    @Resource
    private JPAQueryFactory jpaQueryFactory;

    public void save(Student student) {
        entityManager.persist(student);
        log.info("保存学生信息成功,student:{}", JSONObject.toJSONString(student));
    }

    public Student findByName(String studentName) {
        QStudent qStudent = QStudent.student;
        return jpaQueryFactory.selectFrom(qStudent).where(qStudent.name.eq(studentName)).fetchOne();
    }

    public Student readById(Long id) {
        return entityManager.find(Student.class, id);
    }

    public void update(Student student) {
        entityManager.merge(student);
        entityManager.flush();
    }

    public void remove(Student student) {
        entityManager.remove(student);
    }

    public QueryResults<Student> queryPage(OrderSpecifier[] orderSpecifiers, Integer pageNumber, Integer pageSize) {
        QStudent qStudent = QStudent.student;
        JPAQuery<Student> jpaQuery = jpaQueryFactory.selectFrom(qStudent);
        return jpaQuery.orderBy(orderSpecifiers)
                // 起始页
                .offset(pageNumber)
                // 每页大小
                .limit(pageSize)
                .fetchResults();
    }
}

4. entity

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
@Entity
@Table(name = "student")
@Data
@Accessors(chain = true)
public class Student implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    /**
     * 学号
     */
    private String no;
    /**
     * 学生姓名
     */
    private String name;
    /**
     * 學生年龄
     */
    private Integer age;
    /**
     * 创建时间
     */
    private LocalDateTime createTime;
    /**
     * 更新时间
     */
    private LocalDateTime updateTime;
    /**
     * 创建者
     */
    private String createBy;
    /**
     * 更新者
     */
    private String updateBy;
}

5. configuration

注入 EntityManager 用于与持久层交互，同时使用注入的 EntityManager 创建 JPAQueryFactory 实例。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
@Configuration
public class CustomizeConfiguration {
	
    @PersistenceContext
	private EntityManager entityManager;
    
	/**
	 * 让Spring管理JPAQueryFactory
	 */
	@Bean
	public JPAQueryFactory jpaQueryFactory() {
		return new JPAQueryFactory(entityManager);
	}
}

6. pom

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
<dependencies>

    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-jpamodelgen</artifactId>
        <optional>true</optional>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>com.querydsl</groupId>
        <artifactId>querydsl-jpa</artifactId>
    </dependency>
    <dependency>
        <groupId>com.querydsl</groupId>
        <artifactId>querydsl-apt</artifactId>
        <scope>provided</scope>
    </dependency>

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>

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

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

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
        <exclusions>
            <exclusion>
                <groupId>org.junit.vintage</groupId>
                <artifactId>junit-vintage-engine</artifactId>
            </exclusion>
        </exclusions>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-configuration-processor</artifactId>
    </dependency>

    <!--lombok-->
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.22</version>
    </dependency>

    <!--common-util-->
    <dependency>
        <groupId>org.server</groupId>
        <artifactId>common-util</artifactId>
        <version>0.0.1-SNAPSHOT</version>
    </dependency>

</dependencies>

<build>
    <plugins>
        <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
            <version>2.5.2</version>
        </plugin>
        <!-- query dsl 构建Q版实体类的插件-->
        <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>

7. yml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
spring:
  application:
    name: xxx
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: xxx
    username: xxx
    password: xxx
  jpa:
    show-sql: true # 打印执行的SQL
    properties:
      hibernate:
        # 格式化输出的 SQL
        format_sql: true

server:
  port: 9421
  servlet:
    context-path: /api

8. SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE `student`
(
    `id`          int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `no`          varchar(250)       NOT NULL COMMENT '学号',
    `name`        varchar(50) UNIQUE NOT NULL COMMENT '学生姓名',
    `age`         int                NOT NULL COMMENT '学生年龄',
    `create_time` datetime           NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` datetime           NOT NUll DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
    `create_by`   varchar(50)        NOT NULL COMMENT '创建者',
    `update_by`   varchar(50)        NOT NULL COMMENT '更新者',
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_general_ci
    COMMENT '学生信息表';