mybatis 多表连接查询，万能map

女爷i 2023-10-04 15:27 56阅读 0赞

#### 文章目录 ####

*   *   *   *   *  新建表
                 *   *  表说明
                     *  添加数据
                 *  Java 代码
                 *   *  实体类
                     *  查询语句
                     *  mapper.xml 文件
                     *  mapper 文件
                     *  测试
                 *  总结

在 mybatis 中多表连接查询，会遇到多对一，一对多的情况，多对一比较简单就不说了，一对多时你需要在实体类中定义 list 集合，下面讲讲不定义 list 的写法，直接找答案的可以跳到 mapper.xml 看 resultMap 就行

###### 新建表 ######

###### 表说明 ######

teacher  1----------->n  student   1---------------->n  book

一个老师对应多个学生，一个学生拥有多本书

###### 添加数据 ######

测试数据如下

DROP TABLE IF EXISTS `book`;
    
    CREATE TABLE `book` (
      `id` int(10) NOT NULL AUTO_INCREMENT,
      `book_name` varchar(20) DEFAULT NULL,
      `sid` int(10) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
    
    insert  into `book`(`id`,`book_name`,`sid`) values (1,'java',1),(2,'c',2),(3,'c++',4),(4,'linux',5),(5,'python',1),(6,'javaweb',2),(7,'js',1),(8,'vue',4);
    
    
    DROP TABLE IF EXISTS `student`;
    
    CREATE TABLE `student` (
      `id` int(10) NOT NULL,
      `name` varchar(30) DEFAULT NULL,
      `tid` int(10) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `fktid` (`tid`),
      CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    insert  into `student`(`id`,`name`,`tid`) values (1,'小明',1),(2,'小红',1),(3,'小张',1),(4,'小李',2),(5,'小王',2),(6,'小黑',2),(7,'小赵',1),(8,'小孙',1),(9,'小钱',1),(10,'小李',1),(11,'老王',1);
    
    
    DROP TABLE IF EXISTS `teacher`;
    
    CREATE TABLE `teacher` (
      `id` int(10) NOT NULL,
      `name` varchar(30) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    /*Data for the table `teacher` */
    
    insert  into `teacher`(`id`,`name`) values (1,'秦老师'),(2,'郭老师');

###### Java 代码 ######

###### 实体类 ######

下面讲讲不在实体类定义集合的方法，实体类和表字段一一对应，没有多余字段  
实体类 pojo

Teacher.java

@Data
    @AllArgsConstructor
    @NoArgsConstructor
    public class Teacher {
        
        private Integer id;
        private String name;
    }

Student.java

@Data
    @AllArgsConstructor
    @NoArgsConstructor
    public class Student {
        
        private Integer id;
        private String name;
        private String tid;
    }

Book.java

@Data
    @AllArgsConstructor
    @NoArgsConstructor
    @TableName("book")
    public class Book {
        
        @TableField(value = "id")
        private Integer id;
        @TableField(value = "book_name")
        private String bookName;
        @TableField(value = "sid")
        private Integer sid;
    }

###### 查询语句 ######

执行的 sql

SELECT t.id t_id, t.name t_name, s.id s_id, s.name s_name, s.tid s_tid, b.id b_id, b.book_name
            FROM mybatis.teacher t
                     LEFT JOIN mybatis.student s ON t.id = s.tid
                     LEFT JOIN mybatis.book b ON s.id = b.sid

结果集  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxNTM4MDk3_size_16_color_FFFFFF_t_70]

###### mapper.xml 文件 ######

StudentMapper.xml

<?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.ye.test.mapper.StudentMapper">
    
    	
        <resultMap id="stuOfBookMap" type="map">
            <result column="t_id" property="tid"/>
            <result column="t_name" property="tname"/>
            
            <collection property="student" javaType="java.util.List" resultMap="studentMap"/>
        </resultMap>
        
        <resultMap id="studentMap" type="java.util.Map">
            <result column="s_id" property="sid"/>
            <result column="s_name" property="sname"/>
            <result column="s_tid" property="stid"/>
            <collection property="book" javaType="java.util.List" resultMap="bookMap"/>
        </resultMap>
        
        <resultMap id="bookMap" type="book">
            <result column="b_id" property="id"/>
            <result column="book_name" property="bookName"/>
        </resultMap>
        
        <select id="selectStuOfBook" resultMap="stuOfBookMap">
            SELECT t.id t_id, t.name t_name, s.id s_id, s.name s_name, s.tid s_tid, b.id b_id, b.book_name
            FROM mybatis.teacher t
                     LEFT JOIN mybatis.student s ON t.id = s.tid
                     LEFT JOIN mybatis.book b ON s.id = b.sid
        </select>
    </mapper>

###### mapper 文件 ######

StudentMapper.java

@Mapper
    public interface StudentMapper extends BaseMapper<Student> {
        
        List<Map<String,Object>> selectStuOfBook();
    }

###### 测试 ######

springboot 测试

@Autowired
        StudentMapper studentMapper;
        @Test
        public void selectStuOfBook() {
        
            List<Map<String, Object>> list = studentMapper.selectStuOfBook();
            System.out.println(list);
            System.out.println(JSON.toJSONString(list));
        }

结果

格式化 结果集

[
        {
        
            "student": [
                {
        
                    "sname": "小明", 
                    "book": [
                        {
        
                            "bookName": "java", 
                            "id": 1
                        }, 
                        {
        
                            "bookName": "python", 
                            "id": 5
                        }, 
                        {
        
                            "bookName": "js", 
                            "id": 7
                        }
                    ], 
                    "stid": 1, 
                    "sid": 1
                }, 
                {
        
                    "sname": "小红", 
                    "book": [
                        {
        
                            "bookName": "c", 
                            "id": 2
                        }, 
                        {
        
                            "bookName": "javaweb", 
                            "id": 6
                        }
                    ], 
                    "stid": 1, 
                    "sid": 2
                }, 
                {
        
                    "sname": "小张", 
                    "book": [ ], 
                    "stid": 1, 
                    "sid": 3
                }, 
                {
        
                    "sname": "小赵", 
                    "book": [ ], 
                    "stid": 1, 
                    "sid": 7
                }, 
                {
        
                    "sname": "小孙", 
                    "book": [ ], 
                    "stid": 1, 
                    "sid": 8
                }, 
                {
        
                    "sname": "小钱", 
                    "book": [ ], 
                    "stid": 1, 
                    "sid": 9
                }, 
                {
        
                    "sname": "小李", 
                    "book": [ ], 
                    "stid": 1, 
                    "sid": 10
                }, 
                {
        
                    "sname": "老王", 
                    "book": [ ], 
                    "stid": 1, 
                    "sid": 11
                }
            ], 
            "tname": "秦老师", 
            "tid": 1
        }, 
        {
        
            "student": [
                {
        
                    "sname": "小李", 
                    "book": [
                        {
        
                            "bookName": "c++", 
                            "id": 3
                        }, 
                        {
        
                            "bookName": "vue", 
                            "id": 8
                        }
                    ], 
                    "stid": 2, 
                    "sid": 4
                }, 
                {
        
                    "sname": "小王", 
                    "book": [
                        {
        
                            "bookName": "linux", 
                            "id": 4
                        }
                    ], 
                    "stid": 2, 
                    "sid": 5
                }, 
                {
        
                    "sname": "小黑", 
                    "book": [ ], 
                    "stid": 2, 
                    "sid": 6
                }
            ], 
            "tname": "郭老师", 
            "tid": 2
        }
    ]

###### 总结 ######

万能 map

*  优点
    
     *  可以将实体类和数据库表一一对应，只对该表增删改查时不会产生多余字段（有时看到后端返回好多空字段很烦）
 *  缺点
    
     *  代码阅读比较费劲，在实体类定义 list ，在接收数据时就是对应的实体类， 而使用 map 接收，读代码时不能看到具体字段

更甚者将接收前端参数和返回结果集分别定义一个实体类（比如五个接口参数和结果集不同，将这些定义在一个 params 和 result 实体类中，那你还写什么实体类，有多少表字段都定义一个实体类算了），虽然很多接口用的很爽，但会产生很多无用的字段，自我感觉这种不好，而且使用 swagger 时，参数和结果集根本没法看

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