REST DB querying system

Introduction

DB requests use a pagination and dynamic filter system. You can customize your query by using the queryparameters in URI

The following parameters are supported:

• deleted : Also fetch entities marked as deleted (Admins only) (default false)
• headerOnly : Fetch only the pagination header (Used to count entities) (default false)
• offset : Offset of the query. (default 0)
• limit : Limit of the query. (default 10)
• filter : A list of filters that must be applied to the request.
• order : A list of orders that must be applied to the request.

Pagination information is returned in the response header.

Important files and methods

• GenericRepository: findAllLazyLoading() and countAllLazyLoading() are building the query using QueryDSL, and count the total entities to include in pagination info. handleFilter() and handleOrder() handle filter and order (Incredible). They can be overridden in each repository to handle custom orders and filters.
• LazyLoadingConfiguration : Parse the client request queryparams and store them to be used in the DB query. This class can also be used to build a query Programmatically.
• LazyLoadingConfigurationToDSL : Convert the LazyLoadingConfiguration to QueryDSL objects so they can be used in the final query.
• LazyLoadingResult : return of findAllLazyLoading(). Store the data returned by the query and pagination info

Usage

To build a query, you must :

• Build a LazyLoadingConfiguration object (you can pass the queryparameters as parameter to parse them automatically)
• Add some desired parameters programmatically
• Run the findAllLazyLoading() method.

Exemple :

// Building the LazyLoadingConfiguration. Queryparams as parameter.
LazyLoadingConfiguration config = new LazyLoadingConfiguration(uriInfo.getQueryParameters(), locale, false, false);

// Adding a filter programmatically (using QueryDSL to have typeSafe key). 
// You can also write : String userIDKey = "user.id";
String userIDKey = new StringJoiner(".")
			.add(QNotification.notification.user.getMetadata().getName())
			.add(QUser.user.id.getMetadata().getName())
			.toString();
config.addFilter(userIDKey, LazyLoadingFilterOperator.EQUALS, userID);

//Running the query
LazyLoadingResult<Notification> res = notifService.findLazyLoading(config);

Locale

The locale is fetched in the Accept-Language header (https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/Accept-Language) and stored in the CurrentRequest class. It can then be passed in the LazyLoadingConfiguration constructor.

When no locale is found in the header, the default locale is used.

Pagination

You can add offset and limit parameters to the query.

ex : http://host.com/api/users?offset=10&limit=50

This query will fetch 50 users, starting by the 11th.

Response Header

The response header contains pagination information:

• X-Total-Count : Total number of entities that this query can fetch.
• X-Page-Count : Number of entities actually fetched by the query.
• X-Page-Limit : Number of entities that a page can contain. (reminder of the limit parameter)

You can fetch this header only (and no other data) by adding the headerOnly=true queryparam. It allows to run "COUNT" queries using the same system

Filters

You can add one or many filters to apply to the query

Format

Filter queryparams must be formatted as followed:

?filter=key1=op1:val1!key2=op2:val2,val3...

The key can contains joins, separated by dots.

ex : orga.users.id

Custom filters

You can add custom filters (written in the entity repository) by omitting the operator.

?filter=customKey=val

These custom filters are already implemented for every entities:

Filter	Keyword	Description	Exemple
LOCALIZED_TEXT	localizedText	Find a string in the correct locale text LocalizedText	?filter=title.localizedText=blabla

Main key characters

• . : Create a join in a key
• ! : Separate filters when there are many
• = : Separate the key and the rest (operator and value)
• : : Separate operator and value
• , : Separate values when there are many

Supported operators

** To be noted ** :

• btw and nbtw operator must have two values -in and nin operator must have a list of values
• null et notNull operator must have no value

Operator	Keyword	Description	exemple
EQUALS	eq	Check if the key is equals to the value	?filter=firstname=eq:paul
NOT EQUALS	neq	Check if the key is not equals to the value	?filter=firstname=neq:fred
CONTAINS	cts	Check if the key contains the value	?filter=firstnam=cts:au
LOWER THAN	lt	Check if the key is lower than the value	?filter=age=lt:18
NULL OR LOWER THAN	lt	Check if the key is null or lower than the value	?filter=age=nullOrLt:18
LOWER THAN EQUALS	lte	Check if the key is lower or equals to the value	?filter=age=lte:18
NULL OR LOWER THAN EQUALS	lte	Check if the key is null or lower or equals to the value	?filter=age=nullOrLte:18
GREATER THAN	gt	Check if the key is higher than the value	?filter=age=gt:18
NULL OR GREATER THAN	gt	Check if the key is null or higher than the value	?filter=age=nullOrGt:18
GREATER THAN EQUALS	gte	Check if the key is greater or equals to the value	?filter=age=gte:18
NULL OR GREATER THAN EQUALS	gte	Check if the key is null or greater or equals to the value	?filter=age=nullOrGte:18
IN	in	Check if the key is equals to one of the values	?filter=firstname=in:paul,fred
NOT IN	nin	Check if the key is not equals any of the values	?fitler=firstname=nin:paul,fred
BETWEEN	btw	Check if the key is between the two values	?filter=age=btw:10,18
NOT BETWEEN	nbtw	Check if the key is not between the two values	?fitler=age=nbtw:10,18
NULL	null	Check if the key is null	?filter=address=null
NOT NULL	notNull	Check if the key is not null	?filter=address=notNull

Orders

You can add orders to the query.

Format

Order queryparams must be formatted as followed:

?order=key1:op1!key2:op2

The key can contain joins, separated by dots.

ex : orga.users.id

Main keywords

• . : Create a join in a key
• ! : Separate orders when there are many
• : : Separate the key and the operator

Supported operators

Operator	Keyword	Description	exemple
ASCENDANT	asc	Sort ascending	?order=firstname:asc
DESCENDANT	desc	Sort descending	?order=firstname:desc