Mybatis三：关联映射

最新推荐文章于 2022-08-27 12:12:56 发布

青衫忆笙い

最新推荐文章于 2022-08-27 12:12:56 发布

阅读量603

点赞数

分类专栏： JAVA 文章标签： mybatis java

本文链接：https://blog.csdn.net/qq_38718643/article/details/122327076

版权

JAVA 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

文章目录

1 关联映射

1 关联映射

-- t_user 用户表
DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_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 `t_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','女','北京修正');

-- t_account 账户表
DROP TABLE IF EXISTS `t_account`;
CREATE TABLE `t_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 `t_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `t_account`(`ID`,`UID`,`MONEY`) 
values (1,41,1000),(2,45,1000),(3,41,2000);

-- t_role 角色表
DROP TABLE IF EXISTS `t_role`;
CREATE TABLE `t_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 `t_role`(`ID`,`ROLE_NAME`,`ROLE_DESC`) 
values (1,'院长','管理整个学院'),(2,'总裁','管理整个公司'),(3,'校长','管理整个学校');

-- user_role 中间表(多对多使用)
DROP TABLE IF EXISTS `t_user_role`;
CREATE TABLE `t_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 `t_role` (`ID`),
  CONSTRAINT `FK_Reference_9` FOREIGN KEY (`UID`) REFERENCES `t_user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `t_user_role`(`UID`,`RID`) values (41,1),(45,1),(41,2);

环境搭建(源码见文末链接)

项目整体结构

依赖

<properties>
    <maven.compiler.source>11</maven.compiler.source>
    <maven.compiler.target>11</maven.compiler.target>
</properties>

<dependencies>
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.5</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.21</version>
    </dependency>
    <dependency>
        <groupId>log4j</groupId>
        <artifactId>log4j</artifactId>
        <version>1.2.17</version>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <version>1.18.22</version>
    </dependency>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
    </dependency>
</dependencies>

配置

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

datasource.properties

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://192.168.181.160:3306/test?serverTimezone=CTT
username=root
password=root

mybatis-config.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>

    <properties resource="datasource.properties"/>
    <environments default="mysql">
        <environment id="mysql">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <!--一对一 配置-->
        <mapper resource="mapper/one2one/AccountMapper.xml"/>
        <!--一对多 配置-->
        <mapper resource="mapper/one2many/UserMapper.xml"/>
        <!--多对多 配置-->
        <mapper resource="mapper/many2many/UserMapper.xml"/>
        <mapper resource="mapper/many2many/RoleMapper.xml"/>
    </mappers>
</configuration>

1.1 一对一

在任意一方引入对方主键作为外键。

通过对象进行的关系描述

class A{
    B b;
}
class B{
    A a;
}

编码

实体类

package one2one;

import lombok.Getter;
import lombok.Setter;
/**
 * 需求：查询所有账户信息，并查询该账户所关联的用户名和地址
 * 因此Account的toString方法打印所有信息，而User的toString方法只打印username和address
 */
@Getter
@Setter
public class Account {
    private Integer id;
    private Integer uid;
    private Double money;
    private User user;

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", uid=" + uid +
                ", money=" + money +
                ", user=" + user +
                '}';
    }
}

package one2one;
import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
public class User {
    private int id;
    private String username;
    private String birthday;
    private String sex;
    private String address;

    @Override
    public String toString() {
        return "User{" +
                "username='" + username + '\'' +
                ", address='" + address + '\'' +
                '}';
    }
}

持久层

package one2one;
import java.util.List;

public interface AccountMapper {
    List<Account> findAllAccount();
}

<?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="one2one.AccountMapper">
    <select id="findAllAccount" resultMap="accountMap">
        select t_account.* , t_user.username, t_user.address
        from t_account , t_user
        where t_user.id = t_account.uid
    </select>

    <resultMap id="accountMap" type="one2one.Account">
        <id property="id" column="id"/>
        <result property="uid" column="uid"/>
        <result property="money" column="money"/>
        <association property="user" javaType="one2one.User">
            <result property="username" column="username"/>
            <result property="address" column="address"/>
        </association>
    </resultMap>
</mapper>

测试

package test;

import one2one.Account;
import one2one.AccountMapper;
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 TestOne2One {
    private InputStream inputStream = null;
    private SqlSession session = null;
    private AccountMapper accountMapper = null;

    @Before
    public void init() throws IOException {
        // 1.读取配置文件
        inputStream = Resources.getResourceAsStream("mybatis-config.xml");
        // 2.创建SqlSessionFactory工厂
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
        // 3.使用工厂生产SqlSession对象
        session = factory.openSession();
        // 4.使用SqlSession创建Dao接口的代理对象
        accountMapper = session.getMapper(AccountMapper.class);
        // 5.执行对象方法,即下面的各个测例
    }

    @After
    public void destroy() throws IOException {
        /**
         * 释放资源前提交事务，写在destory()内可以减少代码冗余，免得都写
         *
         * 若是碰到增删改的操作时，千万不要误以为不用提交事务！！！
         */
        session.commit();

        // 6.释放资源
        session.close();
        inputStream.close();
    }

    /**
     * 需求：查询所有账户信息，并查询该账户所关联的用户名和地址
     * 注意：因为一个账户信息只能供某个用户使用，所以从查询账户信息出发关联查询用户信息为一对一查询。
     * 如果从用户信息出发查询用户下的账户信息则为一对多查询(见下面一对多样例)，因为一个用户可以有多个账户。
     */
    @Test
    public void testOne2One() {

        List<Account> accountList = accountMapper.findAllAccount();
        accountList.forEach(account -> System.out.println(account));
        /**
         * Account{id=1, uid=41, money=1000.0, user=User{username='张三', address='北京'}}
         * Account{id=2, uid=45, money=1000.0, user=User{username='赵六', address='北京金燕龙'}}
         * Account{id=3, uid=41, money=2000.0, user=User{username='张三', address='北京'}}
         */
    }
}

1.2 一对多

在“多”的一方引入“一”的一方的主键作为外键。

通过对象进行的关系描述

class A{
    List<B> b;
}
class B{
    A a;
}

编码

实体类

package one2many;
import lombok.Getter;
import lombok.Setter;
import java.util.List;

/**
 * 需求：查询所有用户，同时获取出每个用户下的所有账户信息。
 * 因此User类打印所有信息，Account类打印自己信息(此时不含User信息)
 */
@Getter
@Setter
public class User {
    private int id;
    private String username;
    private String birthday;
    private String sex;
    private String address;
    private List<Account> accounts;

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", birthday='" + birthday + '\'' +
                ", sex='" + sex + '\'' +
                ", address='" + address + '\'' +
                ", accounts=" + accounts +
                '}';
    }
}

package one2many;
import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
public class Account {
    private Integer id;
    private Integer uid;
    private Double money;
    // private User user;

    @Override
    public String toString() {
        return "Account{" +
                "id=" + id +
                ", uid=" + uid +
                ", money=" + money +
                '}';
    }
}

持久层

package one2many;

import java.util.List;

public interface UserMapper {

    /**
     * 查询所有用户，同时获取出每个用户下的所有账户信息
     */
    List<User> findAllUser();
}

<?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="one2many.UserMapper">

    <!--
         因为user、account表都有id属性，且id为主键
         故当resultMap映射的时候，mybatis无法区别，
         因此写sql时需要用到别名，否则使用resultMap映射不能达到预期结果
         (至少把其中一个命名别名)
      -->
    <select id="findAllUser" resultMap="userMap">
        select
            t_user.id as userId,
            username,
            birthday,
            sex,
            address,
            t_account.id as aId,
            uid,
            money
        from t_user left join t_account
         on t_user.id = t_account.uid;
    </select>

    <resultMap id="userMap" type="one2many.User">
        <!-- 此处使用user.id 的别名 userId -->
        <id property="id" column="userId"/>
        <result property="username" column="username"/>
        <result property="birthday" column="birthday"/>
        <result property="sex" column="sex"/>
        <result property="address" column="address"/>
        <!-- ofType指定List中元素类型 -->
        <collection property="accounts" ofType="one2many.Account">
            <!-- 此处使用 account.id 的别名 aId-->
            <id property="id" column="aId"/>
            <result property="uid" column="uid"/>
            <result property="money" column="money"/>
        </collection>
    </resultMap>
</mapper>

测试

package test;

import one2many.User;
import one2many.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.After;
import org.junit.Before;
import org.junit.Test;

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

public class TestOne2Many {
    private InputStream inputStream = null;
    private SqlSession session = null;
    private UserMapper userMapper = null;

    @Before
    public void init() throws IOException {
        // 1.读取配置文件
        inputStream = Resources.getResourceAsStream("mybatis-config.xml");
        // 2.创建SqlSessionFactory工厂
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
        // 3.使用工厂生产SqlSession对象
        session = factory.openSession();
        // 4.使用SqlSession创建Dao接口的代理对象
        userMapper = session.getMapper(UserMapper.class);
        // 5.执行对象方法,即下面的各个测例
    }

    @After
    public void destroy() throws IOException {
        /**
         * 释放资源前提交事务，写在destory()内可以减少代码冗余，免得都写
         *
         * 若是碰到增删改的操作时，千万不要误以为不用提交事务！！！
         */
        session.commit();

        // 6.释放资源
        session.close();
        inputStream.close();
    }

    /**
     * 需求：查询所有用户，同时获取出每个用户下的所有账户信息。
     * 分析：用户信息和他的账户信息为一对多关系，并且查询过程中如果用户没有账户信息，
     * 此时也要将用户信息查询出来，我们想到了左外连接查询比较合适。
     */
    @Test
    public void testOne2Many() {
        List<User> users = userMapper.findAllUser();
        users.forEach(u -> System.out.println(u));
        /**
         * User{id=41, username='张三', birthday='2018-02-27 17:47:08', sex='男', address='北京', accounts=[Account{id=3, uid=41, money=2000.0}, Account{id=1, uid=41, money=1000.0}]}
         * User{id=42, username='李四', birthday='2018-03-02 15:09:37', sex='女', address='北京', accounts=[]}
         * User{id=43, username='王五', birthday='2018-03-04 11:34:34', sex='女', address='北京金燕龙', accounts=[]}
         * User{id=45, username='赵六', birthday='2018-03-04 12:04:06', sex='男', address='北京金燕龙', accounts=[Account{id=2, uid=45, money=1000.0}]}
         * User{id=46, username='小明', birthday='2018-03-07 17:37:26', sex='男', address='北京', accounts=[]}
         * User{id=48, username='小红', birthday='2018-03-08 11:44:00', sex='女', address='北京修正', accounts=[]}
         */
    }
}

1.3 多对多

建立中间表，引入两张表的主键作为外键，使用新的字段作为主键(或让两个主键成为联合主键)

通过对象进行的关系描述

class A{
    List<B> b;
}
class B{
    List<A> a;
}

编码

实体类

package many2many;
import lombok.Data;
import java.util.List;

@Data
public class User {
    private int id;
    private String username;
    private String birthday;
    private String sex;
    private String address;
    List<Role> roles;
}

package many2many;
import lombok.Data;
import java.util.List;

@Data
public class Role {
    private Integer roleId;
    private String roleName;
    private String roleDesc;
    private List<User> users;
}

持久层

package many2many;
import java.util.List;

public interface UserMapper {
    /**
     * user到role的一对多
     * 需求：查询用户时，可以同时得到用户所包含的角色信息
     */
    List<User> findAllUser();
}

package many2many;
import java.util.List;

public interface RoleMapper {
    /**
     * role到user的一对多
     * 需求：查询角色时，可以同时得到角色所赋予的用户信息
     */
    List<Role> findAllRole();
}

<?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="many2many.UserMapper">
    <select id="findAllUser" resultMap="userMap">
        select
            t_user.id as userId,
            username,
            birthday,
            sex,
            address,
            t_role.id as roleId,
            role_name,
            role_desc
        from t_user
        left join t_user_role on t_user.id = t_user_role.uid
        left join t_role on t_role.id = t_user_role.rid
    </select>

    <resultMap id="userMap" type="many2many.User">
        <id property="id" column="userId"/>
        <result property="username" column="username"/>
        <result property="birthday" column="birthday"/>
        <result property="sex" column="sex"/>
        <result property="address" column="address"/>
        <collection property="roles" ofType="many2many.Role">
            <id property="roleId" column="roleId"/>
            <result property="roleName" column="role_name"/>
            <result property="roleDesc" column="role_desc"/>
        </collection>
    </resultMap>
</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="many2many.RoleMapper">
    <!-- 查询所有角色 -->
    <select id="findAllRole" resultMap="roleMap">
        select
            t_role.id as roleId,
            role_name,
            role_desc,
            t_user.id as userId,
            username,
            birthday,
            sex,
            address
        from t_role
        left join t_user_role on t_role.id = t_user_role.rid
        left join t_user on t_user.id = t_user_role.uid
    </select>

    <resultMap id="roleMap" type="many2many.Role">
        <id property="roleId" column="roleId"/>
        <result property="roleName" column="role_name"/>
        <result property="roleDesc" column="role_desc"/>
        <collection property="users" ofType="many2many.User">
            <id property="id" column="userId"/>
            <result property="username" column="username"/>
            <result property="birthday" column="birthday"/>
            <result property="sex" column="sex"/>
            <result property="address" column="address"/>
        </collection>
    </resultMap>
</mapper>

测试

package test;

import many2many.Role;
import many2many.RoleMapper;
import many2many.User;
import many2many.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.After;
import org.junit.Before;
import org.junit.Test;

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

public class TestMany2Many {
    private InputStream inputStream = null;
    private SqlSession session = null;
    private UserMapper userMapper = null; // user到role的一对多
    private RoleMapper roleMapper = null; // role到user的一对多

    @Before
    public void init() throws IOException {
        // 1.读取配置文件
        inputStream = Resources.getResourceAsStream("mybatis-config.xml");
        // 2.创建SqlSessionFactory工厂
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);
        // 3.使用工厂生产SqlSession对象
        session = factory.openSession();
        // 4.使用SqlSession创建Dao接口的代理对象
        userMapper = session.getMapper(UserMapper.class);
        roleMapper = session.getMapper(RoleMapper.class);
        // 5.执行对象方法,即下面的各个测例
    }

    @After
    public void destroy() throws IOException {
        /**
         * 释放资源前提交事务，写在destory()内可以减少代码冗余，免得都写
         *
         * 若是碰到增删改的操作时，千万不要误以为不用提交事务！！！
         */
        session.commit();

        // 6.释放资源
        session.close();
        inputStream.close();
    }

    /**
     * user到role的一对多
     * 需求：查询用户时，可以同时得到用户所包含的角色信息
     */
    @Test
    public void testMany2Many1() {
        List<User> users = userMapper.findAllUser();
        users.forEach(u -> System.out.println(u));
        /**
         * User(id=41, username=张三, birthday=2018-02-27 17:47:08, sex=男, address=北京,
         * roles=[Role(roleId=1, roleName=院长, roleDesc=管理整个学院, users=null),
         * Role(roleId=2, roleName=总裁, roleDesc=管理整个公司, users=null)])
         *
         * User(id=42, username=李四, birthday=2018-03-02 15:09:37, sex=女, address=北京, roles=[])
         * User(id=43, username=王五, birthday=2018-03-04 11:34:34, sex=女, address=北京金燕龙, roles=[])
         *
         * User(id=45, username=赵六, birthday=2018-03-04 12:04:06, sex=男, address=北京金燕龙,
         * roles=[Role(roleId=1, roleName=院长, roleDesc=管理整个学院, users=null)])
         *
         * User(id=46, username=小明, birthday=2018-03-07 17:37:26, sex=男, address=北京, roles=[])
         * User(id=48, username=小红, birthday=2018-03-08 11:44:00, sex=女, address=北京修正, roles=[])
         */
    }

    /**
     * role到user的一对多
     * 需求：查询角色时，可以同时得到角色所赋予的用户信息
     */
    @Test
    public void testMany2Many2() {
        List<Role> roles = roleMapper.findAllRole();
        roles.forEach(role -> System.out.println(role));
        /**
         * Role(roleId=1, roleName=院长, roleDesc=管理整个学院,
         * users=[User(id=41, username=张三, birthday=2018-02-27 17:47:08, sex=男, address=北京, roles=null),
         * User(id=45, username=赵六, birthday=2018-03-04 12:04:06, sex=男, address=北京金燕龙, roles=null)])
         *
         * Role(roleId=2, roleName=总裁, roleDesc=管理整个公司,
         * users=[User(id=41, username=张三, birthday=2018-02-27 17:47:08, sex=男, address=北京, roles=null)])
         *
         * Role(roleId=3, roleName=校长, roleDesc=管理整个学校, users=[])
         */
    }
}