/rsql-jpa-specification

Java Library to Translate RSQL into Spring Data JPA Specification and QueryDSL Predicate

Primary LanguageJavaMIT LicenseMIT

rsql-jpa-specification

Sonatype Nexus (Releases) Sonatype Nexus (Snapshots)

Release Workflow Status Snapshot Workflow Status PR Workflow Status

Translate RSQL query into org.springframework.data.jpa.domain.Specification or com.querydsl.core.types.Predicate and support entities association query.

SpringBoot 3 Support

rsql-jpa-specification supports SpringBoot 3 since version 6.x. (Contributed by chriseteka)

For SpringBoot 2 users, please continue to use version 5.x.

Supported Operators

Since version 5.0.5, you can define your own operators and customize the logic via RSQLCustomPredicate.

Maven Repository

https://oss.sonatype.org/#nexus-search;gav~io.github.perplexhub~rsql*

Add rsql-jpa-spring-boot-starter for RSQL to Spring JPA translation

Maven dependency for rsql-jpa-spring-boot-starter 

  <dependency>
    <groupId>io.github.perplexhub</groupId>
    <artifactId>rsql-jpa-spring-boot-starter</artifactId>
    <version>X.X.X</version>
  </dependency>

Add JpaSpecificationExecutor to your JPA repository interface classes

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> {
}

Add rsql-querydsl-spring-boot-starter for RSQL to Spring JPA and QueryDSL translation

Maven dependency for rsql-querydsl-spring-boot-starter 

  <dependency>
    <groupId>io.github.perplexhub</groupId>
    <artifactId>rsql-querydsl-spring-boot-starter</artifactId>
    <version>X.X.X</version>
  </dependency>

Add JpaSpecificationExecutor and QuerydslPredicateExecutor to your JPA repository interface classes

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> {
}

Use below properties to control the version of Spring Boot, Spring Data and QueryDSL

  <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>

RSQL Syntax Reference

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"; //or

Syntax Reference: RSQL / FIQL parser

Spring Data JPA Specification

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 Syntax

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 case

Sort with JPA Specifications

repository.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,desc

Filtering and Sorting with JPA Specification

Specification<?> specification = RSQLSupport.toSpecification("company.name==name")
    .and(RSQLSupport.toSort("company.name,asc,ic;user.id,desc"));

repository.findAll(specification);

QueryDSL Predicate (BooleanExpression)

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);

Custom Value Converter

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;
	}
});

Custom Operator & Predicate

String rsql = "createDate=dayofweek='2'";
RSQLCustomPredicate<Long> customPredicate = new RSQLCustomPredicate<>(new ComparisonOperator("=dayofweek="), Long.class, input -> {
	Expression<Long> function = input.getCriteriaBuilder().function("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)));