Springboot 省市区地域信息，拿来即用（表、sql等）

悠悠 2023-09-23 18:12 72阅读 0赞

不多说。

首先是省市区地域建表SQL ：

CREATE TABLE `district_info` (
    	`CODE` VARCHAR(128) NOT NULL COMMENT '编码' COLLATE 'utf8_general_ci',
    	`NAME` VARCHAR(255) NOT NULL COMMENT '地区名称' COLLATE 'utf8_general_ci',
    	`PARENT_CODE` VARCHAR(128) NOT NULL COMMENT '上级地区名称' COLLATE 'utf8_general_ci',
    	`FULL_NAME` VARCHAR(255) NOT NULL COMMENT '当前地区对应的全称' COLLATE 'utf8_general_ci',
    	PRIMARY KEY (`CODE`) USING BTREE
    )
    COMMENT='地区信息表'
    COLLATE='utf8_general_ci'
    ENGINE=InnoDB
    ;

![492a30b822fd499aaf6a870917c6cdf1.png][]

然后是省市区地域数据的sql（自取）：  
  
链接：https://pan.baidu.com/s/16x\_KkQEprX4HMgcJwiaSCg  
提取码：ellh

数据填充后模样（部分）：  
![f7d9154d464642308f1de0977c6229d9.png][]

代码：

pom.xml 核心依赖（整合mybatis）：

<dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>2.0.0</version>
            </dependency>
    
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <scope>runtime</scope>
            </dependency>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <scope>provided</scope>
            </dependency>
    
            
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>druid-spring-boot-starter</artifactId>
                <version>1.1.10</version>
            </dependency>
    
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-test</artifactId>
                <scope>test</scope>
            </dependency>

**实体**

District.java

import lombok.Data;
    
    import java.io.Serializable;
    
    
    /**
     * @author JCccc
     * 地域表实体 （省市区）
     */
    @Data
    public class District implements Serializable {
    
        /**
         * 省级编码
         */
        public static final String PROVINCE_CODE = "1";
        /**
         * 地址编码
         */
        private String code;
        /**
         * 地址名称
         */
        private String name;
        /**
         * 地址父级编码
         */
        private String parentCode;
        /**
         * 当前地区对应的全称
         */
        private String fullName;
        
    }

**mapper**

DistrictMapper.java

import com.example.dotest.entity.District;
    import org.apache.ibatis.annotations.Mapper;
    import org.apache.ibatis.annotations.Param;
    import java.util.List;
    
    /**
     * @author JCccc
     */
    @Mapper
    public interface DistrictMapper {
        
    
        /**
         * 查询所有省份
         * @return
         */
         List<District> queryProvince();
    
        /**
         * 查询城市或地区
         * @param code
         * @return
         */
         List<District> queryCity(String code);
    
        /**
         * 通过code查询地区信息
         * @param code
         * @return
         */
         District queryByCode(String code);
    
        /**
         * 查询所有地址信息
         * @return
         */
        List<District> queryDistrictList();
    
        /**
         * 根据编码集合查询
         * @param codeList
         * @return
         */
          List<District> queryByCodeList(@Param("codeList") List<String> codeList);
    
        /**
         *查询省和市
         * @param codeList
         * @return
         */
         List<District> queryProvinceAndCity(@Param("codeList") List<String> codeList);
    
        /**
         *查询上级地区查下级
         * @param codeList
         * @return
         */
         List<District> queryByParentCodes(@Param("codeList") List<String> codeList);
    
        /**
         * 查询省份下第一个城市
         * @param code
         * @return
         */
        District queryFirstCity(@Param("code") String code);
    
    
        /**
         * 新增
         * @param district 新增
         */
        void insertSelective(District district);
    
        /**
         * 更新
         * @param district
         */
        void updateSelective(District district);
    
        /**
         * 删除
         * @param code 编码
         */
        void delByCode(String code);
    
    
        /**
         * 根据名称获取名称地区信息（精准匹配）
         * @param districtNames 地区名称
         * @return 结果
         */
        List<District> allDistrictByNames(@Param("districtNames") List<String> districtNames);
    
    }

**mapper xml**

DistrictMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
    <mapper namespace="com.example.dotest.mapper.DistrictMapper">
        
        <resultMap id="BaseResultMap" type="com.example.dotest.entity.District">
            <result column="CODE" property="code" jdbcType="VARCHAR"/>
            <result column="NAME" property="name" jdbcType="VARCHAR"/>
            <result column="PARENT_CODE" property="parentCode" jdbcType="VARCHAR"/>
            <result column="FULL_NAME" property="fullName" jdbcType="VARCHAR"/>
        </resultMap>
    
        <sql id="Base_Column_List">
           CODE,NAME,PARENT_CODE,FULL_NAME
        </sql>
    
        
        <select id="queryProvince" resultMap="BaseResultMap">
            select
            <include refid="Base_Column_List"/>
            from
            district_info
            where
            PARENT_CODE=1
        </select>
    
        
        <select id="queryCity" resultMap="BaseResultMap" parameterType="java.lang.String">
            select
            <include refid="Base_Column_List"/>
            from
            district_info
            where
            PARENT_CODE=#{code}
        </select>
    
        
        <select id="queryByCode" resultMap="BaseResultMap" parameterType="java.lang.String">
            select
            <include refid="Base_Column_List"/>
            from
            district_info
            where
            CODE=#{code}
        </select>
        
        <select id="queryDistrictList" resultMap="BaseResultMap">
            SELECT
            <include refid="Base_Column_List"/>
            FROM district_info
        </select>
        
        <select id="queryByCodeList" resultMap="BaseResultMap">
            select
            <include refid="Base_Column_List"/>
            FROM district_info
            where
            CODE in
            <foreach collection="codeList" item="codes" open="(" separator="," close=")">
                #{codes}
            </foreach>
        </select>
        
        <select id="queryByParentCodes" resultMap="BaseResultMap">
            select
            <include refid="Base_Column_List"/>
            FROM district_info
            where PARENT_CODE in
            <foreach collection="codeList" item="code" open="(" separator="," close=")">
                #{code}
            </foreach>
        </select>
        
        <select id="queryProvinceAndCity" resultMap="BaseResultMap">
            select
            <include refid="Base_Column_List"/>
            FROM district_info
            where
            PARENT_CODE =1 or PARENT_CODE LIKE "%0000";
        </select>
        
        <select id="queryFirstCity" resultMap="BaseResultMap" parameterType="java.lang.String">
            select
            <include refid="Base_Column_List"/>
            from
            district_info
            where
            PARENT_CODE=#{code}
            order by `code` limit 1
        </select>
    
        <insert id="insertSelective" parameterType="com.example.dotest.entity.District">
            insert into district_info
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="code != null">
                    CODE,
                </if>
                <if test="name != null">
                    NAME,
                </if>
                <if test="parentCode != null">
                    PARENT_CODE,
                </if>
                <if test="fullName != null">
                    FULL_NAME,
                </if>
            </trim>
            <trim prefix="values (" suffix=")" suffixOverrides=",">
                <if test="code != null">
                    #{code,jdbcType=VARCHAR},
                </if>
                <if test="name != null">
                    #{name,jdbcType=VARCHAR},
                </if>
                <if test="parentCode != null">
                    #{parentCode,jdbcType=VARCHAR},
                </if>
                <if test="fullName != null">
                    #{fullName,jdbcType=VARCHAR},
                </if>
            </trim>
        </insert>
        <update id="updateSelective" parameterType="com.example.dotest.entity.District">
            update district_info
            <set>
                <if test="code != null">
                    CODE = #{code,jdbcType=VARCHAR},
                </if>
                <if test="name != null">
                    NAME = #{name,jdbcType=VARCHAR},
                </if>
                <if test="parentCode != null">
                    PARENT_CODE = #{parentCode,jdbcType=VARCHAR},
                </if>
                <if test="fullName != null">
                    FULL_NAME = #{fullName,jdbcType=VARCHAR},
                </if>
            </set>
            where CODE = #{code,jdbcType=VARCHAR}
        </update>
    
        <delete id="delByCode" parameterType="java.lang.String">
            delete from district_info where CODE = #{code,jdbcType=VARCHAR}
        </delete>
    
        
        <select id="allDistrictByNames" resultMap="BaseResultMap">
            select
            <include refid="Base_Column_List"/>
            FROM district_info
            where name in
            <foreach collection="districtNames" item="districtName" separator="," open="(" close=")">
                #{districtName}
            </foreach>
    
        </select>
    
    </mapper>

service层就自己来咯。

简单测试下：  
![46939d6d19424d5896858206a50390ae.png][]

[492a30b822fd499aaf6a870917c6cdf1.png]: https://img-blog.csdnimg.cn/492a30b822fd499aaf6a870917c6cdf1.png
[f7d9154d464642308f1de0977c6229d9.png]: https://img-blog.csdnimg.cn/f7d9154d464642308f1de0977c6229d9.png
[46939d6d19424d5896858206a50390ae.png]: https://img-blog.csdnimg.cn/46939d6d19424d5896858206a50390ae.png