1.数据库设计
表中主要分为四个等级level
:
- 国家
- 省份
- 城市
- 区域
除了国家,其他都有一个父级idparent_id
以下设计级联查询的思路就是通过level
和parent_id
来查询
CREATE TABLE `xp_area` (
`area_id` int(20) NOT NULL COMMENT '地区id',
`area_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '地区名称',
`parent_id` int(20) NULL DEFAULT NULL COMMENT '父级ID',
`short_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '简称',
`level` tinyint(2) NULL DEFAULT NULL COMMENT '地区级别(1:省份province,2:市city,3:区县district,4:街道street)',
`city_code` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '城市编码',
`zip_code` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '邮编',
`merger_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '全名称',
`longitude` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '经度',
`latitude` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '维度',
`pinyin` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '拼音',
PRIMARY KEY (`area_id`) USING BTREE,
INDEX `parent_id`(`parent_id`) USING BTREE,
INDEX `level`(`level`) USING BTREE,
INDEX `areaName`(`area_name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '地区表' ROW_FORMAT = Compact;
- SpringBoot查询如下
@Mapper
public interface AddrDao {
//根据等级level和父级parrent_id查询当前等级的地址数据
@Select("SELECT area_id,area_name FROM area WHERE parent_id=#{parentId} AND level=#{level}")
List<AreaVo> getAddrsByLevelAndParentId(@Param("level") int level, @Param("parentId") int parentId);
}
/**
* 传输省市区级联数据
*/
public class AreaVo {
private String areaName;
private int areaId;
...
}
@Service
public class AddrService {
@Autowired
private AddrDao addrDao;
public List<AreaVo> getAddrsByLevelAndParentId(int level, int parentId) {
return addrDao.getAddrsByLevelAndParentId(level,parentId);
}
}
@CrossOrigin
@RestController
@RequestMapping("/addr")
public class AddrController {
@Autowired
private AddrService addrService;
@GetMapping("/getAddrs")
public ResultBean getAddrs(@RequestParam("level")int level,@RequestParam("parentId")int parentId){
List<AreaVo> addrs = addrService.getAddrsByLevelAndParentId(level,parentId);
System.out.println(addrs.get(0));
if (addrs!=null && addrs.size()>0) return ResultBean.success(addrs);
return ResultBean.error(CodeMsg.ADDRS_QUERY_ERROR);
}
}
ElementUI
使用的是级联选择器, 代码如下
data(){
var _this = this;
return {
//省市区级联
props: {
lazy: true,
lazyLoad (node, resolve) {
var{ level } = node; //等级
var parentId = node.value?node.value:100000; //父节点
var areas = [];
_this.$http.get("addr/getAddrs?level="+(level+1)+"&parentId="+parentId,{withCredentials:true}).then((res)=>{
var data = res.data;
if(data.code==0){
areas = data.data;
}else _this.$message.error(data.msg);
}).catch(err=>{
_this.$message.error("客户端异常");
});
var index = 0;
setTimeout(() => {
const nodes = Array.from(areas)
.map(item => ({
value: item.areaId,
label: item.areaName,
leaf: level >= 2
}));
// 通过调用resolve将子节点数据返回,通知组件数据加载完成
resolve(nodes);
}, 1000);
}
}
}