MyBatis返回多个结果集

---

MyBatis在查询时可以将单一的结果集并列查询组成多结果集；
如：多重List<Map<String,Object>>格式，Object可再次为 List<Map<String,Object>>结构

[
    {
        "userName": "张三",
        "userCode": "RBXLWCzGcqDncROd90Jd",
        "userRole": [
            {
                "roleCode": "test1",
                "userCode": "RBXLWCzGcqDncROd90Jd"
            },
            {
                "roleCode": "test2",
                "userCode": "RBXLWCzGcqDncROd90Jd"
            }
        ]
    }
]

使用

xxMapper.xml：
xml中以userCode为连接，collection的property属性值为二级key

<resultMap id="testResultMap" type="java.util.Map">
    <id property="userCode" column="user_Code" />
    <result property="userName" column="user_Name" />
    <result property="userRemark" column="user_Remark" />
    <collection property="userRole" javaType="list" ofType="map">
        <id property="roleCode" column="role_Code" />
        <result property="userCode" column="user_Code" />
        <result property="roleName" column="role_Name" />
        <result property="roleRemark" column="role_Remark" />
    </collection>
</resultMap>
<select id="selectTestListResultMap"  parameterType="map" resultMap="testResultMap">
    SELECT
    	test_user.user_Code,
    	test_user.user_Name,
    	test_user.user_Remark,
    	test_role.role_Code,
    	test_role.role_Name,
    	test_role.role_Remark,
    	test_role.user_Code
    FROM
    	test_user
    LEFT JOIN test_role ON test_role.user_Code = test_user.user_Code
    WHERE
    	test_user.user_Code IN (
    		SELECT
    			test_role.user_Code
    		FROM
    			test_role
    		WHERE
    			test_role.user_Code = test_user.user_Code
    		GROUP BY
    			test_role.user_Code
    	)
</select>

xxMapper.java：

/**
 * 查询列表信息
 * @param params 参数
 * @return 返回
 */
List<Map<String, Object>> selectTestListResultMap(@Param("params") Map<String, Object> params);

结果集：

[
    {
        "userRemark": "备注用户",
        "userName": "张三",
        "userRole": [
            {
                "roleRemark": "备注权限",
                "roleCode": "test1",
                "roleName": "a",
                "userCode": "RBXLWCzGcqDncROd90Jd"
            },
            {
                "roleRemark": "备注权限",
                "roleCode": "test2",
                "roleName": "b",
                "userCode": "RBXLWCzGcqDncROd90Jd"
            }
        ],
        "userCode": "RBXLWCzGcqDncROd90Jd"
    },
    {
        "userRemark": "备注用户",
        "userName": "李四",
        "userRole": [
            {
                "roleRemark": "备注权限",
                "roleCode": "test3",
                "roleName": "c",
                "userCode": "TALKA0I56vYUp2cVaczZ"
            },
            {
                "roleRemark": "备注权限",
                "roleCode": "test4",
                "roleName": "d",
                "userCode": "TALKA0I56vYUp2cVaczZ"
            },
            {
                "roleRemark": "备注权限",
                "roleCode": "test5",
                "roleName": "e",
                "userCode": "TALKA0I56vYUp2cVaczZ"
            }
        ],
        "userCode": "TALKA0I56vYUp2cVaczZ"
    },
    {
        "userRemark": "备注用户",
        "userName": "王五",
        "userRole": [
            {
                "roleRemark": "备注权限",
                "roleCode": "test6",
                "roleName": "f",
                "userCode": "EMbvTsuedRjItaD0aZzq"
            },
            {
                "roleRemark": "备注权限",
                "roleCode": "test7",
                "roleName": "g",
                "userCode": "EMbvTsuedRjItaD0aZzq"
            }
        ],
        "userCode": "EMbvTsuedRjItaD0aZzq"
    }
]

表：

-- ----------------------------
-- Table structure for test_role
-- ----------------------------
DROP TABLE IF EXISTS `test_role`;
CREATE TABLE `test_role` (
  `tr_PKeyId` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `role_Code` varchar(32) DEFAULT NULL COMMENT '角色代码',
  `role_Name` varchar(32) DEFAULT NULL COMMENT '角色名称',
  `user_Code` varchar(32) DEFAULT NULL COMMENT '用户代码',
  `role_Remark` varchar(100) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`tr_PKeyId`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8 COMMENT='角色表';

-- ----------------------------
-- Records of test_role
-- ----------------------------
INSERT INTO `test_role` VALUES ('16', 'test1', 'a', 'RBXLWCzGcqDncROd90Jd', '备注权限');
INSERT INTO `test_role` VALUES ('19', 'test2', 'b', 'RBXLWCzGcqDncROd90Jd', '备注权限');
INSERT INTO `test_role` VALUES ('20', 'test3', 'c', 'TALKA0I56vYUp2cVaczZ', '备注权限');
INSERT INTO `test_role` VALUES ('21', 'test4', 'd', 'TALKA0I56vYUp2cVaczZ', '备注权限');
INSERT INTO `test_role` VALUES ('22', 'test5', 'e', 'TALKA0I56vYUp2cVaczZ', '备注权限');
INSERT INTO `test_role` VALUES ('23', 'test6', 'f', 'EMbvTsuedRjItaD0aZzq', '备注权限');
INSERT INTO `test_role` VALUES ('24', 'test7', 'g', 'EMbvTsuedRjItaD0aZzq', '备注权限');

-- ----------------------------
-- Table structure for test_user
-- ----------------------------
DROP TABLE IF EXISTS `test_user`;
CREATE TABLE `test_user` (
  `tu_PKeyId` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_Name` varchar(32) DEFAULT NULL COMMENT '用户名称',
  `user_Code` varchar(32) DEFAULT NULL COMMENT '用户代码',
  `user_Remark` varchar(100) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`tu_PKeyId`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COMMENT='用户表';

-- ----------------------------
-- Records of test_user
-- ----------------------------
INSERT INTO `test_user` VALUES ('10', '张三', 'RBXLWCzGcqDncROd90Jd', '备注用户');
INSERT INTO `test_user` VALUES ('11', '李四', 'TALKA0I56vYUp2cVaczZ', '备注用户');
INSERT INTO `test_user` VALUES ('12', '王五', 'EMbvTsuedRjItaD0aZzq', '备注用户');
INSERT INTO `test_user` VALUES ('13', '赵六', 'GosZVJuSCA70TmeRnBJO', '备注用户');