jOOQ with RSQL (FIQL Parser)

A lot of Java applications have SQL persistence and access that layer with the help of higher-level tools such as JPA. However in some cases you need a more granular tool to get to your data by constructing large complex SQL queries. Jooq Object Oriented Querying (jOOQ) is a light database-mapping software library that helps to address the problems ORMs and of that nature face. It differs from ORM and generates code that allows us to build type safe queries, and construct the raw SQL query dynamically at runtime.

Nagivate here to the application code.

Reasons to use jOOQ:

• For statistical queries, nothing comes close to jOOQ due to the dynamic ability to construct SQL queries at runtime.
• This gives great flexibility in performing dynamic joins and filtering on specific conditions.
• jOOQ forces you to understand and write the raw SQL implementation, enabling you to optimize queries where necessary
• Type safety built in to the database columns

RSQL is a query language for parametrized filtering of entries in RESTful APIs. It derives from FIQL (Feed Item Query Language) an URI-friendly syntax for expressing filters across the entries in an Atom Feed. FIQL is great for use in URI; there are no unsafe characters, so URL encoding is not required.

This article focuses on Spring Boot, jOOQ and RSQL and how we can construct SQL queries with conditions expressed by the RSQL FIQL parser.

To get a instance of Postgres running, cd into docker directory and run docker-compose up. Then proceed to run the application by running the main method in RSQLJooqApplication. Flyway is configured to generate sample tables in order to demonstrate passing FIQL queries, then being translated to SQL.

Documentation is in the README of the GitHub project.

Below is the Author table:

Let’s filter for results that match only if the first_name is equal to Bert or if the JK or John is in first_name.

By invoking the URL http://localhost:8080/authors/search?filter=first_name==Bert,first_name=in=(JK, John)

generates the SQL query

select 
  "author"."first_name", 
  "author"."last_name"
from "author"
where (
  "author"."first_name" = 'Bert'
  or "author"."first_name" in (
    'JK', 'John'
  )
)

And as expected, we only get the results which match our WHERE condition.

As you can imagine, the more tables and results you wish to retrieve, you are able to chain multiple queries by passing the parameters when making the GET request. Furthermore based on certain conditions, if you require different columns from a different table, jOOQ allows you to dynamically perform joins on the conditions needed.