程序员社区

MyBatis中多对多的查询

2022-01-22 分类：未分类 阅读(129)

文章目录

      • 数据库的准备
      • 查询角色获取角色下用户信息
        • 编写实体类
        • 编写RoleDao
        • 编写RoleDao的映射配置文件
        • 编写测试类
      • 查询用户获取用户的角色信息
        • 编写实体类
        • 编写UserDao
        • 编写UserDao的映射配置文件
        • 编写测试类

数据库的准备

DROP TABLE IF EXISTS role;

CREATE TABLE role(
id INT NOT NULL,
role_name VARCHAR(32) DEFAULT NULL,
role_desc VARCHAR(32) DEFAULT NULL,
PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO role(id,role_name,role_desc) VALUES(1,'院长','管理整个学院');
INSERT INTO role(id,role_name,role_desc) VALUES(2,'总裁','管理整个公司');
INSERT INTO role(id,role_name,role_desc) VALUES(3,'校长','管理整个学校');

DROP TABLE IF EXISTS user_role
CREATE TABLE user_role(
uid INT NOT NULL,
rid INT NOT NULL,
PRIMARY KEY(uid,rid),
FOREIGN KEY(rid) REFERENCES role(id),
FOREIGN KEY(uid) REFERENCES USER(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO user_role(uid,rid) VALUES(41,1),(45,1),(41,2);

在这里插入图片描述
在这里插入图片描述

查询角色获取角色下用户信息

在这里插入图片描述

编写实体类

User.java

package com.ghh.domain;


import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

import java.io.Serializable;
import java.util.Date;
import java.util.List;

@AllArgsConstructor
@NoArgsConstructor
@Data
@ToString
public class User implements Serializable {
    private Integer userId;
    private String userName;
    private String userAddress;
    private String userSex;
    private Date userBirthday;
}

package com.ghh.domain;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.List;

@AllArgsConstructor
@NoArgsConstructor
@Data

public class Role {
    private Integer roleId;
    private String roleName;
    private String roleDesc;
    //多对多的映射关系,一个角色可以赋予多个用户
    private List<User> users;
}

编写RoleDao

package com.ghh.dao;
import com.ghh.domain.Role;
import java.util.List;

public interface RoleDao {
    List<Role> findAllRole();
}

编写RoleDao的映射配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ghh.dao.RoleDao">
    <select id="findAllRole" resultMap="roleMap">
        select * from role r
        left join user_role ur on r.id = ur.rid
        left join user u on ur.uid = u.id
    </select>

    <resultMap id="roleMap" type="role">
        <id property="roleId" column="id"/>
        <result property="roleName" column="role_name"/>
        <result property="roleDesc" column="role_desc"/>
        <collection property="users" ofType="user">
            <id property="userId" column="id"/>
            <result property="userName" column="username"/>
            <result property="userSex" column="sex"/>
            <result property="userAddress" column="address"/>
            <result property="userBirthday" column="birthday"/>
        </collection>
    </resultMap>
</mapper>

编写测试类

package com.ghh.test;

import com.ghh.dao.RoleDao;
import com.ghh.dao.UserDao;
import com.ghh.domain.Role;
import com.ghh.domain.User;
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.After;
import org.junit.Before;
import org.junit.Test;

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

public class MybatisTest {
    private InputStream inputStream;
    private SqlSession sqlSession;
    private RoleDao mapper;
    @Before
    public void init() throws IOException {
        //1.读取配置文件
        inputStream =
                Resources.getResourceAsStream("SqlMapConfig.xml");
        //2.获取SqlSessionFactoryBuilder对象
        SqlSessionFactoryBuilder builder =
                new SqlSessionFactoryBuilder();
        //3.通过SqlSessionFactoryBuilder对象创建sqlSessionFqctory对象
        SqlSessionFactory sqlSessionFactory =
                builder.build(inputStream);
        //通过SqlSessionFactory对象创建SqlSession对象
        sqlSession = sqlSessionFactory.openSession();
        //获取dao的代理对象
        mapper = sqlSession.getMapper(RoleDao.class);
    }

    @After
    public void destroy() throws IOException {
        sqlSession.commit();
        sqlSession.close();
        inputStream.close();
    }

    @Test
    public void testFindAll() throws IOException {
      List<Role> roles = mapper.findAllRole();
      for(Role role:roles){
          System.out.println(role);
      }
    }
}

Role(roleId=1, roleName=院长, roleDesc=管理整个学院, users=[User(userId=1, userName=老王, userAddress=北京市顺义区, userSex=男, userBirthday=Tue Feb 27 08:00:00 CST 2018)])
Role(roleId=2, roleName=总裁, roleDesc=管理整个公司, users=[User(userId=2, userName=老王, userAddress=北京市顺义区, userSex=男, userBirthday=Tue Feb 27 08:00:00 CST 2018)])
Role(roleId=3, roleName=校长, roleDesc=管理整个学校, users=[User(userId=3, userName=null, userAddress=null, userSex=null, userBirthday=null)])

查询用户获取用户的角色信息

在这里插入图片描述

编写实体类

User.java：

package com.ghh.domain;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

import java.io.Serializable;
import java.util.Date;
import java.util.List;

@AllArgsConstructor
@NoArgsConstructor
@Data
@ToString
public class User implements Serializable {
    private Integer userId;
    private String userName;
    private String userAddress;
    private String userSex;
    private Date userBirthday;

    private List<Role> roles;
}

Role.java：

package com.ghh.domain;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.List;

@AllArgsConstructor
@NoArgsConstructor
@Data

public class Role {
    private Integer roleId;
    private String roleName;
    private String roleDesc;
}

编写UserDao

package com.ghh.dao;
import com.ghh.domain.User;
import java.util.List;
public interface UserDao {
    List<User> findAll();
}

编写UserDao的映射配置文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ghh.dao.UserDao">

    <select id="findAll" resultMap="UserMap">
        select * from user u
        left join user_role ur on u.id = ur.uid
        left join role on ur.rid = role.id
    </select>

    <resultMap id="UserMap" type="com.ghh.domain.User">
        <id property="userId" column="id"/>
        <result property="userName" column="username"/>
        <result property="userAddress" column="address"/>
        <result property="userSex" column="sex"/>
        <result property="userBirthday" column="birthday"/>
        <collection property="roles" ofType="Role">
            <id property="roleId" column="id"/>
            <result property="roleName" column="role_name"/>
            <result property="roleDesc" column="role_desc"/>
        </collection>
    </resultMap>
</mapper>

编写测试类

package com.ghh.test;

import com.ghh.dao.RoleDao;
import com.ghh.dao.UserDao;
import com.ghh.domain.Role;
import com.ghh.domain.User;
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.After;
import org.junit.Before;
import org.junit.Test;

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

public class MybatisTest {
    private InputStream inputStream;
    private SqlSession sqlSession;
    private UserDao mapper;
    @Before
    public void init() throws IOException {
        //1.读取配置文件
        inputStream =
                Resources.getResourceAsStream("SqlMapConfig.xml");
        //2.获取SqlSessionFactoryBuilder对象
        SqlSessionFactoryBuilder builder =
                new SqlSessionFactoryBuilder();
        //3.通过SqlSessionFactoryBuilder对象创建sqlSessionFqctory对象
        SqlSessionFactory sqlSessionFactory =
                builder.build(inputStream);
        //通过SqlSessionFactory对象创建SqlSession对象
        sqlSession = sqlSessionFactory.openSession();
        //获取dao的代理对象
        mapper = sqlSession.getMapper(UserDao.class);
    }

    @After
    public void destroy() throws IOException {
        sqlSession.commit();
        sqlSession.close();
        inputStream.close();
    }

    @Test
    public void testFindAll() throws IOException {
        List<User> users = mapper.findAll();
         for(User user:users){
             System.out.println(user);
         }
    }
}

User(userId=41, userName=老王, userAddress=北京市顺义区, userSex=男, userBirthday=Tue Feb 27 08:00:00 CST 2018, roles=[Role(roleId=41, roleName=院长, roleDesc=管理整个学院, users=null)])
User(userId=45, userName=小马宝莉, userAddress=北京修正, userSex=女, userBirthday=Thu Mar 08 08:00:00 CST 2018, roles=[Role(roleId=45, roleName=院长, roleDesc=管理整个学院, users=null)])
User(userId=42, userName=小二王, userAddress=北京市金燕龙, userSex=女, userBirthday=Fri Mar 02 08:00:00 CST 2018, roles=[Role(roleId=42, roleName=null, roleDesc=null, users=null)])
User(userId=43, userName=小二王, userAddress=北京市金燕龙, userSex=女, userBirthday=Sun Mar 04 08:00:00 CST 2018, roles=[Role(roleId=43, roleName=null, roleDesc=null, users=null)])
User(userId=44, userName=老王, userAddress=北京, userSex=男, userBirthday=Wed Mar 07 08:00:00 CST 2018, roles=[Role(roleId=44, roleName=null, roleDesc=null, users=null)])

赞(0) 打赏
未经允许不得转载：IDEA激活码 » MyBatis中多对多的查询

相关推荐

  • 暂无文章

IDEA永久激活破解教程,IDEA激活码

pycharm激活破解教程,pycharm激活码

Java视频资源

Java电子书

DataGrip激活教程 Docker Git教程 IntellIJ idea IntelliJ IDEA激活码 Java Java IO Java基础 Java基础面试题 Java集合面试题 Java面试题 JVM JVM面试题 Linux MySQL MySQL面试题 Nginx PyCharm PyCharm激活 Redis redis SpringBoot SpringBoot SpringBoot面试题 Web开发 剑指Offer系列 多线程 多线程并发面试题 字符串面试题 数据库 数据库 数据结构与算法 未分类 爬虫 电子书 软件下载

一个分享Java & Python知识的社区