首先是省市区地域建表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）：
实体
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" "" > <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> <!--通过code查询地区信息--> <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> <!--根据code编码查询--> <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> <!--根据父级code编码查询--> <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层就自己来咯。
简单测试下：
