发表更新持久层框架0次访问

MyBatis的递归

前提说明

1.MyBatis递归主要在XML文件和Mapper接口以及对应的实体类,和其它的调用没什么太大的关系,这就只提供XML和Mapper接口以及对应的实体类文件
2.建议所有自己写的字段、表名等等都加用"``"包裹起来，因为防止出现数据库的关键字导致SQL语句错误！
3.先看实例,后面会有讲解说明的.

MyBatis同一张表中多条件递归

建表SQL

建表SQL
1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE `menu` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '菜单表ID',
  `path` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '前端路由的地址',
  `name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '菜单名称',
  `icon` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Icon图标',
  `parentID` int NOT NULL DEFAULT '0' COMMENT '菜单项父类ID(0为一级菜单，其余下级菜单对应本表的id字段的值)',
  `component` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '前端组件名称',
  `url` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '对应后台的URL地址路径',
  `isDelete` tinyint(1) DEFAULT '0' COMMENT '菜单是否删除({删除:1,未删除:0})',
  `index` int NOT NULL DEFAULT '0' COMMENT '排序',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

部分数据

实例代码

>folded 实体类
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
package top.nieqiang.auth.entity;
import java.util.LinkedList;
import java.util.List;
public class Menu {
    private Integer id;
    private String path;
    private String name;
    private String icon;
    private Integer parentID;
    private String component;
    private String url;
    private Boolean isDelete;
    private Integer index;
    
    //以下字段非数据库表对应字段
    private List<Menu> children = new LinkedList<>();
    private Long adminID;
    //省略getter和setter方法
}
>folded 菜单XML
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
<?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="top.nieqiang.auth.mapper.MenuMapper">
    <resultMap id="BaseResultMap" type="top.nieqiang.auth.entity.Menu">
        <id column="id" jdbcType="INTEGER" property="id" />
        <result column="path" jdbcType="VARCHAR" property="path" />
        <result column="name" jdbcType="VARCHAR" property="name" />
        <result column="icon" jdbcType="VARCHAR" property="icon" />
        <result column="parentID" jdbcType="INTEGER" property="parentID" />
        <result column="component" jdbcType="VARCHAR" property="component" />
        <result column="url" jdbcType="VARCHAR" property="url" />
        <result column="isDelete" jdbcType="BIT" property="isDelete" />
        <result column="index" jdbcType="INTEGER" property="index" />
    </resultMap>

    <resultMap id="menuTree" type="top.nieqiang.auth.entity.Menu" extends="BaseResultMap">
        <collection column="{parentID=id,adminID=adminID}" property="children" select="findMenuByRole" ofType="top.nieqiang.auth.entity.Menu">
            <id column="id" jdbcType="INTEGER" property="id" />
            <result column="path" jdbcType="VARCHAR" property="path" />
            <result column="name" jdbcType="VARCHAR" property="name" />
            <result column="icon" jdbcType="VARCHAR" property="icon" />
            <result column="parentID" jdbcType="INTEGER" property="parentID" />
            <result column="component" jdbcType="VARCHAR" property="component" />
            <result column="url" jdbcType="VARCHAR" property="url" />
            <result column="isDelete" jdbcType="BIT" property="isDelete" />
            <result column="index" jdbcType="INTEGER" property="index" />
            <result column="adminID" jdbcType="BIGINT" property="adminID" />
        </collection>
    </resultMap>

    <sql id="Base_Column_List">
        id, path, name, icon, parentID, component, url, isDelete, `index`, adminID
    </sql>

    <sql id="Base_Menu_Tree">
        m.id, m.path, m.name, m.icon, m.parentID, m.component, m.url, m.isDelete, m.`index`
    </sql>

    <select id="findMenuByRole" resultMap="menuTree">
        SELECT <include refid="Base_Menu_Tree"/>, ar.adminID AS adminID FROM `admin_role` ar, `menu_role` mr, menu m 
        WHERE ar.`adminID`=#{adminID} AND ar.`roleID`=mr.`roleID` AND mr.`menuID`=m.`id` AND m.`isDelete`=FALSE AND m.`parentID`=#{parentID} ORDER BY m.index DESC
    </select>
</mapper>
>folded MyBatis的Mapper接口类
1
2
3
4
5
6
7
8
package top.nieqiang.auth.mapper;
import java.util.List;
import org.apache.ibatis.annotations.Mapper;
import top.nieqiang.auth.entity.Menu;
@Mapper
public interface MenuMapper {
    List<Menu> findMenuByRole(Long adminID, Integer parentID);
}
>folded Service实现
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
package top.nieqiang.auth.service.impl;

import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import top.nieqiang.auth.entity.Menu;
import top.nieqiang.auth.mapper.MenuMapper;
import top.nieqiang.auth.service.MenuService;
@Service
public class MenuServiceImpl implements MenuService {
    @Override
    public List<Menu> findMenuByRole(Long adminID) {
        return menuMapper.findMenuByRole(adminID, 0);
    }
}

结果

>folded 返回的结果
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
[
    {
        "id": 1,
        "path": "/home",
        "name": "首 页",
        "icon": "fa fa-list",
        "parentID": 0,
        "component": "Home.vue",
        "url": "/",
        "isDelete": false,
        "index": 0,
        "children": [],
        "adminID": 1
    },{
        "id": 2,
        "path": "/product",
        "name": "商品",
        "icon": "fa fa-list",
        "parentID": 0,
        "component": "Product.vue",
        "url": "/product",
        "isDelete": false,
        "index": 0,
        "children": [
            {
                "id": 3,
                "path": "/product/list",
                "name": "商品列表",
                "icon": "fa fa-user",
                "parentID": 2,
                "component": "Product/ProductList.vue",
                "url": "/product/list",
                "isDelete": false,
                "index": 0,
                "children": [],
                "adminID": 1
            },{
                "id": 4,
                "path": "/product/class",
                "name": "商品分类",
                "icon": "fa fa-user",
                "parentID": 2,
                "component": "Product/ProductClass.vue",
                "url": "/product/detail",
                "isDelete": false,
                "index": 0,
                "children": [],
                "adminID": 1
            },{
                "id": 24,
                "path": "/product/spec",
                "name": "商品规格",
                "icon": "fa fa-list",
                "parentID": 2,
                "component": "Product/ProductSpec.vue",
                "url": "/product/spec",
                "isDelete": false,
                "index": 0,
                "children": [],
                "adminID": 1
            }
        ],
        "adminID": 1
    }
]

解释说明

1.实体类的16和17行对应XML中的17行
2.XML第27行中的select属性对应的是XML中的49行id为findMenuByRole的方法
3.XML第27行中的property属性对应的是实体类中的16行children
3.Service调用Mapper时第二个参数(也就是menuID)为写死0，是因为当Menu中parentID为0的是顶级对象，没有父项的那种，同时menuID在findMenuByRole方法中也等于parentID

MyBatis不同表中单条件递归

上面说了多条件递归,单条件递归那就更简单了.这里举一个游戏规则查询的例子

建表SQL

建表SQL
1
2
3
4
5
6
7
8
CREATE TABLE `rule` (
    `id` int NOT NULL AUTO_INCREMENT COMMENT '拼单规则表自增ID',
    `name` varchar(32) COLLATE utf8mb4_bin NOT NULL COMMENT '规则名称',
    `parentID` int NOT NULL DEFAULT '0' COMMENT '父类ID',
    `createTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `createIP` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '127.0.0.0' COMMENT '创建IP',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

部分数据

实例代码

>folded 实体类
1
2
3
4
5
6
7
8
9
10
11
12
13
14
package com.nieqiang.spell.entity;

import java.util.Date;
import java.util.LinkedList;
import java.util.List;
public class Rule {
    private Integer id;
    private String name;
    private Integer parentID;
    private Date createTime;
    private String createIP;
    private List<Rule> children = new LinkedList<>();
    //getter setter
}
>folded 规则XML
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
<?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.nieqiang.spell.mapper.RuleMapper">
    <resultMap id="BaseResultMap" type="com.nieqiang.spell.entity.Rule">
        <id column="id" jdbcType="INTEGER" property="id" />
        <result column="name" jdbcType="VARCHAR" property="name" />
        <result column="parentID" jdbcType="INTEGER" property="parentID" />
        <result column="createTime" jdbcType="TIMESTAMP" property="createTime" />
        <result column="createIP" jdbcType="VARCHAR" property="createIP" />
    </resultMap>

    <resultMap id="TreeResultMap" type="com.nieqiang.spell.entity.Rule" extends="BaseResultMap">
        <collection column="id" property="children" select="findAll" ofType="com.nieqiang.spell.entity.Rule">
            <id column="id" jdbcType="INTEGER" property="id" />
            <result column="name" jdbcType="VARCHAR" property="name" />
            <result column="parentID" jdbcType="INTEGER" property="parentID" />
            <result column="createTime" jdbcType="TIMESTAMP" property="createTime" />
            <result column="createIP" jdbcType="VARCHAR" property="createIP" />
        </collection>
        
    </resultMap>

    <sql id="Base_Column_List">
        id, name, parentID, createTime, createIP
    </sql>

    <select id="findAll" resultMap="TreeResultMap">
        SELECT <include refid="Base_Column_List" /> FROM rule WHERE parentID = #{parentID}
    </select>
</mapper>
>folded Service实现
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
package com.nieqiang.spell.service.impl;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.nieqiang.spell.entity.Rule;
import com.nieqiang.spell.mapper.RuleMapper;
import com.nieqiang.spell.service.RuleService;
@Service
public class RuleServiceImpl implements RuleService {

    @Autowired
    private RuleMapper ruleMapper;

    @Override
    public List<Rule> findAll() {
        return ruleMapper.findAll(0);
    }
}
>folded MyBatis的Mapper接口类
1
2
3
4
5
6
package com.nieqiang.spell.mapper;
import java.util.List;
import com.nieqiang.spell.entity.Rule;
public interface RuleMapper {
	List<Rule> findAll(int parentID);
}

— END —

#

喜欢这篇文章？打赏一下作者吧

评论

关注我
CSDN

最新文章

归档

分类

链接

标签

Arduino-UNO1
CentOS79
Conda1
Docker11
Elasticsearch6
ElementUI1
Flink CDC1
Hexo主题1
JDK1
JS3
JS/Vue1
Java1
JupyterNotebook1
Linux3
MQ3
MQTT1
Minio4
MyBatis1
MyBatis PageHelper1
MySQL2
Neo4J1
Nginx/CentOS71
Nginx/Docker1
Nginx/Kong1
Nginx/OpenResty2
Nginx/Vue1
Paddle1
Python1
Redis4
RocketMQ1
Shell1
SpringBoot7
SpringBoot/Docker1
SpringCloud1
SpringSecurity2
Ubuntu3
Vue4
xxl-job1
分享2
单片机2
小工具3
微信公众号开发1
数据库4
禅道1
系统9
网络1
脚本2
虚拟机1
×