JPA Querydsl

1. 简介：
2. 基础脚本：
- 2.1：用户信息表
- 2.2: 部门信息表
3.Querydsl-JPA 多表操作：
4. Querydsl 与 spring web 的整合：

1. 简介：

上篇文章咱们对 Querydsl-JPA 对了介绍以及基本讲解，下来咱们开始介绍一些平常咱们经常用的多表联查用 Querydsl-JPA 是如何实现的。

2. 基础脚本：

2.1：用户信息表

-- Create table
create table USER_TMW
(
  id         VARCHAR2(32 CHAR) not null,
  name       VARCHAR2(32 CHAR),
  age        NUMBER(19,2),
  money      NUMBER(19,2),
  begin_time VARCHAR2(32 CHAR),
  end_time   VARCHAR2(32 CHAR),
  dept_id    VARCHAR2(32 CHAR)
)
-- Create/Recreate primary, unique and foreign key constraints 
alter table USER_TMW
  add primary key (ID)
  using index 
  tablespace UFGOV
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
insert into user_tmw (ID, NAME, AGE, MONEY, BEGIN_TIME, END_TIME, DEPT_ID)
values ('8a84a8b36bbbc8e1016bbbd803c60016', '老王', 20.00, 2000.00, '1567579914276', '1567579904276', 'C8477CE676B143E983260B45D05C06B3');
insert into user_tmw (ID, NAME, AGE, MONEY, BEGIN_TIME, END_TIME, DEPT_ID)
values ('0000000000000000000111', '小王', 30.00, 1500.00, '1567579924276', '1567579904276', 'C8477CE676B143E983260B45D05C06B3');
insert into user_tmw (ID, NAME, AGE, MONEY, BEGIN_TIME, END_TIME, DEPT_ID)
values ('0000000000000000000001', '王五', 18.00, 1800.00, '1567579934276', '1567579904276', '8a90959d6b88ce95016b8c547cfb03e7');
insert into user_tmw (ID, NAME, AGE, MONEY, BEGIN_TIME, END_TIME, DEPT_ID)
values ('0000000000000000000011', '小刚', 25.00, 1000.00, '1567579944276', '1567579904276', '8a90959d6b88ce95016b8c547cfb03e7');
insert into user_tmw (ID, NAME, AGE, MONEY, BEGIN_TIME, END_TIME, DEPT_ID)
values ('0000000000000000011111', '张三', 30.00, 2000.00, '1567579954276', '1567579904276', '8a90959d6b92c60e016b937f0d550080');
insert into user_tmw (ID, NAME, AGE, MONEY, BEGIN_TIME, END_TIME, DEPT_ID)
values ('0000000000000000000021', '李四', 30.00, 3000.00, '1567579964276', '1567579904276', '8a90959d6b92c60e016b937f0d550080');

2.2: 部门信息表

-- Create table
create table DEPT_TMW
(
  id          VARCHAR2(32 CHAR) not null,
  dept_name   VARCHAR2(32 CHAR),
  dept_no     VARCHAR2(32 CHAR),
  create_time VARCHAR2(32 CHAR),
  p_dept_id   VARCHAR2(32 CHAR)
)
-- Create/Recreate primary, unique and foreign key constraints 
alter table DEPT_TMW
  add primary key (ID)
  using index 
  tablespace UFGOV
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
insert into dept_tmw (ID, DEPT_NAME, DEPT_NO, CREATE_TIME, P_DEPT_ID)
values ('C8477CE676B143E983260B45D05C06B3', '研发部门', 'N001', '1567579904276', null);
insert into dept_tmw (ID, DEPT_NAME, DEPT_NO, CREATE_TIME, P_DEPT_ID)
values ('8a90959d6b88ce95016b8c547cfb03e7', '测试部门', 'N002', '1567579804276', 'C8477CE676B143E983260B45D05C06B3');
insert into dept_tmw (ID, DEPT_NAME, DEPT_NO, CREATE_TIME, P_DEPT_ID)
values ('8a90959d6b92c60e016b937f0d550080', '运维部门', 'N003', '1567579704276', '8a90959d6b88ce95016b8c547cfb03e7');

3.Querydsl-JPA 多表操作：

3.1：新增用户和部门的实体类

import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.hibernate.annotations.GenericGenerator;
import javax.persistence.*;
/**
 * @ProjectName: queryDsl
 * @Package: com.springboot.demo.bean
 * @ClassName: Dept
 * @Author: tianmengwei
 * @Description:
 * @Date: 2019/9/4 15:10
 */
@AllArgsConstructor
@NoArgsConstructor
@Data
@Entity
@Table(name = "dept_tmw")
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
public class Dept {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "custom-uuid")
    @GenericGenerator(name = "custom-uuid", strategy = "com.springboot.demo.bean.CustomUUIDGenerator")
    @Column(name = "id", nullable = false, length = 32)
    private String id;
    @Column(name = "dept_name")
    private String deptName;
    @Column(name = "dept_no")
    private String deptNo;
    @Column(name = "create_time")
    private String createTime;
    @Column(name = "p_dept_id")
    private String pDeptId;
}
import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.hibernate.annotations.GenericGenerator;
import javax.persistence.*;
import java.math.BigDecimal;
/**
 * @ProjectName: queryDsl
 * @Package: com.springboot.demo
 * @ClassName: User
 * @Author: tianmengwei
 * @Description:
 * @Date: 2019/8/19 19:35
 */
@AllArgsConstructor
@NoArgsConstructor
@Data
@Entity
@Table(name = "user_tmw")
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "custom-uuid")
    @GenericGenerator(name = "custom-uuid", strategy = "com.springboot.demo.bean.CustomUUIDGenerator")
    @Column(name = "id", nullable = false, length = 32)
    private String id;
    @Column(name = "name", length = 10)
    private String name;
    @Column(name = "age")
    private Integer age;
    @Column(name = "money")
    private BigDecimal money;
    @Column(name = "begin_time")
    private String beginTime;
    @Column(name = "end_time")
    private String endTime;
    @Column(name = "dept_id")
    private String deptId;
}

3.2: 多表关联查询结果多字段拼接显示处理（concat()）

public void getDeptUserListByLeftJoin() {
    QDept qDept = QDept.dept;
    QUser qUser = QUser.user;
    List<Tuple> tupleList = jpaQueryFactory.select(qDept.deptNo.concat(":").concat(qDept.deptName).concat(":").concat(qUser.name),
            qUser.age, qUser.money).from(qDept).leftJoin(qUser).on(qDept.id.eq(qUser.deptId))
            .orderBy(qUser.age.desc()).fetch();
    List<Map<String, Object>> resultList = tupleList.stream().map(x -> {
        Map<String, Object> resultMap = new HashMap<>();
        resultMap.put("nameDept", x.get(0, Dept.class));
        resultMap.put("nameUser", x.get(0, User.class));
        resultMap.put("nameDeptUser", x.get(qDept.deptNo.concat(":").concat(qDept.deptName).concat(":").concat(qUser.name)));
        resultMap.put("age", x.get(qUser.age));
        resultMap.put("money", x.get(qUser.money));
        return resultMap;
    }).collect(Collectors.toList());
    String userQueryResultsStr = JSON.toJSONString(resultList);
    System.out.println("getDeptUserListByLeftJoin的结果集:" + userQueryResultsStr);
}

此处，我们在获取返回结果根据 jpaFactory 关联查询返回的类型是 Tuple，Tuple 提供了以下两种方式, 可以根据下标和指定某个属性去获取：

JPA Querydsl - 图1

在根据下标获取的方法第二个形参是指定一个 Class，在此处测试写关联查询的任意一个实体类都可以。

JPA Querydsl - 图2

3.3: 关联查询结果 case when 在 querydsl 中的使用

此处 querydsl 提供了 CaseBuilder 类，我们可以使用该类对字段的值做处理转换，当然我们呢也可以在用 stream 流的时候也进行处理；

public void getDeptUserListByJoin() {
    QDept qDept = QDept.dept;
    QUser qUser = QUser.user;
    StringExpression otherwise = new CaseBuilder().when(qUser.age.gt(18)).then("成年人").when(qUser.age.lt(18)).then("青少年")
            .otherwise("快成年了");
    List<Tuple> tupleList = jpaQueryFactory.select(qUser.name,
            otherwise, qUser.age, qUser.money).from(qDept).join(qUser).on(qDept.id.eq(qUser.deptId))
            .orderBy(qUser.age.desc()).fetch();
    List<Map<String, Object>> resultList = tupleList.stream().map(x -> {
        Map<String, Object> resultMap = new HashMap<>();
        resultMap.put("name", x.get(qUser.name));
        resultMap.put("age", x.get(1, User.class));
        return resultMap;
    }).collect(Collectors.toList());
    String userQueryResultsStr = JSON.toJSONString(resultList);
    System.out.println("getDeptUserListByJoin的结果集:" + userQueryResultsStr);
}

3.4:Querydsl 子查询的使用：

此处 Querydsl 提供了 JPAExpressions 类，我们使用该类做子查询处理。

public void getMaxMoneyUserInfo() {
QUser qUser = QUser.user;
List<User> userList = jpaQueryFactory.selectFrom(qUser)
        .where(qUser.money.eq(JPAExpressions.select(qUser.money.max()).from(qUser))).fetch();
String userQueryResultsStr = JSON.toJSONString(userList);
System.out.println("getMaxMoneyUserInfo的结果集:" + userQueryResultsStr);
}

3.5：Querydsl 多表关联查询返回结果处理：

在上面的多表关联查询我们在 select() 的填充要查询的列名，jpaQueryFactory 处理返回的类型是 Tuple，我们通过流处理在根据需要查询的字段名或者下标获取相应的数据再填充到集合里，有些麻烦了；
此处我们可以通过使用 querydsl 提供的 Projections 类处理，代码如下：定义一个 vo 类，属性与查询的字段名称一致（此处可以采用别名）即可自动装箱到对象里面。

public void getDeptListResultDeal() {
    QDept qDept = QDept.dept;
    QUser qUser = QUser.user;
    List<UserVo> resultList = jpaQueryFactory.select(
            Projections.bean(UserVo.class,
                    qDept.deptNo.concat(":").concat(qDept.deptName).concat(":").concat(qUser.name).as("deptUserName"),
                    qUser.age, qUser.money)).from(qDept).leftJoin(qUser).on(qDept.id.eq(qUser.deptId))
            .orderBy(qUser.age.desc()).fetch();
    String userQueryResultsStr = JSON.toJSONString(resultList);
    System.out.println("getDeptListResultDeal的结果集:" + userQueryResultsStr);
}

3.6：Querydsl 自查询的使用：

此处我们在处理自查询类似写 sql 一样，别名肯定不一样，所以我们在通过 querydsl 的时候创建的创建两个 Q 版的实体类对象，Q 版的实体类提供了有参构造，可以指定别名。

public void getDeptParentInfo() {
    //select t.dept_name,t1.* from dept_tmw t right join dept_tmw t1 on t.id = t1.p_dept_id
    QDept dept1 = new QDept("dept1");
    QDept dept2 = new QDept("dept2");
    StringExpression otherwise = new CaseBuilder().when(dept1.deptName.isNull().or(dept1.deptName.isEmpty()))
            .then("总部")
            .otherwise(dept1.deptName);
    List<Tuple> fetch = jpaQueryFactory.select(otherwise.concat(":").concat(dept2.deptName), dept2.deptNo, dept2.createTime)
            .from(dept1).rightJoin(dept2).on(dept1.id.eq(dept2.pDeptId)).orderBy(dept2.deptNo.desc()).fetch();
    List<Map<String, Object>> collect = fetch.stream().map(x -> {
        Map<String, Object> resultMap = new HashMap<>();
        resultMap.put("name", x.get(0, Dept.class));
        resultMap.put("deptNo", x.get(dept2.deptNo));
        resultMap.put("createTime", x.get(dept2.createTime));
        return resultMap;
    }).collect(Collectors.toList());
    String userQueryResultsStr = JSON.toJSONString(collect);
    System.out.println("getDeptParentInfo的结果集:" + userQueryResultsStr);
}

3.7：Querydsl 查询时间区间范围内的用户：

此处时间传递的值是个时间戳字符串，范围取值把字符串转换为了数值进行的区间查询。

public void getUserListByBetweenCreateTime() {
    QUser qUser = QUser.user;
    List<User> fetch = jpaQueryFactory.selectFrom(qUser).where(qUser.beginTime.between("1567579924276", "1567579954276")).fetch();
    String userQueryResultsStr = JSON.toJSONString(fetch);
    System.out.println("getUserListByBetweenCreateTime的结果集:" + userQueryResultsStr);
}

4. Querydsl 与 spring web 的整合：

此处 spring-data 提供了注解 @QuerydslPredicate 可以将 http 请求的参数转换为 Predicate；

以下代码如下链接通过访问：
http://localhost:8080/user/seach?age=30&money=3000
查询结果就是年龄为 30 并且 money 为 3000 的用户，但具体其他的模糊查询，区间查询还未知。

import com.querydsl.core.types.Predicate;
import com.springboot.demo.bean.User;
import com.springboot.demo.dao.UserDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.querydsl.binding.QuerydslPredicate;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController
@RequestMapping(value = "/user")
public class UserController {
    @Autowired
    private UserDao userDao;
    @RequestMapping(value = "/seach", method = RequestMethod.GET)
    @ResponseBody
    //http://localhost:8080/ar/user/seach?age=30&money=3000
    public Iterable<User> getUsers(
                 @QuerydslPredicate(root = User.class) Predicate predicate) {
        Iterable<User> list = userDao.findAll(predicate);
        return list;
    }
}
import com.springboot.demo.bean.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;
public interface UserDao extends JpaRepository<User, String>, JpaSpecificationExecutor<User>, QuerydslPredicateExecutor<User> {
}

来源 | https://juejin.im/post/6844903935828836360
作者 | tianmengwei