SpringBoot使用JPA多表关联动态查询指定字段

目标需求

在SpringBoot中用JPA实现多表关联动态查询，并且只查询指定字段和返回指定字段
业务对应数据表(关联关系已在图中标出)

某API需要返回的字段

分析：需要进行三表关联一次性查出，JPA转换SQL语句时就限制查询指定字段

Maven依赖

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

项目结构

注：按本文写法查询可不需要repository，repository只用来进行save、update、delete操作

代码

application.properties

#对应数据库为Mysql8
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://192.168.184.133:3306/jpademo
spring.datasource.username=test
spring.datasource.password=test
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

实体类及其注解由IDEA自动生成，生成方法见这里，对于生成后的实体类我们仅需加上关联表的相关代码即可。
StorageFile.java（关联了其它2个表）

@Entity
@Table(name = "storage_file")
public class StorageFile {
    private String fileId;
    private String fileName;
    private Integer fileStatus;
    private Date fileUpdateTime;
    private String dirId;
    private String pathId;
    private StorageDir storageDir; //关联表的实体类 
    private StoragePath storagePath; //关联表的实体类

    @ManyToOne // 定义表之间的记录关系，这里为多对一
    // JoinColumn定义关联字段，name为数据库里的字段名称
    // insertable和updatable设成false防止重复映射问题
    @JoinColumn(name = "dir_id", insertable = false, updatable = false)
    public StorageDir getStorageDir() {
        return storageDir;
    }

    public void setStorageDir(StorageDir storageDir) {
        this.storageDir = storageDir;
    }

    @ManyToOne
    @JoinColumn(name = "path_id", insertable = false, updatable = false)
    public StoragePath getStoragePath() {
        return storagePath;
    }

    public void setStoragePath(StoragePath storagePath) {
        this.storagePath = storagePath;
    }

    @Id
    @Column(name = "file_id")
    public String getFileId() {
        return fileId;
    }

    public void setFileId(String fileId) {
        this.fileId = fileId;
    }

    @Basic
    @Column(name = "file_name")
    public String getFileName() {
        return fileName;
    }

    public void setFileName(String fileName) {
        this.fileName = fileName;
    }

    @Basic
    @Column(name = "file_status")
    public Integer getFileStatus() {
        return fileStatus;
    }

    public void setFileStatus(Integer fileStatus) {
        this.fileStatus = fileStatus;
    }

    @Basic
    @Column(name = "file_update_time")
    public Date getFileUpdateTime() {
        return fileUpdateTime;
    }

    public void setFileUpdateTime(Date fileUpdateTime) {
        this.fileUpdateTime = fileUpdateTime;
    }

    @Basic
    @Column(name = "dir_id")
    public String getDirId() {
        return dirId;
    }

    public void setDirId(String dirId) {
        this.dirId = dirId;
    }

    @Basic
    @Column(name = "path_id")
    public String getPathId() {
        return pathId;
    }

    public void setPathId(String pathId) {
        this.pathId = pathId;
    }
}

StFileVo.java（封装查询返回的字段）

public class StFileVo {
    private String fileName;
    private Date fileUpdateTime;
    private String dirName;
    private Integer dirDeep;
    private Integer pathNode;
    private String pathValue;

    // 需要什么字段，就定义什么样的构造函数，一定要有构造函数
    public StFileVo(String fileName, Date fileUpdateTime, String dirName, Integer dirDeep, Integer pathNode, String pathValue) {
        this.fileName = fileName;
        this.fileUpdateTime = fileUpdateTime;
        this.dirName = dirName;
        this.dirDeep = dirDeep;
        this.pathNode = pathNode;
        this.pathValue = pathValue;
    }
    // Getter ... 
    // Setter ...
}

PageCount.java（封装查询返回的分页信息和数据）

public class PageCount<T> {
    private int page; //当前页
    private int size; //记录数
    private long total; //总记录数
    private List<T> data; //数据

    public PageCount(int page, int size) {
        this.page = page;
        this.size = size;
    }
    
    //获取当前页首条记录下标
    public int first() {
        return (page - 1) * size;
    }

    //获取当前页末条记录下标
    public int max() {
        return page * size;
    }

    //构建select count语句
    public CriteriaQuery<Long> count(CriteriaBuilder criteriaBuilder, Predicate predicate, Class<?> clazz, LinkedHashMap<String,JoinType> joinMap){
        CriteriaQuery<Long> criteriaQuery = criteriaBuilder.createQuery(Long.class);
        Root<?> root = criteriaQuery.from(clazz);
        joinMap.forEach(root::join);
        return criteriaQuery.select(criteriaBuilder.count(root)).where(predicate);
    }
    // Getter ... 
    // Setter ...
}

StorageService.java（JPA动态查询）

@Service
public class StorageService {
    @PersistenceContext
    private EntityManager entityManager;

    public PageCount<StFileVo> findStFileVo(PageCount<StFileVo> pageCount, String dirName, Date fileUpdateTime) {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        // criteriaBuilder.createQuery指定了字段的结果视图
        CriteriaQuery<StFileVo> criteriaQuery = criteriaBuilder.createQuery(StFileVo.class);
        // createQuery.from拥有关联信息的实体类(基础表)
        Root<StorageFile> root = criteriaQuery.from(StorageFile.class);
        // join关联的实体类，方式为inner join
        Join<Object, Object> join = root.join("storageDir", JoinType.INNER);
        Join<Object, Object> join1 = root.join("storagePath", JoinType.INNER);
        Predicate predicate = criteriaBuilder.conjunction();
        List<Expression<Boolean>> expressions = predicate.getExpressions();
        // 设定动态查询条件
        if (fileUpdateTime != null) {
            expressions.add(criteriaBuilder.equal(root.get("fileUpdateTime"), fileUpdateTime));
        }
        if (StringUtils.hasText(dirName)) {
            expressions.add(criteriaBuilder.equal(join.get("dirName"), dirName));
        }
        // select ... from ... where ...
        criteriaQuery.select(criteriaBuilder.construct(StFileVo.class,
                root.get("fileName").alias("fileName"), root.get("fileUpdateTime").alias("fileUpdateTime"),
                join.get("dirName").alias("dirName"), join.get("dirDeep").alias("dirDeep"),
                join1.get("pathNode").alias("pathNode"), join1.get("pathValue").alias("pathValue")))
                .where(predicate);
        // 传入分页参数，查询出需要的记录
        List<StFileVo> stFileVos = entityManager.createQuery(criteriaQuery).setFirstResult(pageCount.first()).setMaxResults(pageCount.max()).getResultList();
        pageCount.setData(stFileVos);
        // 构建joinMap，key和value与root.join的一致
        LinkedHashMap<String,JoinType> joinMap = new LinkedHashMap<String,JoinType>(){{
            put("storageDir",JoinType.INNER);
            put("storagePath",JoinType.INNER);
        }};
        // select count
        Long total = entityManager.createQuery(pageCount.count(criteriaBuilder,predicate,StorageFile.class,joinMap)).getSingleResult();
        pageCount.setTotal(total);
        return pageCount;
    }
}

StorageController.java（请求与响应）

@RestController
@RequestMapping("/test")
public class StorageController {
    private StorageService storageService;
    @Autowired
    public void setStorageService(StorageService storageService) {
        this.storageService = storageService;
    }

    @RequestMapping("/test")
    public PageCount<StFileVo> test(){
        return storageService.findStFileVo(new PageCount<>(1,5),"测试目录",null);
    }
}

运行结果

↑↑↑控制台打印的SQL语句，可以看到select时只查询了API需要的字段

↓↓↓API响应的结果

源码下载

https://github.com/B1ackReimu/jpademo