JPA Criteria API join query One To Many with condition

Question

I have 2 Table

1. Enterprise

ID	Name
1	Alex

2. Product

ID	Name	Status	EnterpriseId
7	Iphone12	ACTIVE	1
8	Iphone11	ACTIVE	1
6	Iphone13	DISABLE	1

The relationship is one to many (one Enterprise having many Product). I want to get an Enterprise with all their Product with a condition is Status of this Product is ACTIVE

How can I get the result in json is

  {
"id": "1",
"name": "Alex",
"products": [
  {
    "id": "7",
    "name": "Iphone12",      
  },
  {
    "id": "8",
    "name": "Iphone11",
  }
 ]
}






  

Answer 1

Assuming that your entities model is:

@Entity
class Enterprise {
   ...
   @OneToMany
   List<Product> products;
}

@Entity
class Product {
   ...

   String status;
}

The following criteria should work:

CriteriaQuery<Enterprise> criteria = builder.createQuery(Enterprise.class);
Root<Author> root = criteria.from( Enterprise.class );
Join<Object, Object> productsJoin = root.join( "products" );
criteria.where( builder.equal( productsJoin.get("status"), "ACTIVE" ) );
List<Enterprise> result = session.createCriteria(criteria).getResultList();

It's the same as the HQL query:

from Enterprise e join e.products p
where p.status = 'ACTIVE'

If you want to load the association eagerly you can replace root.join with root.fetch:

CriteriaQuery<Enterprise> criteria = builder.createQuery(Enterprise.class);
Root<Author> root = criteria.from( Enterprise.class );
Join<Object, Object> productsJoin = (Join<Object, Object>)root.fetch( "products" );
criteria.where( builder.equal( productsJoin.get("status"), "ACTIVE" ) );
List<Enterprise> result = session.createCriteria(criteria).getResultList();

Here's the equivalent HQL query:

from Enterprise e join fetch e.products p
where p.status = 'ACTIVE'

You can find more examples in this article or in the Hibernate ORM documentation.