使用mybatis查询获取省市区三级联动

获取省市区三级联动

• 首先贴出mapper.xml

<!-- json封装返回结果集 -->
	<resultMap id="toRegionInfoMap" type="com.nandi.system.domain.SysRegionInfo">
		<result column="provinceId" property="id"/>
		<result column="provinceName" property="name"/>
		<result column="provinceLevel" property="level"/>
		<result column="provinceDisplayOrder" property="displayOrder"/>
		<result column="provinceVisible" property="visible"/>
		<collection property="children" javaType="java.util.List" ofType="com.nandi.system.domain.SysRegionInfo">
			<result column="cityId" property="id"/>
			<result column="cityName" property="name"/>
			<result column="cityPid" property="pid"/>
			<result column="cityLevel" property="level"/>
			<result column="cityDisplayOrder" property="displayOrder"/>
			<result column="cityVisible" property="visible"/>
			<collection property="children"  javaType="java.util.List" ofType="com.nandi.system.domain.SysRegionInfo">
				<result column="districtId" property="id"/>
				<result column="districtName" property="name"/>
				<result column="districtPid" property="pid"/>
				<result column="districtLevel" property="level"/>
				<result column="districtDisplayOrder" property="displayOrder"/>
				<result column="districtVisible" property="visible"/>
			</collection>
		</collection>
	</resultMap>
	
	<select id="getRegionInfoList" parameterType="Integer" resultMap="toRegionInfoMap">
		SELECT
		province.id AS provinceId,
		province.`name` AS provinceName,
		province.visible AS provinceVisible,
		province.display_order AS provinceDisplayOrder,
		province.`level` AS provinceLevel,
		city.id AS cityId,
		city.`name` AS cityName,
		city.pid AS cityPid,
		city.visible AS cityVisible,
		city.display_order AS cityDisplayOrder,
		city.`level` AS cityLevel,
		district.id AS districtId,
		district.`name` AS districtName,
		district.pid AS districtPid,
		district.visible AS districtVisible,
		district.display_order AS districtDisplayOrder,
		district.`level` AS districtLevel
		FROM
		sys_region province
		INNER JOIN sys_region city ON city.pid = province.id AND city.visible = 1 AND province.visible = 1
		INNER JOIN sys_region district ON district.pid = city.id AND district.visible = 1
		WHERE 1=1
		<if test="district != null and district != ''">
			and district.id = #{district}
		</if>
	</select>

• 接收对象

package com.nandi.system.domain;

import java.util.ArrayList;
import java.util.List;

/**
 * @author NuiiQK
 * @date 2020-11-23
 **/
public class SysRegionInfo {
    private int id;
    private int pid;
    private String name;
    private int visible;
    private int displayOrder;
    private int level;

    List<SysRegionInfo> children = new ArrayList<>();

    public List<SysRegionInfo> getChildren() {
        return children;
    }

    public void setChildren(List<SysRegionInfo> children) {
        this.children = children;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public int getPid() {
        return pid;
    }

    public void setPid(int pid) {
        this.pid = pid;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getVisible() {
        return visible;
    }

    public void setVisible(int visible) {
        this.visible = visible;
    }

    public int getDisplayOrder() {
        return displayOrder;
    }

    public void setDisplayOrder(int displayOrder) {
        this.displayOrder = displayOrder;
    }

    public int getLevel() {
        return level;
    }

    public void setLevel(int level) {
        this.level = level;
    }
}

• 省市区数据表

-- ----------------------------
-- Table structure for sys_region
-- ----------------------------
DROP TABLE IF EXISTS `sys_region`;
CREATE TABLE `sys_region`  (
  `id` int(0) NOT NULL COMMENT '省份code',
  `pid` int(0) UNSIGNED NOT NULL COMMENT '父级code',
  `name` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '名称',
  `visible` tinyint(0) UNSIGNED NOT NULL COMMENT '是否可见 默认1可见 2不可见',
  `display_order` int(0) UNSIGNED NOT NULL COMMENT '显示顺序',
  `level` tinyint(0) UNSIGNED NOT NULL COMMENT '层级',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `parentId`(`pid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 990101 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '全国省市区县表' ROW_FORMAT = Compact;

-- ----------------------------
-- Records of sys_region
-- ----------------------------
INSERT INTO `sys_region` VALUES (110000, 0, '北京市', 1, 0, 1);
INSERT INTO `sys_region` VALUES (110100, 110000, '北京市', 1, 0, 2);
INSERT INTO `sys_region` VALUES (110101, 110100, '东城区', 1, 0, 3);

其余的sql因为太多如果需要可以私聊博主。