Flexible RSQL-based data filtering service

In this article, we will be talking about, how to build universal filtering Spring-service, which works with JPA-model using hibernate and is based on RSQL.

On one of the projects, I encountered a situation, wherewith limited resources, my team had to implement a bunch of filtering REST-endpoints within one of user-APIs. The situation was complicated by the fact that no one knew, which filters should be available over API, and time was not in our favor. We were heavily using Spring Data and although it provides great capabilities of a rapid building of data-repositories, sometimes, it’s difficult to build a universal and dynamic solution using Spring Data.

So, we came up with a generic approach, and finally, we did it with the help of RSQL.

Brief overview

RSQL – is REST-based query language, that lets us define filtering-queries, which, in turn, can be translated into queries to databases.

We are going to use rsql-parser. It ensures parsing of RSQL-queries, and rsql-jpa, in turn, builds JPA Criteria API definitions, which finally gets translated into SQL-queries.

Diagram below depicts general flow of how REST-query gets coverted into SQL-query

RSQL to SQL transition flow

Start project

Let’s bootstrap a new Gradle project. We are going to use:

  1. Java 11
  2. Gradle 5.2.1 with Groovy DSL
  3. Spring boot 2.2.6.RELEASE
  4. Lombok to avoid boilerplate code
  5. rsql-parser:2.1.0
  6. rsql-jpa:2.0.2
  7. H2 DB for development purposes

Let’s define build.gradle file.

plugins {
	id 'org.springframework.boot' version '2.2.6.RELEASE'
	id 'io.spring.dependency-management' version '1.0.9.RELEASE'
	id 'java'
}

group = 'com.dicelogics'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = '11'

repositories {
	mavenCentral()
}

dependencies {
	implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
	implementation 'org.springframework.boot:spring-boot-starter-web'
	implementation("cz.jirutka.rsql:rsql-parser:2.1.0")
	implementation("com.github.tennaito:rsql-jpa:2.0.2")

	implementation("org.projectlombok:lombok:1.18.4")
	annotationProcessor("org.projectlombok:lombok:1.18.4")

	runtime 'com.h2database:h2:1.4.199'

	testImplementation('org.springframework.boot:spring-boot-starter-test') {
		exclude group: 'org.junit.vintage', module: 'junit-vintage-engine'
	}
}

test {
	useJUnitPlatform()
}

Note. If you are using IntelliJ IDEA for development, don’t forget to enable annotation processing for normal work with Lombok.

Connect database

For development and testing purposes, we are using an in-memory H2 database. Create file src/main/resources/application.properties and add properties like below:

spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=password
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect

Once the configuration is defined, Spring is able to automatically creates a connection as well as appropriate DataSource for us.

Define Database Schema

Our database schema consists of two tables: users and orders. Add file src/main/resources/data.sql.

DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS orders;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(250) NOT NULL,
    age INT NOT NULL
);
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(250) NOT NULL,
    price INT NOT NULL,
    order_date TIMESTAMP NOT NULL,
    user_id INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);


INSERT INTO users(id, name, age)
VALUES
    (1, 'John', 20),
    (2, 'Steve', 25),
    (3, 'Joe', 18),
    (4, 'Daniel', 56);

INSERT INTO orders (id, title, price, order_date, user_id)
VALUES
    (11, 'Order 1. User 1', 110, '2020-01-12', 1),
    (12, 'Order 2. User 1', 210, '2020-02-10', 1),
    (13, 'Order 3. User 1', 1010, '2020-01-01', 1),
    (21, 'Order 1. User 2', 300, '2020-03-10', 2),
    (22, 'Order 2. User 2', 300, '2020-02-10', 2),
    (31, 'Order 1. User 3', 1010, '2020-04-01',3),
    (32, 'Order 2. User 3', 110, '2020-02-14', 3),
    (41, 'Order 1. User 4', 333, '2020-01-01', 4);

Spring will automatically execute it, once connection to database is established, so we will have “clear” dataset.

Define JPA data-model

We will define two entities: User

@Entity
@Table(name = "users")
@Setter
@Getter
@NoArgsConstructor
@AllArgsConstructor
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column
    private Long id;

    @Column
    private String name;

    @Column
    private Integer age;
}

Order

@Entity
@Table(name = "orders")
@Setter
@Getter
@NoArgsConstructor
@AllArgsConstructor
public class Order {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column
    private Long id;

    @Column
    private String title;

    @Column
    private Integer price;

    @JsonFormat(pattern = "yyyy-MM-dd")
    @Column(name = "order_date")
    private Timestamp orderTs;

    @ManyToOne
    @JoinColumn(name = "user_id")
    private User user;
}

Define Filter Repository

Let’s define basic Interface FilterRepo

public interface FilterRepo<E> {
    List<E> findByQuery(String rQuery);
}

As you’ve, probably, noticed, in order to make repository more versatile, we’ve defined generic.

And now, we are adding the most important part of the tutorial, AbstractFilterRepo.

public abstract class AbstractFilterRepo<E> implements FilterRepo<E> {

    public List<E> findByQuery(String rsQuery) {
        CriteriaQuery<E> filteringCriteria = createFilteringCriteria(rsQuery);
        TypedQuery<E> query = getEntityManager().createQuery(filteringCriteria);
        return query.getResultList();
    }

    private CriteriaQuery<E> createFilteringCriteria(String queryString) {
        RSQLVisitor<CriteriaQuery<E>, EntityManager> visitor = getJpaCriteriaQueryVisitor();
        CriteriaQuery<E> query = getCriteriaQuery(queryString, visitor);
        return query;
    }

    @SneakyThrows
    private <T> CriteriaQuery<T> getCriteriaQuery(String queryString, RSQLVisitor<CriteriaQuery<T>, EntityManager> visitor) {
        Node rootNode;
        rootNode = new RSQLParser().parse(queryString);
        return rootNode.accept(visitor, getEntityManager());
    }

    protected abstract JpaCriteriaQueryVisitor<E> getJpaCriteriaQueryVisitor();

    protected abstract EntityManager getEntityManager();
}

Let’s walk through a code above and discuss it in detail. Method getCriteriaQuery receives two arguments, first one – RSQL-query itself, and second argument – instance of RSQLVisitor. RSQLVisitor is used to walk through the parse tree, which is actually, representation of passed RSQL-query. You can read more about pattern Visitor, and Parse Tree . The method returns JPA-request, defined by Criteria API, which, in turn, can be executed by EntityManager.

Now, we can define Spring-repository, named OrderRepo:

@Repository
public class OrderFilterRepo extends AbstractFilterRepo<Order> {

    private final EntityManager entityManager;

    @Autowired
    public OrderFilterRepo(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    @Override
    protected JpaCriteriaQueryVisitor<Order> getJpaCriteriaQueryVisitor() {
        return new JpaCriteriaQueryVisitor<Order>();
    }

    @Override
    protected EntityManager getEntityManager() {
        return entityManager;
    }
}

Create Order Rest Controller

For demonstration purposes, we will implement only one REST-endpoint, which receives RSQL-based query and returns order list corresponding to the request.

@RestController
public class OrderRestController {

    private final OrderFilterRepo orderFilterService;

    @Autowired
    public OrderRestController(OrderFilterRepo orderFilterService) {
        this.orderFilterService = orderFilterService;
    }

    @GetMapping(path = "/orders", produces = MediaType.APPLICATION_JSON_VALUE)
    private List<Order> getOrders(@RequestParam("query") String rQuery) {
        return orderFilterService.findByQuery(rQuery);
    }
}

Application launch

Now application can be launched. Let’s run it using command

./gradlew bootRun

Test cases:

1. Fetch all orders, which were made before 2020-02-01 by users older than 20:

Request: http://localhost:8080/orders?query=orderDate<'2020-02-01 00:00:00';user.age>20

Response:

[
   {
      "id":41,
      "title":"Order 1. User 4",
      "price":333,
      "orderDate":"2019-31-12",
      "user":{
         "id":4,
         "name":"Daniel",
         "age":56
      }
   }
]

2. Fetch all orders, with a price more than 300, made by users, who name starts with ‘Jo’:

Request: http://localhost:8080/orders?query=price>300;user.name==Jo*

Response:

[
   {
      "id":13,
      "title":"Order 3. User 1",
      "price":1010,
      "orderDate":"2019-31-12",
      "user":{
         "id":1,
         "name":"John",
         "age":20
      }
   },
   {
      "id":31,
      "title":"Order 1. User 3",
      "price":1010,
      "orderDate":"2020-31-03",
      "user":{
         "id":3,
         "name":"Joe",
         "age":18
      }
   }
]

Summary

So, the considered approach is flexible enough and can be used for the implementation of flexible/generic filtering mechanisms.

But it’s also important to keep in mind, that code, we’ve developed is not ready for use in the production. For a sake of simplicity, we’ve omitted the service layer and also access JPA-model directly – it is unacceptable.

Links

  1. Source code – https://github.com/dicelogics/rsql-server

660
Leave a Reply

avatar
  Subscribe  
newest oldest most voted
Notify of
BotrugH
Guest
BotrugH
Lisashoox
Guest
Amyshoox
Guest
Amyshoox
Guest
Kiashoox
Guest
Lisashoox
Guest
Amyshoox
Guest
Kiashoox
Guest
Lisashoox
Guest
Denshoox
Guest
Eyeshoox
Guest
Alanshoox
Guest
Markshoox
Guest
Kiashoox
Guest
Lisashoox
Guest
Paulshoox
Guest
Jasonshoox
Guest
Maryshoox
Guest
Carlshoox
Guest
Amyshoox
Guest
Carlshoox
Guest
Amyshoox
Guest
Paulshoox
Guest
Alanshoox
Guest
Kiashoox
Guest
Judyshoox
Guest
Maryshoox
Guest
Lisashoox
Guest
Denshoox
Guest
Paulshoox
Guest
Jasonshoox
Guest
Eyeshoox
Guest
Markshoox
Guest
Denshoox
Guest
Amyshoox
Guest
Markshoox
Guest
Maryshoox
Guest
Amyshoox
Guest
Carlshoox
Guest
Lisashoox
Guest
Judyshoox
Guest
Judyshoox
Guest
Amyshoox
Guest
Kiashoox
Guest
Carlshoox
Guest
Kiashoox
Guest
Eyeshoox
Guest
Jasonshoox
Guest
Denshoox
Guest
Alanshoox
Guest