Data condition query and paging

The previous document mainly introduces metadata configuration, including form definition, table relationship management, and form data entry. This paper mainly introduces the implementation of data query and paging in crud API.

outline

Data query API

Data query mainly refers to retrieving the qualified data list according to the input conditions. If the amount of data is large, pagination needs to be considered.

The API is / api/business/{name}, where name is the plural form of object name (compatible object name). The query parameters are as follows:

parameter	type	describe
select	string	Select the fields and associated sub tables required for query. All fields and associated sub tables are queried by default
expand	string	Select the associated primary table to expand. By default, the associated primary table field only queries id and name
search	string	The full-text search keyword is realized through the built-in system field full-text index fullTextBody
filter	string	Intelligent query Condition, the format is the string serialized by Condition object JSON
orderby	string	Sort by, ASC and DESC
offset	int32	Paging start position
limit	int32	Number per page
Field 1	Object	Finally, it is converted to the = operator in mysql
Field 2	Object	Finally, it is converted to the = operator in mysql
......	Object	Finally, it is converted to the = operator in mysql

Field 1, field 2 The relationship between is AND

Query customer

Field query

The customer enters four pieces of data in total

Query by name, mobile phone, email and membership card number respectively. The request url is as follows:
https://demo.crudapi.cn/api/business/customer?offset=0&limit=10&name= Liu Chan & mobile = 13088889999 & email= liushan@crudapi.cn &membershipNo=VIP000000004
Found the customer Liu Chan

Full text search

Search the full text of the customer table through the keyword "Liu", and the request url is as follows:
https://demo.crudapi.cn/api/business/customer?offset=0&limit=10&search= Liu
Find the customers Liu Chan and Liu Bei. The specific principle is as follows: the fields with the "queryable" attribute enabled in the form are finally spliced into a full-text retrieval text fullTextBody, which is updated in time when inserting and updating data. In this way, the keyword query can be realized by using the mysql full-text retrieval function, and the final SQL statement is as follows:

SELECT * FROM `ca_customer` 
WHERE ((MATCH(`fullTextBody`) AGAINST('Zhang*' IN BOOLEAN MODE))) 
ORDER BY id DESC LIMIT 0, 10

Similarly, the keyword "liu" is used to retrieve the full text of the customer table, and the data can also be queried through mailbox matching.

Intelligent query principle

Define interface Condition, leaf Condition LeafCondition inherits Condition, AND composite Condition inherits Condition. CompositeCondition is composed of multiple conditions. Any form of complex query Condition can be realized through AND and OR operations. In this way, intelligent query is realized by using combination mode,

public enum OperatorTypeEnum {
    EQ,
    NE,
    LIKE,
    IN,
    INSELECT,
    SEARCH,
    GE,
    GT,
    LE,
    LT,
    BETWEEN
}

Database query operators include equal to, greater than, LIKE, etc

@JsonPropertyOrder(alphabetic = true)
@JsonIgnoreProperties(ignoreUnknown = true)
@JsonTypeInfo(use = JsonTypeInfo.Id.NAME, include = JsonTypeInfo.As.PROPERTY,property = "name")
@JsonSubTypes(value = {
    @JsonSubTypes.Type(value = CompositeCondition.class, name = "C"),
    @JsonSubTypes.Type(value = LeafCondition.class, name = "L")
})

public interface Condition {
    String toQuerySql();

    List<Object> toQueryValues();
}

Conditional interface

@JsonPropertyOrder(alphabetic = true)
@JsonIgnoreProperties(ignoreUnknown = true)
@Data
public class LeafCondition implements Condition {
    private String name = "L";

    private String columnName;

    @JsonProperty("operatorType")
    private OperatorTypeEnum operatorType;

    @JsonProperty("values")
    private List<Object> valueList = new ArrayList<Object>();
}

Leaf condition

@JsonPropertyOrder(alphabetic = true)
@JsonIgnoreProperties(ignoreUnknown = true)
@Data
public class CompositeCondition implements Condition {
    private String name = "C";

    @JsonProperty("conditionType")
    private ConditionTypeEnum conditionType = ConditionTypeEnum.AND;

    @JsonProperty("conditions")
    private List<Condition> conditionList = new ArrayList<Condition>();
}

Combination condition

Intelligent query verification

Postman verifies that the conditions of mobile equal to 13622228888 are as follows:

{
    "name": "L",
    "columnName": "mobile"
    "operatorType": "EQ",
    "values": ["13622228888"]
}

The serialized value is:

{"name":"L","columnName":"mobile","operatorType":"EQ","values":["13622228888"]}

After encoding by encodeURIComponent:

%7B%22name%22%3A%22L%22%2C%22columnName%22%3A%22mobile%22%2C%22operatorType%22%3A%22EQ%22%2C%22values%22%3A%5B%2213622228888%22%5D%7D

The customer Guan Yu was finally queried because the mobile phone number met the conditions. Similarly, more complex combination conditions can be constructed.

Field select

By default, all fields will be selected. If select is not specified, it means all

If you specify a field, you can query some fields, such as only id, name and mobile. Other fields and sub table profile s do not need to be queried, which can save time and data size.

Association table expansion

In the sub master relationship, only the id and name of the main table are queried by default to avoid too deep query level of the main table.

If the name of the expand associated object is specified, all fields in the main table will be queried, which is applicable to scenarios that do not need to delay loading. For example, when querying customer data, the complete information of customer will be queried together.

Summary

This paper introduces all the parameters of the list query API, including intelligent query, full-text retrieval and field selection. Crud API system realizes object query and paging through configuration.

demo attached

This system is a product level zero code platform, which is different from the automatic code generator. It does not need to generate business codes such as Controller, Service, Repository and Entity. It can be used when the program runs. The real 0 code can cover the basic CRUD RESTful API that has nothing to do with the business.

Official website address: https://crudapi.cn
Test address: https://demo.crudapi.cn/crudapi/login