Spring Data JPA双向关联分页及空关联处理（Hibernate 5）

1. 高效分页获取含空Label的LabelSupplier #

核心是用LEFT JOIN关联Label（避免INNER JOIN过滤掉label为空的记录），结合Spring Data分页能力实现：

方式1：Criteria API 实现 #

@Repository
public class LabelSupplierCustomRepositoryImpl implements LabelSupplierCustomRepository {

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public Page<LabelSupplier> findAllWithNullLabel(Pageable pageable) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        // 主查询：获取LabelSupplier
        CriteriaQuery<LabelSupplier> cq = cb.createQuery(LabelSupplier.class);
        Root<LabelSupplier> root = cq.from(LabelSupplier.class);
        // LEFT JOIN关联Label，确保保留label为空的记录
        root.join("label", JoinType.LEFT);

        cq.select(root);
        TypedQuery<LabelSupplier> query = entityManager.createQuery(cq);
        // 设置分页参数
        query.setFirstResult((int) pageable.getOffset());
        query.setMaxResults(pageable.getPageSize());

        // 统计总记录数
        CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
        countQuery.select(cb.count(countQuery.from(LabelSupplier.class)));
        Long total = entityManager.createQuery(countQuery).getSingleResult();

        return new PageImpl<>(query.getResultList(), pageable, total);
    }
}

方式2：Spring Data Specification 简化实现 #

// 定义查询规范
public class LabelSupplierSpecs {
    public static Specification<LabelSupplier> includeNullLabel() {
        return (root, query, cb) -> {
            root.join("label", JoinType.LEFT);
            return cb.conjunction(); // 无额外过滤条件
        };
    }
}

// Repository接口继承JpaSpecificationExecutor
public interface LabelSupplierRepository extends JpaRepository<LabelSupplier, Long>, JpaSpecificationExecutor<LabelSupplier> {}

// 调用示例
Page<LabelSupplier> resultPage = labelSupplierRepository.findAll(LabelSupplierSpecs.includeNullLabel(), PageRequest.of(0, 10));

2. 整合无供应商的Label到分页结果 #

由于Hibernate 5不支持RIGHT JOIN，采用UNION ALL合并两个查询结果（LabelSupplier集合 + 无供应商的Label集合），通过DTO统一返回格式：

步骤1：定义统一返回DTO #

public class LabelSupplierWithLabelDTO {
    private Long supplierId;
    private String supplierName;
    private Long labelId;
    private String labelName;

    // 从LabelSupplier转换
    public LabelSupplierWithLabelDTO(LabelSupplier supplier) {
        if (supplier != null) {
            this.supplierId = supplier.getId();
            this.supplierName = supplier.getSupplierName();
        }
        Label label = supplier.getLabel();
        if (label != null) {
            this.labelId = label.getId();
            this.labelName = label.getName();
        }
    }

    // 从无供应商的Label转换
    public LabelSupplierWithLabelDTO(Label label) {
        this.labelId = label.getId();
        this.labelName = label.getName();
        // 供应商字段设为null
        this.supplierId = null;
        this.supplierName = null;
    }

    // Getters & Setters
}

步骤2：JPQL UNION ALL 实现分页查询 #

@Repository
public class CombinedResultRepositoryImpl implements CombinedResultRepository {

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public Page<LabelSupplierWithLabelDTO> getCombinedResults(Pageable pageable) {
        // 子查询1：所有LabelSupplier（含空Label）
        String supplierQuery = "SELECT new com.yourpackage.dto.LabelSupplierWithLabelDTO(s) FROM LabelSupplier s LEFT JOIN s.label l";
        // 子查询2：无供应商的Label
        String labelQuery = "SELECT new com.yourpackage.dto.LabelSupplierWithLabelDTO(l) FROM Label l WHERE l.suppliers IS EMPTY";
        // 合并查询
        String combinedJpql = supplierQuery + " UNION ALL " + labelQuery;

        TypedQuery<LabelSupplierWithLabelDTO> query = entityManager.createQuery(combinedJpql, LabelSupplierWithLabelDTO.class);
        query.setFirstResult((int) pageable.getOffset());
        query.setMaxResults(pageable.getPageSize());

        // 统计总记录数
        Long supplierCount = entityManager.createQuery("SELECT COUNT(s) FROM LabelSupplier s", Long.class).getSingleResult();
        Long labelCount = entityManager.createQuery("SELECT COUNT(l) FROM Label l WHERE l.suppliers IS EMPTY", Long.class).getSingleResult();
        Long total = supplierCount + labelCount;

        return new PageImpl<>(query.getResultList(), pageable, total);
    }
}

3. Spring Data JPA 优雅实践建议 #

• Repository层：
  • 优先使用JpaSpecificationExecutor处理动态查询，减少硬编码JPQL
  • 复杂跨实体查询用@Query注解编写JPQL/原生SQL，配合DTO投影避免多余字段查询
• 服务层：
  • 封装分页逻辑与DTO转换，禁止直接返回实体类给前端
  • 单独处理总记录数计算，避免重复查询
• DTO转换：
  • 使用MapStruct自动生成转换代码，替代手动编写构造方法/setter，减少冗余代码：

    @Mapper(componentModel = "spring")
    public interface LabelSupplierMapper {
        LabelSupplierMapper INSTANCE = Mappers.getMapper(LabelSupplierMapper.class);
    
        LabelSupplierWithLabelDTO toDto(LabelSupplier supplier);
    
        @Mapping(target = "supplierId", ignore = true)
        @Mapping(target = "supplierName", ignore = true)
        LabelSupplierWithLabelDTO toDto(Label label);
    }
    

• 性能优化：
  • 给关联字段（如LabelSupplier.label_id）添加索引，提升JOIN查询速度
  • 大数据量下，优先使用数据库原生分页，避免内存分页

内容的提问来源于stack exchange，提问作者Alex