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