F005MyBatis学习笔记-MyBatis的多表关联查询

淩亂°似流年 2023-10-07 08:54 2阅读 0赞

# 一、 概述 #

## 1、一对一 ##

> 举例：身份证号对人，一个身份证好只能对应一个人，一个人也只能对应一个身份证号，这就是一对一；

> 特例：Mybatis将多对一看做是一对一；

## 2、多对多 ##

> 举例：学生对老师，一个老师可以教多个学生，一个学生也可以被多个老师教，这就是多对多；

# 二、一对多案例演示 #

## 1、需求 ##

### 用户和账户： ###

一个用户可以有多个账户；

一个账户只能属于一个用户（多个账户也可以属于同一个用户）；

## 2、步骤 ##

第一步：建立两张表：用户表、账户表；

> 让用户表和账户表之间具备一对多的关系，需要在账户表中添加外键；

第二步：建立两个实体类：用户实体类和账户实体类；

> 让用户和账户的实体类体现出一对多的关系；

第三步：建立两个配置文件：用户的配置文件、账户的配置文件；

第四步：实现配置：当我们查询用户时，可以同时得到用户下所包含账户的信息；当我们查询账户时，可以得到账户所属用户的信息；

## 3、一对多代码演示 ##

### SQL语句： ###

CREATE TABLE `account` (
      `ID` int(11) NOT NULL COMMENT '编号',
      `UID` int(11) default NULL COMMENT '用户编号',
      `MONEY` double default NULL COMMENT '金额',
      PRIMARY KEY  (`ID`),
      KEY `FK_Reference_8` (`UID`),
      CONSTRAINT `FK_Reference_8` FOREIGN KEY (`UID`) REFERENCES `user` (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    
    
    insert  into `account`(`ID`,`UID`,`MONEY`) values (1,46,1000),(2,45,1000),(3,46,2000);

### pom.xml: ###

<?xml version="1.0" encoding="UTF-8"?>
    <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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
    
        <groupId>org.example</groupId>
        <artifactId>mybatis0203</artifactId>
        <version>1.0-SNAPSHOT</version>
        <packaging>jar</packaging>
        <dependencies>
            <dependency>
                <groupId>org.mybatis</groupId>
                <artifactId>mybatis</artifactId>
                <version>3.5.3</version>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.19</version>
            </dependency>
            <dependency>
                <groupId>log4j</groupId>
                <artifactId>log4j</artifactId>
                <version>1.2.17</version>
            </dependency>
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>4.13-beta-3</version>
                <scope>test</scope>
            </dependency>
        </dependencies>
    
    </project>

### IAccountDao: ###

package com.zibo.mybatis_crud.dao;
    
    import com.zibo.mybatis_crud.domain.Account;
    import com.zibo.mybatis_crud.domain.AccountUser;
    
    import java.util.List;
    
    public interface IAccountDao {
        //查询所有账户
        List<Account> findAll();
        //查询所有账户带user的用户名和地址信息
        List<AccountUser> findAllAccount();
    }

### IUserDao: ###

package com.zibo.mybatis_crud.dao;
    
    import com.zibo.mybatis_crud.domain.User;
    
    import java.util.List;
    
    public interface IUserDao {
        //查询所有，同时获取到用户所有的账户信息
        List<User> findAll();
        //根据id查询一条数据
        User findById(Integer userId);
    }

### Account: ###

package com.zibo.mybatis_crud.domain;
    
    public class Account {
        private Integer id;
        private Integer uid;
        private double money;
    
        private User user;
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public Integer getUid() {
            return uid;
        }
    
        public void setUid(Integer uid) {
            this.uid = uid;
        }
    
        public double getMoney() {
            return money;
        }
    
        public void setMoney(double money) {
            this.money = money;
        }
    
        public User getUser() {
            return user;
        }
    
        public void setUser(User user) {
            this.user = user;
        }
    
        @Override
        public String toString() {
            return "Account{" +
                    "id=" + id +
                    ", uid=" + uid +
                    ", money=" + money +
                    '}';
        }
    }

### AccountUser: ###

package com.zibo.mybatis_crud.domain;
    
    public class AccountUser extends Account {
        private String username;
        private String address;
    
        public String getUsername() {
            return username;
        }
    
        public void setUsername(String username) {
            this.username = username;
        }
    
        public String getAddress() {
            return address;
        }
    
        public void setAddress(String address) {
            this.address = address;
        }
    
        @Override
        public String toString() {
            return super.toString() + "      AccountUser{" +
                    "username='" + username + '\'' +
                    ", address='" + address + '\'' +
                    '}';
        }
    }

### User: ###

package com.zibo.mybatis_crud.domain;
    
    import java.io.Serializable;
    import java.util.Date;
    import java.util.List;
    
    public class User implements Serializable {
        private Integer id;
        private String username;
        private Date birthday;
        private String sex;
        private String address;
    
        //一对多关系映射：主表实体应该包含从表实体的集合引用
        private List<Account> accounts;
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getUsername() {
            return username;
        }
    
        public void setUsername(String username) {
            this.username = username;
        }
    
        public Date getBirthday() {
            return birthday;
        }
    
        public void setBirthday(Date birthday) {
            this.birthday = birthday;
        }
    
        public String getSex() {
            return sex;
        }
    
        public void setSex(String sex) {
            this.sex = sex;
        }
    
        public String getAddress() {
            return address;
        }
    
        public void setAddress(String address) {
            this.address = address;
        }
    
        public List<Account> getAccounts() {
            return accounts;
        }
    
        public void setAccounts(List<Account> accounts) {
            this.accounts = accounts;
        }
    
        @Override
        public String toString() {
            return "User{" +
                    "id=" + id +
                    ", username='" + username + '\'' +
                    ", birthday=" + birthday +
                    ", sex='" + sex + '\'' +
                    ", address='" + address + '\'' +
                    '}';
        }
    }

### IAccountDao.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.zibo.mybatis_crud.dao.IAccountDao">
        
        <resultMap id="accountUserMap" type="account">
            <id property="id" column="aid"/>
            <result property="uid" column="uid"/>
            <result property="money" column="money"/>
            
            <association property="user" column="uid" javaType="user">
                <id property="id" column="id"/>
                <result property="username" column="username"/>
                <result property="address" column="address"/>
                <result property="sex" column="sex"/>
                <result property="birthday" column="birthday"/>
            </association>
        </resultMap>
        
        <select id="findAll" resultMap="accountUserMap">
            select u.*,a.id as aid,a.uid,a.money from account a,user u where u.id = a.uid;
        </select>
        
        <select id="findAllAccount" resultType="accountUser">
            select a.*,u.username,u.address from account a,user u where u.id = a.uid;
        </select>
    </mapper>

### IUserDao.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.zibo.mybatis_crud.dao.IUserDao">
        
        <resultMap id="userAccountMap" type="user">
            <id property="id" column="id"/>
            <result property="username" column="username"/>
            <result property="address" column="address"/>
            <result property="sex" column="sex"/>
            <result property="birthday" column="birthday"/>
            
            <collection property="accounts" ofType="account">
                <id column="aid" property="id"/>
                <result column="uid" property="uid"/>
                <result column="money" property="money"/>
            </collection>
        </resultMap>
        
        <select id="findAll" resultMap="userAccountMap">
            select * from user u left outer join account a on u.id = a.uid
        </select>
        
        <select id="findById" parameterType="Integer" resultType="user">
            select * from user where id = #{随便写};
        </select>
    </mapper>

### log4j.properties: ###

# Set root category priority to INFO and its only appender to CONSOLE.
    #log4j.rootCategory=INFO, CONSOLE            debug   info   warn error fatal
    log4j.rootCategory=debug, CONSOLE, LOGFILE
    
    # Set the enterprise logger category to FATAL and its only appender to CONSOLE.
    log4j.logger.org.apache.axis.enterprise=FATAL, CONSOLE
    
    # CONSOLE is set to be a ConsoleAppender using a PatternLayout.
    log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender
    log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout
    log4j.appender.CONSOLE.layout.ConversionPattern=%d{ISO8601} %-6r [%15.15t] %-5p %30.30c %x - %m\n
    
    # LOGFILE is set to be a File appender using a PatternLayout.
    log4j.appender.LOGFILE=org.apache.log4j.FileAppender
    log4j.appender.LOGFILE.File=d:\axis.log
    log4j.appender.LOGFILE.Append=true
    log4j.appender.LOGFILE.layout=org.apache.log4j.PatternLayout
    log4j.appender.LOGFILE.layout.ConversionPattern=%d{ISO8601} %-6r [%15.15t] %-5p %30.30c %x - %m\n

### SqlMapConfig.xml: ###

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE configuration
            PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-config.dtd">
    <configuration>
        
        <typeAliases>
            
    
            
            <package name="com.zibo.mybatis_crud.domain"/>
        </typeAliases>
    
        
        <environments default="mysql">
    
            <environment id="mysql">
    
                <transactionManager type="JDBC">
    
                </transactionManager>
    
                <dataSource type="POOLED">
    
                    <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                    <property name="url" value="jdbc:mysql://localhost:3306/zibo?serverTimezone=UTC"/>
                    <property name="username" value="root"/>
                    <property name="password" value="zibo15239417242"/>
                </dataSource>
            </environment>
        </environments>
    
        <mappers>
    
    
            <package name="com.zibo.mybatis_crud.dao"/>
        </mappers>
    </configuration>

### Main: ###

package com.zibo.mybatis_crud;
    
    import com.zibo.mybatis_crud.dao.IAccountDao;
    import com.zibo.mybatis_crud.dao.IUserDao;
    import com.zibo.mybatis_crud.domain.Account;
    import com.zibo.mybatis_crud.domain.AccountUser;
    import com.zibo.mybatis_crud.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.List;
    
    public class Main {
        private InputStream in;
        private SqlSession sqlSession;
        private IUserDao iUserDao;
        private IAccountDao iAccountDao;
        /**
         * 初始化
         */
        @Before
        public void init() throws IOException {
            //1、读取配置文件
            in = Resources.getResourceAsStream("SqlMapConfig.xml");
            //2、创建SqlSessionFactory工厂
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            SqlSessionFactory factory = builder.build(in);
            //3、通过SqlSessionFactory工厂生产SqlSession对象
            sqlSession = factory.openSession(true);
            //4、使用SqlSession创建Dao接口的代理对象
            iUserDao = sqlSession.getMapper(IUserDao.class);
            iAccountDao = sqlSession.getMapper(IAccountDao.class);
        }
        @After
        public void end() throws IOException {
            //6、释放资源
            sqlSession.close();
            in.close();
        }
        /**
         * 测试查询所有
         */
        @Test
        public void testFindAll(){
            //5、使用代理对象执行方法
            List<User> users = iUserDao.findAll();
            for (User user : users) {
                System.out.println(user);
                System.out.println(user.getAccounts());
            }
        }
        @Test
        public void testFindById(){
            //5、使用代理对象执行方法
            User user = iUserDao.findById(45);
            System.out.println(user);
        }
    
    
        @Test
        public void testFindAll_A(){
            //5、使用代理对象执行方法
            List<Account> accounts = iAccountDao.findAll();
            for (Account account : accounts) {
                System.out.println(account);
                System.out.println(account.getUser());
            }
        }
    
        @Test
        public void testFindAllAccount_A(){
            //5、使用代理对象执行方法
            List<AccountUser> accounts = iAccountDao.findAllAccount();
            for (AccountUser account : accounts) {
                System.out.println(account);
            }
        }
    }

### 文件位置结构图： ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI5Njg5MzQz_size_16_color_FFFFFF_t_70][]

# 三、多对多案例演示 #

## 1、需求 ##

### 示例：用户和角色 ###

一个用户可以有多个角色；

一个角色可以赋予多个用户；

## 2、步骤 ##

第一步：建立两张表：用户表、角色表；

> 让用户表和角色表具有多对多的关系，需要使用中间表，中间表中包含各自的主键，在中间表中是外键；

第二步：建立两个实体类：用户实体类和角色实体类；

> 让用户和角色的实体类体现出多对多的关系；
> 
> 各自包含对方一个集合；

第三步：建立两个配置文件：用户的配置文件、角色的配置文件；

第四步：实现配置：当我们查询用户时，可以同时得到用户下所包含角色的信息；当我们查询角色时，可以得到账户所属用户的信息；

## 3、代码演示 ##

### SQL: ###

CREATE TABLE `user` (
      `id` int(11) NOT NULL auto_increment,
      `username` varchar(32) NOT NULL COMMENT '用户名称',
      `birthday` datetime default NULL COMMENT '生日',
      `sex` char(1) default NULL COMMENT '性别',
      `address` varchar(256) default NULL COMMENT '地址',
      PRIMARY KEY  (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    
    insert  into `user`(`id`,`username`,`birthday`,`sex`,`address`) values (41,'老王','2018-02-27 17:47:08','男','北京'),(42,'小二王','2018-03-02 15:09:37','女','北京金燕龙'),(43,'小二王','2018-03-04 11:34:34','女','北京金燕龙'),(45,'传智播客','2018-03-04 12:04:06','男','北京金燕龙'),(46,'老王','2018-03-07 17:37:26','男','北京'),(48,'小马宝莉','2018-03-08 11:44:00','女','北京修正');
    
    
    CREATE TABLE `role` (
      `ID` int(11) NOT NULL COMMENT '编号',
      `ROLE_NAME` varchar(30) default NULL COMMENT '角色名称',
      `ROLE_DESC` varchar(60) default NULL COMMENT '角色描述',
      PRIMARY KEY  (`ID`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    
    
    insert  into `role`(`ID`,`ROLE_NAME`,`ROLE_DESC`) values (1,'院长','管理整个学院'),(2,'总裁','管理整个公司'),(3,'校长','管理整个学校');
    
    
    CREATE TABLE `user_role` (
      `UID` int(11) NOT NULL COMMENT '用户编号',
      `RID` int(11) NOT NULL COMMENT '角色编号',
      PRIMARY KEY  (`UID`,`RID`),
      KEY `FK_Reference_10` (`RID`),
      CONSTRAINT `FK_Reference_10` FOREIGN KEY (`RID`) REFERENCES `role` (`ID`),
      CONSTRAINT `FK_Reference_9` FOREIGN KEY (`UID`) REFERENCES `user` (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    insert  into `user_role`(`UID`,`RID`) values (41,1),(45,1),(41,2);

### IRoleDao： ###

package com.zibo.mybatis_crud.dao;
    
    import com.zibo.mybatis_crud.domain.Role;
    
    import java.util.List;
    
    public interface IRoleDao {
        //查询所有，同时获取到角色所有的用户信息
        List<Role> findAll();
    }

### IUserDao： ###

package com.zibo.mybatis_crud.dao;
    
    import com.zibo.mybatis_crud.domain.User;
    
    import java.util.List;
    
    public interface IUserDao {
        //查询所有，同时获取到用户所有的角色信息
        List<User> findAll();
    }

### Role： ###

package com.zibo.mybatis_crud.domain;
    
    import java.io.Serializable;
    import java.util.List;
    
    public class Role implements Serializable {
        private Integer roleId;
        private String roleName;
        private String roleDesc;
    
        //多对多关系映射
        private List<User> users;
    
        public Integer getRoleId() {
            return roleId;
        }
    
        public void setRoleId(Integer roleId) {
            this.roleId = roleId;
        }
    
        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;
        }
    
        public List<User> getUsers() {
            return users;
        }
    
        public void setUsers(List<User> users) {
            this.users = users;
        }
    
        @Override
        public String toString() {
            return "Role{" +
                    "roleId=" + roleId +
                    ", roleName='" + roleName + '\'' +
                    ", roleDesc='" + roleDesc + '\'' +
                    '}';
        }
    }

### User： ###

package com.zibo.mybatis_crud.domain;
    
    import java.io.Serializable;
    import java.util.Date;
    import java.util.List;
    
    public class User implements Serializable {
        private Integer id;
        private String username;
        private Date birthday;
        private String sex;
        private String address;
    
        //多对多
        private List<Role> roles;
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getUsername() {
            return username;
        }
    
        public void setUsername(String username) {
            this.username = username;
        }
    
        public Date getBirthday() {
            return birthday;
        }
    
        public void setBirthday(Date birthday) {
            this.birthday = birthday;
        }
    
        public String getSex() {
            return sex;
        }
    
        public void setSex(String sex) {
            this.sex = sex;
        }
    
        public String getAddress() {
            return address;
        }
    
        public void setAddress(String address) {
            this.address = address;
        }
    
        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 + '\'' +
                    ", birthday=" + birthday +
                    ", sex='" + sex + '\'' +
                    ", address='" + address + '\'' +
                    '}';
        }
    
    
    }

### IRoleDao.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.zibo.mybatis_crud.dao.IRoleDao">
        
        <resultMap id="RoleMap" type="role">
            <id property="roleId" column="rid"/>
            <result property="roleName" column="role_name"/>
            <result property="roleDesc" column="role_desc"/>
            <collection property="users" ofType="user">
                <id property="id" column="id"/>
                <result property="username" column="username"/>
                <result property="address" column="address"/>
                <result property="sex" column="sex"/>
                <result property="birthday" column="birthday"/>
            </collection>
        </resultMap>
        
        <select id="findAll" resultMap="RoleMap">
            select u.*,r.id as rid,r.role_name,r.role_desc from role r
                left outer join user_role ur on r.ID = ur.rid
                left outer join user u on u.id = ur.uid;
        </select>
    </mapper>

### IUserDao.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.zibo.mybatis_crud.dao.IUserDao">
        
        <resultMap id="userMap" type="user">
            <id property="id" column="id"/>
            <result property="username" column="username"/>
            <result property="address" column="address"/>
            <result property="sex" column="sex"/>
            <result property="birthday" column="birthday"/>
            
            <collection property="roles" ofType="role">
                <id property="roleId" column="rid"/>
                <result property="roleName" column="role_name"/>
                <result property="roleDesc" column="role_desc"/>
            </collection>
        </resultMap>
        
        <select id="findAll" resultMap="userMap">
            select u.*,r.id as rid,r.role_name,r.role_desc from user u
                left outer join user_role ur on u.id = ur.UID
                left outer join role r on ur.RID = r.ID;
        </select>
    </mapper>

### Main: ###

package com.zibo.mybatis_crud;
    
    import com.zibo.mybatis_crud.dao.IRoleDao;
    import com.zibo.mybatis_crud.dao.IUserDao;
    import com.zibo.mybatis_crud.domain.Role;
    import com.zibo.mybatis_crud.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.List;
    
    public class Main {
        private InputStream in;
        private SqlSession sqlSession;
        private IUserDao iUserDao;
        private IRoleDao iRoleDao;
        /**
         * 初始化
         */
        @Before
        public void init() throws IOException {
            //1、读取配置文件
            in = Resources.getResourceAsStream("SqlMapConfig.xml");
            //2、创建SqlSessionFactory工厂
            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            SqlSessionFactory factory = builder.build(in);
            //3、通过SqlSessionFactory工厂生产SqlSession对象
            sqlSession = factory.openSession(true);
            //4、使用SqlSession创建Dao接口的代理对象
            iUserDao = sqlSession.getMapper(IUserDao.class);
            iRoleDao = sqlSession.getMapper(IRoleDao.class);
        }
        @After
        public void end() throws IOException {
            //6、释放资源
            sqlSession.close();
            in.close();
        }
        /**
         * 测试查询所有
         */
        @Test
        public void testFindAll(){
            //5、使用代理对象执行方法
            List<User> users = iUserDao.findAll();
            for (User user : users) {
                System.out.println(user);
                System.out.println(user.getRoles());
            }
        }
    
        @Test
        public void testFindAll_R(){
            //5、使用代理对象执行方法
            List<Role> roles = iRoleDao.findAll();
            for (Role role : roles) {
                System.out.println(role);
                System.out.println(role.getUsers());
            }
        }
    }

其他配置文件等，见一对多代码演示；

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI5Njg5MzQz_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200204113149559.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzI5Njg5MzQz,size_16,color_FFFFFF,t_70