Mybatis级联查询（多对一、一对多）Demo及问题

Mybatis级联查询（多对一、一对多）Demo及问题

• 1、问题描述
• 2、工程源码
• • A、首先是数据库部分
  • B、工程代码
• 3、问题解决
• 4、总结

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', 'admin@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', 'test@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', 'test@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', 'test@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', 'test@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', 'test@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', 'test@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', 'test@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', 'test@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', 'test@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', 'test@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', 'test@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, '206@qq.com1', 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

<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.0modelVersion><parent><groupId>org.springframework.bootgroupId><artifactId>spring-boot-starter-parentartifactId><version>2.2.6.RELEASEversion><relativePath/> parent><groupId>cn.edu.njustgroupId><artifactId>mangoartifactId><version>0.0.1-SNAPSHOTversion><name>mangoname><description>Demo project for Spring Bootdescription><properties><java.version>1.8java.version><skipTests>trueskipTests>properties><dependencies><dependency><groupId>org.projectlombokgroupId><artifactId>lombokartifactId><optional>trueoptional>dependency><dependency><groupId>org.springframework.bootgroupId><artifactId>spring-boot-starter-testartifactId><scope>testscope><exclusions><exclusion><groupId>org.junit.vintagegroupId><artifactId>junit-vintage-engineartifactId>exclusion><exclusion><groupId>org.springframework.bootgroupId><artifactId>spring-boot-starter-loggingartifactId>exclusion>exclusions>dependency><dependency><groupId>io.springfoxgroupId><artifactId>springfox-swagger2artifactId><version>2.9.2version>dependency><dependency><groupId>io.springfoxgroupId><artifactId>springfox-swagger-uiartifactId><version>2.9.2version>dependency><dependency><groupId>org.mybatis.spring.bootgroupId><artifactId>mybatis-spring-boot-starterartifactId><version>1.3.2version>dependency><dependency><groupId>mysqlgroupId><artifactId>mysql-connector-javaartifactId><version>5.1.47version>dependency><dependency><groupId>tk.mybatisgroupId><artifactId>mapper-spring-boot-starterartifactId><version>1.2.4version><exclusions><exclusion><artifactId>mybatisartifactId><groupId>org.mybatisgroupId>exclusion><exclusion><artifactId>mybatis-springartifactId><groupId>org.mybatisgroupId>exclusion>exclusions>dependency><dependency><groupId>com.github.pagehelpergroupId><artifactId>pagehelper-spring-boot-starterartifactId><version>1.2.3version><exclusions><exclusion><artifactId>mybatis-spring-boot-starterartifactId><groupId>org.mybatis.spring.bootgroupId>exclusion>exclusions>dependency><dependency><groupId>org.springframework.bootgroupId><artifactId>spring-boot-starter-log4jartifactId><version>1.3.3.RELEASEversion>dependency><dependency><groupId>junitgroupId><artifactId>junitartifactId><version>4.12version><scope>testscope>dependency><dependency><groupId>com.alibabagroupId><artifactId>druid-spring-boot-starterartifactId><version>1.1.10version>dependency>dependencies><build><plugins><plugin><groupId>org.springframework.bootgroupId><artifactId>spring-boot-maven-pluginartifactId>plugin><plugin><groupId>org.mybatis.generatorgroupId><artifactId>mybatis-generator-maven-pluginartifactId><version>1.3.2version><configuration><verbose>trueverbose><overwrite>trueoverwrite>configuration><dependencies><dependency><groupId>mysqlgroupId><artifactId>mysql-connector-javaartifactId><version>5.1.47version>dependency><dependency><groupId>tk.mybatisgroupId><artifactId>mapperartifactId><version>4.0.0version>dependency>dependencies>plugin>plugins>build>project>

application.yml

server:port: 8005logging:config: classpath:log4j.propertieslevel:dao: debugorg:mybatis: debugspring:datasource:driver-class-name: com.mysql.jdbc.Driverusername: rootpassword: rooturl: jdbc:mysql://localhost:3306/mango?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8druid:driver-class-name: com.mysql.jdbc.Driverurl: jdbc:mysql://localhost:3306/mango?useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTCusername: rootpassword: rootfilters: stat,wall,log4j,configmax-active: 100initial-size: 1max-wait: 60000min-idle: 1time-between-eviction-runs-millis: 60000min-evictable-idle-time-millis: 300000validation-query: select 'x'test-while-idle: truetest-on-borrow: falsetest-on-return: falsepool-prepared-statements: truemax-open-prepared-statements: 50max-pool-prepared-statement-per-connection-size: 20jackson:default-property-inclusion: non_null# 指定数据库中表生成实体的路径
mybatis:type-aliases-package: cn.edu.njust.mango.entitymapper-locations: classpath:mapper/*.xmlconfiguration:#    输出sql日志信息log-impl: org.apache.ibatis.logging.stdout.StdOutImpl#pagehelper
pagehelper:helperDialect: mysqlreasonable: truesupportMethodsArguments: trueparams:count: countSql

log4j.properties

#Console Log
log4j.rootLogger=INFO,console,debug,info,warn,errorLOG_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

<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 dwhere id = #{dept_id}select><select id="queryAllByPageRequestAndConditionAndRoleAndDept" resultType="cn.edu.njust.mango.vo.SysUserVO">SELECT*FROMsys_user uLEFT JOIN sys_user_role ru ON u.id = ru.user_idLEFT JOIN sys_role r on r.id = ru.role_idLEFT JOIN sys_dept d on d.id = u.dept_idselect>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 {@Autowiredprivate SysUserMapper sysUserMapper;@Testvoid 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

<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 dwhere id = #{dept_id}select><select id="queryAllByPageRequestAndConditionAndRoleAndDept" resultMap="queryForListMap">SELECT*FROMsys_user uLEFT JOIN sys_user_role ru ON u.id = ru.user_idLEFT JOIN sys_role r on r.id = ru.role_idLEFT JOIN sys_dept d on d.id = u.dept_idselect>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

<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 dwhere id = #{dept_id}select><select id="queryAllByPageRequestAndConditionAndRoleAndDept" resultMap="queryForListMap">SELECTu.*,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'FROMsys_user uLEFT JOIN sys_user_role ru ON u.id = ru.user_idLEFT JOIN sys_role r on r.id = ru.role_idLEFT JOIN sys_dept d on d.id = u.dept_idselect>mapper>

再次debug

我们发现张三5的数据成功上来了，成功解决。

4、总结

  书上的代码直接运行绝大部分是对的，但是总有一些软件的更新使得作者无能为力。之前的API是对的，但是之后就废弃了或修改了是常有的事。所以我们需要跟踪源代码。这只是一个小小的问题，如果没有前辈的无私奉献，很难想象我们自己一天能学到多少内容。感谢各位前辈的辛勤付出，让我们少走了很多的弯路！

点个赞再走呗！欢迎留言哦！

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！