This is the 20th day of my participation in the November Gwen Challenge. Check out the details: The last Gwen Challenge 2021

1. The source of the problem

  1. In the development of often need to carry out a page cascade effect, the most common use is the provincial and municipal level three linkage effect, such as Beijing and then the Beijing subordinate district Fengtai District, Haidian District, Changping District… Return to the front

2. Solutions

This solution only involves the back end (PS: The main reason is that there are many projects separated from the front and back ends, and the front end will not be operated).

2.1 Database Design

CREATE TABLE `sys_china`  (
  `Id` int(11) NOT NULL COMMENT 'primary key id',
  `Name` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'name',
  `Pid` int(11) NULL DEFAULT NULL,
  `status` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '1' COMMENT '1: enabled,2: disabled '.PRIMARY KEY (`Id`) USING BTREE,
  INDEX `FK_CHINA_REFERENCE_CHINA`(`Pid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COMMENT = 'Provincial list' ;
Copy the code

2.2 Back-end code (Springboot +mysql)

Code Address:Github.com/Dr-Water/Le…The SQL code is also in the PROJECT’s SQL folderThe database has four fields:

column meaning
id The primary key id
name Names of provinces, cities and counties
pid This entry records the parent class ID
status Enable status of this record

2.3 Operation test procedure

The project start up test using the postman: request path: localhost: 8010 / link/city/queryById

  1. When the front end first requests it, it passes the ID to 0 and then it looks up all the provinces and municipalities in the country,

2. Get the id of the province or municipality and then request the interface so that all the cities or districts in the province can be found out

2.4 Data Storage and Output

1. It is best to save the primary key ID and name of provinces and counties in the database. 2. When the front-end display can directly take the name to display the name of provinces and counties, or use id to request 3 from the database. When the amount of data is very large, it may cause great pressure on the database if id is used to request the database. At this time, all provinces, cities and counties can be checked out and put into REDIS to reduce the pressure on the database. If the data in the value list is not very large, you can directly send the data to the front end in the form of a large map with id as key and name as value. When the front end displays the data, the front end uses ID to match the values in the map