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

向右看齐 2023-10-09 17:27 42阅读 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  
;

然后是省市区地域数据的sql（自取）：

链接：https://pan.baidu.com/s/16x\_KkQEprX4HMgcJwiaSCg  
提取码：ellh

数据填充后模样（部分）：

代码：

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层就自己来咯。

简单测试下：

————————————————  
版权声明：本文为CSDN博主「小目标青年」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。  
原文链接：https://blog.csdn.net/qq\_35387940/article/details/128247645