mybatis同表级联查询

最新推荐文章于 2023-04-05 15:23:33 发布

Shockymus

最新推荐文章于 2023-04-05 15:23:33 发布

阅读量2.5k

点赞数

分类专栏： mybatis 文章标签： mybatis 级联查询

本文链接：https://blog.csdn.net/u012485016/article/details/64500972

版权

mybatis 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

事发场景：地区联动查询，比如查询杭州市同时查询出市区列表

mybatis配置就不说了，网上很多。
主要代码都是generator自动生成，以下代码去掉不必要的注释
首先是表结构：笔者sql水平菜，所以还是放上来做个记录

area表：

'area', 'CREATE TABLE `area` (\n  `id` int(11) NOT NULL AUTO_INCREMENT,\n  `name` varchar(20) DEFAULT NULL,\n  `area_id` int(6) unsigned zerofill DEFAULT NULL,\n  `parent_id` int(6) unsigned zerofill DEFAULT NULL,\n  PRIMARY KEY (`id`)\n) ENGINE=InnoDB AUTO_INCREMENT=96 DEFAULT CHARSET=utf8'

表数据：

表数据：

全部数据：

insert into `area` (`name`,area_id, parent_id)
values
('杭州',000001,null),
('南京',000002,null),
('上海',000003,null),
('苏州',000004,null),
('上城区',010001,000001),
('下城区',010002,000001),
('江干区',010003,000001),
('拱墅区',010004,000001),
('西湖区',010005,000001),
('滨江区',010006,000001),
('萧山区',010007,000001),
('余杭区',010008,000001),
('四季青',010101,010001),
('复兴',010102,010001),
('南星桥',010103,010001),
('近江',010104,010001),
('望江门',010105,010001),
('清泰(城战)',010106,010001),
('吴山',010107,010001),
('菜市桥',010108,010001),
('解百',010109,010001),
('马市街',010110,010001),
('河坊街',010111,010001),
('武林',010201,010002),
('潮鸣',010202,010002),
('庆春',010203,010002),
('宝善',010204,010002),
('三塘',010205,010002),
('石桥',010206,010002),
('东新',010207,010002),
('和平',010208,010002),
('西湖文化广场',010209,010002),
('朝晖',010210,010002),
('孩儿巷',010211,010002),
('湖滨',010212,010002),
('流水苑',010213,010002),
('莫衙营',010214,010002),
('天水',010215,010002),
('长庆',010216,010002),
('下沙物美',010301,010003),
('下沙金沙湖',010302,010003),
('钱江新城',010303,010003),
('四季青',010304,010003),
('采荷',010305,010003),
('南肖埠',010306,010003),
('九堡',010307,010003),
('闸弄口',010308,010003),
('三里亭',010309,010003),
('火车东站',010310,010003),
('景芳',010311,010003),
('下沙沿江',010312,010003),
('彭埠',010313,010003),
('下沙江滨',010314,010003),
('拱宸桥',010401,010004),
('大关',010402,010004),
('桥西',010403,010004),
('卖鱼桥',010404,010004),
('瓜山',010405,010004),
('祥符',010406,010004),
('米市巷',010407,010004),
('万达',010408,010004),
('长乐',010409,010004),
('文新',010501,010005),
('益乐',010502,010005),
('翠苑',010503,010005),
('申花',010504,010005),
('九莲',010505,010005),
('文教',010506,010005),
('学军',010507,010005),
('古荡',010508,010005),
('黄龙',010509,010005),
('三敦',010510,010005),
('转塘',010511,010005),
('西溪',010512,010005),
('保俶路',010513,010005),
('文苑',010514,010005),
('骆家庄',010515,010005),
('西兴',010601,010006),
('浦沿',010601,010006),
('一桥南',010602,010006),
('滨盛',010603,010006),
('四桥南',010604,010006),
('区政府',010605,010006),
('奥体',010606,010006),
('长河',010607,010006),
('开发区',010701,010007),
('北干',010702,010007),
('城厢',010703,010007),
('宁围',010704,010007),
('蜀山',010705,010007),
('临平',010801,010008),
('老余杭',010802,010008),
('良渚',010803,010008),
('闲林',010804,010008),
('未来科技城',010805,010008);

area.java：

package ××;

import java.util.List;

public class Area {

    private Integer id;
    private String name;
    private Integer areaId;
    private Integer parentId;
    private List<Area> subAreas;//子区域列表，用于关联
    public List<Area> getSubAreas() {
        return subAreas;
    }

    public void setSubAreas(List<Area> subAreas) {
        this.subAreas = subAreas;
    }

    /×无关代码删除×/
}

area mapper文件：

<?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="zushangfang.dao.AreaMapper">
  <resultMap id="BaseResultMap" type="zushangfang.pojo.Area">
    <!--
      WARNING - @mbg.generated
      This element is automatically generated by MyBatis Generator, do not modify.
      This element was generated on Tue Mar 21 19:12:33 CST 2017.
    -->
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="area_id" jdbcType="INTEGER" property="areaId" />
    <result column="parent_id" jdbcType="INTEGER" property="parentId" />

  </resultMap>
  <resultMap type="zushangfang.pojo.Area" id="areas">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="area_id" jdbcType="INTEGER" property="areaId" />
    <result column="parent_id" jdbcType="INTEGER" property="parentId" />
    <collection property="subAreas" ofType="zushangfang.pojo.Area">
        <id column="id" jdbcType="INTEGER" property="id" />
        <result column="name" jdbcType="VARCHAR" property="name" />
        <result column="area_id" jdbcType="INTEGER" property="areaId" />
        <result column="parent_id" jdbcType="INTEGER" property="parentId" />
    </collection>
  </resultMap>
  <!--因为是一对多的关系，用collection-->
  <select id="queryCity" parameterType="java.lang.Integer" resultMap="areas">
    select
    a.id, a.name, a.area_id, a.parent_id
    from area a left join area b on a.parent_id = b.area_id  where b.parent_id = #{id}
  </select>
  <!-- 一对多的查询，用左连接，查询需要0.00057s,用内连接需要耗时0.262s-->
  <!--做连接的where需要在查询好后再写，也就是，尽量能不用子查询就不用子查询-->
  <!--删除无关代码-->
</mapper>

查询方法：
-@Override public List<Area> queryCity(int cityNum) { // TODO Auto-generated method stub List<Area> areas = areaMapper.queryCity(cityNum); System.out.println(areas.size()); if (areas.size() > 0) { List<Area> subLists = areas.get(0).getSubAreas(); // System.out.println(areas.get(0).getSubAreas()); if (subLists.size() > 0) { System.out.println(subLists.get(0).getName()); } } return areas == null ? new ArrayList<Area>() : areas; }
查询结果：

信息: Server startup in 4809 ms
83
四季青

查询结果

一般的级联查询到这里就可以了，但是，凡是就怕个但是，这是对同一张表进行的级联，上面的sql语句中除了因为a与b的位置写反以外，还存在一个问题：
没有对两个表的别名作区分，因为如果是同一张表(准确的说是有相同列名的表)，如果不对列的别名作区分的话，最终结果的子列表中的数据是不正确的，这里因为同表很特殊，我取到的三级地区83个，每个对象的子列表中保存的也是三级地区83个。
最终修改的sql如下：

<select id="queryCity" parameterType="java.lang.Integer" resultMap="areas">
    select
    a.id, a.name, a.area_id,b.id bid,b.name bname,b.area_id barea_id
    from area a left join area b on b.parent_id = a.area_id  where a.parent_id=#{id};
  </select>