本文介绍了在MyBatis中如何进行一对多的关系映射，特别是在实体类中不定义list集合的情况下，通过XML映射文件实现多表连接查询。详细展示了创建数据库表、插入数据、Java实体类定义、SQL查询语句以及mapper.xml配置，并提供了测试结果，总结了这种方法的优缺点。
在 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
结果集
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">
<!-- type 一般写全类名，java.util.Map, map 也可以使用 -->
<resultMap id="stuOfBookMap" type="map">
<result column="t_id" property="tid"/>
<result column="t_name" property="tname"/>
<!-- property 可以随便指定，map 集合的 key -->
<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}]
格式化 结果集
[
{
"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 时，参数和结果集根本没法看
扫一扫
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
