目录
—— 在后端对城市列表进行省市县处理,在将处理好的在前端直接进行展示
—— 后端只需根据id进行查找,前端根据点击的省市县id向后端发送请求,点那个级别,就用该级别的id去后端进行查找在展示。
效果图
环境搭建
tb_city 城市表
CREATE TABLE tb_city(
c_id VARCHAR(32) PRIMARY KEY COMMENT '城市ID',
city_name VARCHAR(20) COMMENT '城市名称' ,
parent_id VARCHAR(32) COMMENT '父ID'
);
INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('320000','江苏省','0');
INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('140000','山西省','0');
INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('130000','河北省','0');
INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('320100','南京市','320000');
INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('320102','玄武区','320100');
INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('320103','白下区','320100');
INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('321300','宿迁市','320000');
INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('321322','沭阳县','321300');
INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('321323','泗阳县','321300');
INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('140100','太原市','140000');
INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('140106','迎泽区','140100');
INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('140108','尖草坪区','140100');
INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('140800','运城市','140000');
INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('140823','闻喜县','140800');
INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('140828','夏 县','140800');
INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('130100','石家庄市','130000');
INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('130127','高邑县','130100');
INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('130185','鹿泉市','130100');
INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('131000','廊坊市','130000');
INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('131003','广阳区','131000');
INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('131022','固安县','131000');
tb_user 用户表
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user` (
`u_id` varchar(32) NOT NULL COMMENT '用户编号',
`user_name` varchar(50) DEFAULT NULL COMMENT '用户名',
`password` varchar(32) DEFAULT NULL COMMENT '密码',
`gender` bit(1) DEFAULT NULL COMMENT '性别,1表示男,0表示女',
PRIMARY KEY (`u_id`),
UNIQUE KEY `user_name` (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `tb_user`(`u_id`,`user_name`,`password`,`gender`) values ('1','jack','1234',''),('10','jack5','1234',''),('2','rose','1234','\0'),('3','张三','1234',''),('4','tom','1234',''),('5','jack2','1234',''),('6','jack1','1234',''),('7','jack3','1234',''),('8','jack4','1234',''),('cd0d2523b5024589af142787de8a7b2a','jack6','1234','');
ALTER TABLE tb_user ADD COLUMN city_ids VARCHAR(50);
UPDATE `tb_user` SET `city_ids`='32000,321300,321322' WHERE `u_id`='1';
方案1:分析(侧重后端)
—— 在后端对城市列表进行省市县处理,在将处理好的在前端直接进行展示
1. 后端实现
-
步骤:
-
步骤1:JavaBean City
-
步骤2:JavaBean CityVo
-
步骤3:CityMapper
-
步骤4:CityService
-
步骤5:CityController
-
-
步骤1:JavaBean City
package com.czxy.domain;
import lombok.Data;
import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.Table;
/**
* @author 桐叔
* @email liangtong@itcast.cn
*/
@Table(name = "tb_city") //声明表名
@Data
public class City {
@Id //声明主键
@Column(name = "c_id") //声明列名
private String cid;
@Column(name = "city_name")
private String cityName;
@Column(name = "parent_id") //使用重音符处理关键字desc
private String parentId;
}
-
步骤2:JavaBean CityVo
package com.czxy.vo;
import com.fasterxml.jackson.annotation.JsonInclude;
import lombok.Data;
import java.util.ArrayList;
import java.util.List;
/** element ui 级联菜单需要的数据格式,属性名固定值(value、label、children)
/**
* @author 桐叔
* @email liangtong@itcast.cn
*/
*/
@Data
public class CityVo {
private String value ; //id
private String label; //名称
@JsonInclude(JsonInclude.Include.NON_EMPTY) //显示非空内容
private List<CityVo> children = new ArrayList<>();
}
-
步骤3:CityMapper
package com.czxy.mapper;
import com.czxy.domain.City;
import tk.mybatis.mapper.common.Mapper;
/**
* @author 桐叔
* @email liangtong@itcast.cn
*/
@org.apache.ibatis.annotations.Mapper
public interface CityMapper extends Mapper<City> {
}
-
步骤4:CityService
-
接口
package com.czxy.service; import com.czxy.vo.CityVo; import java.util.List; /** * @author 桐叔 * @email liangtong@itcast.cn */ public interface CityService { /** * 查询所有的城市,处理过的省市县 * @return */ public List<CityVo> selectAll(); }
-
实现类
package com.czxy.service.impl; import com.czxy.domain.City; import com.czxy.mapper.CityMapper; import com.czxy.service.CityService; import com.czxy.vo.CityVo; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import tk.mybatis.mapper.entity.Example; import javax.annotation.Resource; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; /** * @author 桐叔 * @email liangtong@itcast.cn */ @Service @Transactional public class CityServiceImpl implements CityService { @Resource private CityMapper cityMapper; @Override public List<CityVo> selectAll() { //1 查询所有,parentId排序 Example example = new Example(City.class); example.orderBy("parentId").asc(); List<City> cityList = cityMapper.selectByExample(example); //2 处理数据,省市县依次包含 // 2.1 准备2个集合 // 2.1.1 list集合,用于存放所有的省 List<CityVo> resultList = new ArrayList<>(); // 2.1.2 map集合,用于存放所有城市,方便市找省,方便县找市 Map<String,CityVo> cache = new HashMap<>(); // 2.2 遍历查询到的所有数据 cityList cityList.forEach(city -> { // 0) 将city封装成cityVo CityVo cityVo = new CityVo(); cityVo.setValue(city.getCid()); cityVo.setLabel(city.getCityName()); // 1) 通过parentId从map获得数据 CityVo parentCityVo = cache.get(city.getParentId()); if(parentCityVo == null) { // 2) 如果是省,没有数据,直接添加到list集合 resultList.add(cityVo); } else { // 3) 如果不是省,就有数据,将当前封装对象添加现在的城市孩子 parentCityVo.getChildren().add(cityVo); } // 4) 将当前城市添加到map cache.put(city.getCid(),cityVo); }); // 3 返回 CityVo处理后的数据 return resultList; } }
-
-
步骤5:CityController
package com.czxy.controller;
import com.czxy.service.CityService;
import com.czxy.vo.BaseResult;
import com.czxy.vo.CityVo;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.annotation.Resource;
import java.util.List;
/**
* @author 桐叔
* @email liangtong@itcast.cn
*/
@RestController
@RequestMapping("/city")
@CrossOrigin
public class CityController {
@Resource
private CityService cityService;
@GetMapping
public BaseResult selectAll() {
// 查询
List<CityVo> cityVoList = cityService.selectAll();
// 返回
return BaseResult.ok("查询成功", cityVoList);
}
}
2. 前端实现
-
级联菜单模板
<el-cascader
v-model="user.citys" // 绑定注册用户user中的城市字段
:options="cityList" // 遍历的集合
:props="{ expandTrigger: 'hover' }"
></el-cascader>
-
ajax查询
<script>
import axios from 'axios';
export default {
data() {
return {
user: {}, //用户
cityList: [], //城市数据
};
},
methods: {
async selectAllCity() {
// ajax
var url = `http://localhost:8888/city`
let { data:baseResult } = await axios.get(url)
// 填充数据
this.cityList = baseResult.data
}
},
mounted() {
// 查询所有的市
this.selectAllCity()
},
}
</script>
方案2:分析(侧重前端)
—— 后端只需根据id进行查找,前端根据点击的省市县id向后端发送请求,点那个级别,就用该级别的id去后端进行查找在展示。
1. 后端实现
步骤1:mapper
package com.czxy.mapper;
import com.czxy.domain.City;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import tk.mybatis.mapper.common.Mapper;
import java.util.List;
/**
* @author 桐叔
* @email liangtong@itcast.cn
*/
@org.apache.ibatis.annotations.Mapper
public interface CityMapper extends Mapper<City> {
/**
* 通过父id 查询所有的城市
* @param parentId
* @return
*/
@Select("SELECT c_id cid,city_name cityName, parent_id parentId FROM tb_city WHERE parent_id = #{parentId}")
public List<City> selectAllCityByParentId(@Param("parentId") String parentId);
}
步骤2:service
- 接口
package com.czxy.service;
import com.czxy.domain.City;
import com.czxy.vo.CityVo;
import java.util.List;
/**
* @author 桐叔
* @email liangtong@itcast.cn
*/
public interface CityService {
/**侧重后端方法
* 查询所有的城市,处理过的省市县
* @return
*/
public List<CityVo> selectAll();
/**侧重前端方法
* 通过父id 查询所有的城市
* @param parentId
* @return
*/
public List<City> selectAllCityByParentId(String parentId);
}
(String parentId);
- 实现类
package com.czxy.service.impl;
import com.czxy.domain.City;
import com.czxy.mapper.CityMapper;
import com.czxy.service.CityService;
import com.czxy.vo.CityVo;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import tk.mybatis.mapper.entity.Example;
import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* @author 桐叔
* @email liangtong@itcast.cn
*/
@Service
@Transactional
public class CityServiceImpl implements CityService {
@Resource
private CityMapper cityMapper;
// 侧重后端方法
@Override
public List<CityVo> selectAll() {
//1 查询所有,parentId排序
Example example = new Example(City.class);
example.orderBy("parentId").asc();
List<City> cityList = cityMapper.selectByExample(example);
//2 处理数据,省市县依次包含
// 2.1 准备2个集合
// 2.1.1 list集合,用于存放所有的省
List<CityVo> resultList = new ArrayList<>();
// 2.1.2 map集合,用于存放所有城市,方便市找省,方便县找市
Map<String,CityVo> cache = new HashMap<>();
// 2.2 遍历查询到的所有数据 cityList
cityList.forEach(city -> {
// 0) 将city封装成cityVo
CityVo cityVo = new CityVo();
cityVo.setValue(city.getCid());
cityVo.setLabel(city.getCityName());
// 1) 通过parentId从map获得数据
CityVo parentCityVo = cache.get(city.getParentId());
if(parentCityVo == null) {
// 2) 如果是省,没有数据,直接添加到list集合
resultList.add(cityVo);
} else {
// 3) 如果不是省,就有数据,将当前封装对象添加现在的城市孩子
parentCityVo.getChildren().add(cityVo);
}
// 4) 将当前城市添加到map
cache.put(city.getCid(),cityVo);
});
// 3 返回 CityVo处理后的数据
return resultList;
}
// 侧重前端方法
@Override
public List<City> selectAllCityByParentId(String parentId) {
return cityMapper.selectAllCityByParentId(parentId);
}
}
步骤3:controller
package com.czxy.controller;
import com.czxy.domain.City;
import com.czxy.service.CityService;
import com.czxy.vo.BaseResult;
import com.czxy.vo.CityVo;
import org.springframework.web.bind.annotation.*;
import javax.annotation.Resource;
import java.util.List;
/**
* @author 桐叔
* @email liangtong@itcast.cn
*/
@RestController
@RequestMapping("/city")
@CrossOrigin
public class CityController {
@Resource
private CityService cityService;
//侧重后端方法
@GetMapping
public BaseResult selectAll() {
// 查询
List<CityVo> cityVoList = cityService.selectAll();
// 返回
return BaseResult.ok("查询成功", cityVoList);
}
/** 侧重前端方法
* 通过父id 查询所有的城市
* @param parentId
* @return
*/
@GetMapping("/parentId/{parentId}")
public BaseResult selectAllCityByParentId(@PathVariable("parentId") String parentId) {
//查询
List<City> cityList = cityService.selectAllCityByParentId(parentId);
//返回
return BaseResult.ok("查询成功", cityList);
}
}
2. 前端实现(查询多次)
<template>
<div>
<el-cascader :props="cityProps"></el-cascader>
</div>
</template>
<script>
// 导入axios
import axios from 'axios'
let id = 0;
export default {
data() {
return {
cityProps: {
value: 'cid',
label: 'cityName',
lazy: true,
async lazyLoad (node, resolve) {
// 第一次加载,所有省,0
// 第二次之后,上一次点击的id
//1 获得父id
var parentId = '0'
if(node.value) {
parentId = node.value
}
//2 通过父id ajax查询
var url = `http://localhost:8888/city/parentId/${parentId}`
let { data:baseResult } = await axios.get(url)
var nodes = baseResult.data
//3 处理数据,优化县,给所有的县添加叶子属性 leaf: true
if(node.level == 2) {
nodes.forEach(city=>{
city.leaf = true
})
}
// 通过调用resolve将子节点数据返回,通知组件数据加载完成
resolve(nodes);
}
}
}
},
}
</script>
<style>
</style>