Filter Child Collections using Criteria API or JPQl.

package com.jpa;

import java.io.Serializable;
import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;

@Entity
class Department implements Serializable {

private static final long serialVersionUID = 5311019741003844465L;

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;
private String name;

@OneToMany(mappedBy = "department", cascade = CascadeType.ALL, fetch = FetchType.EAGER)
List employee;

public List getEmployee() {
return employee;
}

public void setEmployee(List employee) {
this.employee = employee;
}

public int getId() {
return id;
}

public void setId(int id) {
this.id = id;
}

public String getName() {
return name;
}

public void setName(String deptName) {
name = deptName;
}

@Override
public String toString() {
return "Department [id=" + id + ", name=" + name + "]";
}

}

---------------------------------------------------------------------------------------------------------
package com.jpa;

import java.io.Serializable;
import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
import javax.persistence.Table;

@Entity
@Table(name = "EMP")
public class Employee implements Serializable {
private static final long serialVersionUID = -7525159319448407701L;

@Id
@Column(name = "EMP_ID")
private int id;
private String name;

@OneToMany(mappedBy = "employee", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
Set bank;

@ManyToOne(fetch = FetchType.LAZY, optional = false)
@JoinColumn(name = "DEPT_ID")
Department department;

public Set getBank() {
return bank;
}

public void setBank(Set bank) {
this.bank = bank;
}

public Department getDepartment() {
return department;
}

public void setDepartment(Department department) {
this.department = department;
}

public int getId() {
return id;
}

public void setId(int id) {
this.id = id;
}

public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

@Override
public String toString() {
return "Employee [department=" + department + ", id=" + id + ", name="
+ name + "]";
}

}

--------------------------------------------------------------------------------------------------------
package com.jpa; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.FetchType; import javax.persistence.Id; import javax.persistence.JoinColumn; import javax.persistence.ManyToOne;
import javax.persistence.Table;

@Entity
@Table(name = "BANK")
public class Bank {

@Id
@Column(name = "BIC")
private String bic;
private String name;

@ManyToOne(fetch = FetchType.LAZY, optional = false)
@JoinColumn(name = "EMP_ID")
Employee employee;

public String getBic() {
return bic;
}

public void setBic(String bic) {
this.bic = bic;
}

public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

public Employee getEmployee() {
return employee;
}

public void setEmployee(Employee employee) {
this.employee = employee;
}

@Override
public String toString() {
return "Bank [bic=" + bic + ", employee=" + employee + ", name=" + name
+ "]";
}

}

--------------------------------------------------------------------------------------------------
package com.jpa;

import javax.persistence.metamodel.ListAttribute;
import javax.persistence.metamodel.SingularAttribute;
import javax.persistence.metamodel.StaticMetamodel;

@StaticMetamodel(Department.class)
public class Department_ {

public static volatile ListAttribute employee;
public static volatile SingularAttribute name;
}

---------------------------------------------------------------------------------------------------
package com.jpa;

import javax.persistence.metamodel.SetAttribute;
import javax.persistence.metamodel.SingularAttribute;
import javax.persistence.metamodel.StaticMetamodel;

@StaticMetamodel(Employee.class)
public class Employee_ {
public static volatile SetAttribute bank;
public static volatile SingularAttribute id;
}

--------------------------------------------------------------------------------------------------
package com.jpa;

import javax.persistence.metamodel.SingularAttribute;
import javax.persistence.metamodel.StaticMetamodel;

@StaticMetamodel(Bank.class)
public class Bank_ {
public static volatile SingularAttribute bic;
}

--------------------------------------------------------------------------------------------------
package com.jpa;

import java.util.ArrayList;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.ListJoin;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import javax.persistence.criteria.SetJoin;

public class Main {
static EntityManagerFactory emf = Persistence
.createEntityManagerFactory("JPAService");

static EntityManager em = emf.createEntityManager();

public static void main(String[] a) throws Exception {
callDB();

}

public static void callDB() {
EntityTransaction transaction = em.getTransaction();
transaction.begin();

// Using Criteria
CriteriaBuilder cb = em.getCriteriaBuilder(); // Step 1
CriteriaQuery cqry = cb.createQuery(Department.class); // Step 1

// Interesting stuff happens here
Root department = cqry.from(Department.class); // Step 2
// ListJoin employees =
// department.join(Department_.employee);
ListJoin employees = (ListJoin) department
.fetch(Department_.employee);
SetJoin banks = (SetJoin) employees
.fetch(Employee_.bank);

Predicate pred1 = cb.equal(department.get(Department_.name), "SCIENCE");


Predicate pred2 = cb.equal(employees.get(Employee_.id), 222);
Predicate pred3 = cb.equal(banks.get(Bank_.bic), "NCBHKKXXX");
cqry.where(cb.and(pred1, pred2, pred3));

TypedQuery createQuery = em.createQuery(cqry);
System.out.println(createQuery.getSingleResult().getEmployee().get(0)
.getBank());

//OR //Using JPQl
Query q = em
.createQuery("SELECT d FROM Department d JOIN FETCH d.employee emp JOIN FETCH emp.bank bank WHERE d.name =:deptName AND emp.id IN
(:empId) AND bank.bic IN (:bic)");
q.setParameter("deptName", "SCIENCE");
q.setParameter("empId", 222);
q.setParameter("bic", "NCBHKKXXX");

System.out.println("****************" + q);
Department department = (Department) q.getSingleResult();
System.out.println("****************" + department.getEmployee());
System.out.println("****************"
+ department.getEmployee().get(0).getBank());
System.out.println("****************"
+ department.getEmployee().get(0).getBank().size());
transaction.commit();
em.close();

emf.close();
}
}

-----------------------------------------------------------------------------------------------------
persistence.xml
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence" version="1.0">
<persistence-unit name="JPAService" transaction-type="RESOURCE_LOCAL">
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<class>com.cogni.Employee</class>
<properties>
<property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect"/>
<property name="hibernate.hbm2ddl.auto" value="update"/>
<property name="hibernate.connection.driver_class" value="org.hsqldb.jdbcDriver"/>
<property name="hibernate.connection.username" value="sa"/>
<property name="hibernate.connection.password" value=""/>
<property name="hibernate.show_sql" value="true"/>
<property name="hibernate.connection.url" value="jdbc:hsqldb:file:C:\JPA"/>
</properties>
</persistence-unit>
</persistence>