It has been a long time for the company to do this general query function. Today, I would like to summarize how the general query is designed step by step and what is the original intention of its design. I’m going to approach this article in the form of a syllogism: What is a generic query? Why? How do you do that?

origin

As we all know, almost every company (especially Internet companies) has an internal system, which generally has the following characteristics:

  • The system is used by internal employees
  • The system mainly provides necessary data support for other systems
  • There are no special requirements for the interface UI, simple and single style
  • Pay attention to the data

(Note that all the screenshots in this article are from the development environment and the data is meaningless):

Or this:

Does something like this sound familiar to you? That’s right. That’s what we’re talking about today. Spring Boot and JPA are used at the back end of the technical stack, and Element – UI + vue is used at the front end of MySQL

Why generic queries

Before we define what a generic query is, let’s talk about why we need a generic query, and then step by step what a generic query is. For a function like the screenshot above, we usually go through the following process from the requirement to the launch:

  • Requirements from operations or others
  • Product Manager Design
  • Technology development
  • A functional test
  • The deployment of online

And technology development that piece may need front and back end coordination, the front end needs to realize the page list, the back end needs to develop the interface to provide the list of data required, and then the front and back end respectively self-test interface joint adjustment and so on.

Does a simple feature like this really require so many steps and so many people to get it up and running? In retrospect, such a list features a similarly repetitive front and back end.

The front end is a list, the difference is the data, table header, search criteria. The back-end is generally to provide an interface, interface implementation is generally to write a SQL from the database to obtain data, generally do not need to do special processing of the data directly back to the front end, the difference is the SQL script is not the same.

So, is there a way to solve this problem? The answer is yes, thanks to generic query applications.

What are generic queries

Through the above analysis, we are in urgent need of a function that can simplify the complicated development tasks above, because the front and back end to achieve this list function code is very similar or even repeated. I think in software design everything that is repetitive can be reused. So the general query is best to achieve zero development, simple testing, zero deployment online, only need a general simple configuration can achieve such a list function.

How to implement the general query function

What are the main aspects of designing a generic query? And how to design? We can think of the front and rear ends separately

The front end

Similarities:

  • It’s a consistent list

Differences:

  • The data in the list is different
  • Different display methods of list data (text display, picture display, etc.)
  • The header of the list is different
  • Lists are retrieved under different conditions
  • List pages function differently (exportExcel, add, etc.)
  • .

The back-end

Similarities:

  • Is commonSQLRetrieves data from the database and returns results

Differences:

  • Of the querySQLDifferent statements
  • Query conditions, accept different parameters
  • Returns different results
  • .

Design ideas

From the above comparison, consider the following: write a common set of code on both the front and back ends, and all the list needs is to configure the data SQL, configure the table style, configure the search criteria, configure the columns in the table, and then write the configuration to the database. When a function is loaded, the configuration is fetched from the database, the business SQL is fetched, and the SQL is executed to fetch the data in the list.

Here’s an example

Such as I have a list of personnel information according to the multiple latitude function, we can put the personnel information to the database, the SQL script configuration into the personnel information request list page when a back-end data, we find the corresponding SQL access to personnel information and execute the statement, and then returns the results back to the front is not ok, So it’s very important to find what particular business SQL is based on. Normally we find a record by ID, but obviously you can’t do that here because the front end can’t determine what the ID is. The best way to do this is to use the route Key of the front page as the Key value of this recordEach page in the front end has a unique route that we can use to determine which business SQL statement to execute. This solves the problem of executing SQL. Input parameters can be specified by parameter name and parameter value, and output parameters are finally given to the front end of JSON. We can return a Map uniformly.

If the design idea is not too understand it does not matter, on the code:

Database table structure design:

-- CREATE TABLE IF NOT EXISTS 'T_QUERY_SQL_CONFIG' (' id 'BIGint (20) NOT NULL AUTO_INCREMENT COMMENT' 'route_key' varchar(150) DEFAULT NULL COMMENT 'routing key',' config_name 'varchar(100) DEFAULT NULL COMMENT 'SQL config name ', 'rule_key' varchar(100) DEFAULT NULL COMMENT 'KEY in the table ', 'data_url' varchar(255) DEFAULT NULL COMMENT 'aged' tinyInt (1) NOT NULL DEFAULT '0' COMMENT 'paged ', 'exported' tinyint(1) NOT NULL DEFAULT '0' COMMENT 'exported ', Form_query_id bigINT (20) DEFAULT NULL COMMENT 'Query table ID', 'form_del_id' bigint(20) DEFAULT NULL COMMENT 'ID', Query_need_condition tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Whether to query the list with conditions ', Component_path vARCHar (255) DEFAULT NULL COMMENT "component_path varchar(255) DEFAULT NULL COMMENT", button_config varchar(2000) DEFAULT NULL COMMENT "JSON", Js_value varchar(500) DEFAULT NULL COMMENT ' 'edit_operation' tinyint(2) NOT NULL DEFAULT '0' COMMENT ' 'del_operation' tinyint(2) NOT NULL DEFAULT '0' COMMENT 'Delete operation 1 'del_URL' varchar(255) DEFAULT NULL COMMENT 'delete column URL', Table_config varchar(800) DEFAULT NULL COMMENT 'table configuration' 'deleted' tinyInt (1) NOT NULL DEFAULT '0' COMMENT 'Logical deletion flag ', 'version' bigint(20) NOT NULL DEFAULT '0' COMMENT 'version ',' created_by 'varchar(20) DEFAULT NULL COMMENT' creator ', 'created_date' TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 'updated_by' varchar(20) DEFAULT NULL COMMENT 'updated_date ', `updated_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE INDEX unique_index_routeKey (route_key ASC), PRIMARY KEY (' id ') USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET= UTf8MB4 COMMENT=' utf8MB4 '; -- CREATE TABLE IF NOT EXISTS 'T_QUERY_COLUMN_CONFIG' (' id 'BIGint (20) NOT NULL AUTO_INCREMENT COMMENT' 'sql_config_id' bigint(20) DEFAULT NULL COMMENT 'general query configuration ID',' column_name 'varchar(100) DEFAULT NULL COMMENT' column ID', 'column_display' varchar(100) DEFAULT NULL COMMENT '表 示中文 ', 'sorted' tinyint(1) NOT NULL DEFAULT '0' COMMENT 'sorted ', 'display_way' tinyint(2) NOT NULL DEFAULT '0' COMMENT 'The column is displayed as 0. Image ', 'searchable' tinyint(1) NOT NULL DEFAULT '0' COMMENT 'whether to search ', 'search_type' tinyint(2) NOT NULL DEFAULT '0' COMMENT 'Search type 0. 2. Time plug-in ', 'json_value' vARCHAR (500) DEFAULT NULL COMMENT ' 'placeholder' vARCHar (100) DEFAULT NULL COMMENT 'placeholder ', 'column_width' int(11) DEFAULT NULL COMMENT '表 width ', general_form vARCHar (500) DEFAULT NULL COMMENT' 表 width ', General_query varchar(500) DEFAULT NULL COMMENT ' ', js_value varchar(500) DEFAULT NULL COMMENT ' ', 'js_operated' tinyint(1) NOT NULL DEFAULT '0' COMMENT 'whether JS operated ', 'exported' tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Exported ', 'deleted' TINyInt (1) NOT NULL DEFAULT '0' COMMENT 'Logical deletion flag ',' version 'BigINT (20) NOT NULL DEFAULT '0' COMMENT' version ', 'created_by' varchar(20) DEFAULT NULL COMMENT 'creator ',' created_date 'TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, 'updated_by' varchar(20) DEFAULT NULL COMMENT 'updated_by' DEFAULT NULL COMMENT ', `updated_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (' id ') USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET= UTf8MB4 COMMENT=' utf8MB4 ';Copy the code

T_QUERY_SQL_CONFIG is a general query configuration table, mainly used to configure list behavior, such as whether pagination, whether excel can be exported, list style configuration, corresponding front-end routing, etc. The T_QUERY_COLUMN_CONFIG table configures the columns in the table. T_QUERY_SQL_CONFIG and T_QUERY_COLUMN_CONFIG are one-to-many, that is, one column configuration corresponds to multiple column configurations in the table.

General query configuration phase

I combined with the front-end UI design to explain how to design the configuration of the general query

The above figure can be divided into three parts: data source configuration, table configuration, and other configuration data can come from SQL statements or interfaces, but SQL statements are the most common.

Fill in the SQL: This SQL is the service SQL that performs a certain function. For example, this SQL is to query the popover list, and this SQL is to obtain the popover list.

The name of the configuration: Just write down the name

Corresponding routing: This is very important, is corresponding to the front of the page routing, my design is each page routing corresponding to a list page, naturally also corresponding to a query function. For example, the following function fetches the list of topics on the page and uses the front end route to locate the configuration of the entire general query, so the route Key is unique.

Button configuration: it is mainly used to configure the buttons above the list. The topic button is generated by this configuration as shown below.

Asynchronous component Path: VUE of front-end custom components, need this reason is to support some complex scenarios, such as can click on a column in the list, and bring up a new page after click, general query is unable to do it this way, because this is not fixed, I can’t know this list by clicking on what needs to be done after the operation, therefore, The solution here is to leave the unknown to the user to implement, which is also an extension to the general query.