基于ContentItems的streamId查询Packages：Spring Data JPA实现

完全可以通过Spring Data JPA的Criteria Query实现你的需求，既能避免原生SQL的语法、命名错误，还能支持动态条件扩展。以下是具体实现步骤和代码示例：

1. 确保实体类关联映射正确 #

首先需要在实体类中配置好多对多的关联关系，对应你数据库中的中间表：

Packages 实体（对应packages表） #

@Entity
@Table(name = "packages")
public class Packages {
    @Id
    @Column(name = "package_id")
    private String packageId;

    @Column(name = "package_name")
    private String packageName;

    @Column(name = "account_id")
    private String accountId;

    // 与ProtectedItems的多对多关联
    @ManyToMany
    @JoinTable(
        name = "jt_packages_protected_items",
        joinColumns = {
            @JoinColumn(name = "p_account_id", referencedColumnName = "account_id"),
            @JoinColumn(name = "package_id", referencedColumnName = "package_id")
        },
        inverseJoinColumns = {
            @JoinColumn(name = "pi_account_id", referencedColumnName = "account_id"),
            @JoinColumn(name = "protected_item_id", referencedColumnName = "protected_item_id")
        }
    )
    private Set<ProtectedItems> protectedItems;

    // getter、setter省略
}

ProtectedItems 实体（对应protected_items表） #

@Entity
@Table(name = "protected_items")
public class ProtectedItems {
    @Id
    @Column(name = "protected_item_id")
    private String protectedItemId;

    @Column(name = "account_id")
    private String accountId;

    // 与ContentItems的多对多关联
    @ManyToMany
    @JoinTable(
        name = "jt_protected_items_stream_mappings",
        joinColumns = {
            @JoinColumn(name = "pi_account_id", referencedColumnName = "account_id"),
            @JoinColumn(name = "protected_item_id", referencedColumnName = "protected_item_id")
        },
        inverseJoinColumns = {
            @JoinColumn(name = "ci_account_id", referencedColumnName = "account_id"),
            @JoinColumn(name = "content_id_extension", referencedColumnName = "content_id_extension")
        }
    )
    private Set<ContentItems> contentItems;

    // getter、setter省略
}

ContentItems 实体（对应stream_mappings表） #

@Entity
@Table(name = "stream_mappings")
public class ContentItems {
    @Id
    @Column(name = "content_id_extension")
    private String contentIdExtension;

    @Column(name = "stream_id")
    private String streamId;

    @Column(name = "account_id")
    private String accountId;

    // getter、setter省略
}

2. Criteria Query实现查询 #

定义结果DTO #

如果需要返回原SQL指定的字段（stream_id、package_id、package_name），可以创建一个DTO来接收结果：

public class PackageStreamDTO {
    private String streamId;
    private String packageId;
    private String packageName;

    // 注意：构造方法参数顺序要和查询选择的字段顺序一致
    public PackageStreamDTO(String streamId, String packageId, String packageName) {
        this.streamId = streamId;
        this.packageId = packageId;
        this.packageName = packageName;
    }

    // getter、setter省略
}

编写查询逻辑 #

通过EntityManager创建Criteria Query，实现分页和条件过滤：

@Repository
public class PackageCustomRepositoryImpl {

    @PersistenceContext
    private EntityManager entityManager;

    public List<PackageStreamDTO> findPackagesByStreamId(String streamId, String accountId, int page, int size) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<PackageStreamDTO> query = cb.createQuery(PackageStreamDTO.class);

        // 从Packages实体作为查询根
        Root<Packages> packageRoot = query.from(Packages.class);
        // 关联ProtectedItems（内连接）
        Join<Packages, ProtectedItems> protectedItemsJoin = packageRoot.join("protectedItems", JoinType.INNER);
        // 关联ContentItems（内连接）
        Join<ProtectedItems, ContentItems> contentItemsJoin = protectedItemsJoin.join("contentItems", JoinType.INNER);

        // 构建查询条件
        Predicate streamIdPredicate = cb.equal(contentItemsJoin.get("streamId"), streamId);
        Predicate accountIdPredicate = cb.equal(contentItemsJoin.get("accountId"), accountId);
        Predicate finalCondition = cb.and(streamIdPredicate, accountIdPredicate);

        // 选择需要的字段，映射到DTO构造方法
        query.select(cb.construct(
                PackageStreamDTO.class,
                contentItemsJoin.get("streamId"),
                packageRoot.get("packageId"),
                packageRoot.get("packageName")
        )).where(finalCondition);

        // 设置分页参数（OFFSET = page * size，LIMIT = size）
        TypedQuery<PackageStreamDTO> typedQuery = entityManager.createQuery(query);
        typedQuery.setFirstResult(page * size);
        typedQuery.setMaxResults(size);

        return typedQuery.getResultList();
    }
}

3. 基于Specification的动态查询扩展 #

如果需要支持更灵活的动态条件，可以使用Specification：

编写Specification类 #

public class PackageSpecifications {
    public static Specification<Packages> withStreamIdAndAccountId(String streamId, String accountId) {
        return (root, query, cb) -> {
            Join<Packages, ProtectedItems> piJoin = root.join("protectedItems", JoinType.INNER);
            Join<ProtectedItems, ContentItems> ciJoin = piJoin.join("contentItems", JoinType.INNER);
            return cb.and(
                    cb.equal(ciJoin.get("streamId"), streamId),
                    cb.equal(ciJoin.get("accountId"), accountId)
            );
        };
    }
}

扩展Repository接口 #

让Repository继承JpaSpecificationExecutor：

public interface PackageRepository extends JpaRepository<Packages, String>, JpaSpecificationExecutor<Packages> {
}

调用查询 #

// 分页参数：page从0开始，对应原SQL的OFFSET 10（page=1，size=10）
Pageable pageable = PageRequest.of(1, 10);
Specification<Packages> spec = PackageSpecifications.withStreamIdAndAccountId("4004", "0d7af311-fa5b-4fac-b899-075d4e75cc82");
Page<Packages> resultPage = packageRepository.findAll(spec, pageable);

这种方式完全基于实体映射生成SQL，无需手写复杂的关联语句，还能轻松添加更多过滤条件，避免了原生SQL的各类错误。

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