ageery / pgqp

Pretty Good Query Producer (PGQP)

Pretty Good Query Producer (PGQP)

Overview

PGQP is a lightweight Java library that generates database queries from POJO field values in a type-safe way using a database framework like JPA ^[1].

Using a query definition that specifies how POJO fields are to be converted into restriction clauses and how sort enums map to columns in tables, PGQP not only creates the necessary where and order by clauses, but it also generates any needed join clauses (in addition to the select and from clauses).

Motivation

A common database-related task is to transform non-empty values in a POJO (e.g., a bean that backs a search form) into a database query.

This can be a tedious process for POJOs with many fields:

• test if a field is set;

• if it is, add a where-clause, adding joins as necessary;

• repeat for each field in the POJO.

The first beneft offered by PGQP is that, using Java-8 method handles, it allows the mapping between field values and where-clauses to be specified independently of the application of the mappings. That is, the library user specifies the mappings and the library takes care of generating the restrictions in the query.

A second tedious part of translating bean fields to where clauses occurs when multiple tables are involved. In order to add restrictions related to a table not part of the root table (the from clause), it is necessary to create a join to the table the restriction column is in. However, in most cases, tables should only be joined once, making it necessary to keep track of which tables have already been joined in the where clause of the query.

The second benefit offered by PGQP is that it allows the restriction mapping to specify the table of the field it is related to and how the table should be joined into the query. That is, the library user specifies the table information for the restrictions and PGQP takes care of generating the needed joins.

Further complicating things, some restrictions, if naively mapped to regular joins, may produce duplicate rows in the result set because the criteria is one-to-many with the root table. One solution to this problem is to use a distinct attribute in the projection. An issue with this solution is that it often suffers from performance problems and may lead to issues with other parts of the query (e.g., BLOB columns) ^[2].

The third benefit offered by PGQP is that it detects one-to-many restrictions and translates these restrictions into a semi-join (in-clause + sub-query restriction).

Finally, in order to add order by clauses that potentially span multiple tables, it is necessary to insure that the tables containing the columns to be sorted on are part of the query ()using the proper type of join) and that the sort columns are in the projection.

The fourth benefit of PGQP is that it insures that tables needed for the order by clauses are properly joined into the query and that the sort columns are in the select clause.

API

The QueryHandler interface defines the entry point for the library. The interface has two methods: one that generates an "entity" (data) query (toEntityQuery) and one that generates a count query (toCountQuery). Both methods take a QueryDefinition object as a parameter and return a query object in some implementation (e.g., a JPA CriteriaQuery object).

The QueryDefinition class has two fields: an arbitrary criteria POJO and an (optional) list of SortInfo objects.

The SortInfo class has two fields: a sort enum and a sort direction (ascending or descending).

Finally, while PGQP works with regular types (e.g., strings, numbers, dates, etc.), it also provides a generic CriteriaField class that encapsulates a value and an operation, as defined in the StandardOperation enum. This enum contains common operations such as equality and inequality, comparison (e.g., less than and greater than), and string functions such as starts with, contains, etc. The CriteriaField class can be used to make the POJO mapping even more flexible.

Implementations

PGQP currently targets JPA and makes use of the static metamodel generator classes ^[3].

The JoinDefinition class is used to specify how to navigate from one entity to another. It maps a field on a parent table to a child table (e.g., Person_.employer).

THe RestrictionMapping classes is used to map a bean property to how the value should be used in restricting the query.

The SortDefinition class defines how an enum maps to an attribute in an entity class (e.g., Person_.birthdate).

The fundamental JPA-related class in the PGQP is RestrictionDefinition. This class specifies how a restriction clause is to be applied to a query. This is done using a BiFunction that takes a QueryContext object (CriteriaBuilder, CriteriaQuery and Path) plus the value of the attribute in the bean and returns a javax.persistence.criteria.Predicate. The PGQP library takes care of properly adding the restrictions generated from RestrictionDefinition objects to the CriteriaQuery.

While there is a lot of expressive power in specifying the restriction mapping in this way, the most common BiFunction implementations can be found in the JpaCriteriaHandlers class (e.g., CONTAINS_FIELD_HANDLER which generates a like clause with leading and trailing wildcards).

Examples

Entity Mappings

In the examples below, there are two entity classes: Person and Business. Project Lombok is used to generate getters and setters.

Person.java

@Data
@Entity
public class Person {
	@Id
	private Integer id;
	private String firstName;
	private String lastName;
	private LocalDate birthdate;
	@ManyToOne
	private Person parent;
	@ManyToOne
	private Business employer;
	@OneToMany(mappedBy = "parent")
	private Collection<Person> children;
}

Business.java

@Data
@Entity
public class Business {
	@Id
	private Integer id;
	private String name;
	private String address;
	@ManyToOne
	private Person owner;
	@OneToMany(mappedBy = "employer")
	private Set<Person> employees;
}

Join Mappings

To work with Person-related queries (i.e., queries that return Person objects), we first need to map the various joins that may be needed when creating a query. Ordinarily, we’d encapsulate these definitions in a class so we can use them in other parts of the mapping.

A JoinDefinition instance is parameterized with the type of table it is joining from and the type of table it is. We use the JPA Metamodel generated classes to define these definitions in a type-safe way.

The root table has no parent; it is always present in the query.

Root Person mapping

private JoinDefinition<?, Person> PERSON_TABLE = new JoinDefinition<>("person",
    Person.class);

To join to the business table, we start from the person table and navigate via the employer field.

Business mapping

JoinDefinition<Person, Business> BUSINESS_TABLE = new JoinDefinition<>("business",
    Business.class, PERSON_TABLE, new AttributeInfo<>(Person_.employer));

To join to owner information, we start from the business entity and navigate via the owner field. Note that the type of the join, like the root table, is Person.

Owner mapping

JoinDefinition<Business, Person> BUSINESS_OWNER_TABLE = new JoinDefinition<>("owner",
    Person.class, BUSINESS_TABLE, new AttributeInfo<>(Business_.owner));

Finally, to work with restrictions related to a Person’s children, we define a join mapping from Person via the children field. Note that this mapping is potentially one-to-many: a Person may have zero-or-more children. Note that this join type is also Person.

Children mapping

JoinDefinition<Person, Person> CHILD_TABLE = new JoinDefinition<>("child",
    Person.class, PERSON_TABLE, new AttributeInfo<>(Person_.children));

Sort Mappings

Having defined the various types of tables in the system, we can now define sorts and how they map to entity fields.

First we define the sort enum that defines the fields that can be sorted on.

PersonSort.java Enum

public enum PersonSort {
	FIRST_NAME, LAST_NAME, BUSINESS_NAME, OWNER_LAST_NAME
}

A SortDefinition maps an enum value to a table join and field:

PersonSort mappings

new SortDefinition<>(PersonSort.FIRST_NAME, PERSON_TABLE, Person_.firstName)
new SortDefinition<>(PersonSort.LAST_NAME, PERSON_TABLE, Person_.lastName)
new SortDefinition<>(PersonSort.BUSINESS_NAME, BUSINESS_TABLE, Person_.name)
new SortDefinition<>(PersonSort.OWNER_LAST_NAME, BUSINESS_OWNER_TABLE, Person_.lastName)

Search Criteria Mapping

The search criteria is driven by a bean. PGQP works with regular types — String and Boolean below; it can also work with special types that specify both a value and an operation — CriteriaField<String> lastName below is an example of this.

PersonCriteria.java

@Data
@Accessors(chain = true)
public class PersonCriteria {
	private String firstName;
	private CriteriaField<String> lastName;
	private String companyName;
	private String ownerName;
	private String childName;
	private Boolean underageChildren;
	private Boolean employed;
	private Boolean hasChildren;
}

To map a criteria to a restriction, we use the RestrictionMapping class. The standard constructor takes a getter and a RestrictionDefinition object.

The RestrictionDefinition constructor takes a table join, a table field and a BiFunction that produces a JPA Predicate object. The BiFunction construct gives complete control over the Predicate construction. However, in many cases we simply want to use a well-defined mapping like equality, contains, is null, etc. Here are a few examples.

Person first name mapping to a contains restriction

new RestrictionMapping<>(PersonCriteria::getFirstName,
    new RestrictionDefinition<>(PERSON_TABLE, Person_.firstName, CONTAINS_FIELD_HANDLER))

Business name mapping to a like restriction

new RestrictionMapping<>(PersonCriteria::getCompanyName,
    new RestrictionDefinition<>(BUSINESS_TABLE, Business_.name, LIKE_FIELD_HANDLER))

Person employed mapping to a not null restriction

new RestrictionMapping<>(PersonCriteria::getEmployed,
    new RestrictionDefinition<>(PERSON_TABLE, Person_.employer,
    notNullFieldHandler(Business.class)))

Here is a more complicated example:

Person mapping for whether they have underaged children

new RestrictionMapping<>(PersonCriteria::getUnderageChildren,
    new RestrictionDefinition<>(CHILD_TABLE, Person_.birthdate,
    (context, value) -> value ?
        context.getCriteriaBuilder().greaterThan(context.getPath(), LocalDate.now().minusYears(18))
		: context.getCriteriaBuilder().lessThan(context.getPath(), LocalDate.now().minusYears(18)))

Finally, here’s an example of creating a sub-query for whether a Person has children:

Person mapping for whether they have children

new RestrictionMapping<>(PersonCriteria::getHasChildren,
    new RestrictionDefinition<>(PERSON_TABLE, Person_.id, (context, value) -> {
	    Subquery<Integer> sq = context.getQuery().subquery(Integer.class);
	    Root<Person> root = sq.from(Person.class);
	    sq.select(root.get(Person_.parent).get(Person_.id)).where(root.get(Person_.parent).isNotNull());
		In<Integer> in = context.getCriteriaBuilder().in(context.getPath()).value(sq);
		return value ? in : in.not();
    }
))

QueryHandler Construction

To create a query handler, we create a list of the restriction handlers and the sort mappings. In addition, for the purpose of creating sub-queries, we need to specify the root JoinDefinition (PERSON_TABLE), the type of the primary key field (Integer.class) and how to access the primary key from the root table (r -> r.get(Person_.id)).

With these pieces, we can create our query handler.

JpaQueryHandler creation

QueryHandler<CriteriaQuery<Person>, CriteriaQuery<Long>, Person, PersonCriteria, PersonSort> queryHandler =
new JpaQueryHandler<>(entityManager, Person.class, PERSON_TABLE, Integer.class, r -> r.get(Person_.id),
    getRestrictions(), getSorts());

QueryHandler Examples

With a JpaQueryHandler instance, we can execute queries against the database via the JPA EntityManager.

Here are some examples.

Query to find people with the first name of Sally, ordered by last name

PersonCriteria criteria = new PersonCriteria().setFirstName("Sally");
QueryDefinition<PersonCriteria, PersonSort> qd = new QueryDefinition<>(criteria, PersonSort.LAST_NAME);
CriteriaQuery<Person> cq = queryHandler.toEntityQuery(qd);
TypeQuery<Person> tq = entityManager.createQuery(cq);
List<Person> people = tq.getResultList();

Note that the toEntityQuery returns a CriteriaQuery object which can be further manipulated, if needed (e.g., to set a limit and/or offset for the query). Note also that, using the exact same QueryDefinition object, we could instead do a count query by change the queryHandler.toEntityQuery to queryHandler.toCountQuery.

Query to find people with the last name of Smith, ordered by first name descending, last name ascending

entityManager.createQuery(
	queryHandler.toEntityQuery(new QueryDefinition<>(
		new PersonCriteria().setLastName(new CriteriaField<String>(StandardOperation.EQ, "Smith")),
		Arrays.asList(
		    new SortInfo<>(PersonSort.FIRST_NAME, Direction.DESC),
		    new SortInfo<>(PersonSort.LAST_NAME, Direction.ASC)))))
	.getResultList();

Query to find the number of unemployed people with no children

entityManager.createQuery(
	queryHandler.toCountQuery(new QueryDefinition<>(
		new PersonCriteria()
			.setHasChildren(false)
			.setEmployed(false))))
	.getSingleResult();

As you can see, PGQP provides a higher-level abstraction over querying: simply specify the applicable fields on the criteria bean and (optionally) specify a list of sort enums. The details involved in constructing the query are contained in the mappings that were used to create the QueryHandler but the caller does not need to be aware of them.

For more examples, see the tests in the src/test directory.

Conclusion

PGQP is a high-level abstraction for querying. It provides a framework for specifying individual restrictions and sorts in isolation and takes care of combining these into a single query. In so doing, it simplifies query construction by allowing developers to work with an arbitrary criteria POJO and not have to work with the underlying data store itself (e.g., JPA).

---

1. In the same way that Spring Data uses data access implementations (e.g., JPA) to provide higher-level repository functionality, PGQP uses the data access implementation to provide higher-level query functionality

2. https://blog.jooq.org/2016/03/09/sql-join-or-exists-chances-are-youre-doing-it-wrong/

3. https://docs.jboss.org/hibernate/orm/5.0/topical/html/metamodelgen/MetamodelGenerator.html