Mybatis级联查询（多对一、一对多）Demo及问题
程序员文章站
2022-04-16 10:01:18
...
1、问题描述
今天做数据报表的时候，不想使用java代码做各种组合了，直接在MySQL里面搞级联查询，但是遇到了很多问题，在这里记录一下，防止后来的人踩坑。首先搭建一下环境，如果有人想学习，也可以直接拷贝代码运行即可。
2、工程源码
A、首先是数据库部分
sql
create database mango;
use mango;
CREATE TABLE `sys_role` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
`name` varchar(100) DEFAULT NULL COMMENT '角色名称',
`remark` varchar(100) DEFAULT NULL COMMENT '备注',
`create_by` varchar(50) DEFAULT NULL COMMENT '创建人',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`last_update_by` varchar(50) DEFAULT NULL COMMENT '更新人',
`last_update_time` datetime DEFAULT NULL COMMENT '更新时间',
`del_flag` tinyint(4) DEFAULT '0' COMMENT '是否删除 -1：已删除 0：正常',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='角色管理'
CREATE TABLE `sys_user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
`name` varchar(50) NOT NULL COMMENT '用户名',
`nick_name` varchar(150) DEFAULT NULL COMMENT '昵称',
`avatar` varchar(150) DEFAULT NULL COMMENT '头像',
`password` varchar(100) DEFAULT NULL COMMENT '密码',
`salt` varchar(40) DEFAULT NULL COMMENT '加密盐',
`email` varchar(100) DEFAULT NULL COMMENT '邮箱',
`mobile` varchar(100) DEFAULT NULL COMMENT '手机号',
`status` tinyint(4) DEFAULT NULL COMMENT '状态 0：禁用 1：正常',
`dept_id` bigint(20) DEFAULT NULL COMMENT '机构ID',
`create_by` varchar(50) DEFAULT NULL COMMENT '创建人',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`last_update_by` varchar(50) DEFAULT NULL COMMENT '更新人',
`last_update_time` datetime DEFAULT NULL COMMENT '更新时间',
`del_flag` tinyint(4) DEFAULT '0' COMMENT '是否删除 -1：已删除 0：正常',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8 COMMENT='用户管理'
CREATE TABLE `sys_user_role` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
`user_id` bigint(20) DEFAULT NULL COMMENT '用户ID',
`role_id` bigint(20) DEFAULT NULL COMMENT '角色ID',
`create_by` varchar(50) DEFAULT NULL COMMENT '创建人',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`last_update_by` varchar(50) DEFAULT NULL COMMENT '更新人',
`last_update_time` datetime DEFAULT NULL COMMENT '更新时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=90 DEFAULT CHARSET=utf8 COMMENT='用户角色'
CREATE TABLE `sys_dept` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
`name` varchar(50) DEFAULT NULL COMMENT '机构名称',
`parent_id` bigint(20) DEFAULT NULL COMMENT '上级机构ID，一级机构为0',
`order_num` int(11) DEFAULT NULL COMMENT '排序',
`create_by` varchar(50) DEFAULT NULL COMMENT '创建人',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`last_update_by` varchar(50) DEFAULT NULL COMMENT '更新人',
`last_update_time` datetime DEFAULT NULL COMMENT '更新时间',
`del_flag` tinyint(4) DEFAULT '0' COMMENT '是否删除 -1：已删除 0：正常',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='机构管理'
INSERT INTO `sys_user_role`(`id`, `user_id`, `role_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`) VALUES (1, 1, 1, NULL, NULL, NULL, NULL);
INSERT INTO `sys_user_role`(`id`, `user_id`, `role_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`) VALUES (2, 2, 1, NULL, NULL, NULL, NULL);
INSERT INTO `sys_user_role`(`id`, `user_id`, `role_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`) VALUES (26, 5, 3, NULL, NULL, NULL, NULL);
INSERT INTO `sys_user_role`(`id`, `user_id`, `role_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`) VALUES (33, 6, 2, NULL, NULL, NULL, NULL);
INSERT INTO `sys_user_role`(`id`, `user_id`, `role_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`) VALUES (34, 4, 2, NULL, NULL, NULL, NULL);
INSERT INTO `sys_user_role`(`id`, `user_id`, `role_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`) VALUES (35, 9, 2, NULL, NULL, NULL, NULL);
INSERT INTO `sys_user_role`(`id`, `user_id`, `role_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`) VALUES (36, 10, 3, NULL, NULL, NULL, NULL);
INSERT INTO `sys_user_role`(`id`, `user_id`, `role_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`) VALUES (37, 11, 2, NULL, NULL, NULL, NULL);
INSERT INTO `sys_user_role`(`id`, `user_id`, `role_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`) VALUES (38, 12, 3, NULL, NULL, NULL, NULL);
INSERT INTO `sys_user_role`(`id`, `user_id`, `role_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`) VALUES (39, 15, 2, NULL, NULL, NULL, NULL);
INSERT INTO `sys_user_role`(`id`, `user_id`, `role_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`) VALUES (41, 16, 3, NULL, NULL, NULL, NULL);
INSERT INTO `sys_user_role`(`id`, `user_id`, `role_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`) VALUES (42, 8, 2, NULL, NULL, NULL, NULL);
INSERT INTO `sys_user_role`(`id`, `user_id`, `role_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`) VALUES (43, 7, 4, NULL, NULL, NULL, NULL);
INSERT INTO `sys_user_role`(`id`, `user_id`, `role_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`) VALUES (45, 18, 2, NULL, NULL, NULL, NULL);
INSERT INTO `sys_user_role`(`id`, `user_id`, `role_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`) VALUES (46, 17, 3, NULL, NULL, NULL, NULL);
INSERT INTO `sys_user_role`(`id`, `user_id`, `role_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`) VALUES (47, 3, 4, NULL, NULL, NULL, NULL);
INSERT INTO `sys_user_role`(`id`, `user_id`, `role_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`) VALUES (48, 21, 2, NULL, NULL, NULL, NULL);
INSERT INTO `sys_user_role`(`id`, `user_id`, `role_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`) VALUES (57, 31, 2, NULL, NULL, NULL, NULL);
INSERT INTO `sys_user_role`(`id`, `user_id`, `role_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`) VALUES (58, 30, 2, NULL, NULL, NULL, NULL);
INSERT INTO `sys_user_role`(`id`, `user_id`, `role_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`) VALUES (59, 32, 3, NULL, NULL, NULL, NULL);
INSERT INTO `sys_user_role`(`id`, `user_id`, `role_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`) VALUES (73, 33, 8, NULL, NULL, NULL, NULL);
INSERT INTO `sys_user_role`(`id`, `user_id`, `role_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`) VALUES (74, 25, 8, NULL, NULL, NULL, NULL);
INSERT INTO `sys_user_role`(`id`, `user_id`, `role_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`) VALUES (75, 25, 2, NULL, NULL, NULL, NULL);
INSERT INTO `sys_user_role`(`id`, `user_id`, `role_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`) VALUES (80, 22, 2, NULL, NULL, NULL, NULL);
INSERT INTO `sys_user_role`(`id`, `user_id`, `role_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`) VALUES (81, 23, 3, NULL, NULL, NULL, NULL);
INSERT INTO `sys_user_role`(`id`, `user_id`, `role_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`) VALUES (82, 24, 4, NULL, NULL, NULL, NULL);
INSERT INTO `sys_user_role`(`id`, `user_id`, `role_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`) VALUES (83, 26, 3, NULL, NULL, NULL, NULL);
INSERT INTO `sys_user_role`(`id`, `user_id`, `role_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`) VALUES (85, 29, 2, NULL, NULL, NULL, NULL);
INSERT INTO `sys_user_role`(`id`, `user_id`, `role_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`) VALUES (86, 28, 4, NULL, NULL, NULL, NULL);
INSERT INTO `sys_user_role`(`id`, `user_id`, `role_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`) VALUES (87, 27, 3, NULL, NULL, NULL, NULL);
INSERT INTO `sys_user_role`(`id`, `user_id`, `role_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`) VALUES (88, 39, 4, NULL, NULL, NULL, NULL);
INSERT INTO `sys_user_role`(`id`, `user_id`, `role_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`) VALUES (89, 39, 3, NULL, NULL, NULL, NULL);
INSERT INTO `sys_role`(`id`, `name`, `remark`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (1, 'admin', '超级管理员', 'admin', '2019-01-19 11:11:11', 'admin', '2019-01-19 19:07:18', 0);
INSERT INTO `sys_role`(`id`, `name`, `remark`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (2, 'mng', '项目经理', 'admin', '2019-01-19 11:11:11', 'admin', '2019-01-19 11:39:28', 0);
INSERT INTO `sys_role`(`id`, `name`, `remark`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (3, 'chenwei', '陈伟', 'admin', '2020-05-07 15:52:53', 'admin', '2020-05-07 15:52:59', 0);
INSERT INTO `sys_role`(`id`, `name`, `remark`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (4, 'test', '测试人员', 'admin', '2019-01-19 11:11:11', 'admin', '2019-01-19 11:11:11', 0);
INSERT INTO `sys_dept`(`id`, `name`, `parent_id`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (1, '轻尘集团', NULL, 0, 'admin', '2018-09-23 19:35:22', NULL, NULL, 0);
INSERT INTO `sys_dept`(`id`, `name`, `parent_id`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (2, '牧尘集团', NULL, 1, 'admin', '2018-09-23 19:35:55', NULL, NULL, 0);
INSERT INTO `sys_dept`(`id`, `name`, `parent_id`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (3, '三国集团', NULL, 2, 'admin', '2018-09-23 19:36:24', NULL, NULL, 0);
INSERT INTO `sys_dept`(`id`, `name`, `parent_id`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (4, '上海分公司', 2, 0, 'admin', '2018-09-23 19:37:03', NULL, NULL, 0);
INSERT INTO `sys_dept`(`id`, `name`, `parent_id`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (5, '北京分公司', 1, 1, 'admin', '2018-09-23 19:37:17', NULL, NULL, 0);
INSERT INTO `sys_dept`(`id`, `name`, `parent_id`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (6, '北京分公司', 2, 1, 'admin', '2018-09-23 19:37:28', NULL, NULL, 0);
INSERT INTO `sys_dept`(`id`, `name`, `parent_id`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (7, '技术部', 5, 0, 'admin', '2018-09-23 19:38:00', NULL, NULL, 0);
INSERT INTO `sys_dept`(`id`, `name`, `parent_id`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (8, '技术部', 4, 0, 'admin', '2018-09-23 19:38:10', NULL, NULL, 0);
INSERT INTO `sys_dept`(`id`, `name`, `parent_id`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (9, '技术部', 6, 0, 'admin', '2018-09-23 19:38:17', NULL, NULL, 0);
INSERT INTO `sys_dept`(`id`, `name`, `parent_id`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (10, '市场部', 5, 0, 'admin', '2018-09-23 19:38:45', NULL, NULL, 0);
INSERT INTO `sys_dept`(`id`, `name`, `parent_id`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (11, '市场部', 6, 0, 'admin', '2018-09-23 19:39:01', NULL, NULL, 0);
INSERT INTO `sys_dept`(`id`, `name`, `parent_id`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (12, '魏国', 3, 0, 'admin', '2018-09-23 19:40:42', NULL, NULL, 0);
INSERT INTO `sys_dept`(`id`, `name`, `parent_id`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (13, '蜀国', 3, 1, 'admin', '2018-09-23 19:40:54', NULL, NULL, 0);
INSERT INTO `sys_dept`(`id`, `name`, `parent_id`, `order_num`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (14, '吴国', 3, 2, 'admin', '2018-09-23 19:41:04', NULL, NULL, 0);
INSERT INTO `sys_user`(`id`, `name`, `nick_name`, `avatar`, `password`, `salt`, `email`, `mobile`, `status`, `dept_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (1, 'admin', '字节跳动万岁', NULL, 'bd1718f058d8a02468134432b8656a86', 'YzcmCZNvbXocrsz9dm8e', 'aaa@qq.com', '13612345678', 1, 4, 'admin', '2018-08-14 11:11:11', 'admin', '2018-08-14 11:11:11', 0);
INSERT INTO `sys_user`(`id`, `name`, `nick_name`, `avatar`, `password`, `salt`, `email`, `mobile`, `status`, `dept_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (22, 'liubei', '刘备', NULL, 'fd80ebd493a655608dc893a9f897d845', 'YzcmCZNvbXocrsz9dm8e', 'aaa@qq.com', '13889700023', 1, 7, 'admin', '2018-09-23 19:43:00', 'admin', '2019-01-10 11:41:13', 0);
INSERT INTO `sys_user`(`id`, `name`, `nick_name`, `avatar`, `password`, `salt`, `email`, `mobile`, `status`, `dept_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (23, 'zhaoyun', '赵云', NULL, 'fd80ebd493a655608dc893a9f897d845', 'YzcmCZNvbXocrsz9dm8e', 'aaa@qq.com', '13889700023', 1, 7, 'admin', '2018-09-23 19:43:44', 'admin', '2018-09-23 19:43:52', 0);
INSERT INTO `sys_user`(`id`, `name`, `nick_name`, `avatar`, `password`, `salt`, `email`, `mobile`, `status`, `dept_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (24, 'zhugeliang', '诸葛亮', NULL, 'fd80ebd493a655608dc893a9f897d845', 'YzcmCZNvbXocrsz9dm8e', 'aaa@qq.com', '13889700023', 7, 11, 'admin', '2018-09-23 19:44:23', 'admin', '2018-09-23 19:44:29', 0);
INSERT INTO `sys_user`(`id`, `name`, `nick_name`, `avatar`, `password`, `salt`, `email`, `mobile`, `status`, `dept_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (25, 'caocao', '曹操', NULL, 'fd80ebd493a655608dc893a9f897d845', 'YzcmCZNvbXocrsz9dm8e', 'aaa@qq.com', '13889700023', 1, 8, 'admin', '2018-09-23 19:45:32', 'admin', '2019-01-10 17:59:14', 0);
INSERT INTO `sys_user`(`id`, `name`, `nick_name`, `avatar`, `password`, `salt`, `email`, `mobile`, `status`, `dept_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (26, 'dianwei', '典韦', NULL, 'fd80ebd493a655608dc893a9f897d845', 'YzcmCZNvbXocrsz9dm8e', 'aaa@qq.com', '13889700023', 1, 10, 'admin', '2018-09-23 19:45:48', 'admin', '2018-09-23 19:45:57', 0);
INSERT INTO `sys_user`(`id`, `name`, `nick_name`, `avatar`, `password`, `salt`, `email`, `mobile`, `status`, `dept_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (27, 'xiahoudun', '夏侯惇', NULL, 'fd80ebd493a655608dc893a9f897d845', 'YzcmCZNvbXocrsz9dm8e', 'aaa@qq.com', '13889700023', 1, 8, 'admin', '2018-09-23 19:46:09', 'admin', '2018-09-23 19:46:17', 0);
INSERT INTO `sys_user`(`id`, `name`, `nick_name`, `avatar`, `password`, `salt`, `email`, `mobile`, `status`, `dept_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (28, 'xunyu', '荀彧', NULL, 'fd80ebd493a655608dc893a9f897d845', 'YzcmCZNvbXocrsz9dm8e', 'aaa@qq.com', '13889700023', 1, 10, 'admin', '2018-09-23 19:46:38', 'admin', '2018-11-04 15:33:17', 0);
INSERT INTO `sys_user`(`id`, `name`, `nick_name`, `avatar`, `password`, `salt`, `email`, `mobile`, `status`, `dept_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (29, 'sunquan', '孙权', NULL, 'fd80ebd493a655608dc893a9f897d845', 'YzcmCZNvbXocrsz9dm8e', 'aaa@qq.com', '13889700023', 1, 10, 'admin', '2018-09-23 19:46:54', 'admin', '2018-09-23 19:47:03', 0);
INSERT INTO `sys_user`(`id`, `name`, `nick_name`, `avatar`, `password`, `salt`, `email`, `mobile`, `status`, `dept_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (30, 'zhouyu', '周瑜', NULL, 'fd80ebd493a655608dc893a9f897d845', 'YzcmCZNvbXocrsz9dm8e', 'aaa@qq.com', '13889700023', 1, 11, 'admin', '2018-09-23 19:47:28', 'admin', '2018-09-23 19:48:04', 0);
INSERT INTO `sys_user`(`id`, `name`, `nick_name`, `avatar`, `password`, `salt`, `email`, `mobile`, `status`, `dept_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (31, 'luxun', '陆逊', NULL, 'fd80ebd493a655608dc893a9f897d845', 'YzcmCZNvbXocrsz9dm8e', 'aaa@qq.com', '13889700023', 1, 11, 'admin', '2018-09-23 19:47:44', 'admin', '2018-09-23 19:47:58', 0);
INSERT INTO `sys_user`(`id`, `name`, `nick_name`, `avatar`, `password`, `salt`, `email`, `mobile`, `status`, `dept_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (32, 'huanggai', '黄盖', NULL, 'fd80ebd493a655608dc893a9f897d845', 'YzcmCZNvbXocrsz9dm8e', 'aaa@qq.com', '13889700023', 1, 11, 'admin', '2018-09-23 19:48:38', 'admin', '2018-09-23 19:49:02', 0);
INSERT INTO `sys_user`(`id`, `name`, `nick_name`, `avatar`, `password`, `salt`, `email`, `mobile`, `status`, `dept_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (33, 'zhangsan', '张三', NULL, NULL, NULL, 'aaa@qq.com', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0);
INSERT INTO `sys_user`(`id`, `name`, `nick_name`, `avatar`, `password`, `salt`, `email`, `mobile`, `status`, `dept_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (34, 'zhangsan1', '张三1', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0);
INSERT INTO `sys_user`(`id`, `name`, `nick_name`, `avatar`, `password`, `salt`, `email`, `mobile`, `status`, `dept_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (35, 'zhangsan2', '张三2', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0);
INSERT INTO `sys_user`(`id`, `name`, `nick_name`, `avatar`, `password`, `salt`, `email`, `mobile`, `status`, `dept_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (37, 'zhangsan3', '张三3', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0);
INSERT INTO `sys_user`(`id`, `name`, `nick_name`, `avatar`, `password`, `salt`, `email`, `mobile`, `status`, `dept_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (38, 'zhangsan4', '张三4', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0);
INSERT INTO `sys_user`(`id`, `name`, `nick_name`, `avatar`, `password`, `salt`, `email`, `mobile`, `status`, `dept_id`, `create_by`, `create_time`, `last_update_by`, `last_update_time`, `del_flag`) VALUES (39, 'zhangsan5', '张三5', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0);
这里有四个表，说明一下：sys_user和sys_dept是多对一关系，sys_role和sys_user是多对一关系。sys_user_role是sys_user和sys_role的关联表，这里主要是之前想在java代码里面操作的时候建立的第三方表，如果直接使用MySQL级联查询，感觉这个是多余的(而且是副作用，读者可以自行体会，但是我们接下来把这个问题都解决了，其他都SO EASY!)。
B、工程代码
新建项目什么的我就不演示了，需要的可以自行查阅相关资料。这里直接给出源码。
pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.2.6.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>cn.edu.njust</groupId>
<artifactId>mango</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>mango</name>
<description>Demo project for Spring Boot</description>
<properties>
<java.version>1.8</java.version>
<!--跳过测试-->
<skipTests>true</skipTests>
</properties>
<dependencies>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
<exclusion>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-logging</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger2</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-swagger-ui</artifactId>
<version>2.9.2</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<!--mapper 引入可以使用通用mapper-->
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper-spring-boot-starter</artifactId>
<version>1.2.4</version>
<exclusions>
<exclusion>
<artifactId>mybatis</artifactId>
<groupId>org.mybatis</groupId>
</exclusion>
<exclusion>
<artifactId>mybatis-spring</artifactId>
<groupId>org.mybatis</groupId>
</exclusion>
</exclusions>
</dependency>
<!--pagehelper 引入分页插件依赖的jar包-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.3</version>
<exclusions>
<exclusion>
<artifactId>mybatis-spring-boot-starter</artifactId>
<groupId>org.mybatis.spring.boot</groupId>
</exclusion>
</exclusions>
</dependency>
<!--日志-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-log4j</artifactId>
<version>1.3.3.RELEASE</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<!--druid-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.10</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
<!--引入分页插件-->
<plugin>
<groupId>org.mybatis.generator</groupId>
<artifactId>mybatis-generator-maven-plugin</artifactId>
<version>1.3.2</version>
<configuration>
<verbose>true</verbose>
<overwrite>true</overwrite>
</configuration>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper</artifactId>
<version>4.0.0</version>
</dependency>
</dependencies>
</plugin>
</plugins>
</build>
</project>
application.yml
server:
port: 8005
logging:
config: classpath:log4j.properties
level:
dao: debug
org:
mybatis: debug
spring:
datasource:
driver-class-name: com.mysql.jdbc.Driver
username: root
password: root
url: jdbc:mysql://localhost:3306/mango?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8
druid:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/mango?useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
username: root
password: root
filters: stat,wall,log4j,config
max-active: 100
initial-size: 1
max-wait: 60000
min-idle: 1
time-between-eviction-runs-millis: 60000
min-evictable-idle-time-millis: 300000
validation-query: select 'x'
test-while-idle: true
test-on-borrow: false
test-on-return: false
pool-prepared-statements: true
max-open-prepared-statements: 50
max-pool-prepared-statement-per-connection-size: 20
jackson:
default-property-inclusion: non_null
# 指定数据库中表生成实体的路径
mybatis:
type-aliases-package: cn.edu.njust.mango.entity
mapper-locations: classpath:mapper/*.xml
configuration:
# 输出sql日志信息
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
#pagehelper
pagehelper:
helperDialect: mysql
reasonable: true
supportMethodsArguments: true
params:
count: countSql
log4j.properties
#Console Log
log4j.rootLogger=INFO,console,debug,info,warn,error
LOG_PATTERN=[%d{yyyy-MM-dd HH:mm:ss.SSS}] boot%X{context} - %5p [%t] --- %c{1}: %m%n
#A1--Print log to Console
log4j.appender.console=org.apache.log4j.ConsoleAppender
log4j.appender.console.Threshold=DEBUG
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=${LOG_PATTERN}
log4j.appender.info=org.apache.log4j.DailyRollingFileAppender
log4j.appender.info.Threshold=INFO
log4j.appender.info.File=${LOG_PATH}/${LOG_FILE}_info.log
log4j.appender.info.DatePattern='.'yyyy-MM-dd
log4j.appender.info.layout = org.apache.log4j.PatternLayout
log4j.appender.info.layout.ConversionPattern=${LOG_PATTERN}
log4j.appender.error=org.apache.log4j.DailyRollingFileAppender
log4j.appender.error.Threshold=ERROR
log4j.appender.error.File=${LOG_PATH}/${LOG_FILE}_error.log
log4j.appender.error.DatePattern='.'yyyy-MM-dd
log4j.appender.error.layout = org.apache.log4j.PatternLayout
log4j.appender.error.layout.ConversionPattern=${LOG_PATTERN}
log4j.appender.debug=org.apache.log4j.DailyRollingFileAppender
log4j.appender.debug.Threshold=DEBUG
log4j.appender.debug.File=${LOG_PATH}/${LOG_FILE}_debug.log
log4j.appender.debug.DatePattern='.'yyyy-MM-dd
log4j.appender.debug.layout = org.apache.log4j.PatternLayout
log4j.appender.debug.layout.ConversionPattern=${LOG_PATTERN}
log4j.appender.warn=org.apache.log4j.DailyRollingFileAppender
log4j.appender.warn.Threshold=WARN
log4j.appender.warn.File=${LOG_PATH}/${LOG_FILE}_warn.log
log4j.appender.warn.DatePattern='.'yyyy-MM-dd
log4j.appender.warn.layout = org.apache.log4j.PatternLayout
log4j.appender.warn.layout.ConversionPattern=${LOG_PATTERN}
SysUser.java
package cn.edu.njust.mango.entity;
import io.swagger.annotations.ApiModel;
import lombok.Data;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import java.io.Serializable;
import java.util.Date;
/**
* 用户管理(SysUser)实体类
*
* @author Chen
* @since 2020-05-06 15:24:25
*/
@Data
@ApiModel("用户管理")
public class SysUser implements Serializable {
private static final long serialVersionUID = -44575028142368237L;
/**
* 编号
*/
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
/**
* 用户名
*/
private String name;
/**
* 昵称
*/
private String nickName;
/**
* 头像
*/
private String avatar;
/**
* 密码
*/
private String password;
/**
* 加密盐
*/
private String salt;
/**
* 邮箱
*/
private String email;
/**
* 手机号
*/
private String mobile;
/**
* 状态 0：禁用 1：正常
*/
private Object status;
/**
* 机构ID
*/
private Long deptId;
/**
* 创建人
*/
private String createBy;
/**
* 创建时间
*/
private Date createTime;
/**
* 更新人
*/
private String lastUpdateBy;
/**
* 更新时间
*/
private Date lastUpdateTime;
/**
* 是否删除 -1：已删除 0：正常
*/
private Object delFlag;
}
SysUserRole.java
package cn.edu.njust.mango.entity;
import java.util.Date;
import javax.persistence.GeneratedValue;
import java.io.Serializable;
import io.swagger.annotations.*;
import lombok.Data;
import javax.persistence.*;
/**
* 用户角色(SysUserRole)实体类
*
* @author Chen
* @since 2020-05-06 15:24:25
*/
@Data
@ApiModel("用户角色")
public class SysUserRole implements Serializable {
private static final long serialVersionUID = -48730270108013484L;
/**
* 编号
*/
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
/**
* 用户ID
*/
private Long userId;
/**
* 角色ID
*/
private Long roleId;
/**
* 创建人
*/
private String createBy;
/**
* 创建时间
*/
private Date createTime;
/**
* 更新人
*/
private String lastUpdateBy;
/**
* 更新时间
*/
private Date lastUpdateTime;
}
SysRole.java
package cn.edu.njust.mango.entity;
import java.util.Date;
import javax.persistence.GeneratedValue;
import java.io.Serializable;
import io.swagger.annotations.*;
import lombok.Data;
import javax.persistence.*;
/**
* 角色管理(SysRole)实体类
*
* @author Chen
* @since 2020-05-06 15:24:25
*/
@Data
@ApiModel("角色管理")
public class SysRole implements Serializable {
private static final long serialVersionUID = -79886721138103042L;
/**
* 编号
*/
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
/**
* 角色名称
*/
private String name;
/**
* 备注
*/
private String remark;
/**
* 创建人
*/
private String createBy;
/**
* 创建时间
*/
private Date createTime;
/**
* 更新人
*/
private String lastUpdateBy;
/**
* 更新时间
*/
private Date lastUpdateTime;
/**
* 是否删除 -1：已删除 0：正常
*/
private Object delFlag;
}
SysDept.java
package cn.edu.njust.mango.entity;
import io.swagger.annotations.ApiModel;
import lombok.Data;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import java.io.Serializable;
import java.util.Date;
/**
* 机构管理(SysDept)实体类
*
* @author Chen
* @since 2020-05-06 15:24:25
*/
@Data
@ApiModel("机构管理")
public class SysDept implements Serializable {
private static final long serialVersionUID = -50559626000073116L;
/**
* 编号
*/
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
/**
* 机构名称
*/
private String name;
/**
* 上级机构ID，一级机构为0
*/
private Long parentId;
/**
* 排序
*/
private Integer orderNum;
/**
* 创建人
*/
private String createBy;
/**
* 创建时间
*/
private Date createTime;
/**
* 更新人
*/
private String lastUpdateBy;
/**
* 更新时间
*/
private Date lastUpdateTime;
/**
* 是否删除 -1：已删除 0：正常
*/
private Object delFlag;
}
SysUserVO.java 这里为了不和单表操作相耦合，直接搞一个
VO类，里面存放多对一及一对多的数据。
package cn.edu.njust.mango.vo;
import cn.edu.njust.mango.entity.SysDept;
import cn.edu.njust.mango.entity.SysRole;
import cn.edu.njust.mango.entity.SysUser;
import lombok.Data;
import java.util.List;
/**
* @author Chen
* @version 1.0
* @date 2020/5/7 7:31
* @description:
*/
@Data
public class SysUserVO extends SysUser {
/**
* 非数据库字段
*/
private SysDept dept;
/**
* 非数据库字段
*/
private List<SysRole> roles;
}
SysUserMapper.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="cn.edu.njust.mango.mapper.SysUserMapper">
<resultMap type="cn.edu.njust.mango.entity.SysUser" id="SysUserMap">
<result property="id" column="id" jdbcType="INTEGER"/>
<result property="name" column="name" jdbcType="VARCHAR"/>
<result property="nickName" column="nick_name" jdbcType="VARCHAR"/>
<result property="avatar" column="avatar" jdbcType="VARCHAR"/>
<result property="password" column="password" jdbcType="VARCHAR"/>
<result property="salt" column="salt" jdbcType="VARCHAR"/>
<result property="email" column="email" jdbcType="VARCHAR"/>
<result property="mobile" column="mobile" jdbcType="VARCHAR"/>
<result property="status" column="status" jdbcType="OTHER"/>
<result property="deptId" column="dept_id" jdbcType="INTEGER"/>
<result property="createBy" column="create_by" jdbcType="VARCHAR"/>
<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
<result property="lastUpdateBy" column="last_update_by" jdbcType="VARCHAR"/>
<result property="lastUpdateTime" column="last_update_time" jdbcType="TIMESTAMP"/>
<result property="delFlag" column="del_flag" jdbcType="OTHER"/>
</resultMap>
<resultMap id="queryForListMap" type="cn.edu.njust.mango.vo.SysUserVO">
<id column="id" property="id" jdbcType="INTEGER"/>
<result property="name" column="name" jdbcType="VARCHAR"/>
<result property="nickName" column="nick_name" jdbcType="VARCHAR"/>
<result property="avatar" column="avatar" jdbcType="VARCHAR"/>
<result property="password" column="password" jdbcType="VARCHAR"/>
<result property="salt" column="salt" jdbcType="VARCHAR"/>
<result property="email" column="email" jdbcType="VARCHAR"/>
<result property="mobile" column="mobile" jdbcType="VARCHAR"/>
<result property="status" column="status" jdbcType="OTHER"/>
<result property="deptId" column="dept_id" jdbcType="INTEGER"/>
<result property="createBy" column="create_by" jdbcType="VARCHAR"/>
<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
<result property="lastUpdateBy" column="last_update_by" jdbcType="VARCHAR"/>
<result property="lastUpdateTime" column="last_update_time" jdbcType="TIMESTAMP"/>
<result property="delFlag" column="del_flag" jdbcType="OTHER"/>
<association property="dept" javaType="cn.edu.njust.mango.entity.SysDept">
<id property="id" column="d_id" jdbcType="INTEGER"/>
<result property="name" column="d_name" jdbcType="VARCHAR"/>
<result property="parentId" column="d_parent_id" jdbcType="INTEGER"/>
<result property="orderNum" column="d_order_num" jdbcType="INTEGER"/>
<result property="createBy" column="d_create_by" jdbcType="VARCHAR"/>
<result property="createTime" column="d_create_time" jdbcType="TIMESTAMP"/>
<result property="lastUpdateBy" column="d_last_update_by" jdbcType="VARCHAR"/>
<result property="lastUpdateTime" column="d_last_update_time" jdbcType="TIMESTAMP"/>
<result property="delFlag" column="d_del_flag" jdbcType="OTHER"/>
</association>
<collection property="roles" javaType="java.util.List" ofType="cn.edu.njust.mango.entity.SysRole">
<id column="role_id" property="id" jdbcType="INTEGER"/>
<result property="name" column="role_name" jdbcType="VARCHAR"/>
<result property="remark" column="role_remark" jdbcType="VARCHAR"/>
<result property="createBy" column="role_create_by" jdbcType="VARCHAR"/>
<result property="createTime" column="role_create_time" jdbcType="TIMESTAMP"/>
<result property="lastUpdateBy" column="role_last_update_by" jdbcType="VARCHAR"/>
<result property="lastUpdateTime" column="role_last_update_time" jdbcType="TIMESTAMP"/>
<result property="delFlag" column="role_del_flag" jdbcType="OTHER"/>
</collection>
</resultMap>
<select id="getDept" resultType="cn.edu.njust.mango.entity.SysDept" parameterType="INTEGER">
SELECT
*
FROM sys_dept d
where id = #{dept_id}
</select>
<select id="queryAllByPageRequestAndConditionAndRoleAndDept" resultType="cn.edu.njust.mango.vo.SysUserVO">
SELECT
*
FROM
sys_user u
LEFT JOIN sys_user_role ru ON u.id = ru.user_id
LEFT JOIN sys_role r on r.id = ru.role_id
LEFT JOIN sys_dept d on d.id = u.dept_id
</select>
</mapper>
SysUserMapper.java
package cn.edu.njust.mango.mapper;
import cn.edu.njust.mango.base.mapper.IBaseMapper;
import cn.edu.njust.mango.entity.SysUser;
import cn.edu.njust.mango.pojo.PageRequest;
import cn.edu.njust.mango.vo.SysUserVO;
import java.util.List;
/**
* 用户管理(SysUser)表数据库访问层
*
* @author Chen
* @since 2020-05-06 14:16:48
*/
public interface SysUserMapper extends IBaseMapper<SysUser> {
List<SysUserVO> queryAllByPageRequestAndConditionAndRoleAndDept(PageRequest pageRequest);
}
SysUserMapperTest1.java
package cn.edu.njust.mango.mapper;
import cn.edu.njust.mango.pojo.PageRequest;
import cn.edu.njust.mango.vo.SysUserVO;
import org.junit.jupiter.api.Test;
import org.junit.runner.RunWith;
import org.junit.runners.JUnit4;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;
/**
* @author Chen
* @version 1.0
* @date 2020/5/7 15:34
* @description:
*/
@SpringBootTest
@RunWith(JUnit4.class)
class SysUserMapperTest1 {
@Autowired
private SysUserMapper sysUserMapper;
@Test
void queryAllByPageRequestAndConditionAndRoleAndDept() {
PageRequest pageRequest = new PageRequest();
pageRequest.setPageSize(100);
List<SysUserVO> sysUserVOS = sysUserMapper.queryAllByPageRequestAndConditionAndRoleAndDept(pageRequest);
System.out.println(sysUserVOS);
}
}
3、问题解决
好了工程搭建完毕，我们开始跑程序了。为了方便测试，我们打断点查看数据。直接运行测试类。控制台输出如下：
我们在System.out.println(sysUserVOS);处打断点。
然后我们查看数据全是null，难道是我们的多对一，一对多没有设置成功？但是我们查看SQL语句日志，确实是执行了啊！
java
JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@672d8370] will not be managed by Spring
==> Preparing: SELECT count(0) FROM sys_user u LEFT JOIN sys_user_role ru ON u.id = ru.user_id LEFT JOIN sys_role r ON r.id = ru.role_id LEFT JOIN sys_dept d ON d.id = u.dept_id
==> Parameters:
<== Columns: count(0)
<== Row: 20
<== Total: 1
==> Preparing: SELECT * FROM sys_user u LEFT JOIN sys_user_role ru ON u.id = ru.user_id LEFT JOIN sys_role r on r.id = ru.role_id LEFT JOIN sys_dept d on d.id = u.dept_id LIMIT ?
==> Parameters: 100(Integer)
<== Columns: id, name, nick_name, avatar, password, salt, email, mobile, status, dept_id, create_by, create_time, last_update_by, last_update_time, del_flag, id, user_id, role_id, create_by, create_time, last_update_by, last_update_time, id, name, remark, create_by, create_time, last_update_by, last_update_time, del_flag, id, name, parent_id, order_num, create_by, create_time, last_update_by, last_update_time, del_flag
<== Row: 1, admin, 字节跳动万岁, null, bd1718f058d8a02468134432b8656a86, YzcmCZNvbXocrsz9dm8e, admin@qq.com, 13612345678, 1, 4, admin, 2018-08-14 11:11:11.0, admin, 2018-08-14 11:11:11.0, 0, 1, 1, 1, null, null, null, null, 1, admin, 超级管理员, admin, 2019-01-19 11:11:11.0, admin, 2019-01-19 19:07:18.0, 0, 4, 上海分公司, 2, 0, admin, 2018-09-23 19:37:03.0, null, null, 0
<== Row: 31, luxun, 陆逊, null, fd80ebd493a655608dc893a9f897d845, YzcmCZNvbXocrsz9dm8e, test@qq.com, 13889700023, 1, 11, admin, 2018-09-23 19:47:44.0, admin, 2018-09-23 19:47:58.0, 0, 57, 31, 2, null, null, null, null, 2, mng, 项目经理, admin, 2019-01-19 11:11:11.0, admin, 2019-01-19 11:39:28.0, 0, 11, 市场部, 6, 0, admin, 2018-09-23 19:39:01.0, null, null, 0
<== Row: 30, zhouyu, 周瑜, null, fd80ebd493a655608dc893a9f897d845, YzcmCZNvbXocrsz9dm8e, test@qq.com, 13889700023, 1, 11, admin, 2018-09-23 19:47:28.0, admin, 2018-09-23 19:48:04.0, 0, 58, 30, 2, null, null, null, null, 2, mng, 项目经理, admin, 2019-01-19 11:11:11.0, admin, 2019-01-19 11:39:28.0, 0, 11, 市场部, 6, 0, admin, 2018-09-23 19:39:01.0, null, null, 0
<== Row: 32, huanggai, 黄盖, null, fd80ebd493a655608dc893a9f897d845, YzcmCZNvbXocrsz9dm8e, test@qq.com, 13889700023, 1, 11, admin, 2018-09-23 19:48:38.0, admin, 2018-09-23 19:49:02.0, 0, 59, 32, 3, null, null, null, null, 3, chenwei, 陈伟, admin, 2020-05-07 15:52:53.0, admin, 2020-05-07 15:52:59.0, 0, 11, 市场部, 6, 0, admin, 2018-09-23 19:39:01.0, null, null, 0
<== Row: 33, zhangsan, 张三, null, null, null, 206@qq.com1, null, null, null, null, null, null, null, 0, 73, 33, 8, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null
<== Row: 25, caocao, 曹操, null, fd80ebd493a655608dc893a9f897d845, YzcmCZNvbXocrsz9dm8e, test@qq.com, 13889700023, 1, 8, admin, 2018-09-23 19:45:32.0, admin, 2019-01-10 17:59:14.0, 0, 74, 25, 8, null, null, null, null, null, null, null, null, null, null, null, null, 8, 技术部, 4, 0, admin, 2018-09-23 19:38:10.0, null, null, 0
<== Row: 25, caocao, 曹操, null, fd80ebd493a655608dc893a9f897d845, YzcmCZNvbXocrsz9dm8e, test@qq.com, 13889700023, 1, 8, admin, 2018-09-23 19:45:32.0, admin, 2019-01-10 17:59:14.0, 0, 75, 25, 2, null, null, null, null, 2, mng, 项目经理, admin, 2019-01-19 11:11:11.0, admin, 2019-01-19 11:39:28.0, 0, 8, 技术部, 4, 0, admin, 2018-09-23 19:38:10.0, null, null, 0
<== Row: 22, liubei, 刘备, null, fd80ebd493a655608dc893a9f897d845, YzcmCZNvbXocrsz9dm8e, test@qq.com, 13889700023, 1, 7, admin, 2018-09-23 19:43:00.0, admin, 2019-01-10 11:41:13.0, 0, 80, 22, 2, null, null, null, null, 2, mng, 项目经理, admin, 2019-01-19 11:11:11.0, admin, 2019-01-19 11:39:28.0, 0, 7, 技术部, 5, 0, admin, 2018-09-23 19:38:00.0, null, null, 0
<== Row: 23, zhaoyun, 赵云, null, fd80ebd493a655608dc893a9f897d845, YzcmCZNvbXocrsz9dm8e, test@qq.com, 13889700023, 1, 7, admin, 2018-09-23 19:43:44.0, admin, 2018-09-23 19:43:52.0, 0, 81, 23, 3, null, null, null, null, 3, chenwei, 陈伟, admin, 2020-05-07 15:52:53.0, admin, 2020-05-07 15:52:59.0, 0, 7, 技术部, 5, 0, admin, 2018-09-23 19:38:00.0, null, null, 0
<== Row: 24, zhugeliang, 诸葛亮, null, fd80ebd493a655608dc893a9f897d845, YzcmCZNvbXocrsz9dm8e, test@qq.com, 13889700023, 7, 11, admin, 2018-09-23 19:44:23.0, admin, 2018-09-23 19:44:29.0, 0, 82, 24, 4, null, null, null, null, 4, test, 测试人员, admin, 2019-01-19 11:11:11.0, admin, 2019-01-19 11:11:11.0, 0, 11, 市场部, 6, 0, admin, 2018-09-23 19:39:01.0, null, null, 0
<== Row: 26, dianwei, 典韦, null, fd80ebd493a655608dc893a9f897d845, YzcmCZNvbXocrsz9dm8e, test@qq.com, 13889700023, 1, 10, admin, 2018-09-23 19:45:48.0, admin, 2018-09-23 19:45:57.0, 0, 83, 26, 3, null, null, null, null, 3, chenwei, 陈伟, admin, 2020-05-07 15:52:53.0, admin, 2020-05-07 15:52:59.0, 0, 10, 市场部, 5, 0, admin, 2018-09-23 19:38:45.0, null, null, 0
<== Row: 29, sunquan, 孙权, null, fd80ebd493a655608dc893a9f897d845, YzcmCZNvbXocrsz9dm8e, test@qq.com, 13889700023, 1, 10, admin, 2018-09-23 19:46:54.0, admin, 2018-09-23 19:47:03.0, 0, 85, 29, 2, null, null, null, null, 2, mng, 项目经理, admin, 2019-01-19 11:11:11.0, admin, 2019-01-19 11:39:28.0, 0, 10, 市场部, 5, 0, admin, 2018-09-23 19:38:45.0, null, null, 0
<== Row: 28, xunyu, 荀彧, null, fd80ebd493a655608dc893a9f897d845, YzcmCZNvbXocrsz9dm8e, test@qq.com, 13889700023, 1, 10, admin, 2018-09-23 19:46:38.0, admin, 2018-11-04 15:33:17.0, 0, 86, 28, 4, null, null, null, null, 4, test, 测试人员, admin, 2019-01-19 11:11:11.0, admin, 2019-01-19 11:11:11.0, 0, 10, 市场部, 5, 0, admin, 2018-09-23 19:38:45.0, null, null, 0
<== Row: 27, xiahoudun, 夏侯惇, null, fd80ebd493a655608dc893a9f897d845, YzcmCZNvbXocrsz9dm8e, test@qq.com, 13889700023, 1, 8, admin, 2018-09-23 19:46:09.0, admin, 2018-09-23 19:46:17.0, 0, 87, 27, 3, null, null, null, null, 3, chenwei, 陈伟, admin, 2020-05-07 15:52:53.0, admin, 2020-05-07 15:52:59.0, 0, 8, 技术部, 4, 0, admin, 2018-09-23 19:38:10.0, null, null, 0
<== Row: 39, zhangsan5, 张三5, null, null, null, null, null, null, null, null, null, null, null, 0, 88, 39, 4, null, null, null, null, 4, test, 测试人员, admin, 2019-01-19 11:11:11.0, admin, 2019-01-19 11:11:11.0, 0, null, null, null, null, null, null, null, null, null
<== Row: 39, zhangsan5, 张三5, null, null, null, null, null, null, null, null, null, null, null, 0, 89, 39, 3, null, null, null, null, 3, chenwei, 陈伟, admin, 2020-05-07 15:52:53.0, admin, 2020-05-07 15:52:59.0, 0, null, null, null, null, null, null, null, null, null
<== Row: 34, zhangsan1, 张三1, null, null, null, null, null, null, null, null, null, null, null, 0, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null
<== Row: 35, zhangsan2, 张三2, null, null, null, null, null, null, null, null, null, null, null, 0, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null
<== Row: 37, zhangsan3, 张三3, null, null, null, null, null, null, null, null, null, null, null, 0, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null
<== Row: 38, zhangsan4, 张三4, null, null, null, null, null, null, null, null, null, null, null, 0, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null
<== Total: 20
嗯回头看代码是这里出了问题。
当我们设置了Map之后，这里应该使用我们的Map了，而不是使用实体对象，修改该文件。
SysUserMapper.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="cn.edu.njust.mango.mapper.SysUserMapper">
<resultMap type="cn.edu.njust.mango.entity.SysUser" id="SysUserMap">
<result property="id" column="id" jdbcType="INTEGER"/>
<result property="name" column="name" jdbcType="VARCHAR"/>
<result property="nickName" column="nick_name" jdbcType="VARCHAR"/>
<result property="avatar" column="avatar" jdbcType="VARCHAR"/>
<result property="password" column="password" jdbcType="VARCHAR"/>
<result property="salt" column="salt" jdbcType="VARCHAR"/>
<result property="email" column="email" jdbcType="VARCHAR"/>
<result property="mobile" column="mobile" jdbcType="VARCHAR"/>
<result property="status" column="status" jdbcType="OTHER"/>
<result property="deptId" column="dept_id" jdbcType="INTEGER"/>
<result property="createBy" column="create_by" jdbcType="VARCHAR"/>
<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
<result property="lastUpdateBy" column="last_update_by" jdbcType="VARCHAR"/>
<result property="lastUpdateTime" column="last_update_time" jdbcType="TIMESTAMP"/>
<result property="delFlag" column="del_flag" jdbcType="OTHER"/>
</resultMap>
<resultMap id="queryForListMap" type="cn.edu.njust.mango.vo.SysUserVO">
<id column="id" property="id" jdbcType="INTEGER"/>
<result property="name" column="name" jdbcType="VARCHAR"/>
<result property="nickName" column="nick_name" jdbcType="VARCHAR"/>
<result property="avatar" column="avatar" jdbcType="VARCHAR"/>
<result property="password" column="password" jdbcType="VARCHAR"/>
<result property="salt" column="salt" jdbcType="VARCHAR"/>
<result property="email" column="email" jdbcType="VARCHAR"/>
<result property="mobile" column="mobile" jdbcType="VARCHAR"/>
<result property="status" column="status" jdbcType="OTHER"/>
<result property="deptId" column="dept_id" jdbcType="INTEGER"/>
<result property="createBy" column="create_by" jdbcType="VARCHAR"/>
<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
<result property="lastUpdateBy" column="last_update_by" jdbcType="VARCHAR"/>
<result property="lastUpdateTime" column="last_update_time" jdbcType="TIMESTAMP"/>
<result property="delFlag" column="del_flag" jdbcType="OTHER"/>
<association property="dept" javaType="cn.edu.njust.mango.entity.SysDept">
<id property="id" column="d_id" jdbcType="INTEGER"/>
<result property="name" column="d_name" jdbcType="VARCHAR"/>
<result property="parentId" column="d_parent_id" jdbcType="INTEGER"/>
<result property="orderNum" column="d_order_num" jdbcType="INTEGER"/>
<result property="createBy" column="d_create_by" jdbcType="VARCHAR"/>
<result property="createTime" column="d_create_time" jdbcType="TIMESTAMP"/>
<result property="lastUpdateBy" column="d_last_update_by" jdbcType="VARCHAR"/>
<result property="lastUpdateTime" column="d_last_update_time" jdbcType="TIMESTAMP"/>
<result property="delFlag" column="d_del_flag" jdbcType="OTHER"/>
</association>
<collection property="roles" javaType="java.util.List" ofType="cn.edu.njust.mango.entity.SysRole">
<id column="role_id" property="id" jdbcType="INTEGER"/>
<result property="name" column="role_name" jdbcType="VARCHAR"/>
<result property="remark" column="role_remark" jdbcType="VARCHAR"/>
<result property="createBy" column="role_create_by" jdbcType="VARCHAR"/>
<result property="createTime" column="role_create_time" jdbcType="TIMESTAMP"/>
<result property="lastUpdateBy" column="role_last_update_by" jdbcType="VARCHAR"/>
<result property="lastUpdateTime" column="role_last_update_time" jdbcType="TIMESTAMP"/>
<result property="delFlag" column="role_del_flag" jdbcType="OTHER"/>
</collection>
</resultMap>
<select id="getDept" resultType="cn.edu.njust.mango.entity.SysDept" parameterType="INTEGER">
SELECT
*
FROM sys_dept d
where id = #{dept_id}
</select>
<select id="queryAllByPageRequestAndConditionAndRoleAndDept" resultMap="queryForListMap">
SELECT
*
FROM
sys_user u
LEFT JOIN sys_user_role ru ON u.id = ru.user_id
LEFT JOIN sys_role r on r.id = ru.role_id
LEFT JOIN sys_dept d on d.id = u.dept_id
</select>
</mapper>
我之所以不厌其烦的贴出所有代码，是怕部分读者在实际操作的时候出现错误。这里就暂且麻烦一点，其实只是改成这样。
接下来再Debug。
一阵窃喜，好像成功了，有数据了。但是真的是这样吗？我们展开其中的一个数据。
咱就拿zhangsan5举例吧！这里好多数据是空的，但是数据库不是这样的啊！
39 zhangsan5 张三5 0 88 39 4 4 test 测试人员 admin 2019-01-19 11:11:11 admin 2019-01-19 11:11:11 0
39 zhangsan5 张三5 0 89 39 3 3 chenwei 陈伟 admin 2020-05-07 15:52:53 admin 2020-05-07 15:52:59 0
这里由于数据太多，不好截图，读者可以运行源代码查看，这里我把zhangsan5的两条数据选中了。我们明明发现remark是有值的，但是查询的时候没有出来，而且查询结果的名称也不对。这里主要是不同表的名称有重复。我们需要起别名。还是修改select语句，修改成如下样式。
完整代码如下：
SysUserMapper.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="cn.edu.njust.mango.mapper.SysUserMapper">
<resultMap type="cn.edu.njust.mango.entity.SysUser" id="SysUserMap">
<result property="id" column="id" jdbcType="INTEGER"/>
<result property="name" column="name" jdbcType="VARCHAR"/>
<result property="nickName" column="nick_name" jdbcType="VARCHAR"/>
<result property="avatar" column="avatar" jdbcType="VARCHAR"/>
<result property="password" column="password" jdbcType="VARCHAR"/>
<result property="salt" column="salt" jdbcType="VARCHAR"/>
<result property="email" column="email" jdbcType="VARCHAR"/>
<result property="mobile" column="mobile" jdbcType="VARCHAR"/>
<result property="status" column="status" jdbcType="OTHER"/>
<result property="deptId" column="dept_id" jdbcType="INTEGER"/>
<result property="createBy" column="create_by" jdbcType="VARCHAR"/>
<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
<result property="lastUpdateBy" column="last_update_by" jdbcType="VARCHAR"/>
<result property="lastUpdateTime" column="last_update_time" jdbcType="TIMESTAMP"/>
<result property="delFlag" column="del_flag" jdbcType="OTHER"/>
</resultMap>
<resultMap id="queryForListMap" type="cn.edu.njust.mango.vo.SysUserVO">
<id column="id" property="id" jdbcType="INTEGER"/>
<result property="name" column="name" jdbcType="VARCHAR"/>
<result property="nickName" column="nick_name" jdbcType="VARCHAR"/>
<result property="avatar" column="avatar" jdbcType="VARCHAR"/>
<result property="password" column="password" jdbcType="VARCHAR"/>
<result property="salt" column="salt" jdbcType="VARCHAR"/>
<result property="email" column="email" jdbcType="VARCHAR"/>
<result property="mobile" column="mobile" jdbcType="VARCHAR"/>
<result property="status" column="status" jdbcType="OTHER"/>
<result property="deptId" column="dept_id" jdbcType="INTEGER"/>
<result property="createBy" column="create_by" jdbcType="VARCHAR"/>
<result property="createTime" column="create_time" jdbcType="TIMESTAMP"/>
<result property="lastUpdateBy" column="last_update_by" jdbcType="VARCHAR"/>
<result property="lastUpdateTime" column="last_update_time" jdbcType="TIMESTAMP"/>
<result property="delFlag" column="del_flag" jdbcType="OTHER"/>
<association property="dept" javaType="cn.edu.njust.mango.entity.SysDept">
<id property="id" column="d_id" jdbcType="INTEGER"/>
<result property="name" column="d_name" jdbcType="VARCHAR"/>
<result property="parentId" column="d_parent_id" jdbcType="INTEGER"/>
<result property="orderNum" column="d_order_num" jdbcType="INTEGER"/>
<result property="createBy" column="d_create_by" jdbcType="VARCHAR"/>
<result property="createTime" column="d_create_time" jdbcType="TIMESTAMP"/>
<result property="lastUpdateBy" column="d_last_update_by" jdbcType="VARCHAR"/>
<result property="lastUpdateTime" column="d_last_update_time" jdbcType="TIMESTAMP"/>
<result property="delFlag" column="d_del_flag" jdbcType="OTHER"/>
</association>
<collection property="roles" javaType="java.util.List" ofType="cn.edu.njust.mango.entity.SysRole">
<id column="role_id" property="id" jdbcType="INTEGER"/>
<result property="name" column="role_name" jdbcType="VARCHAR"/>
<result property="remark" column="role_remark" jdbcType="VARCHAR"/>
<result property="createBy" column="role_create_by" jdbcType="VARCHAR"/>
<result property="createTime" column="role_create_time" jdbcType="TIMESTAMP"/>
<result property="lastUpdateBy" column="role_last_update_by" jdbcType="VARCHAR"/>
<result property="lastUpdateTime" column="role_last_update_time" jdbcType="TIMESTAMP"/>
<result property="delFlag" column="role_del_flag" jdbcType="OTHER"/>
</collection>
</resultMap>
<select id="getDept" resultType="cn.edu.njust.mango.entity.SysDept" parameterType="INTEGER">
SELECT
*
FROM sys_dept d
where id = #{dept_id}
</select>
<select id="queryAllByPageRequestAndConditionAndRoleAndDept" resultMap="queryForListMap">
SELECT
u.*,
d.id as d_id,
d.name as d_name,
d.parent_id as d_parent_id,
d.order_num as d_order_num,
d.create_by as d_create_by,
d.create_time as d_create_time,
d.last_update_by as d_last_update_by,
d.last_update_time as d_last_update_time,
d.del_flag as d_del_flag,
r.id as 'role_id',
r.name as 'role_name',
r.remark as 'role_remark',
r.create_by as 'role_create_by',
r.create_time as 'role_create_time',
r.last_update_by as 'role_last_update_by',
r.last_update_time as 'role_last_update_time',
r.del_flag as 'role_del_flag'
FROM
sys_user u
LEFT JOIN sys_user_role ru ON u.id = ru.user_id
LEFT JOIN sys_role r on r.id = ru.role_id
LEFT JOIN sys_dept d on d.id = u.dept_id
</select>
</mapper>
再次debug
我们发现张三5的数据成功上来了，成功解决。
4、总结
书上的代码直接运行绝大部分是对的，但是总有一些软件的更新使得作者无能为力。之前的API是对的，但是之后就废弃了或修改了是常有的事。所以我们需要跟踪源代码。这只是一个小小的问题，如果没有前辈的无私奉献，很难想象我们自己一天能学到多少内容。感谢各位前辈的辛勤付出，让我们少走了很多的弯路！
点个赞再走呗！欢迎留言哦！
上一篇： hive调优
下一篇： Tomcat内存溢出解决办法