通过mybatis递归获取树结构数据

sql：

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50724
 Source Host           : localhost:3306
 Source Schema         : local_test

 Target Server Type    : MySQL
 Target Server Version : 50724
 File Encoding         : 65001

 Date: 25/03/2020 17:23:37
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for citys
-- ----------------------------
DROP TABLE IF EXISTS `citys`;
CREATE TABLE `citys`  (
  `id` int(5) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `pid` int(5) NULL DEFAULT NULL COMMENT '父id',
  `city` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '城市',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of citys
-- ----------------------------
INSERT INTO `citys` VALUES (1, 0, '湖南');
INSERT INTO `citys` VALUES (2, 0, '江西');
INSERT INTO `citys` VALUES (3, 0, '广东');
INSERT INTO `citys` VALUES (4, 1, '长沙');
INSERT INTO `citys` VALUES (5, 1, '常德');
INSERT INTO `citys` VALUES (6, 1, '岳阳');
INSERT INTO `citys` VALUES (7, 2, '萍乡');

SET FOREIGN_KEY_CHECKS = 1;
View Code

 

 

先上代码：

 

这个是pojo类

 1 import com.baomidou.mybatisplus.annotation.TableField;
 2 import com.baomidou.mybatisplus.annotation.TableId;
 3 import com.baomidou.mybatisplus.annotation.TableName;
 4 import lombok.Data;
 5 
 6 import java.util.List;
 7 
 8 
 9 @TableName("citys")
10 @Data
11 public class Citys {
12     @TableId("id")
13     private Integer id;
14 
15     @TableField("pid")
16     private Integer pId;
17 
18 
19     @TableField("city")
20     private String city;
21 
22 
23     @TableField(exist = false)
24     private List<Citys> list;
25 }

 

 

这个是Mapper

 1 import com.bjsxt.entity.Citys;
 2 
 3 import java.util.List;
 4 
 5 public interface CitysMapper {
 6 
 7     List<Citys> getAll();
 8 
 9     List<Citys> getCitys();
10 }

 

 

这个是mapper.xml

 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 3 <mapper namespace="com.demo.mapper.CitysMapper">
 4 
 5     <resultMap id="BaseResultMap" type="com.bjsxt.entity.Citys">
 6         <id column="id" jdbcType="BIGINT" property="id"/>
 7         <result column="pid" jdbcType="BIGINT" property="pId"/>
 8         <result column="city" jdbcType="VARCHAR" property="city"/>
 9         <collection property="list" ofType="citys" select="getCitysByPid" column="{pid=id}"/>
10 
11     </resultMap>
12 
13     <sql id="Base_Column_List">
14         id,pid,city
15     </sql>
16 
17 
18     <select id="getAll" resultType="citys">
19         select * from citys
20 
21     </select>
22 
23     <select id="getCitys" resultMap="BaseResultMap">
24         select
25         <include refid="Base_Column_List"/>
26         from citys
27         where pid=0
28 
29     </select>
30 
31 
32     <select id="getCitysByPid" resultMap="BaseResultMap">
33         select 
34         <include refid="Base_Column_List"/>
35         from citys
36         where pid=#{pid}
37 
38     </select>
39  
40 </mapper>

 

直接调用：

1 @Test
2     public void testGetCitys(){
3         List<Citys> list = this.citysMapper.getCitys();
4         list.forEach(System.out::println);
5     }

 

打印结果：

 

posted @ 2020-03-26 21:41  鳄鱼菌  阅读(1754)  评论(0)  编辑  收藏  举报