如果你不幸搜到了这篇文章,那么你可能跟我一样遇到了这个非常奇葩的需求:
1.输入一个国家(或省、市等)的ID,获取这个国家(或省、市等)下的城市及城市的城市........
2.输入一个街道(或区、县等)的ID,获取他的上级(如:市、区、国等)..........
数据库(仅为测试实现):省市区数据库
CItysMapper.class
@Mapper
public interface CitysMapper {
/**
* 套娃(递归)查询,效率低,执行的sql多,不考虑
*/
List<Citys> findByPid(@Param("pid") Integer pid, @Param("fore") List<Integer> fore, @Param("fore2") List<Integer> fore2);
/**
* 根据id查询城市类型
*/
Integer findCityType(Integer pid);
/**
* 根据城市id查询这个城市和这个城市的子集
* @param pid 城市id
* @param type 城市类型
* @param fore 循环参数,用于拼接查询字段和表名
* @param fore2 循环参数,用于拼接条件
*/
List<Citys> findCity(@Param("pid") String pid, @Param("ctyped") Integer type, @Param("fore") List<Integer> fore, @Param("fore2") List<Integer> fore2);
/**
* 根据城市id获取这个城市的上级和上级的上级
* @param pid 城市id
* @param type 城市类型
* @param fore 循环参数,用于拼接查询字段和表名
* @param fore2 循环参数,用于拼接条件
*/
List<Citys> findFather(@Param("pid") String pid, @Param("ctyped") Integer type, @Param("fore") List<Integer> fore, @Param("fore2") List<Integer> fore2);
}
CItysMapper.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.czxy.dao.OrderMapper">
<!--套娃查询(极度嫌弃)-->
<resultMap id="cityResultMap" type="com.czxy.domain.Citys" autoMapping="true">
<id column="id" property="id"></id>
<collection property="city" select="findByPid" column="id" autoMapping="true">
</collection>
</resultMap>
<!-- 根据id获取所有子集,递归查询,跟上面一伙的(嫌弃) -->
<select id="findByPid" resultMap="cityResultMap">
select * from citys where parent_id = #{pid}
</select>
<!-- 套娃存储(这个示例表示国-省-市-区的,所以最多4层就足够了,要是更多就继续累加就好了) -->
<!-- PS:不会写这种动态的,有没有大佬教教我 -->
<resultMap id="cityResultMap2" type="com.czxy.domain.Citys">
<id column="c0id" property="id"></id>
<result column="c0parent_id" property="parentId"></result>
<result column="c0cname" property="cname"></result>
<result column="c0ctype" property="ctype"></result>
<collection property="city" javaType="java.util.List" ofType="com.czxy.domain.Citys">
<id column="c1id" property="id"></id>
<result column="c1parent_id" property="parentId"></result>
<result column="c1cname" property="cname"></result>
<result column="c1ctype" property="ctype"></result>
<collection property="city" javaType="java.util.List" ofType="com.czxy.domain.Citys">
<id column="c2id" property="id"></id>
<result column="c2parent_id" property="parentId"></result>
<result column="c2cname" property="cname"></result>
<result column="c2ctype" property="ctype"></result>
<collection property="city" javaType="java.util.List" ofType="com.czxy.domain.Citys">
<id column="c3id" property="id"></id>
<result column="c3parent_id" property="parentId"></result>
<result column="c3cname" property="cname"></result>
<result column="c3ctype" property="ctype"></result>
<collection property="city" javaType="java.util.List" ofType="com.czxy.domain.Citys">
<id column="c4id" property="id"></id>
<result column="c4parent_id" property="parentId"></result>
<result column="c4cname" property="cname"></result>
<result column="c4ctype" property="ctype"></result>
</collection>
</collection>
</collection>
</collection>
</resultMap>
<!-- 套娃存储结束 -->
<!-- 获取该城市的城市类型 -->
<select id="findCityType" resultType="java.lang.Integer">
select ctype from citys where id = #{pid}
</select>
<!-- 根据任意一个id查询该城市及该城市的子城市 -->
<!-- PS:全是拼接的sql,已经吐了,不想解释,自己加油理解吧 -->
<select id="findCity" resultMap="cityResultMap2">
select
<foreach collection="fore" item="f" separator=",">
c${f}.id c${f}id,
c${f}.parent_id c${f}parent_id,
c${f}.cname c${f}cname,
c${f}.ctype c${f}ctype
</foreach>
from
<foreach collection="fore" item="f" separator=",">
citys c${f}
</foreach>
where
<foreach collection="fore2" item="f">
c${f}.id = c${f+1}.parent_id and
</foreach>
c${ctyped+1 > fore.size() ? fore.size()-1 : ctyped + 1}.parent_id = ${pid}
</select>
<!-- 根据任意一个id查询地区对象和地区的父类 -->
<select id="findFather" resultMap="cityResultMap2">
select
<foreach collection="fore" item="f" separator=",">
c${f}.id c${f}id,
c${f}.parent_id c${f}parent_id,
c${f}.cname c${f}cname,
c${f}.ctype c${f}ctype
</foreach>
from
<foreach collection="fore" item="f" separator=",">
citys c${f}
</foreach>
where
<foreach collection="fore2" item="f">
c${f}.id = c${f+1}.parent_id and
</foreach>
c${ctyped > fore.size() ? fore.size()-1 : ctyped}.id = ${pid}
</select>
</mapper>
Ctiys.class
@Data//偷懒
public class Citys {
private Integer id;
private Integer parentId;
private String cname;
private Integer ctype;
private List<Citys> city;
@Override
public String toString() {
return "\nCitys{" +
"id=" + id +
", parentId=" + parentId +
", cname='" + cname + '\'' +
", ctype=" + ctype +
", city=" + city +
"}";
}
}
CitysMapperTest.class
@RunWith(SpringRunner.class)
@SpringBootTest(classes = XmlApplication.class)
public class CitysMapperTest {
@Autowired
private CitysMapper mapper;
@Test
public void test2(){
System.out.println(findCity(1));
System.out.println(findCity(3));
System.out.println(findFather(3402));
System.out.println(findFather(3401));
System.out.println(findFather(3));
System.out.println(findFather(1));
}
/**
* 查询子集核心代码
* @param pid
* @return
*/
public List<Citys> findCity(Integer pid){
//获取地区类型(0国家 1省 2市 3区县)
Integer type = mapper.findCityType(pid);
//获取遍历次数
Integer forNum = 4 - type;
//因为xml里面只能foreach,不够朴素,所以需要两个集合控制遍历
//a1用于拼接查询字段和表名
//a2用于拼接条件
ArrayList<Integer> a1 = new ArrayList<>();
ArrayList<Integer> a2 = new ArrayList<>();
for (Integer i = 0; i < forNum; i++) {
a1.add(i);
if (i < forNum-1){
a2.add(i);
}
}
return mapper.findCity(pid.toString(),type, a1,a2);
}
/**
* 查询父级核心代码
* 解释同上
* @param pid
* @return
*/
public List<Citys> findFather(Integer pid){
Integer type = mapper.findCityType(pid);
Integer type2 = type;
ArrayList<Integer> a1 = new ArrayList<>();
ArrayList<Integer> a2 = new ArrayList<>();
for (Integer i = 0; i <= type2; i++) {
a1.add(i);
if (i < type2){
a2.add(i);
}
}
return mapper.findFather(pid.toString(),type, a1,a2);
}
}
剩下的以后再补吧
