spring jpa查询条件动态拼接+只查询部分字段+多表关联

提问: jpa如何同时实现以下3个功能?

1. 查询条件动态拼接
2. 多表关联查询
3. 自定义返回字段

答: 简单实现JpaRepository接口是行不通的; 需要通过EntityManager构造复杂查询, 实现思路如下

实现类

@RequestMapping("/keyword")
@RestController
public class KeywordController{

    @PersistenceContext
    private EntityManager entityManager;
    
    @RequestMapping("/jpaDynamic")
    public Object jpaDynamic(String keyword, String className, String priority){

        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

        // 指定结果视图
        CriteriaQuery<KeywordVO> criteriaQuery = criteriaBuilder.createQuery(KeywordVO.class);

        // 查询基础表
        Root<KeywordDO> root = criteriaQuery.from(KeywordDO.class);

        // 关联查询, 需在主体内有对应@JoinColum属性
        Join<Object, Object> joinKeywordClass = root.join("keywordClass", JoinType.LEFT);

        // 查询条件动态拼接
        Predicate predicate = criteriaBuilder.conjunction();
        List<Expression<Boolean>> expressions = predicate.getExpressions();
        if (ObjectUtil.isNotNull(keyword)) {
            expressions.add(criteriaBuilder.or(
                    criteriaBuilder.like(root.get("nameOne"), "%" + keyword + "%"),
                    criteriaBuilder.like(root.get("nameTwo"), "%" + keyword + "%")
            ));
        }
        if (ObjectUtil.isNotNull(className)) {
            expressions.add(criteriaBuilder.like(joinKeywordClass.get("name"), "%" + className + "%"));
        }
        if (ObjectUtil.isNotNull(priority)) {
         expressions.add(criteriaBuilder.in(joinKeywordClass.get("priority")).value(StringUtil.splitToIntList(priority)));
        }

        // 选择返回字段, 需要在VO类添加相应的构造函数
        criteriaQuery.select(criteriaBuilder.construct(KeywordVO.class,
                root.get("nameOne").alias("nameOne"),
                root.get("nameTwo").alias("nameTwo"),
                joinKeywordClass.get("name").alias("className"),
                joinKeywordClass.get("priority").alias("classPriority")
        )).where(predicate);

        // 获取结果集,也可以设置分页查询,对应关键字 limit ?, ?
        return entityManager.createQuery(criteriaQuery).setFirstResult(1).setMaxResults(10).getResultList();
//        return entityManager.createQuery(criteriaQuery).getResultList();
    }
}

VO类, 需要添加相应的构造函数

/**
 * 关键词
 */
public class KeywordVO {
    /** 关键词类型id */
    private Long classId;
    /** 关键词1 */
    private String nameOne;
    /** 关键词2 */
    private String nameTwo;
    /** 使用类型 */
    private String useType;
    private KeywordClassDO keywordClass;
    private String className;
    private Integer classPriority;

    public KeywordVO() {
    }
    
    /**
    * 前面自定义查询返回的字段, 需要按顺序生成构造函数
    */
    public KeywordVO(String nameOne, String nameTwo, String className, Integer classPriority) {
        this.nameOne = nameOne;
        this.nameTwo = nameTwo;
        this.className = className;
        this.classPriority = classPriority;
    }
    ...
}

实体类

/**
 * 关键词类型实体
 */
@Entity
@Table(name = "keyword_class")
public class KeywordClassDO extends RecordEntity {
    /** 名称 */
    @Length(max = 256, message = "0-256个任意字符")
    @Column(name = "name")
    private String name;
    /** 类型优先级, 1优先级最高, 5最小  1，2，3，4，5 */
    @Column(name = "priority")
    private Integer priority;
    ...
}

/**
 * 关键词实体
 */
@Entity
@Table(name = "keyword")
public class KeywordDO extends RecordEntity {
    /** 关键词类型id */
    @Column(name = "class_id")
    private Long classId;
    /** 关键词1 */
    @Length(max = 256, message = "0-256个任意字符")
    @Column(name = "name_one")
    private String nameOne;
    /** 关键词2 */
    @Length(max = 256, message = "0-256个任意字符")
    @Column(name = "name_two")
    private String nameTwo;
    /** 使用类型 */
    @Length(max = 128, message = "0-128个任意字符")
    @Column(name = "use_type")
    private String useType;

    @ManyToOne
    @JoinColumn(name = "class_id", insertable = false, updatable = false)
    private KeywordClassDO keywordClass;
    ...
}