fritzthecat-blog: JPA Criteria Query Examples with Subqueries

This article is about JPA database actions on an unidirectional many-to-many relation between two entity-types. You will find associated JPQL (Java Persistence Query Language) and Criteria-API queries here. Additionally my article about Criteria API may be helpful.

Entity Base Class

All annotations in subsequent classes are imported from javax.persistence and thus do not refer to any JPA provider (like Hibernate or EclipseLink).

Following is useful as base class for database entities with an required identity-attribute (Id):

@MappedSuperclass
public abstract class BaseEntity
{
    @Id
    private String id = UUID.randomUUID().toString();

    public final String getId() {
        return id;
    }
    
    @Override
    public final boolean equals(Object o) {
        if (this == o)    // performance optimization
            return true;
        
        if (o == null || getClass() != o.getClass()) // exclude aliens
            return false;   // and one-to-one entities with same id
        
        return id.equals(((BaseEntity) o).id); // delegate equality to id
    }
    
    @Override
    public final int hashCode() {
        return id.hashCode();
    }
}

Having an Id value already at construction time solves the problems with entity-objects lost in hash containers (due to broken hashcode/equals contract when delegating to an ID field whose value is null after construction). With this class, we can delegate both hashCode() and equals() to the id field at any time.

Entity Classes

An individual can be member of several collectives, a collective can refer to several individuals.

Both of following entity types represents a database table, while the JPA provider (e.g. Hibernate EntityManager) creates a third table Collective_Individual, implementing the m:n relation between Individual and Collective.

@Entity
public class Individual extends BaseEntity
{
    private String name;
    
    public Individual() {
    }
    public Individual(String name) {
        this.name = name;
    }
    
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    
    @Override
    public String toString() {
        return getName();
    }
}

@Entity
public class Collective extends BaseEntity
{
    private String name;

    @ManyToMany(cascade = CascadeType.ALL)
    private Set<Individual> individuals = new HashSet<>();
    
    public Collective() {
    }
    public Collective(String name) {
        this.name = name;
    }
    
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    
    public Set<Individual> getIndividuals() {
        return individuals;
    }
    public void setIndividuals(Set<Individual> individuals) {
        this.individuals = individuals;
    }
    
    @Override
    public String toString() {
        return getName()+" "+getIndividuals();
    }
}

A collective holds a list of individuals, but individuals do not refer backwards with a list of collectives they are in. It may be illegal to cascade the deletion of a collective to the related individuals. Nevertheless the relation cascades with CascadeType.ALL, which is quite useful to avoid orphaned individuals. When JPA deletes a collective with this cascade-type, it will automatically delete its individuals, but only those that are not referenced by a still existing collective (see according test below).

Test Frame

Here is the test class frame, including test data. Please find the test's base classes (AbstractJpaTest, JpaUtil etc.) in my article about JPA Unit Tests across Providers and Database Products. Such a test can be run on several JPA providers, crossed with several database products. It does not need persistence.xml configuration. It also truncates all database tables after each test.

public class IndividualToCollectiveTest extends AbstractJpaTest
{
    @Override
    protected List<Class<?>> getManagedClasses() {
        return List.of(Collective.class, Individual.class);
    }

    @Override
    protected PersistenceUnitInfo[] getPersistenceUnits(List<Class<?>> managedClasses)    {
        return new PersistenceUnitInfo[] {
            new HibernatePersistenceUnit(managedClasses)
        };
    }

    @Override
    protected Properties[] getDatabasePropertiesSets(String persistenceUnitName)  {
        return new Properties[] {
            new H2Properties(persistenceUnitName)
        };
    }
    
    private Individual leader1 = new Individual("Leader 1");
    private Individual deputy1 = new Individual("Deputy 1");
    private Individual sharedMember = new Individual("Shared Member");
    private Individual member21 = new Individual("Member 2/1");
    private Individual orphan = new Individual("Orphan");
    private Collective collective1 = new Collective("Collective 1");
    private Collective collective2 = new Collective("Collective 2");
    
    private void insertMembers(EntityManager entityManager) {
        collective1.getIndividuals().add(sharedMember);
        
        collective2.getIndividuals().add(sharedMember);
        collective2.getIndividuals().add(member21);
        
        JpaUtil.persistAll(new Object[] { collective1, collective2, orphan }, entityManager);
        
        final List<Collective> collectives = JpaUtil.findAll(Collective.class, entityManager);
        assertEquals(2, collectives.size());
        
        final List<Individual> individuals = JpaUtil.findAll(Individual.class, entityManager);
        assertEquals(3, individuals.size());
    }

    // insert tests and queries here ....

}

The getManagedClasses() implementation on line 4 lists all involved persistence classes, in deletion dependency order. (A collective must be deleted before the individuals it refers to.) This method, together with the methods on line 9 and 16, replaces persistence.xml.

There are not many but sufficient test data. We have two collectives, the first has a member "Shared Member" (individual) that also belongs to the second collective. The second collective has an additional member "Member 2/1" that does not belong to any other collective. There is also an "Orphan" individual that belongs to no collective at all.

Cascading Deletion Test

The following test removes "Collective 2" and asserts that "Shared Member" is still present afterwards, but "Member 2/1" has been deleted by cascading.

    @Test
    public void sharedPersonsShouldBeRetainedOnRemoveCascading() {
        executeForAll(entityManager -> {
            insertMembers(entityManager);
            
            // remove a collective cascading
            JpaUtil.remove(collective2, entityManager);
            
            final List<Collective> collectives = JpaUtil.findAll(Collective.class, entityManager);
            assertEquals(1, collectives.size());
            assertTrue(collectives.contains(collective1));
            
            final List<Individual> individuals = JpaUtil.findAll(Individual.class, entityManager);
            assertEquals(2, individuals.size());
            assertFalse(individuals.contains(member21)); // was just in deleted collective
            assertTrue(individuals.contains(sharedMember)); // still present because in both collectives
            assertTrue(individuals.contains(orphan)); // still present because in no collective
        });
    }

This behavior should happen due to the @ManyToMany(cascade = CascadeType.ALL) annotation on the Set individuals in class Collective.

Query Tests

Orphan Query

The next test calls a query that finds out orphans that are not referred to by any collective.

    @Test
    public void findOrphans() {
        executeForAll(entityManager -> {
            insertMembers(entityManager);
            
            List<Individual> orphans = queryOrphanedIndividuals(entityManager);
            System.err.println(orphans);
            assertEquals(1, orphans.size());
            assertTrue(orphans.contains(orphan));
        });
    }

Here is the query, in both JPQL and Criteria-API form. Mind that the JPA layer generated an artificial table Collective_Individual that has no Java class representation and implements the m:n relation between collective and individual.

        select i 
        from Individual i 
        where  not exists (
            select c.id
            from Collective c 
            inner join Collective_Individual c2i on c.id = c2i.Collective_id 
            inner join Individual i2c on c2i.individuals_id = i2c.id
            where i.id = i2c.id
         )

    private List<Individual> queryOrphanedIndividuals(EntityManager entityManager) {
        final CriteriaBuilder builder = entityManager.getCriteriaBuilder();
        
        final CriteriaQuery<Individual> individualQuery = builder.createQuery(Individual.class);
        final Root<Individual> individualRoot = individualQuery.from(Individual.class);
        
        final Subquery<Collective> collectiveQuery = individualQuery.subquery(Collective.class);
        final Root<Collective> collectiveRoot = collectiveQuery.from(Collective.class);
        collectiveQuery.select(collectiveRoot);
        final Join<Collective,Individual> collectiveJoin = collectiveRoot.join("individuals"); // OneToMany property in Collective
        collectiveQuery.where(builder.equal(individualRoot.get("id"), collectiveJoin.get("id")));
        
        individualQuery.where(builder.not(builder.exists(collectiveQuery)));
        
        return entityManager.createQuery(individualQuery).getResultList();
    }

Criteria queries are not easy to read, but they are fully typed and their logic is reusable. Mind that the property names "individuals" and "id" on lines 10 and 11 should come from source code generated by some generator like the Maven artifact hibernate-jpamodelgen.

Membership Count Query

Following test queries individuals with no collective membership, then with one membership, then with two.

    @Test
    public void findIndividualsWithNumberOfMemberships() {
        executeForAll(entityManager -> {
            insertMembers(entityManager);
            
            List<Individual> orphans = queryIndividualsHavingNumberOfMemberships(entityManager, 0L);
            assertEquals(1, orphans.size());
            assertEquals(orphan, orphans.get(0));
            
            List<Individual> inOneCollective = queryIndividualsHavingNumberOfMemberships(entityManager, 1L);
            assertEquals(1, inOneCollective.size());
            assertTrue(inOneCollective.contains(member21));
            
            List<Individual> inTwoCollectives = queryIndividualsHavingNumberOfMemberships(entityManager, 2L);
            assertEquals(1, inTwoCollectives.size());
            assertTrue(inTwoCollectives.contains(sharedMember));
        });
    }

Following query delivers individuals that are in 0, 1, 2 or ... collectives. The number of memberships is passed as runtime-parameter to the query.

        select i
        from Individual i 
        where (
            select count(c.id) 
            from Collective c 
            inner join Collective_Individual c2i on c.id = c2i.Collective_id 
            inner join Individual i2c on c2i.individual_id = i2c.id 
            where i.id = i2c.id
        ) = :numberOfMemberships

    private List<Individual> queryIndividualsHavingNumberOfMemberships(EntityManager entityManager, Long numberOfMemberships) {
        final CriteriaBuilder builder = entityManager.getCriteriaBuilder();
        
        final CriteriaQuery<Individual> individualQuery = builder.createQuery(Individual.class);
        final Root<Individual> individualRoot = individualQuery.from(Individual.class);
        
        final Subquery<Long> collectiveCountQuery = individualQuery.subquery(Long.class); // is a count query
        final Root collectiveCountRoot = collectiveCountQuery.from(Collective.class);
        collectiveCountQuery.select(builder.count(collectiveCountRoot.get("id")));
        final Join collectiveJoin = collectiveCountRoot.join("individuals"); // ManyToMany property in Collective
        collectiveCountQuery.where(builder.equal(individualRoot.get("id"), collectiveJoin.get("id")));
        
        individualQuery.where(builder.equal(collectiveCountQuery, numberOfMemberships));
        
        return entityManager.createQuery(individualQuery).getResultList();
    }

Both queries are good examples of how to add nested sub-queries via Criteria-API. Mind that you do not need to explictly join the Collective_Individual table in the criteria-join on line 10!

I do not want to discuss every line of code here. Criteria queries are hard to write and hard to read. You need to understand the involved classes, and that Predicates (WHERE clause conditions) always come from the CriteriaBuilder.

Good luck:-)

fritzthecat-blog

Blog-Archiv

Mittwoch, 2. August 2023

JPA Criteria Query Examples with Subqueries