05MyBatis多表操作(一对一、一对多、多对多)

梓源先生

已于 2022-04-01 09:50:21 修改

阅读量372

点赞数

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

于 2022-03-11 09:34:01 首次发布

本文链接：https://blog.csdn.net/nie13739606256/article/details/123416088

版权

mybatis 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

数据库表之间的关系分为：一对一、一对多、多对多

1一对一查询

用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户

一对一查询的需求：查询一个订单，与此同时查询出该订单所属的用户

表sql语句


DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ordertime` timestamp(0) NULL DEFAULT NULL,
  `total` double(255, 0) NULL DEFAULT NULL,
  `uid` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;

INSERT INTO `orders` VALUES (1, '2022-03-10 15:23:59', 3000, 1);
INSERT INTO `orders` VALUES (2, '2022-03-10 15:23:59', 5000, 1);
INSERT INTO `orders` VALUES (3, '2022-03-10 15:23:59', 5000, 2);
INSERT INTO `orders` VALUES (4, '2022-03-10 15:23:59', 7000, 2);

DROP TABLE IF EXISTS `role`;
CREATE TABLE `role`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rolename` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `roledesc` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;


INSERT INTO `role` VALUES (1, '院长', '负责巡逻');
INSERT INTO `role` VALUES (2, '讲师', '讲课');
INSERT INTO `role` VALUES (3, '班主任', '监督学生');


DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `password` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Compact;


INSERT INTO `user` VALUES (1, 'zhangsan', '123');
INSERT INTO `user` VALUES (2, 'lisi', '234');


DROP TABLE IF EXISTS `user_role`;
CREATE TABLE `user_role`  (
  `user_id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;

INSERT INTO `user_role` VALUES (1, 1);
INSERT INTO `user_role` VALUES (1, 1);
INSERT INTO `user_role` VALUES (2, 2);

SET FOREIGN_KEY_CHECKS = 1;

创建maven项目，依赖和配置文件参考01Mybatis入门_技术改变世界-CSDN博客

目录结构为

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标签加载外部properties文件-->
    <properties resource="jdbc.properties"></properties>
    <!-- 自定义别名  映射文件中可直接使用user-->
    <typeAliases>
        <typeAlias type="com.nie.pojo.Order" alias="order"></typeAlias>
        <typeAlias type="com.nie.pojo.User" alias="user"></typeAlias>
        <typeAlias type="com.nie.pojo.Role" alias="role"></typeAlias>
    </typeAliases>
    <!--数据源环境-->
    <environments default="developement">
        <environment id="developement">
            <transactionManager type="JDBC"></transactionManager>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>

    <!--加载映射文件-->
    <mappers>
        <mapper resource="com/nie/mybatis/OrderMapper.xml"></mapper>
        <mapper resource="com/nie/mybatis/UserMapper.xml"></mapper>
    </mappers>

</configuration>

实体类

@Data
public class User {

    private int id;
    private String username;
    private String password;
    //描述的是当前用户存在哪些订单 一对多使用
    private List<Order> orderList;

    //描述的是当前用户具备哪些角色  多对多使用
    private List<Role> roleList;
}

@Data
public class Order {

    private int id;
    private Date ordertime;
    private double total;
    //当前订单属于哪一个用户
    private User user;
}

mapper接口

public interface OrderMapper {
    //查询全部的方法
    public List<Order> findAll();
}

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.nie.mapper.OrderMapper">
    <!--一对一-->
    <!--order 在mybatis-config.xml配置文件中定义好别名是类Order的对象-->
    <resultMap id="orderMap" type="order">
        <!--手动指定字段与实体属性的映射关系
            column: 数据表的字段名称
            property：实体的属性名称
        -->
        <id column="id" property="id"></id>
        <result column="ordertime" property="ordertime"></result>
        <result column="total" property="total"></result>
        <!--方式1-->
        <!--<result column="uid" property="user.id"></result>
        <result column="username" property="user.username"></result>
        <result column="password" property="user.password"></result>
        <result column="birthday" property="user.birthday"></result>-->
        <!--方式2-->
        <!--
            property: 当前实体(order)中的属性名称(private User user)
            javaType: 当前实体(order)中的属性的类型(User)
        -->
        <association property="user" javaType="user">
            <id column="uid" property="id"></id>
            <result column="username" property="username"></result>
            <result column="password" property="password"></result>
        </association>

    </resultMap>
    <!--返回resultMap在orderMap封装的参数、查询的字段跟resultMap一一对应-->
    <select id="findAll" resultMap="orderMap">
          SELECT a.id ,ordertime,total,a.uid,username,`password` FROM `orders` a
        join `user` b on a.uid=b.id;
    </select>

</mapper>

测试类

    @Test
    public void test01() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession session = sqlSessionFactory.openSession(true);//开启事务 默认是关闭的
        OrderMapper mapper = session.getMapper(OrderMapper.class);
        List<Order> all = mapper.findAll();
        System.out.println(all);
        session.close();
    }

2一对多查询

用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户

一对多查询的需求：查询一个用户，与此同时查询出该用户具有的订单

UserMapper接口

public interface UserMapper {

    public List<User> findAll();//一对多
     
    public List<User> findUserAndRoleAll();//多对多

}

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.nie.mapper.UserMapper">
    <!--一对多-->
    <resultMap id="userMap" type="user">
        <id column="id" property="id"></id>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <!--配置集合信息
            property:集合名称
            ofType：当前集合中的数据类型
        -->
        <collection property="orderList" ofType="order">
            <!--封装order的数据-->
            <id column="oid" property="id"></id>
            <result column="ordertime" property="ordertime"></result>
            <result column="total" property="total"></result>
        </collection>
    </resultMap>

    <select id="findAll" resultMap="userMap">
        select a.*,b.id oid,b.ordertime,b.total from `user`a LEFT JOIN orders b on a.id=b.uid
    </select>

    <!--多对多-->

    <resultMap id="userRoleMap" type="user">
        <!--user的信息-->
        <id column="uid" property="id"></id>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <!--user内部的roleList信息-->
        <collection property="roleList" ofType="role">
            <id column="rid" property="id"></id>
            <result column="roleName" property="roleName"></result>
            <result column="roleDesc" property="roleDesc"></result>
        </collection>
    </resultMap>

    <select id="findUserAndRoleAll" resultMap="userRoleMap">
        SELECT u.id uid,username,`password`,r.id rid,rolename,roledesc
        FROM USER u,user_role ur,role r WHERE u.id=ur.user_id AND ur.role_id=r.id
    </select>

</mapper>

测试方法

    @Test
    public void test02() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession session = sqlSessionFactory.openSession(true);//开启事务 默认是关闭的
        UserMapper mapper = session.getMapper(UserMapper.class);
        List<User> all = mapper.findAll();
        System.out.println(all);
        session.close();
    }

3多对多查询

实体类

@Data
public class Role {

    private int id;
    private String roleName;
    private String roleDesc;
}

mapper文件上面代码以提供

测试方法

    @Test
    public void test03() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession session = sqlSessionFactory.openSession(true);//开启事务 默认是关闭的
        UserMapper mapper = session.getMapper(UserMapper.class);
        List<User> userAndRoleAll = mapper.findUserAndRoleAll();
        System.out.println(userAndRoleAll);
        session.close();
    }

总结：MyBatis多表配置方式：

一对一配置：使用<resultMap>做配置

一对多配置：使用<resultMap>+<collection>做配置

多对多配置：使用<resultMap>+<collection>做配置

梓源先生

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
05MyBatis多表操作(一对一、一对多、多对多)

数据库表之间的关系分为：一对一、一对多、多对多1一对一查询用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户一对一查询的需求：查询一个订单，与此同时查询出该订单所属的用户表sql语句DROP TABLE IF EXISTS `orders`;CREATE TABLE `orders` ( `id` int(11) NOT NULL AUTO_INCREMENT, `ordertime` timestamp(0) NULL DEFAULT N
复制链接

扫一扫