My Software-Developer Blog: JPA Criteria Query

The JPA Criteria API strives to provide all SQL capacities in an object-oriented way. It is the typed alternative to JPA query language, to be used with ORMs like Hibernate and EclipseLink.

This article is about how to build a database-query with the Criteria API. Please refer to my previous articles about JPA to find Java sources that may not be listed here.

Steps

Simple

Here is a very simple query, just selecting all records from database-table Team:

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Team> query = builder.createQuery(Team.class);
query.from(Team.class);
TypedQuery<Team> typedQuery = entityManager.createQuery(query);
return typedQuery.getResultList();

Minimal steps are:

get a CriteriaBuilder from JPA EntityManager
get a CriteriaQuery from CriteriaBuilder
define a Root by calling from() on the CriteriaQuery
wrap a TypedQuery from EntityManager around the CriteriaQuery
fetch the result using one of the TypedQuery methods

Compared to the according JPQL-query

SELECT t from Team t

this is quite elaborate. But it was just the start!-)

Complex

Following query joins three tables, sets up a WHERE-condition using wildcards, and builds explicit return-objects (DTOs).

Using the EntityManager, create a criteria-builder:

EntityManager entityManager = ....;
CriteriaBuilder builder = entityManager.getCriteriaBuilder();

Using the builder, create a criteria-query, targeting a result type (here it is an explicit data-transfer-object, not an entity-type):
```
CriteriaQuery<ViewDto> query = builder.createQuery(ViewDto.class);
```

Using the query, create a from-root, targeting the main entity class, and add all needed joins:

Root<Team> teamRoot = query.from(Team.class);
Join<Team,Responsibility> responsibilityJoin = teamRoot.joinSet("responsibilities");
Join<Responsibility,Person> personJoin = responsibilityJoin.join("person");

Using the builder, create a SELECT clause, containing all needed attributes, or just an entity object (here it is the construction of the DTO)

CompoundSelection<ViewDto> selectionConstructor = builder.construct(
                ViewDto.class, 
                teamRoot, 
                responsibilityJoin.get("name"), 
                personJoin.get("name"));
query.select(selectionConstructor);

Using the builder, create WHERE conditions ("%" is the wildcard for LIKE):

Predicate whereCondition = builder.and(
                builder.like(responsibilityJoin.get("name"), "%"+responsibilityName+"%"),
                builder.like(personJoin.get("name"), "%"+personName+"%"));
query.where(whereCondition);

Using the EntityManager, wrap a typed query around the built criteria-query:
```
TypedQuery<ViewDto> typedQuery = entityManager.createQuery(query);
```
Call a result-method on the typed query, e.g. getResultList():
```
return typedQuery.getResultList();
```

This resembles following untyped JPQL query:

SELECT 
    new fri.jpa.example.TeamDao$ViewDto(t, r.name, p.name) 
FROM Team t 
    JOIN t.responsibilities r 
    JOIN r.person p 
WHERE 
    r.name like :responsibilityName  
    and p.name like :personName

The ViewDto is a static inner class of fri.jpa.example.TeamDao, the '$' replaces the '.' inside the fully qualified class name in SELECT clause. The :responsibilityName and :personName in WHERE clause are named parameter placeholders.

Example Code

DAO

Here is the DAO containing the complex query:

package fri.jpa.example;

import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.Query;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.*;

public class TeamDao
{
    private final EntityManager em;
    
    public TeamDao(EntityManager em) {
        this.em = em;
    }
    
    
    public static class ViewDto
    {
        public final Team team;
        public final String responsibilityName;
        public final String personName;
        
        public ViewDto(Team team, String responsibilityName, String personName) {
            this.team = team;
            this.responsibilityName = responsibilityName;
            this.personName = personName;
        }
    }
     
    public List<ViewDto> findByResponsibilityAndPersonViaWildcard(
            String responsibilityName, 
            String personName)
    {
        final CriteriaBuilder builder = em.getCriteriaBuilder();
        final CriteriaQuery<ViewDto> query = builder.createQuery(ViewDto.class);
        
        final Root<Team> teamRoot = query.from(Team.class);
        final Join<Team,Responsibility> responsibilityJoin = teamRoot.joinSet("responsibilities");
        final Join<Responsibility,Person> personJoin = responsibilityJoin.join("person");
        
        final CompoundSelection<ViewDto> selectionConstructor = builder.construct(
                ViewDto.class, 
                teamRoot, 
                responsibilityJoin.get("name"), 
                personJoin.get("name"));
        query.select(selectionConstructor);
        
        final Predicate whereCondition = builder.and(
                builder.like(responsibilityJoin.get("name"), "%"+responsibilityName+"%"),
                builder.like(personJoin.get("name"), "%"+personName+"%"));
        query.where(whereCondition);
        
        final TypedQuery<ViewDto> typedQuery = em.createQuery(query);
        return typedQuery.getResultList();
    }
}

Unit Test

Following is the unit test for the query.
Please refer to my previous articles about JPA to find out how you can use this super-class to test both Hibernate and EclipseLink.

package fri.jpa.example;

import static org.junit.Assert.*;
import java.util.*;
import java.util.function.Consumer;
import javax.persistence.*;
import org.junit.*;
import fri.jpa.example.TeamDao.ViewDto;

public abstract class JpaTest
{
    private EntityManager em;

    @Test
    public void findTeamsByResponsibilityAndPersonViaWildcard() {
        final Person peter = transactional(em::persist, newPerson("Peter"));
        final Person paul = transactional(em::persist, newPerson("Paul"));
        final Person mary = transactional(em::persist, newPerson("Mary"));
        
        final Team developmentTeam = new Team();
        final Responsibility developer = newResponsibility("Developer", peter);
        developmentTeam.getResponsibilities().add(developer);
        final Responsibility operator = newResponsibility("Operator", paul);
        developmentTeam.getResponsibilities().add(operator);
        final Responsibility devops = newResponsibility("DevOps", mary);
        developmentTeam.getResponsibilities().add(devops);
        transactional(em::persist, developmentTeam);
        
        final Team managerTeam = new Team();
        final Responsibility developmentManager = newResponsibility("DevelopmentManager", mary);
        managerTeam.getResponsibilities().add(developmentManager);
        final Responsibility roomManager = newResponsibility("RoomManager", paul);
        managerTeam.getResponsibilities().add(roomManager);
        transactional(em::persist, managerTeam);
        
        final TeamDao teamDao = new TeamDao(em);
        final List<ViewDto> result = teamDao.findByResponsibilityAndPersonViaWildcard("Dev", "r");
        // "Dev" is in "Developer" and "DevOps", "r" is in "Peter" and "Mary"
        
        assertEquals(3, result.size());
        
        final ViewDto developerView = result.stream()
                .filter(v -> v.responsibilityName.equals(developer.getName()))
                .findFirst()
                .get();
        assertNotNull(developerView);
        assertEquals(developmentTeam, developerView.team);
        assertEquals(peter.getName(), developerView.personName);
        
        final ViewDto devopsView = result.stream()
                .filter(v -> v.responsibilityName.equals(devops.getName()))
                .findFirst()
                .get();
        assertNotNull(devopsView);
        assertEquals(developmentTeam, devopsView.team);
        assertEquals(mary.getName(), devopsView.personName);
        
        final ViewDto managerView = result.stream()
                .filter(v -> v.responsibilityName.equals(developmentManager.getName()))
                .findFirst()
                .get();
        assertNotNull(managerView);
        assertEquals(managerTeam, managerView.team);
        assertEquals(mary.getName(), managerView.personName);
    }

    private Person newPerson(String name) {
        final Person person = new Person();
        person.setName(name);
        return person;
    }

    private Responsibility newResponsibility(String name, Person person) {
        final Responsibility responsibility = new Responsibility();
        responsibility.setName(name);
        responsibility.setPerson(person);
        return responsibility;
    }

    private <P> P transactional(Consumer<P> entityManagerFunction, P parameter) {
        final EntityTransaction transaction = em.getTransaction();
        try {
            transaction.begin();
            entityManagerFunction.accept(parameter);
            transaction.commit();
            return parameter;
        }
        catch (Throwable th)    {
            th.printStackTrace();
            transaction.rollback();
            throw th;
        }
    }

    /** @return the name of the persistence-unit to use for all tests. */
    protected abstract String getPersistenceUnitName();
    
    @Before
    public void setUp() {
        em = Persistence.createEntityManagerFactory(getPersistenceUnitName()).createEntityManager();
    }
    
    @After
    public void tearDown()  {
        for (Team team : findTeams())
            transactional(em::remove, team);
        for (Responsibility responsibility : findResponsibilities())
            transactional(em::remove, responsibility);
        for (Person person : findPersons())
            transactional(em::remove, person);
    }
}

Conclusion

The JPA Criteria API is complex. But it is strongly typed, and that's a big advantage over String queries, even when these are shorter. Mind that there are also CriteriaUpdate and CriteriaDelete classes beside CriteriaQuery, to be used for UPDATE and DELETE statements.

My Software-Developer Blog

Blog-Archiv

Sonntag, 12. Januar 2020

JPA Criteria Query