Sometimes in web-based applications, you might end up needing an “advanced search” feature for your entities which happens to have many fields and relations. Supporting each of these search requirements results in a huge amount of code and a tight coupling between the backend code and UI. And if some business requirement changes, you have to update your backend code to support the newly requested features. On the other hand, a dynamic query builder, that can convert REST query parameters to SQL queries, can remove this tight coupling and make developers’ lives easier!

Spring framework has built-in support for these kinds of dynamic queries in its Spring Data module by leveraging the QueryDSL library and it also has web support so it can dynamically create SQL queries from the REST parameters.

By taking ideas from Spring Data, I tried to implement a dynamic REST query language in Micronaut by using JPA and QueryDSL and made it available as a library on Maven repository. This implementation uses little to no runtime reflections thanks to Micronaut’s awesome compile-time Introspection which can result in less memory usage and better performance and it’s suitable for Microservices architecture.

Micronaut is a JVM-based framework for building modular JVM applications suitable for Microservices architectures. In Micronaut, all bean configuration, wiring, parsing queries, etc happen at compile time by using Annotation Processing instead of runtime and it avoids using reflection and runtime bytecode generation. This results in faster startup times, lower memory footprint, and easier unit testing. The coding style is very similar to Spring Framework.

Requirements for using the library and running the example project:

• JDK 11 or above
• Micronaut Framework
• Micronaut Data
• JPA

Example Project

I’m going to use my library in an example project and explain the features. After that, I’ll explain how this library works in more detail. For the full source code of the example project, visit the Github page.

For this library to work, you should have Micronaut Framework, Micronaut Data, and JPA in your project. Add querydsl-dynamic-query library as a dependency in your project:

implementation "com.snourian.micronaut:querydsl-dynamic-query:0.2.0"

Note: This is the very first preview release of the library (And also my first ever library!) so there might be some bugs here and there. Also, some types of relations are not supported as noted in the readme file inside Github. Any contribution to this library to make it better and more stable would be greatly appreciated! 🙂

In our example project, we are going to use Micronaut Data and Hibernate for creating our JPA Repositories and H2 as our in-memory database. I’ve also used the Mapstruct library for easily mapping domain classes to DTOs which is optional. It creates the mapping classes at compile-time and avoids using reflections and it’s suitable for use in Micronaut. Mapstruct still doesn’t support jakarta.inject.* annotations in the time of writing this post. Therefore, I had to add the javax.inject dependency and use the javax package instead. (Actually, they’re going to support it in their next final version).

dependencies {
    annotationProcessor("io.micronaut:micronaut-http-validation")
    annotationProcessor("io.micronaut.data:micronaut-data-processor")
    annotationProcessor("jakarta.persistence:jakarta.persistence-api:$jpaVersion")
    annotationProcessor("org.mapstruct:mapstruct-processor:$mapstructVersion")
    implementation("org.mapstruct:mapstruct:$mapstructVersion")
    implementation("com.snourian.micronaut:querydsl-dynamic-query:$querydslDynQueryVersion")
    implementation("javax.inject:javax.inject:$javaxInjectVersion")
    implementation("io.micronaut:micronaut-http-client")
    implementation("io.micronaut:micronaut-runtime")
    implementation("io.micronaut.data:micronaut-data-hibernate-jpa")
    implementation("io.micronaut.sql:micronaut-hibernate-jpa")
    implementation("io.micronaut.sql:micronaut-jdbc-hikari")
    implementation("javax.annotation:javax.annotation-api")
    runtimeOnly("ch.qos.logback:logback-classic")
    runtimeOnly("com.h2database:h2")
    implementation("io.micronaut:micronaut-validation")
}

Now, before diving into the good parts, let’s create our entities. Figure 1 shows our sample entities.

I have created some sample data in JSON format inside the data folder in the root directory. By calling the GET /populate endpoint, the program reads JSON files, converts them to entities, and persists the entities inside the H2 database.

Now, we want to build an advanced search for our department entity to query through the projects and employees in various ways. Let’s create a Repository for Department:

@Repository
public interface DepartmentRepository implements JpaRepository<Department, Long> {

}

As you have already noticed, it looks the same as Spring repositories. For our dynamic query implementation, I wanted to integrate it into the Repository classes so there won’t be any need to inject other classes into our Service or Controller classes. Therefore, our repository should also implement the QuerydslPredicateExecutor interface. this interface requires two methods to be implemented: getEntityManager() and getEntityClass(). Since we need to access the EntityManager in our repository to build queries via QueryDSL, we have to convert our class to an abstract class. So our final Repository class looks like below:

@Repository
public abstract class DepartmentRepository implements JpaRepository<Department, Long>,
        QuerydslPredicateExecutor<Department> {

    @PersistenceContext
    private EntityManager em;

    @Override
    public EntityManager getEntityManager() {
        return em;
    }

    @Override
    public Class<Department> getEntityClass() {
        return Department.class;
    }
}

By implementing the QuerydslPredicateExecutor interface, we now have access to methods for executing QueryDSL predicates. Below you can see some of the methods:

Optional<T> findOne(QueryParameters params)
Optional<T> findOne(Predicate predicate)
List<T> findAll(QueryParameters params, Sort sort)
List<T> findAll(Predicate predicate, Sort sort)
Page<T> findAll(QueryParameters params, Pageable pageable)
Page<T> findAll(Predicate predicate, Pageable pageable)
Page<T> findAll(QueryParameters params, Pageable pageable, Map<String, Object> hints)
Page<T> findAll(Predicate predicate, Pageable pageable, Map<String, Object> hints)
    ...

Now, we need a REST endpoint for our advanced search. Therefore, the frontend can create a predicate with REST query parameters and send it to our backend:

@Get("/search{?values*}")
public HttpResponse<?> search(@RequestBean QueryParameters values, Pageable pageable) {
    return HttpResponse.ok(departmentService.search(values, pageable));
}

And the search(QueryParameters values, Pageable pageable) method in our Service class:

@Transactional
public Page<DepartmentDto> search(QueryParameters params, Pageable pageable) {
    return departmentRepository.findAll(params, pageable)
            .map(departmentMapper::toDto);
}

And that’s all you need to do! Now, let’s fire up our application, populate our in-memory database by calling the /populate endpoint and run some queries.

GET /search
Query: select department from Department department

GET /search?id=eq(11)
Query: select department from Department department where department.id = ?1

GET /search?location.city=eq(Gotham)
Query: select department from Department department where department.location.city = ?1

GET /search?employees.rank=in(Manager,Chief)&employees.gender=eq(Female)
Query: select department from Department department
  inner join department.employees as department_employees
where department_employees.rank in (?1, ?2) and department_employees.gender = ?3

GET /search?projects.title=string_contains_ic(stream)&projects.technologies.name=in(Python,Go)
Query: select department from Department department
  inner join department.projects as department_projects
  inner join department_projects.technologies as department_projects_technologies
where lower(department_projects.title) like ?1 escape '!' and department_projects_technologies.name in (?2, ?3)

GET /search?employees.score=gt(70)&employees.rank=ne(Manager)&sort=id,desc&page=2&size=1
Query: select department from Department department
  inner join department.employees as department_employees
where department_employees.score > ?1 and department_employees.rank <> ?2
order by department.id desc limit ? offset ?

If you want to make an “OR” predicate, just add ‘EXPR_TYPE=anyOf’ to your parameters:

GET /search?employees.score=gt(70)&employees.rank=eq(Manager)&EXPR_TYPE=anyOf
Query: select department from Department department
  inner join department.employees as department_employees
where department_employees.score > ?1 or department_employees.rank = ?2

If you want to see what predicates are supported, check out com.snourian.micronaut.querydsl.expression.operator.PredicateOperator enum:

EQ, NE, IS_NULL, IS_NOT_NULL, BETWEEN, GOE, GT, LOE, LT, 
MATCHES (regex), MATCHES_IC (regex), STRING_IS_EMPTY, STARTS_WITH, 
STARTS_WITH_IC, EQ_IGNORE_CASE, ENDS_WITH, ENDS_WITH_IC, STRING_CONTAINS, 
STRING_CONTAINS_IC, LIKE, LIKE_IC, LIKE_ESCAPE, LIKE_ESCAPE_IC, IN, NOT_IN

And if you want to add some general customization to the final Predicate, override custom(Predicate predicate) method inside your Repository class:

@Repository
public abstract class DepartmentRepository implements JpaRepository<Department, Long>,
        QuerydslPredicateExecutor<Department> {

    // ...
    @Override
    public Predicate customize(Predicate predicate) {
        // Make some customizations to the final predicate. For instance, append an AND or OR predicate
        // Refer to QueryDSL documentation to see how to work with Predicates and Expressions
        return predicate;
    }
    // ...
}

QueryDSL has also a library that can be added as an annotationProcessor in Gradle build script which generates Q-classes from your entities. These Q classes can be used to easily create and run queries with QueryDSL. If you want to work more directly with QueryDSL, I recommend you to add this library to your Gradle build file:

annotationProcessor "com.querydsl:querydsl-apt:${queryDslVersion}:jpa"

Implementation Details

Now let’s talk a little about the library itself. As I have mentioned before, the source code is available on Github.

First of all, we need to process HTTP query params. For creating queries with QueryDSL, we need to know the path and type of the fields. For example, if we want to create a predicate for “name” field of the Technology entity, we need to know how to reach this field from the root entity (Department –> Project –> Technology) and also its type (which is String). To get this data, I have used the BeanIntrospection feature of Micronaut.

Since Micronaut 1.1, a compile-time replacement for the JDK’s Introspector class has been included.
The BeanIntrospector and BeanIntrospection interfaces allow looking up bean introspections to instantiate and read/write bean properties without using reflection or caching reflective metadata, which consume excessive memory for large beans.

Micronaut Documentation

The com.snourian.micronaut.querydsl.expression.ExpressionFactory class is responsible for converting HTTP query parameters to a data structure that includes the path and type of each requested field. Since Micronaut generates BeanIntrospection data for JPA entities at compile-time, we can easily access the information we want from the entity class by calling BeanIntrospection.getIntrospection(type) method.

We also need to extract the operator and values from HTTP query parameters. ExpressionFactory.extractOpAndValues(Class propertyType, String value) will do this job for us. The operator and values inside HTTP parameters are in String format and we need to convert them to the appropriate data type. The operator will be converted to its Enum type (com.snourian.micronaut.querydsl.expression.operator.PredicateOperator) and ExpressionFactory.toTypedValues(Class type, String[] values) converts the values to the data type of the field. If the type of the field is not one of the known data types, we’ll use Jackson’s two-step conversion to convert String to our desired type. For example, Jackson’s conversion will be used to convert String to the appropriate Enum type.

When the work of the ExpressionFactory class is done, it creates an instance of the com.snourian.micronaut.querydsl.expression.ExpressionEntries class that holds all the information we want to use to build our query (path, data type, values, etc). Now, the com.snourian.micronaut.querydsl.QuerydslPredicateBuilder<T> class comes into play to do the rest of the work.

The QuerydslPredicateBuilder class traverses the data inside ExpressionFactory to build the final Predicate object. There are two important Map fields in this class. the HashMap<String, Path<?>> pathCache holds the path data of the fields. By using this cache, we don’t need to create separate Path objects for each field that we are processing. For example, if we have project.title=eq(…)&project.technologies.name=eq(…) predicate, we only create the path for ‘project’ only once but use it twice in our code. The LinkedHashMap<String, JoinsData> joins field holds the joins expressions that will be used later. We are using the LinkedHashMap implementation because the order of joins is important in a SQL query. After processing paths, building expressions and etc, the final Predicate will be ready to be used inside a JPAQuery.

The QuerydslPredicateExecutor<T> class which we implement in our Repository class is the main class that calls the QuerydslPredicateBuilder. and it’s also responsible for building the final JPAQuery object and fetching the result. It also uses the data inside the joins cache from the Builder class to create a join query. To leverage Micronaut’s Pageable and Sort objects, the com.snourian.micronaut.querydsl.QuerydslHelper class will be used. I took the ideas for creating this class from Spring Data support for QueryDSL.

This is the very first version of the library and unfortunately, I have very limited free time so I might not be able to update it regularly. Therefore, contributions are very welcome! Help me make this library better 😊

What’s not working:

• Fields with @ElementCollection annotation.
• Map<?, ?> relations.
• Maybe more! You tell me!

Refactoring and Optimization ideas:

• Use a tree data structure for PredicateEntry class instead of the current implementation and traverse it with DFS when building the final query.
• Refactor ExpressionFactory class to use less if/else statements.
• Only call BeanIntrospection once for the root Entity in PredicateEntry class.
• Currently, this library only supports “AND” and “OR” expressions. Support complex expressions by combining and/or predicates instead of only using “AND” or “OR”.
• I tried my best not to use reflection and this library heavily relies on Micronaut’s compile-time bean introspection. But I haven’t tested compiling to native image. It might need some work to support it; or maybe not!
• Adding debug/trace logs and unit tests!