（三）Mybatis的多对多查询（有mapper.xml）+注解开发(无mapper.xml)

于 2022-07-10 23:14:17 发布
阅读量590 收藏 1
点赞数
分类专栏： Spring & SpringMVC & Mybatis 文章标签： mybatis java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43563049/article/details/125610189
版权

一、Mybatis的多对多关系的mapper映射

（一个用户具有多个角色，一个角色可以被多个用户拥有）
1. 建表sql语句
CREATE TABLE `sys_role` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `roleName` varchar(50) DEFAULT NULL,
  `roleDesc` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

/*Data for the table `sys_role` */

insert  into `sys_role`(`id`,`roleName`,`roleDesc`) values (1,'院长','负责全面工作'),(2,'研究员','课程研发工作'),(3,'讲师','授课工作'),(4,'助教','协助解决学生的问题');

/*Table structure for table `sys_user` */

DROP TABLE IF EXISTS `sys_user`;

CREATE TABLE `sys_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  `password` varchar(80) DEFAULT NULL,
  `phoneNum` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ;

/*Data for the table `sys_user` */

insert  into `sys_user`(`id`,`username`,`email`,`password`,`phoneNum`) values (1,'zhangsan','zhangsan@itcast.cn','123','13888888888'),(2,'lisi','lisi@itcast.cn','123','13999999999'),(3,'wangwu','wangwu@itcast.cn','123','18599999999');

/*Table structure for table `sys_user_role` */

DROP TABLE IF EXISTS `sys_user_role`;

CREATE TABLE `sys_user_role` (
  `userId` bigint(20) NOT NULL,
  `roleId` bigint(20) NOT NULL,
  PRIMARY KEY (`userId`,`roleId`),
  KEY `roleId` (`roleId`),
  CONSTRAINT `sys_user_role_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `sys_user` (`id`),
  CONSTRAINT `sys_user_role_ibfk_2` FOREIGN KEY (`roleId`) REFERENCES `sys_role` (`id`)
);

/*Data for the table `sys_user_role` */

insert  into `sys_user_role`(`userId`,`roleId`) values (1,1),(1,2),(2,2),(2,3);
2. 构建相应的User类和Role类
package com.bean;

import java.util.List;

public class User {

    private Long id;
    private String username;
    private String email;
    private String password;
    private String phoneNum;
    private List<Role> roles;
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getPhoneNum() {
        return phoneNum;
    }

    public void setPhoneNum(String phoneNum) {
        this.phoneNum = phoneNum;
    }

    public List<Role> getRoles() {
        return roles;
    }

    public void setRoles(List<Role> roles) {
        this.roles = roles;
    }
    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", email='" + email + '\'' +
                ", password='" + password + '\'' +
                ", phoneNum='" + phoneNum + '\'' +
                ", rolist='" + roles + '\'' +
                '}';
    }
}


package com.bean;
public class Role {
    private Long id;
    private String roleName;
    private String roleDesc;
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getRoleName() {
        return roleName;
    }

    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }

    public String getRoleDesc() {
        return roleDesc;
    }

    public void setRoleDesc(String roleDesc) {
        this.roleDesc = roleDesc;
    }

    @Override
    public String toString() {
        return "Role{" +
                "id=" + id +
                ", roleName='" + roleName + '\'' +
                ", roleDesc='" + roleDesc + '\'' +
                '}';
    }
}
3. 编写UserMapper接口
package com.mapper;

import com.bean.User;

import java.io.IOException;
import java.util.List;

public interface UserMapper {
    
    List<User> findUserAndRoleAll() throws IOException;

}
4. 编写UserMapper.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.mapper.UserMapper">
    
    <resultMap id="UserRoleMap" type="user">
        <!--user的信息-->
        <id column="userId" property="id"></id>
        <result column="username" property="username"></result>
        <result column="email" property="email"></result>
        <result column="password" property="password"></result>
        <result column="phoneNum" property="phoneNum"></result>
        <!--内部的role信息-->
        <collection property="roles" ofType="role">
            <id column="roleId" property="id"></id>
            <result column="roleName" property="roleName"></result>
            <result column="roleDesc" property="roleDesc"></result>
        </collection>
    </resultMap>
    
    <select id="findUserAndRoleAll" resultMap="UserRoleMap">
        SELECT * FROM sys_user u,sys_user_role ur,sys_role r WHERE u.id=ur.userId and ur.roleId=r.id
    </select>

</mapper>
5. 编写测试类
package com;

import com.bean.User;
import com.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class TestDemo {

    @Test
    public void test() throws IOException{
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession session = sessionFactory.openSession(true);
       UserMapper userMapper = session.getMapper(UserMapper.class);
        List<User> all = userMapper.findUserAndRoleAll();
        for (User user : all) {
            System.out.println(user);
        }
    }

}

在这里插入图片描述

二、Mybatis的多对多关系的注解开发

  • 常用注解：@Result（实现结果集封装），@Results（与Result一起使用，封装多个结果集），@One（实现一对一结果集封装），@Many（实现一对多结果集封装）
1. 删除UserMapper.xml文件，修改SqlMapConfig.xml文件如下
   <!-- 指定映射关系 -->
    <mappers>
        <!--指定接口所在的包-->
        <package name="com.mapper"></package>
    </mappers>
2. 创建UserMapper.java和RoleMapper.java接口
package com.mapper;
import com.bean.User;
import org.apache.ibatis.annotations.Many;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import java.io.IOException;
import java.util.List;
public interface UserMapper {
    @Select("select * from sys_user")
    @Results({
            @Result(id = true,column = "id",property = "id"),
            @Result(id = true,column = "username",property = "username"),
            @Result(id = true,column = "email",property = "email"),
            @Result(id = true,column = "password",property = "password"),
            @Result(id = true,column = "phoneNum",property = "phoneNum"),
            @Result(
                    javaType = List.class,//要封装的实体类型
                    property = "roles",//要封装的属性名称
                    column = "id",//根据sys_user的id字段查询sys_user_role的对应的记录
                    many = @Many(select = "com.mapper.RoleMapper.findByUserId")//查询哪个接口的方法获得对应数据
            )
    })
    List<User> findUserAndRoleAll() throws IOException;
}


package com.mapper;
import com.bean.Role;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface RoleMapper {
    @Select("select * from sys_user_role ur,sys_role r where ur.roleId = r.id and ur.userId = #{uid}")
    List<Role> findByUserId(int uid);
}
3. 编写测试代码
package com;
import com.bean.User;
import com.mapper.UserMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class TestDemo {
    private UserMapper userMapper;
    @Before
    public void before() throws IOException{
        InputStream resourceAsStream = Resources.getResourceAsStream("SqlMapConfig.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession session = sessionFactory.openSession(true);
        userMapper = session.getMapper(UserMapper.class);

    }

    @Test
    public void test() throws IOException{
        List<User> all = userMapper.findUserAndRoleAll();
        for (User user : all) {
            System.out.println(user);
        }

    }

}
4. 测试结果如下

在这里插入图片描述

hellσ
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值