fritzthecat-blog: JPA Join Types with Criteria API

This article is about database table JOINs with the JPA Criteria API.

Join Keywords

INNER

SELF

OUTER
- LEFT
- RIGHT
- FULL
CROSS

OUTER is not a join-type, it is a category. It means that more records than just the related ones will be in the query's result set. All joins except INNER and SELF implicitly are outer joins: LEFT, RIGHT, FULL. The words "left" and "right" refer to the side of the JOIN keyword where the outer-joined table is.

In the following I will show just INNER, LEFT, RIGHT and FULL joins. When I tried them out with the JPA Criteria API, I discovered that just INNER and LEFT are supported!

Example

To illustrate the different join keywords, I use a minimal set of data, consisting of cities and houses. A city contains zero-to-many houses, a house can belong to a city. Thus they do not depend on each other, but are in some way hierarchical.

CITY

ID	NAME
1	Washington
2	Aleppo

HOUSE

ID	NAME	CITY_ID
3	Kremlin	(NULL)
4	Pentagon	1

Following are the different SQL joins and their result lists.

INNER

SELECT
  c.NAME, h.NAME 
FROM
  CITY c 
JOIN
  HOUSE h on c.id = h.CITY_ID

CITY.NAME	HOUSE.NAME
Washington	Pentagon

We could also write "INNER JOIN". This is the default join.
There is just one result row, because there is just one city connected to a house.

LEFT

SELECT
  c.NAME, h.NAME 
FROM
  CITY c 
LEFT JOIN
  HOUSE h on c.id = h.CITY_ID

CITY.NAME	HOUSE.NAME
Washington	Pentagon
Aleppo	(NULL)

We could also write "LEFT OUTER JOIN".
There are two result rows, all cities with or without a house. The outer joined table is City, being left of the JOIN keyword.

RIGHT

SELECT
  c.NAME, h.NAME 
FROM
  CITY c 
RIGHT JOIN
  HOUSE h on c.id = h.CITY_ID

CITY.NAME	HOUSE.NAME
Washington	Pentagon
(NULL)	Kremlin

We could also write "RIGHT OUTER JOIN".
There are two result rows, all houses with or without a city. The outer joined table is House, being right of the JOIN keyword.

This also could be written as LEFT JOIN when swapping the tables in FROM and JOIN clauses, thus the RIGHT JOIN is rarely used in real world.

SELECT
  c.NAME, h.NAME 
FROM
  HOUSE h 
LEFT JOIN
  CITY c on c.id = h.CITY_ID

FULL

SELECT
  c.NAME, h.NAME 
FROM
  CITY c 
FULL JOIN
  HOUSE h on c.id = h.CITY_ID

CITY.NAME	HOUSE.NAME
Washington	Pentagon
Aleppo	(NULL)
(NULL)	Kremlin

We could also write "FULL OUTER JOIN".
This combines LEFT and RIGHT joins, thus there are three result rows, all cities with or without a house, and all houses with or without a city. Mind that this is not a cartesian product of both tables!

Java Source Code

Entities

Here come the Java persistence classes used by following unit-test.

City.java

import java.util.*;
import javax.persistence.*;
import fri.jpa.util.BacklinkSettingSet;

@Entity
public class City
{
    @Id @GeneratedValue
    private Long id;
    
    @Column(unique = true)
    private String name;
    
    /** The houses of this city. */
    @OneToMany(mappedBy = "city", cascade = CascadeType.ALL, orphanRemoval = true)
    private Set<House> houses = new HashSet<>();
    
    public Object getId() {
        return id;
    }
    
    public Set<House> getHouses() {
        return new BacklinkSettingSet<House,City>(
                houses,
                this,
                (element, owner) -> element.setCity(owner));
    }
    
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
}

House.java

import javax.persistence.*;

@Entity
public class House
{
    @Id @GeneratedValue
    private Long id;
    
    @Column(unique = true)
    private String name;
    
    /** The city this house is located at. */
    @ManyToOne
    private City city;
    
    public Object getId() {
        return id;
    }
    
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    
    public City getCity() {
        return city;
    }
    void setCity(City city) {
        this.city = city;
    }
}

BacklinkSettingSet.java

import java.util.Set;
import java.util.function.BiConsumer;

/** {@inheritDoc} */
public class BacklinkSettingSet<ELEMENT,OWNER> 
    extends BacklinkSettingCollection<ELEMENT,OWNER> 
    implements Set<ELEMENT>
{
    /** {@inheritDoc} */
    public BacklinkSettingSet(
            Set<ELEMENT> relations,
            OWNER owner,
            BiConsumer<ELEMENT, OWNER> backLinkSetter)
    {
        super(relations, owner, backLinkSetter);
    }
}

Please see my recent article about backlinks for the super-class of BacklinkSettingSet.java.

Unit Test

The unit-test builds the test data described above, and carries out joins using the Criteria-API. Please see my recent article about setting up a JPA project for how you can make the abstract test run with both Hibernate and EclipseLink.

JpaJoinTest is the skeleton class, for now without any test method. It implements set-up (test-data build) and tear-down (cleanup), and prepares utilities like the JPA EntityManager for the tests to come. Please put all methods listed below into it.

JpaJoinTest.java

import static org.junit.Assert.*;
import java.util.List;
import javax.persistence.*;
import javax.persistence.criteria.*;
import org.junit.*;

public abstract class JpaJoinTest
{
    // setup
    
    private EntityManager entityManager;

    /** @return the name of the persistence-unit to use for all tests. */
    protected abstract String getPersistenceUnitName();
    
    private House kremlin;
    private House pentagon;
    private City washington;
    private City aleppo;
    
    /**
     * Builds test data: one city with a house,
     * one city without house, and one house without city.
     */
    @Before
    public void setUp() {
        entityManager = Persistence.createEntityManagerFactory(getPersistenceUnitName()).createEntityManager();
        
        // build test data
        // house without city
        kremlin = newHouse("Kremlin");
        // house with city
        pentagon = newHouse("Pentagon");
        // city with house
        washington = newCity("Washington");
        washington.getHouses().add(pentagon);
        // city without house
        aleppo = newCity("Aleppo");
        
        JpaUtil.persistAll(
                new Object[] { kremlin, aleppo, washington },
                entityManager); // washington will cascade-persist pentagon
    }
    
    @After
    public void tearDown()  {
        JpaUtil.removeAll(
                new Class<?>[] { City.class, House.class },
                entityManager);
    }
    

    /** Join query result class. */
    public static class CityAndHouse
    {
        public final City city;
        public final House house;
        
        public CityAndHouse(City city, House house) {
            this.city = city;
            this.house = house;
        }
    }
    
    
    private House newHouse(String name) {
        final House house = new House();
        house.setName(name);
        return house;
    }

    private City newCity(String name) {
        final City city = new City();
        city.setName(name);
        return city;
    }
}

JpaUtil provides saving, listing and deleting entities in context of transactions. It uses Java 8 functional lambda features.

JpaUtil.java

import java.util.List;
import java.util.function.Consumer;
import javax.persistence.EntityManager;
import javax.persistence.EntityTransaction;

public final class JpaUtil
{
    /**
     * @param <T> the class of the objects in returned result list.
     * @param persistenceClass the database table to be queried.
     * @param em JPA database access.
     * @return all records from given table.
     */
    public static <T> List<T> findAll(Class<T> persistenceClass, EntityManager em) {
        return em
            .createQuery(
                    "select x from "+persistenceClass.getName()+" x", 
                    persistenceClass)
            .getResultList();
    }
    
    /**
     * @param persistenceClass the database table to be queried.
     * @param em JPA database access.
     * @return the number of records in given table.
     */
    public static Long countAll(Class<?> persistenceClass, EntityManager em) {
        return em
            .createQuery(
                    "select count(x) from "+persistenceClass.getName()+" x", 
                    Long.class)
            .getSingleResult();
    }
    
    public static void persistAll(Object[] entities, EntityManager em) {
        JpaUtil.transactional(
            em, 
            (toPersist) -> {
                for (Object entity : toPersist)
                    em.persist(entity);
            },
            entities
        );
    }

    public static void removeAll(Class<?>[] persistenceClasses, EntityManager em) {
        JpaUtil.transactional(
            em, 
            (entityTypes) -> {
                for (Class<?> entityType : entityTypes)
                    for (Object entity : findAll(entityType, em))
                        em.remove(entity);
            },
            persistenceClasses
        );
    }

    /** @return the given parameter. */
    public static <P> P transactional(
            EntityManager entityManager, 
            Consumer<P> entityManagerFunction, 
            P parameter)
    {
        final EntityTransaction transaction = entityManager.getTransaction();
        try {
            transaction.begin();
            entityManagerFunction.accept(parameter);
            transaction.commit();
            return parameter;
        }
        catch (Throwable th)    {
            if (transaction.isActive())
                transaction.rollback();
            throw th;
        }
    }
    
    
    private JpaUtil() {} // do not instantiate
}

Criteria Join

Following is a join-implementation using the JPA Criteria API:

    private List<CityAndHouse> runCityJoin(JoinType joinType) {
        // build query
        final CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        final CriteriaQuery<CityAndHouse> query = cb.createQuery(CityAndHouse.class);
        final Root<City> city = query.from(City.class);
        final Join<City,House> house = city.join("houses", joinType);
        query.select(cb.construct(CityAndHouse.class, city, house));
        query.orderBy(cb.asc(city.get("name")), cb.asc(house.get("name")));
        
        // run query
        return entityManager.createQuery(query).getResultList();
    }

The parameter joinType determines how the JOIN will work. The query starts with City and joins House. It uses CityAndHouse (inner class prepared by JpaJoinTest) as result class. There is also an order-by clause to sort the result list, needed by test-assertions.

If you have meta-data for your entity-types by hand (such can be generated by the JPA-provider), please use these instead of my string properties "houses" and "name".

As JPA does not support RIGHT JOIN, I also implemented the substituting LEFT JOIN that gives the same result. The implementation is the same as above, just City and House have been swapped:

    private List<CityAndHouse> runHouseJoin(JoinType joinType) {
        // build query
        final CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        final CriteriaQuery<CityAndHouse> query = cb.createQuery(CityAndHouse.class);
        final Root<House> house = query.from(House.class);
        final Join<House,City> city = house.join("city", joinType);
        query.select(cb.construct(CityAndHouse.class, city, house));
        query.orderBy(cb.asc(city.get("name")), cb.asc(house.get("name")));
        
        // run query
        return entityManager.createQuery(query).getResultList();
    }

Test Methods

Now finally here are the tests, with beautiful descriptive names:

    /**
     * An inner join contains just connected left and right entities.
     */
    @Test
    public void innerJoinShouldFindCitieswWithHousesOnly() {
        final List<CityAndHouse> result = runCityJoin(JoinType.INNER); // INNER is default
        
        assertEquals(1, result.size());
        
        final CityAndHouse cityAndHouse = result.get(0);
        assertEquals(washington, cityAndHouse.city);
        assertEquals(pentagon, cityAndHouse.house);
    }
    
    /**
     * A left join is an outer join.
     * It contains connected and unconnected left entities,
     * but only connected right entities.
     */
    @Test
    public void leftJoinShouldFindCitieswWithAndWithoutHouses() {
        final List<CityAndHouse> result = runCityJoin(JoinType.LEFT);
        
        assertEquals(2, result.size());
        
        final CityAndHouse aleppoAndNull = result.get(0);
        assertEquals(aleppo, aleppoAndNull.city);
        assertNull(aleppoAndNull.house);
        
        final CityAndHouse washingtonAndPentagon = result.get(1);
        assertEquals(washington, washingtonAndPentagon.city);
        assertEquals(pentagon, washingtonAndPentagon.house);
    }
    
    /**
     * A right join is an outer join.
     * It contains only connected left entities,
     * but connected and unconnected right entities.
     */
    @Test
    public void reverseLeftJoinShouldFindCitiesWithHousesAndHousesWithoutCity() {
        final List<CityAndHouse> result = runHouseJoin(JoinType.LEFT);
        assertRightJoin(result);
    }
    
    /**
     * JPA: "Right outer joins and right outer fetch joins 
     * are not required to be supported in Java Persistence 2.0. 
     * Applications that use RIGHT join types will not be portable."
     * @see https://docs.oracle.com/javaee/6/api/javax/persistence/criteria/JoinType.html
     */
    @Test
    @Ignore("Both Hibernate/EclipseLink throw UnsupportedOperationException: RIGHT_JOIN_NOT_SUPPORTED")
    public void rightJoinShouldFindCitiesWithHousesAndHousesWithoutCity() {
        final List<CityAndHouse> result = runCityJoin(JoinType.RIGHT);
        assertRightJoin(result);
    }
    
    private void assertRightJoin(List<CityAndHouse> result) {
        assertEquals(2, result.size());
        
        final CityAndHouse washingtonAndPentagon = result.get(0);
        assertEquals(washington, washingtonAndPentagon.city);
        assertEquals(pentagon, washingtonAndPentagon.house);
        
        final CityAndHouse nullAndKremlin = result.get(1);
        assertNull(nullAndKremlin.city);
        assertEquals(kremlin, nullAndKremlin.house);
    }
    
    /**
     * A full join is an outer join.
     * It contains connected and unconnected left entities
     * and connected and unconnected right entities.
     * <p/>
     * But: JPA JoinType does not provide the full outer join type!
     */
    @Test
    @Ignore
    public void fullOuterJoinShouldFindCitiesWithOrWithoutHousesAndHousesWithoutCity() {
        final List<CityAndHouse> result = runCityJoin(JoinType.valueOf("FULL"));
        // there is no JoinType.FULL !
        
        assertEquals(3, result.size());
        
        final CityAndHouse aleppoAndNull = result.get(0);
        assertEquals(aleppo, aleppoAndNull.city);
        assertNull(aleppoAndNull.house);
        
        final CityAndHouse nullAndKremlin = result.get(1);
        assertNull(nullAndKremlin.city);
        assertEquals(kremlin, nullAndKremlin.house);
        
        final CityAndHouse washingtonAndPentagon = result.get(2);
        assertEquals(washington, washingtonAndPentagon.city);
        assertEquals(pentagon, washingtonAndPentagon.house);
    }

The INNER and the LEFT test are the only ones that worked. I had to retrofit the RIGHT test by swapping the entity-types to emulate the RIGHT JOIN (I called it reverseLeftJoinShouldFindCitiesWithHousesAndHousesWithoutCity). The real RIGHT test and the FULL test have been @Ignored. I left them inside just for completeness.

The assertions make sure that the results are like what I listed in the introduction. The order-by clause of the query made the get-indexes of the results reliable.

Postgres Database

To make sure that my results are realistic I used, besides H2, another database. Postgres is an old but powerful UNIX database that is freely available. It is pre-installed on most LINUX distributions. To find out if it is on your machine, open a command line terminal and type "psql --help". If it answers, you can type "psql" and then list your databases by typing "\l". You will see that template1 is present, which you could use for tests. You can then connect via "\c template1" to that database, where "\d" will describe tables, views and sequences. With "\q" you can quit. Make sure in your postgresql.conf that listen_addresses = 'localhost' (which is default), so that it rejects connections other than your own machine.

Here are my JPA persistence.xml connection properties for PostgreSQL:

            <property name="javax.persistence.jdbc.url" value="jdbc:postgresql://localhost/template1"/>
            <property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver"/>
            <property name="javax.persistence.jdbc.user" value="postgres"/>
            <property name="javax.persistence.jdbc.password" value="postgres"/>

            <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>

            <property name="eclipselink.target-database" value="PostgreSQL"/>

You may have noticed that the entities City and House do not extend BaseEntity (that provides an always-present application-made UUID). This is because Postgres has a bug with primary keys of type UUID:

PSQLException: ERROR: column "id" is of type uuid but expression is of type bytea

Workaround would be to have a 36-character String id that represents the UUID.

Conclusion

When I started to work with databases in the Nineties, there were just INNER and OUTER joins. Looks like we are still there when using JPA. But - who needs more? Do these additional keywords, made for maybe 10% of the users, really make sense? Didn't they just add complexity that made life for the other 90% much harder?

fritzthecat-blog

Blog-Archiv

Sonntag, 26. Januar 2020

JPA Join Types with Criteria API