CODEWITHSUNDEEP
springhibernatejpacriteriaspring-data
Tilman Rossmy
Tilman Rossmy

Reputation: 312

translate jpa query to predicate for spring-data specification

I need to translate this jpa query into a predicate (for spring-data specification):

select v.id from VideoImpl  v , in (v.assetSet.publishings) as p where p.channelId='23FFBE1B-65CE-4188-ADD2-C724186C2C9F'

where assetSet is a ManyToOne and publishings a OneToMany relationship

the Hibernate generated sql looks like this:

select videoimpl0_.id as col_0_0_ from Video videoimpl0_ inner join Asset videoimpl0_1_ on videoimpl0_.id=videoimpl0_1_.id inner join AssetSet assetsetim1_ on videoimpl0_1_.assetSetId=assetsetim1_.id left outer join AssetSet_Image assetsetim1_1_ on assetsetim1_.id=assetsetim1_1_.assetSetId inner join Publishing publishing2_ on assetsetim1_.id=publishing2_.assetSetId where publishing2_.channelId='23FFBE1B-65CE-4188-ADD2-C724186C2C9F'

Anybody has a clue?

Upvotes: 0

Views: 1807

Answers (1)

Tilman Rossmy
Tilman Rossmy

Reputation: 312

in the end it was quite easy to do. I concentrated on the generated sql, rather than look at the jpa query with this unfamiliar in(*) multiselect expression. So this works:

private static <T> Predicate appendPublishingChannelIdCondition(Predicate predicate, ExpressionCache<T> cache, CriteriaBuilder cb, CriteriaQuery<?> query, String channelId) {
    Subquery<String> publishingSubquery = query.subquery(String.class);
    Root<PublishingImpl> publishingRoot = publishingSubquery.from(PublishingImpl.class);
    Predicate channelPredicate = cb.equal(publishingRoot.get("channelId"), channelId);
    Subquery<String> assetSetId = publishingSubquery.select(publishingRoot.<String>get("assetSetId")).where(channelPredicate);
    Path attributePath = cache.getAttributePath("assetSet");
    return cb.and(predicate, attributePath.in(assetSetId));
}

Upvotes: 1

Related Questions