How to use Plural Attributes in JPA for criteria query?

Question

This is my root entity ArticleType from which I want to generate a query. I want to fetch a collection articleTypeVarianteOptionCollection and add some condition for that collection.

public class ArticleType extends BaseEntity implements Serializable
{
    private static final long                   serialVersionUID    = 1L;
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "art_typ_index")
    private Integer                             artTypIndex;
    @Column(name = "art_typ_code", nullable = false)
    private String                              artTypCode;
    @OneToMany(mappedBy = "atvoIndexArticleType", fetch = FetchType.LAZY)
    private Set<ArticleTypeVarianteOption>      articleTypeVarianteOptionCollection;

    public Integer getArtTypIndex()
    {
        return artTypIndex;
    }

    public void setArtTypIndex(Integer artTypIndex)
    {
        this.artTypIndex = artTypIndex;
    }


    public String getArtTypCode()
    {
        return artTypCode;
    }

    public void setArtTypCode(String artTypCode)
    {
        this.artTypCode = artTypCode;
    }


    @XmlTransient
    public Set<ArticleTypeVarianteOption> getArticleTypeVarianteOptionCollection()
    {
        return articleTypeVarianteOptionCollection;
    }

    public void setArticleTypeVarianteOptionCollection(Set<ArticleTypeVarianteOption> articleTypeVarianteOptionCollection)
    {
        this.articleTypeVarianteOptionCollection = articleTypeVarianteOptionCollection;
    }


}

This is my OptionArticle entity :

public class ArticleTypeOption extends BaseEntity implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id 
      @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Basic(optional = false)
    @Column(name = "ato_index")
    private Integer atoIndex;

    @Column(name = "ato_isremoved")
    private Integer atoIsremoved;
    @JoinColumn(name = "ato_index_art_type", referencedColumnName = "art_typ_index")
    @ManyToOne(fetch = FetchType.LAZY)
    private ArticleType atoIndexArtType;
    @JoinColumn(name = "ato_index_option", referencedColumnName = "opt_art_index")
    @ManyToOne(fetch = FetchType.LAZY)
    private OptionArticle atoIndexOption;

    public ArticleTypeOption() {
    }

    public ArticleTypeOption(Integer atoIndex) {
        this.atoIndex = atoIndex;
    }

    public Integer getAtoIndex() {
        return atoIndex;
    }

    public void setAtoIndex(Integer atoIndex) {
        this.atoIndex = atoIndex;
    }


    public Integer getAtoIsremoved() {
        return atoIsremoved;
    }

    public void setAtoIsremoved(Integer atoIsremoved) {
        this.atoIsremoved = atoIsremoved;
    }

    public ArticleType getAtoIndexArtType() {
        return atoIndexArtType;
    }

    public void setAtoIndexArtType(ArticleType atoIndexArtType) {
        this.atoIndexArtType = atoIndexArtType;
    }

    public OptionArticle getAtoIndexOption() {
        return atoIndexOption;
    }

    public void setAtoIndexOption(OptionArticle atoIndexOption) {
        this.atoIndexOption = atoIndexOption;
    }
}

My query would be :

SELECT 
articleType
FROM ArticleType articleType 
LEFT JOIN articleType.articleTypeVarianteOptionCollection atOption
where atOption.atoIsremoved = 0;

I have tried this for where clause in jpa :-

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<T> criteriaQry = criteriaBuilder.createQuery(entityClass);
Root<T> root = criteriaQry.from(entityClass);
criteriaQry.select(root).distinct(true);

for (PluralAttribute<? super T, ?, ?> pa : root.getModel().getPluralAttributes())
{
    System.out.println(pa.getName());
    System.out.println(pa.getCollectionType());
}

Now how to add where clause using this PluralAttribute ?
Thanks in advance.

Answer 1

You may use something like this:

Fetch artTypeFetch = root.fetch("atoIndexArtType", JoinType.LEFT);
artTypeFetch.fetch("articleTypeVarianteOptionCollection", JoinType.LEFT);

Answer 2

First, let's start with the SQL query:

SELECT 
articleType
FROM ArticleType articleType 
LEFT JOIN articleType.articleTypeVarianteOptionCollection atOption
where atOption.atoIsremoved = 0;

Whenever you use the LEFT JOIN table in the WHERE condition, the JOIN will behave like an INNER JOIN.

So, this is how you translate this SQL query into Criteria:

Integer atoIsremoved = ...;

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<ArticleType> criteria = criteriaBuilder.createQuery(ArticleType.class);

Root<ArticleType> root = criteria.from(ArticleType.class);
criteria.select(root).distinct(true);

Join<ArticleType, ArticleTypeVarianteOption> joinOptions = root.join(
    "articleTypeVarianteOptionCollection", 
    JoinType.LEFT
);

criteria.where(
    criteriaBuilder.or(
        criteriaBuilder.isNull(
           joinOptions.get("id")
        ), 
        criteriaBuilder.equal(
            joinOptions.get("atoIsremoved"), atoIsremoved
        )
    )
);

TypedQuery<ArticleType> query = entityManager.createQuery(criteria);
List<ArticleType> resultList = query.getResultList();

Answer 3

It's possible to achieve what you need using JPQL. The query is similar to the criteria solution but is more readable to me:

SELECT distinct a FROM ArticleType a 
LEFT JOIN FETCH a.articleTypeViarianteOptionCollection atOption   
WHERE atOption is null OR atOption.atoIsremoved=0

Answer 4

To add where clause (condition), I have to use joins as specified below as joinOptions.
And to retrieve data I have to fetch those data as fetch Relation.

    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery<ArticleType> criteria = criteriaBuilder.createQuery(ArticleType.class);
    Root<ArticleType> root = criteria.from(ArticleType.class);
    criteria.select(root).distinct(true);

    Join<ArticleType, ArticleTypeVarianteOption> joinOptions = root.join("articleTypeVarianteOptionCollection");

    if (fetchRelations != null)
    {
        for (String fetchReln : fetchRelations)
        {
            FetchParent<ArticleType, ArticleType> fetch = root;

            for (String reln : fetchReln.split("\\."))
            {
                FetchParent<ArticleType, ArticleType> originalFetch = fetch;
                for (String childReln : reln.split(":"))
                {
                    fetch = originalFetch.fetch(childReln, JoinType.LEFT);
                }
                originalFetch = fetch;
            }
        }
    }

    Predicate[] predArray = new Predicate[2];
    predArray[0] = criteriaBuilder.equal(joinOptions.get("atvoIndexConfig"), configId);
    predArray[1] = criteriaBuilder.equal(joinOptions.get("atvoIndexArticleType"), articleTypeId);

    criteria.where(predArray);

    TypedQuery<ArticleType> typedQry = entityManager.createQuery(criteria);
    ArticleType articleTypeResult;

    try
    {
        articleTypeResult = typedQry.getSingleResult();
    }
    catch (NoResultException ex)
    {
        articleTypeResult = null;
    }
    return articleTypeResult;