fritzthecat-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.

fritzthecat-blog

Blog-Archiv

Sonntag, 12. Januar 2020

JPA Criteria Query