Mybatis中表关系查询结果集映射

小刘同学要加油呀

已于 2023-07-17 20:24:48 修改

阅读量567

点赞数 9

分类专栏： mybatis 文章标签： java 数据库 mybatis

于 2023-07-17 17:44:31 首次发布

本文链接：https://blog.csdn.net/qq_37400096/article/details/131755243

版权

mybatis 专栏收录该内容

6 篇文章 1 订阅

订阅专栏

文章目录

前言
1. 实体类设计
2.插入数据
3.表关联查询

前言

resultMap 元素是 MyBatis 中最重要最强大的元素。它可以让你从 90% 的 JDBC ResultSets 数据提取代码中解放出来，并在一些情形下允许你进行一些 JDBC 不支持的操作。实际上，在为一些比如连接的复杂语句编写映射代码的时候，一份 resultMap 能够代替实现同等功能的数千行代码。ResultMap 的设计思想是，对简单的语句做到零配置，对于复杂一点的语句，只需要描述语句之间的关系就行了。

ER实体关系：

一对一
一对多
多对多

1. 实体类设计

1.1 用户表

建表sql:

CREATE TABLE `User` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

java实体类:

public class User implements Serializable {
    private static final long serialVersionUID = 741077730204987800L;
    
    private Long id;
    
    private String name;
    
    private Integer age;
}

1.2 地址表

建表的sql


CREATE TABLE `Address` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL,
  `province` varchar(100) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `county` varchar(100) DEFAULT NULL,
  `detail` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='地址表';

java代码：

public class Address implements Serializable {
    private static final long serialVersionUID = 960792204085182238L;
    
    private Long id;
    
    private Long userId;
    
    private String province;
    
    private String city;
    
    private String county;
    
    private String detail;
    }

1.3 博客表

建表的sql

CREATE TABLE `Blogs` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL,
  `title` varchar(100) DEFAULT NULL,
  `content` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

java实体类：

public class Blogs implements Serializable {
    private static final long serialVersionUID = -82344806309770256L;
    
    private Long id;
    
    private Long userId;
    
    private String title;
    
    private String content;
 }

1.4 粉丝互关表

建表的sql

-- test.Fans definition

CREATE TABLE `Fans` (
  `id` varchar(100) DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `fan_id` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='粉丝互关表';

实体类

public class Fans implements Serializable {
    private static final long serialVersionUID = -76540062746539253L;
    
    private String id;
    
    private Long userId;
    
    private String fanId;
    }

2.插入数据

INSERT INTO `User`(id, name, age)VALUES(11, 'elite', 20);
INSERT INTO `User`(id, name, age)VALUES(12, 'bob', 19);
INSERT INTO `User`(id, name, age)VALUES(13, 'johon', 22);
INSERT INTO `User`(id, name, age)VALUES(14, 'smith', 21);
INSERT INTO `User`(id, name, age)VALUES(15, 'jackon', 20);
INSERT INTO Address(id, user_id, province, city, county, detail)VALUES(1, 11, 'bj', 'xx', 'xx', '北京西路');
INSERT INTO Address(id, user_id, province, city, county, detail)VALUES(2, 12, 'sh', 'xx', 'xx', '上海路');
INSERT INTO Address(id, user_id, province, city, county, detail)VALUES(3, 13, 'sz', 'xx', 'xx', '深圳南山路');
INSERT INTO Address(id, user_id, province, city, county, detail)VALUES(4, 14, 'gz', 'xx', 'xx', '广州路');
INSERT INTO Address(id, user_id, province, city, county, detail)VALUES(5, 15, 'hz', 'xx', 'xx', '杭州');
INSERT INTO Blogs(id, user_id, title, content)VALUES(1, 11, 'java', 'java数据类型...');
INSERT INTO Blogs(id, user_id, title, content)VALUES(2, 11, 'python', '大数据开发..');
INSERT INTO Blogs(id, user_id, title, content)VALUES(3, 11, 'c#', '桌面应用开发...');
INSERT INTO Fans(id, user_id, fan_id)VALUES(1, 11, 12);
INSERT INTO Fans(id, user_id, fan_id)VALUES(2, 11, 13);
INSERT INTO Fans(id, user_id, fan_id)VALUES(3, 12, 11);
INSERT INTO Fans(id, user_id, fan_id)VALUES(4, 12, 14);
INSERT INTO Fans(id, user_id, fan_id)VALUES(5, 12, 15);

3.表关联查询

3.1 一对一关系

表user和address是一对一的关系，可以直接定义一个dto来接收数据。
定义数据传输对象：

public class UADTO {

    private Long id;

    private String name;

    private Integer age;

    private String province;

    private String city;

    private String county;

    private String detail;
    }

定义resultmap结果集以及查询

<!--定义用户地址结果集-->
    <resultMap type="com.elite.mybatis.dto.UADTO" id="UAMap">
        <result property="id" column="id" jdbcType="INTEGER"/>
        <result property="name" column="name" jdbcType="VARCHAR"/>
        <result property="age" column="age" jdbcType="INTEGER"/>
        <result property="userId" column="user_id" jdbcType="INTEGER"/>
        <result property="province" column="province" jdbcType="VARCHAR"/>
        <result property="city" column="city" jdbcType="VARCHAR"/>
        <result property="county" column="county" jdbcType="VARCHAR"/>
        <result property="detail" column="detail" jdbcType="VARCHAR"/>
    </resultMap>

   <!--用户与地址的查询-->
    <sql id="uasql">
        select
            u.id,
            u.name,
            u.age,
            a.province,
            a.city,
            a.county,
            a.detail
        from
            User u,
            Address a
        where
            u.id = a.user_id
    </sql>
   <select id="getUAInfo" resultMap="UAMap">
       <include refid="uasql"></include>
   </select>

测试：

 /**
     * 测试一对一关系的查询
     */
    @Test
    public void testOne2One(){
        CollectionMapper c = sqlSession.getMapper(CollectionMapper.class);
        List<UADTO> uadtoList = c.getUAInfo();
        uadtoList.forEach(uadto -> {
            System.out.println(uadto.toString());
        });
    }

查询结果

3.2 一对多关系

用户和发表的博客ER关系属于一对多关系。
定义传输的DTO对象

/**
 * 用户表和博客发布的记录
 */
public class UBDTO {

    private Long id;

    private String name;

    private Integer age;

    private List<Blogs> blogs;
    }

定义结果集

 <!--定义用户与博客-->
    <resultMap id="UBMap" type="com.elite.mybatis.dto.UBDTO">
        <id property="id" column="id" jdbcType="INTEGER"/>
        <result property="name" column="name" jdbcType="VARCHAR"/>
        <result property="age" column="age" jdbcType="INTEGER"/>
        <collection property="blogs" ofType="com.elite.mybatis.entity.Blogs">
            <id property="id" column="id" jdbcType="INTEGER"/>
            <id property="userId" column="user_id" jdbcType="INTEGER"/>
            <result property="title" column="title" jdbcType="VARCHAR"/>
            <result property="content" column="content" jdbcType="VARCHAR"/>
        </collection>
    </resultMap>
    <!--用户与地址的查询-->
    <sql id="ubsql">
        select
            u.id,
            u.name,
            u.age,
            b.id,
            b.user_id,
            b.title,
            b.content
        from
            User u inner join Blogs b on (u.id = b.user_id)
    </sql>
    <select id="getUBInfo" resultMap="UBMap">
        <include refid="ubsql"></include>
    </select>

测试

/**
     * 测试一对多关系的查询
     */
    @Test
    public void testOne2Multi(){
        CollectionMapper c = sqlSession.getMapper(CollectionMapper.class);
        List<UBDTO> ubInfo = c.getUBInfo();
        ubInfo.forEach(ubdto -> {
            System.out.println(ubdto.toString());
        });
    }

一对多查询结果

3.3 多对多关系

用户表和粉丝表之间关系
定义dto

/**
 * 粉丝互关表
 */
public class UFDTO {
    private Long id;

    private String name;

    private Integer age;

    private List<User> fans;
    }

定义查询的结果集

 <!--定义用户与粉丝互关表-->
    <resultMap id="UFMap" type="com.elite.mybatis.dto.UFDTO">
        <id property="id" column="id" jdbcType="INTEGER"/>
        <result property="name" column="name" jdbcType="VARCHAR"/>
        <result property="age" column="age" jdbcType="INTEGER"/>
        <collection property="fans" ofType="com.elite.mybatis.entity.User">
            <id property="id" column="id" jdbcType="INTEGER"/>
            <result property="name" column="name" jdbcType="VARCHAR"/>
            <result property="age" column="age" jdbcType="INTEGER"/>
        </collection>
    </resultMap>
    <!--用户与粉丝查询-->
    <sql id="ufsql">
        select
            u.id,
            u.name,
            u.age,
            f1.id,
            f1.name,
            f1.age
        from
            User u,
            Fans f,
            User f1
        where
            u.id = f.user_id
          and f.fan_id  = f1.id
    </sql>
    <select id="getUFInfo" resultMap="UFMap">
        <include refid="ufsql"></include>
    </select>

测试

 /**
     * 测试多对多关系的查询
     */
    @Test
    public void testMulti2Multi(){
        CollectionMapper c = sqlSession.getMapper(CollectionMapper.class);
        List<UFDTO> ufdtoList = c.getUFInfo();
        ufdtoList.forEach(ufdto -> {
            System.out.println(ufdto.toString());
        });
    }