Translate RSQL query into org.springframework.data.jpa.domain.Specification or com.querydsl.core.types.Predicate and support entities association query.
rsql-jpa-specification supports SpringBoot 3 since version 6.x. (Contributed by chriseteka)
For SpringBoot 2 users, please continue to use version 5.x.
https://oss.sonatype.org/#nexus-search;gav~io.github.perplexhub~rsql*
<dependency>
<groupId>io.github.perplexhub</groupId>
<artifactId>rsql-jpa-spring-boot-starter</artifactId>
<version>X.X.X</version>
</dependency>package com.perplexhub.repository;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import com.perplexhub.model.User;
public interface UserRepository extends JpaRepository<User, String>, JpaSpecificationExecutor<User> {
}package io.github.perplexhub.rsql;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.transaction.annotation.EnableTransactionManagement;
@EnableJpaRepositories(basePackages = { "io.github.xxx.yyy.repository" })
@EnableTransactionManagement
@SpringBootApplication
public class Application {
public static void main(String[] args) throws Exception {
SpringApplication.run(Application.class, args);
}
}
<dependency>
<groupId>io.github.perplexhub</groupId>
<artifactId>rsql-querydsl-spring-boot-starter</artifactId>
<version>X.X.X</version>
</dependency>package com.perplexhub.repository;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;
import com.perplexhub.model.User;
public interface UserRepository extends JpaRepository<User, String>, JpaSpecificationExecutor<User>, QuerydslPredicateExecutor<User> {
} <properties>
<spring-boot.version>3.0.0</spring-boot.version>
<spring-data-releasetrain.version>2022.0.0</spring-data-releasetrain.version>
<querydsl.version>4.1.4</querydsl.version>
</properties>filter = "id=bt=(2,4)";// id>=2 && id<=4 //between
filter = "id=nb=(2,4)";// id<2 || id>4 //not between
filter = "company.code=like=em"; //like %em%
filter = "company.code=ilike=EM"; //ignore case like %EM%
filter = "company.code=icase=EM"; //ignore case equal EM
filter = "company.code=notlike=em"; //not like %em%
filter = "company.code=inotlike=EM"; //ignore case not like %EM%
filter = "company.code=ke=e*m"; //like %e*m%
filter = "company.code=ik=E*M"; //ignore case like %E*M%
filter = "company.code=nk=e*m"; //not like %e*m%
filter = "company.code=ni=E*M"; //ignore case not like %E*M%
filter = "company.code=ic=E^^M"; //ignore case equal E^^M
filter = "company.code==demo"; //equal
filter = "company.code=='demo'"; //equal
filter = "company.code==''"; //equal to empty string
filter = "company.code==dem*"; //like dem%
filter = "company.code==*emo"; //like %emo
filter = "company.code==*em*"; //like %em%
filter = "company.code==^EM"; //ignore case equal EM
filter = "company.code==^*EM*"; //ignore case like %EM%
filter = "company.code=='^*EM*'"; //ignore case like %EM%
filter = "company.code!=demo"; //not equal
filter = "company.code=in=(*)"; //equal to *
filter = "company.code=in=(^)"; //equal to ^
filter = "company.code=in=(demo,real)"; //in
filter = "company.code=out=(demo,real)"; //not in
filter = "company.id=gt=100"; //greater than
filter = "company.id=lt=100"; //less than
filter = "company.id=ge=100"; //greater than or equal
filter = "company.id=le=100"; //less than or equal
filter = "company.id>100"; //greater than
filter = "company.id<100"; //less than
filter = "company.id>=100"; //greater than or equal
filter = "company.id<=100"; //less than or equal
filter = "company.code=isnull="; //is null
filter = "company.code=null="; //is null
filter = "company.code=na="; //is null
filter = "company.code=nn="; //is not null
filter = "company.code=notnull="; //is not null
filter = "company.code=isnotnull="; //is not null
filter = "company.code=='demo';company.id>100"; //and
filter = "company.code=='demo' and company.id>100"; //and
filter = "company.code=='demo',company.id>100"; //or
filter = "company.code=='demo' or company.id>100"; //orSyntax Reference: RSQL / FIQL parser
Pageable pageable = PageRequest.of(0, 5); //page 1 and page size is 5
repository.findAll(RSQLSupport.toSpecification(filter));
repository.findAll(RSQLSupport.toSpecification(filter), pageable);
repository.findAll(RSQLSupport.toSpecification(filter, true)); // select distinct
repository.findAll(RSQLSupport.toSpecification(filter, true), pageable);
// use static import
import static io.github.perplexhub.rsql.RSQLSupport.*;
repository.findAll(toSpecification(filter));
repository.findAll(toSpecification(filter), pageable);
repository.findAll(toSpecification(filter, true)); // select distinct
repository.findAll(toSpecification(filter, true), pageable);
// property path remap
filter = "compCode=='demo';compId>100"; // "company.code=='demo';company.id>100" - protect our domain model #10
Map<String, String> propertyPathMapper = new HashMap<>();
propertyPathMapper.put("compId", "company.id");
propertyPathMapper.put("compCode", "company.code");
repository.findAll(toSpecification(filter, propertyPathMapper));
repository.findAll(toSpecification(filter, propertyPathMapper), pageable);sort = "id"; // order by id asc
sort = "id,asc"; // order by id asc
sort = "id,asc;company.id,desc"; // order by id asc, company.id desc
sort = "name,asc,ic" // order by name ascending ignore caserepository.findAll(RSQLSupport.toSort("id,asc;company.id,desc"));
// sort with custom field mapping
Map<String, String> propertyMapping = new HashMap<>();
propertyMapping.put("userID", "id");
propertyMapping.put("companyID", "company.id");
repository.findAll(RSQLSupport.toSort("userID,asc;companyID,desc", propertyMapping)); // same as id,asc;company.id,descSpecification<?> specification = RSQLSupport.toSpecification("company.name==name")
.and(RSQLSupport.toSort("company.name,asc,ic;user.id,desc"));
repository.findAll(specification);Pageable pageable = PageRequest.of(0, 5); //page 1 and page size is 5
repository.findAll(RSQLSupport.toPredicate(filter, QUser.user));
repository.findAll(RSQLSupport.toPredicate(filter, QUser.user), pageable);
// use static import
import static io.github.perplexhub.rsql.RSQLSupport.*;
repository.findAll(toPredicate(filter, QUser.user));
repository.findAll(toPredicate(filter, QUser.user), pageable);
// property path remap
filter = "compCode=='demo';compId>100"; // "company.code=='demo';company.id>100" - protect our domain model #10
Map<String, String> propertyPathMapper = new HashMap<>();
propertyPathMapper.put("compId", "company.id");
propertyPathMapper.put("compCode", "company.code");
repository.findAll(toPredicate(filter, QUser.user, propertyPathMapper));
repository.findAll(toPredicate(filter, QUser.user, propertyPathMapper), pageable);SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
RSQLJPASupport.addConverter(Date.class, s -> {
try {
return sdf.parse(s);
} catch (ParseException e) {
return null;
}
});String rsql = "createDate=dayofweek='2'";
RSQLCustomPredicate<Long> customPredicate = new RSQLCustomPredicate<>(new ComparisonOperator("=dayofweek="), Long.class, input -> {
Expression<Long> function = input.getCriteriaBuilder().function("ISO_DAY_OF_WEEK", Long.class, input.getPath());
return input.getCriteriaBuilder().lessThan(function, (Long) input.getArguments().get(0));
});
List<User> users = userRepository.findAll(toSpecification(rsql, Arrays.asList(customPredicate)));String rsql = "name=around='May'";
RSQLCustomPredicate<String> customPredicate = new RSQLCustomPredicate<>(new ComparisonOperator("=around="), String.class, input -> {
if ("May".equals(input.getArguments().get(0))) {
return input.getPath().in(Arrays.asList("April", "May", "June"));
}
return input.getCriteriaBuilder().equal(input.getPath(), (String) input.getArguments().get(0));
});
List<User> users = userRepository.findAll(toSpecification(rsql, Arrays.asList(customPredicate)));String rsql = "company.id=between=(2,3)";
RSQLCustomPredicate<Long> customPredicate = new RSQLCustomPredicate<>(new ComparisonOperator("=between=", true), Long.class, input -> {
return input.getCriteriaBuilder().between(input.getPath().as(Long.class), (Long) input.getArguments().get(0), (Long) input.getArguments().get(1));
});
List<User> users = userRepository.findAll(toSpecification(rsql, Arrays.asList(customPredicate)));String rsql = "city=notAssigned=''";
RSQLCustomPredicate<String> customPredicate = new RSQLCustomPredicate<>(new ComparisonOperator("=notAssigned="), String.class, input -> {
return input.getCriteriaBuilder().isNull(input.getRoot().get("city"));
});
List<User> users = userRepository.findAll(toSpecification(rsql, Arrays.asList(customPredicate)));For the LIKE statement in different RDBMS, the most commonly used special characters are:
%: Represents any sequence of zero or more characters. For example,LIKE '%abc'would match any string ending withabc._: Represents any single character. For example,LIKE 'a_c'would match a three-character string starting withaand ending withc.
%and_: Function in the same way as in MySQL/MariaDB and PostgreSQL.[]: Used to specify a set or range of characters. For instance,LIKE '[a-c]%'would match any string starting witha,b, orc.^: Used within[]to exclude characters. For example, LIKE '[^a-c]%' would match any string not starting witha,b, orc.
%and_: Function similarly to MySQL/MariaDB and PostgreSQL.ESCAPE: Allows specifying an escape character to include % or _ literally in the search. For example,LIKE '%\_%' ESCAPE '\'would match a string containing an underscore.
To use escape character in RSQL, you must use QuerySupport to build the Specification with appropriate escape character.
char escapeChar = '$';
QuerySupport query = QuerySupport.builder()
.rsqlQuery("name=like='" + escapeChar + "%'")
.likeEscapeCharacter(escapeChar)
.build();
List<Company> users = companyRepository.findAll(toSpecification(query));Above RSQL with default escape character $ for searching string containing _:
my_table.my_column=like='$_'Will produce the following SQL:
SELECT * FROM my_table WHERE my_column LIKE '%$_%' ESCAPE '$'It's possible to make rsql queries on jsonb fields. For example, if you have a jsonb field named data in your entity, you can make queries like this:
{
"data": {
"name": "demo",
"user" : {
"id": 1,
"name": "demo"
},
"roles": [
{
"id": 1,
"name": "admin"
},
{
"id": 2,
"name": "user"
}
]
}
}String rsql = "data.name==demo";
List<User> users = userRepository.findAll(toSpecification(rsql));String rsql = "data.user.id==1";
List<User> users = userRepository.findAll(toSpecification(rsql));String rsql = "data.roles.id==1";
List<User> users = userRepository.findAll(toSpecification(rsql));The library use jsonb_path_exists function under the hood.
Json primitive types are supported such as
- string
- number
- boolean
- array
Since Postgresql 13 jsonb supports temporal values with datetime() function.
As Date time values are string in jsonb, you can make queries on them as well.
You must use the ISO 8601 format for date time values.
If your request conform timezone pattern, the library will use `jsonb_path_exists_tz.
Then consider the timezone consideration of the official documentation
RSQL can call a stored procedure with the following syntax for both search and sort.
In order to be authorized to call a stored procedure, it must be whitelisted and not blacklisted.
The only way to whitelist or blacklist a stored procedure is to use the QuerySupport when performing the search or the SortSupport when performing the sort.
String rsql = "@concat[greetings|#123]=='HELLO123'";
QuerySupport querySupport = QuerySupport.builder()
.rsqlQuery(rsql)
.procedureWhiteList(List.of("concat", "upper"))
.build();
List<Item> companies = itemRepository.findAll(toSpecification(querySupport));Regex like expression can be used to whitelist or blacklist stored procedure.
A procedure must be prefixed with @ and called with [] for arguments.
@procedure_name[arg1|arg2|...]
Arguments are separated by | and can be:
- constant (null, boolean, number, string), prefixed with
# - column name
- other procedure call
@procedure_name[arg1|arg2|...]
@procedure_name[column1|column2|...]
@procedure_name[@function_name[arg1|arg2|...]|column1|#textvalue|#123|#true|#false|#null]
For text value, since space is not supported by RSQL, you can use \t to replace space.
String rsql1 = "@upper[code]==HELLO";
String rsql2 = "@concat[@upper[code]|name]=='TESTTest Lab'";
String rsql3 = "@concat[@upper[code]|#123]=='HELLO123'";String sort1 = "@upper[code],asc";
String sort2 = "@concat[@upper[code]|name],asc";
String sort3 = "@concat[@upper[code]|#123],asc";