JPA Criteria API - Accessing fields of One-to-Many collection association

Question

How could I write the following JPQL query using Criteria API?

" select a from A a left join fetch a.bs b where b.i like 'X%' "

A-to-B is a One-To-Many relationship where A and B is like following:

@Entity
public class A {

    @OneToMany(cascade={CascadeType.PERSIST})
    private Set<B> bs = new HashSet<B>();   

    //...
}

@Entity
public class B {

    private String i;
    
    //...
}

I tried the foolowing:

CriteriaBuilder cb = em.getCriteriaBuilder();           
CriteriaQuery<A> cq = cb.createQuery( A.class );
Root<A> aRoot = cq.from( A.class );
Fetch<A, B> bs = aRoot.fetch(A_.bs, JoinType.LEFT);
cq.where(cb.like(what_do_i_do_here, "X%"));
cq.select(aRoot);

I need to get all those As along with its associated Bs where the i value of the associated Bs start with an "X".

EDIT:

If I try the method given at How to properly express JPQL "join fetch" with "where" clause as JPA 2 CriteriaQuery? I incorrectly get 2 As in result while I should get just 1 A whose associated B_.i has the value that starts with "X".

I got the issue resolved using the following code:

CriteriaBuilder cb = em.getCriteriaBuilder();           
CriteriaQuery<A> cq = builder.createQuery( A.class );
Root<A> root = cq.from( A.class );
Join<A, B> bs = (Join<A, B>) root.fetch(Guide_.students, JoinType.LEFT);
cq.where(builder.like(bs.get(B_.i), builder.parameter(String.class, "i")));
cq.select(root);
            
TypedQuery<A> query = em.createQuery(criteria).setParameter("i", "X%");         
List<A> as= query.getResultList();          
for (A a: as) {
  System.out.println(a);
}

Answer 1

You can use Join<ParentTable,ChildTable> to get and filter child entities. Try below code

CriteriaBuilder cb = em.getCriteriaBuilder();           
CriteriaQuery<A> cq = cb.createQuery( A.class );
Root<A> aRoot = cq.from( A.class );
Join<A, B> bs = root.join(A_.B);
cq.where(cb.like(bs.get(B_.i), "X%"));
cq.select(aRoot);

Note - I didn't get a chance to test this code. You can refer to working code which I implemented for similar problem here https://github.com/bsridharpatnaik/SpecificationTest

Edit - Providing Detailed Answer

I have made new push to the repo. Please take latest pull.

I created exact same example that you mentioned.

Entity classes

public class A 
{
    
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    Long aid;

    @OneToMany(cascade={CascadeType.PERSIST})
    private Set<B> bs = new HashSet<B>();
// get & set
}

public class B 
{

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    Long bid;
    
    String i;
//get & set
}

Now, as per your query, you want to filter all records from class A where corresponding B.i starts with x.

Below is service class. to make it easy to understand, I wrote criteroa query logic in separate class

@Service
public class Service1 
{
    @Autowired
    aRepo repo;
    
    @Autowired
    ModelSpecification modelSpecification;

    @Autowired
    EntityManager entityManager;
    public List<?> getResults() throws ParseException 
    {
        //ModelSpecification modelSpecification = new ModelSpecification();
        CriteriaQuery<A> query = modelSpecification.getSpecQuery();
        TypedQuery<A> typedQuery = entityManager.createQuery(query);
        List<A> resultList = typedQuery.getResultList();
        return resultList;
    }
}

Below is the criteria query logic

@Component
public class ModelSpecification 
{
    @Autowired
    EntityManager entityManager;
    
    public CriteriaQuery<A> getSpecQuery() throws ParseException
    {
        CriteriaBuilder builder = entityManager.getCriteriaBuilder();
        CriteriaQuery<A> query = builder.createQuery(A.class);
        Root<A> root = query.from(A.class);
        Join<A,B>   ab = root.join(A_.BS);
        query.where(builder.like(ab.get(B_.I),"x"+"%"));
        return query;
    }
}

I am filtering all A records where B_.I starts with x.

Output Class A has below entries for aid - 1,2 Class B has below entries for

join table a_bs has below entries

Now, if I hit API, I should get only A record with aid =1 as only it is associated with B.i starting with x.