I have three entities with many to many relationship between them.
Packages <--> join table <--> ProtectedItems <--> join table <--> ContentItems.

In ContentItems Table, i have a field called streamId. I want to query all packages that are associated with a particular streamId. Currently the native sql query i am using is -

(SELECT sm.stream_id, p.package_id, p.package_name FROM packages p
    INNER JOIN jt_packages_protected_items jtppi
    ON p.account_id=jtppi.p_account_id AND p.package_id=jtppi.package_id 
        INNER JOIN protected_items pi
        ON jtppi.pi_account_id=pi.account_id AND jtppi.protected_item_id=pi.protected_item_id
            INNER JOIN jt_protected_items_stream_mappings jtpism
            ON pi.account_id=jtpism.pi_account_id AND pi.protected_item_id=jtpism.protected_item_id
                INNER JOIN stream_mappings sm
                ON jtpism.ci_account_id=sm.account_id AND jtpism.content_id_extension=sm.content_id_extension
                WHERE sm.stream_id='4004' AND sm.account_id='0d7af311-fa5b-4fac-b899-075d4e75cc82') LIMIT 10 OFFSET 10 ;

Is it possible to convert the above query in to something more specific to spring data jpa - like specification/criteria query etc which is more dynamic in nature and helps avoid any sql/name mistakes.
If it's possible to use criteria query, what would the code look like - any help is greatly appriciated.

CodePudding user response：

Specification example:

public class PackagesSpecification implements Specification<Packages> {
    private String streamId;

    public PackagesSpecification(String streamId) {
        this.streamId = streamId;
    }

    @Override
    public Predicate toPredicate(Root<Packages> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
        Join<Packages, ProtectedItems> protectedItemsJoin = root.join("protectedItems", JoinType.INNER);
        Join<ProtectedItems, ContentItems> contentItemsJoin = protectedItemsJoin.join("contentItems", JoinType.INNER);

        return criteriaBuilder.equal(contentItemsJoin.get("streamId"), streamId);
    }
}

@Entity
public class Packages {
    @Id
    private Long id;

    private String packageName;

    @ManyToMany
    List<ProtectedItems> protectedItems;
}

@Entity
public class ProtectedItems {
    @Id
    private Long id;

    @ManyToMany
    private List<ContentItems> contentItems;

    @ManyToMany
    private List<Packages> packages;
}

@Entity
public class ContentItems {

    @Id
    private Long id;

    @ManyToMany
    private List<ProtectedItems> protectedItems;

    private String streamId;

}

Generated query:

    select
        packages0_.id as id1_29_,
        packages0_.package_name as package_2_29_ 
    from
        packages packages0_ 
    inner join
        packages_protected_items protectedi1_ 
            on packages0_.id=protectedi1_.packages_id 
    inner join
        protected_items protectedi2_ 
            on protectedi1_.protected_items_id=protectedi2_.id 
    inner join
        protected_items_content_items contentite3_ 
            on protectedi2_.id=contentite3_.protected_items_id 
    inner join
        content_items contentite4_ 
            on contentite3_.content_items_id=contentite4_.id 
    where
        contentite4_.stream_id=?

Criteria example:

        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Packages> query = criteriaBuilder.createQuery(Packages.class);
        Root<Packages> root = query.from(Packages.class);

        Join<Packages, ProtectedItems> protectedItemsJoin = root.join("protectedItems", JoinType.INNER);
        Join<ProtectedItems, ContentItems> contentItemsJoin = protectedItemsJoin.join("contentItems", JoinType.INNER);
        query.select(root).where(criteriaBuilder.equal(contentItemsJoin.get("streamId"), streamId));

        entityManager.createQuery(query).getResultList();

UPDATE
Specification with multiple filter parameters
Specification oriented only for one return entity so you do not have ability to return multiple entities via specification.

public class PackagesSpecification implements Specification<Packages> {
    private String streamId;
    private Long packageId;

    public PackagesSpecification(Long packageId, String streamId) {
        this.streamId = streamId;
        this.packageId = packageId;
    }

    @Override
    public Predicate toPredicate(Root<Packages> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
        Join<Packages, ProtectedItems> protectedItemsJoin = root.join("protectedItems", JoinType.INNER);
        Join<ProtectedItems, ContentItems> contentItemsJoin = protectedItemsJoin.join("contentItems", JoinType.INNER);

        List<Predicate> predicates = new ArrayList<>();

        if (streamId != null) {
            predicates.add(criteriaBuilder.equal(contentItemsJoin.get("streamId"), streamId));
        }

        if (packageId != null) {
            predicates.add(criteriaBuilder.equal(root.get("id"), packageId));
        }

        return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
    }

Criteria with multiple filter parameters and multiple return entities

    public List<Tuple> find(Long packageId, String streamId) {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Tuple> query = criteriaBuilder.createTupleQuery();

        Root<Packages> root = query.from(Packages.class);
        Join<Packages, ProtectedItems> protectedItemsJoin = root.join("protectedItems", JoinType.INNER);
        Join<ProtectedItems, ContentItems> contentItemsJoin = protectedItemsJoin.join("contentItems", JoinType.INNER);

        List<Predicate> predicates = new ArrayList<>();

        if (streamId != null) {
            predicates.add(criteriaBuilder.equal(contentItemsJoin.get("streamId"), streamId));
        }

        if (packageId != null) {
            predicates.add(criteriaBuilder.equal(root.get("id"), packageId));
        }

        query.multiselect(root, contentItemsJoin).where(criteriaBuilder.and(predicates.toArray(new Predicate[0])));

        return entityManager.createQuery(query).getResultList();
    }

Generated query:

    select
        packages0_.id as id1_29_0_,
        contentite4_.id as id1_12_1_,
        packages0_.package_name as package_2_29_0_,
        contentite4_.stream_id as stream_i2_12_1_ 
    from
        packages packages0_ 
    inner join
        packages_protected_items protectedi1_ 
            on packages0_.id=protectedi1_.packages_id 
    inner join
        protected_items protectedi2_ 
            on protectedi1_.protected_items_id=protectedi2_.id 
    inner join
        protected_items_content_items contentite3_ 
            on protectedi2_.id=contentite3_.protected_items_id 
    inner join
        content_items contentite4_ 
            on contentite3_.content_items_id=contentite4_.id 
    where
        contentite4_.stream_id=? 
        and packages0_.id=?