springboot整合mybatis （三）一对多配置

xiatianit

已于 2023-10-30 14:05:05 修改

阅读量598

点赞数

分类专栏： Mybatis 文章标签： spring boot java mysql

于 2022-09-06 14:03:12 首次发布

本文链接：https://blog.youkuaiyun.com/xiatianit/article/details/123756409

版权

Mybatis 专栏收录该内容

8 篇文章

订阅专栏

表结构

如下两张表sys_user用户表、admin_sys_user_role角色表 sys_user.id 与 admin_sys_user_role.user_id关联查询；用户表为主表、用户角色表为从表；

sys_user用户表

 
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `password` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `icon` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '头像',
  `email` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '邮箱',
  `nick_name` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '昵称',
  `note` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注信息',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `login_time` datetime(0) NULL DEFAULT NULL COMMENT '最后登录时间',
  `status` int(1) NULL DEFAULT 1 COMMENT '帐号启用状态：0->禁用；1->启用',
  `supply_id` int(20) NULL DEFAULT NULL COMMENT '供应商',
  `store_id` bigint(11) NULL DEFAULT 1 COMMENT '所属店铺',
  `store_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 513 ;
 
INSERT INTO `sys_user` VALUES (2, 'admin', '$2a$10$4eDuF3pka0gA1c7OS1wRz.5GN9gQKlo7LX1Mfn3wkg2u4MladxsAK', 'http://macro-oss.oss-cn-shenzhen.aliyuncs.com/mall/images/20190129/170157_yIl3_1767531.jpg', 'admin@163.com', '系统管理员', '系统管理员', '2018-10-08 13:32:47', '2019-04-05 11:31:38', 1, 1, 1, '一家花店');
INSERT INTO `sys_user` VALUES (511, 'test', '$2a$10$m4WlZDOvkIQ8eIQD7..zWO7CAXgPKGJ/WP5UuT8wtXgx9/O758v6q', NULL, NULL, 'test', NULL, '2021-02-15 09:06:15', NULL, 1, NULL, 1, NULL);
INSERT INTO `sys_user` VALUES (512, 'zs', '$2a$10$4NA8jNfWfjVWX/Bvy65jz.iqWbou5mHfjrQtW9YyH4PY8nTKNeQZa', NULL, NULL, 'zs', NULL, '2022-01-09 10:48:34', NULL, 1, NULL, 1, NULL);
 
SET FOREIGN_KEY_CHECKS = 1;

admin_sys_user_role角色表

DROP TABLE IF EXISTS `admin_sys_user_role`;
CREATE TABLE `admin_sys_user_role`  (
  `user_id` bigint(20) NOT NULL COMMENT '用户ID',
  `role_id` bigint(20) NOT NULL COMMENT '角色ID',
  PRIMARY KEY (`user_id`, `role_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin COMMENT = '用户和角色关联表' ROW_FORMAT = Dynamic;

INSERT INTO `admin_sys_user_role` VALUES (1, 1);
INSERT INTO `admin_sys_user_role` VALUES (2, 2);
INSERT INTO `admin_sys_user_role` VALUES (2, 100);
INSERT INTO `admin_sys_user_role` VALUES (100, 1);
INSERT INTO `admin_sys_user_role` VALUES (100, 2);
INSERT INTO `admin_sys_user_role` VALUES (100, 100);

SET FOREIGN_KEY_CHECKS = 1;

实体

注意：为了不写get、set方法可以在pom.xml文件里加入lombok依赖包，@Data才起作用。

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>RELEASE</version>
            <scope>compile</scope>
        </dependency>

package com.example.demo27c.bean;

import lombok.Data;

/**
 * @Author zhaox
 * @Date 22/3/26
 */
@Data
public class User {
    private Integer id;
    private String username;
    private String password;
    private String icon;
}

package com.example.demo27c.bean;

import lombok.Data;

/**
 * @Author zhaox
 * @Date 22/3/26
 */
@Data
public class UserRole {
    private Integer userId;
    private Integer roleId;
}


import lombok.Data;

import java.util.List;

@Data
public class UserVO {
    private Integer id;
    private String username;
    private String password;
    private String icon; 
 
    private List<UserRole> userRoles;
}

*Mapper.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="com.example.demo27c.mapper.UserMapper">
    <resultMap id="BaseResultMap" type="com.example.demo27c.bean.UserVO">
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="username" jdbcType="VARCHAR" property="username"/>
        <result column="password" jdbcType="VARCHAR" property="password"/>
        <result column="icon" jdbcType="VARCHAR" property="icon"/>
    </resultMap>

 
    <select id="getUser" resultType="com.example.demo27c.bean.UserVO">
        SELECT
            *
        FROM
            sys_user
        WHERE
            id =2
    </select>
 
</mapper>

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo27c.mapper.UserRoleMapper">
    <resultMap id="BaseResultMap" type="com.example.demo27c.bean.UserRole">
        <result column="user_id" jdbcType="INTEGER" property="userId"/>
        <result column="role_id" jdbcType="INTEGER" property="roleId"/>
    </resultMap>
 
    <select id="getUserRole" resultType="com.example.demo27c.bean.UserRole" parameterType="java.lang.Integer">
        SELECT
            *
        FROM
            admin_sys_user_role
        WHERE
            user_id = #{id,jdbcType=VARCHAR}
    </select>
 
</mapper>

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo27c.mapper.UserRoleMegerMapper">
    <resultMap id="UserRoleMap" type="com.example.demo27c.bean.UserRole">
        <result column="user_id" jdbcType="INTEGER" property="userId"/>
        <result column="role_id" jdbcType="INTEGER" property="roleId"/>
    </resultMap>

    <resultMap id="UserRoleMegerMap" type="com.example.demo27c.bean.UserVO">
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="username" jdbcType="VARCHAR" property="username"/>
        <result column="password" jdbcType="VARCHAR" property="password"/>
        <result column="icon" jdbcType="VARCHAR" property="icon"/>
<!--        
property="userRoles" 表示UserRoleMegerMapper 里定义的属性
 column="id" 主表tb_user的关联字段
  select="getUserRole"  从表的map映射id
-->
        <collection property="userRoles"  column="id" select="getUserRole" />
    </resultMap>

  
<!--    这里返回的必须是resultMap才起作用-->
    <select id="getUserRoleMeger"  resultMap="UserRoleMegerMap" >
        SELECT  * FROM sys_user  WHERE  id =2 
    </select>

    <!--    这里返回的必须是resultMap才起作用-->
    <select id="getUserRole" resultMap="UserRoleMap" >
        SELECT  * FROM  admin_sys_user_role  WHERE user_id = #{id}
    </select>
 
</mapper>

映射说明

mapper类

package com.example.demo27c.mapper;
 
import com.example.demo27c.bean.UserVO;
import org.apache.ibatis.annotations.Mapper;
  
@Mapper
public interface UserRoleMegerMapper { 
    UserVO getUserRoleMeger(); 
}

controller

package com.example.demo27c.controller;

/**
 * @Author zhaox
 * @Date 22/3/23
 */
import com.example.demo27c.mapper.UserMapper;
import com.example.demo27c.mapper.UserRoleMapper;
import com.example.demo27c.mapper.UserRoleMegerMapper;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.annotation.Resource;

@RestController
public class LoginController {

    @Resource
    UserMapper umapper;

    @Resource
    UserRoleMapper userRoleMapper;

    @Resource
    UserRoleMegerMapper urmMapper;

    /**
     * 使用映射文件失效
     * @return
     */
    @RequestMapping("/user/index")
    public Object Index() {  
        return urmMapper.getUserRoleMeger(); 
    }

    /**
     * 使用java代码实现
     * @return
     */
    @RequestMapping("/user/index")
    public Object Index2() { 
        // Map<String,Object> map = null;
        // UserVO u =   umapper.getUser(0);
        // List<UserRole> ur = userRoleMapper.getUserRole(2);
        // u.setUserRoles(ur);
        // return u;

        return urmMapper.getUserRoleMeger(); 
    }



}