[case10] Use RSQL to Realize End-to-End Dynamic Query

  jpa

Order

This article mainly studies how to use RSQL to realize dynamic data query from front end to back end.

RSQL

RSQL(RESTful Service Query Language) is a superset of Feed Item Query Language (FIQL) and a query language for RESTful services. Here we usersql-jpaTo practice, it depends onrsql-parserTo parse the RSQL syntax, and then escape the parsed RSQL to JPA’s Specification.

maven

<dependency>
    <groupId>com.github.tennaito</groupId>
    <artifactId>rsql-jpa</artifactId>
    <version>2.0.2</version>
</dependency>

It relies on rsql-parser

Example

domain

@Entity
public class TodoTask {

    @javax.persistence.Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;

    private Long totalNum = 0L;

    private String title;

    @Version
    private Long version;

    //...
}    

controller

@RestController
@RequestMapping("/rsql")
public class RsqlController {

    @Autowired
    TodoTaskService todoTaskService;

    /**
     * @param condition
     * @param page
     * @param size
     * @return
     */
    @GetMapping("")
    public Page<TodoTask> query(@RequestParam String condition,
                                @RequestParam(required = false,defaultValue = "0") int page,
                                @RequestParam(required = false,defaultValue = "20") int size){
        return todoTaskService.query(condition,new PageRequest(page,size));
    }
}

service

@Component
public class TodoTaskService {

    @Autowired
    private EntityManager entityManager;

    public Page<TodoTask> query(String condition, Pageable pageable){
        // 1.Create the JPA Visitor
        RSQLVisitor<CriteriaQuery<TodoTask>, EntityManager> visitor = new JpaCriteriaQueryVisitor<TodoTask>();
        // 2.Parse a RSQL into a Node
        Node rootNode = new RSQLParser().parse(condition);
        // 3.Create CriteriaQuery
        CriteriaQuery<TodoTask> criteriaQuery = rootNode.accept(visitor, entityManager);
        List<TodoTask> total = entityManager.createQuery(criteriaQuery).getResultList();
        List<TodoTask> resultList = entityManager.createQuery(criteriaQuery)
                .setFirstResult(pageable.getOffset()).setMaxResults(pageable.getPageSize()).getResultList();

        return new PageImpl<>(resultList,pageable, total.size());
    }
}

EntityManager is directly used here to query. There are three steps in total, 1 is to create RSQLVisitor, 2 is to parse condition to node, 3 is to create CriteriaQuery based on Node, and then you can query based on CriteriaQuery.

run

curl -i http://localhost:8080/rsql?condition=title==hello
curl -i http://localhost:8080/rsql?condition=totalNum%3E50
curl -i http://localhost:8080/rsql?condition=totalNum%3E50;title==hello

Where %3E is the url escape of >, if there are multiple and conditions, use; Separated

Summary

RSQL is a powerful abstract language that can be used as a general query language for REST services. spring-data-rest also provides similar functions and is more powerful. However, this kind of query does not seem to support or query. In addition, when there is a large amount of data, going directly to db query may cause slow query, because not all fields have indexes, but it is more suitable for going to elasticsearch.

doc