How to get collection of property in Join JPA criteria API

Question

I'm trying to get a list of property policy_type_id from ListAttribute<Article, PolicyType>, but I can't figure out how to do it.

I come up with an inefficient method was select whole Collection of PolicyType then filter it later

        Root<ArticleVersion> a = cq.from(ArticleVersion.class);
        Join<ArticleVersion, Article> join1 = a.join(ArticleVersion_.article, JoinType.INNER);
        cq.where(getCondition(cb, join1));
        cq.multiselect(join1.get(Article_.article_id), join1.get(Article_.policyTypes), a);

Sadly, hibernate generate an error query like this select article1_.article_id as col_0_0_, . as col_1_0_, articlever0_.article_version_id as col_2_0_ . As you can see, there is a . in select that make query broken (which I believe select all)

@Entity
@Table(name = "PolicyType", schema = "SM_Request")
@Getter
@Setter
@NoArgsConstructor
public class PolicyType {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int policy_type_id;

    @ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    @JoinTable(name = "PolicyTypeArticle", schema = "SM_Request", 
    joinColumns = @JoinColumn(name = "policy_type_id"), inverseJoinColumns = @JoinColumn(name = "article_id"))
    @JsonIgnore
    private List<Article> articles;
}

score 0 · Accepted Answer · answered May 15 '21 at 14:35

After long searching, I think that hibernate doesn't support query tuple of primitive types and list of objects (which is kinda sad, compare to LINQ to query). I decided to break down my query into smaller parts. First, I select tuples of article_id and ArticleVersion. After that, I select a list of PolicyType which also contains article_id, and union 2 lists back.

By the time I wrote this, I have an idea that I could select all 3 joins together and transform data the way I want. But It really depend on many aspects, like how many join or which type of join you're using, how fast data in each table grown (JOIN queries vs multiple queries)

How to get collection of property in Join JPA criteria API

1 Answers1