解决mybatis一对多查询resultMap只返回了一条记录问题
作者:黑夜长行 时间:2022-07-08 08:36:31
问题描述:因为领导的一个需求,需要用到使用resultMap,很久没使用了,结果就除了点意外。就记录下这个问题
准备两个类:author(作者)和book(书),数据库创建对应的author->book一对多的数据
@Data
public class Author {
private Integer id;
private String name;
private String phone;
private String address;
private List<Book> books;
}
@Data
public class Book {
private Integer id;
private String name;
private String press;
private BigDecimal price;
private Integer authorId;
}
开始的Mapper.xml文件
<resultMap id="bookMap" type="com.example.demo.dto.Author">
<id column="id" property="id"></id>
<result column="name" property="name"></result>
<result column="address" property="address"></result>
<result column="phone" property="phone"></result>
<collection property="books" ofType="com.example.demo.dto.Book">
<id column="id" property="id"></id>
<result column="name" property="name"></result>
<result column="press" property="press"></result>
<result column="price" property="price"></result>
<result column="author_id" property="authorId"></result>
</collection>
</resultMap>
<select id="queryAuthorInfo" parameterType="java.lang.String" resultMap="bookMap">
select t1.*,t2.* from
author t1 inner join book t2 on t1.id=t2.author_id
where t1.id=#{id}
</select>
使用postman执行查看结果:
{
"code": "200",
"msg": "成功",
"data": {
"id": 1,
"name": "法外狂徒张三",
"phone": null,
"address": null,
"books": [
{
"id": 1,
"name": "法外狂徒张三",
"press": "人民出版社",
"price": 10.00,
"authorId": 1
}
]
}
}
发现问题:本来author对应book有两条记录,结果books里面只返回了一条记录。
问题原因:2张表的主键都叫id,所以导致结果不能正确展示。
解决方法:1、主键使用不用的字段名。2、查询sql时使用别名
1、主键使用不用的字段名,涉及到更改数据库,只需要更改其中一个即可 。这里演示将book的id更改为book_id
<resultMap id="bookMap" type="com.example.demo.dto.Author">
<id column="id" property="id"></id>
<result column="name" property="name"></result>
<result column="address" property="address"></result>
<result column="phone" property="phone"></result>
<collection property="books" ofType="com.example.demo.dto.Book">
<!---更改book类的id为bookId,数据库book的id更改为book_id-->
<id column="book_id" property="bookId"></id>
<result column="name" property="name"></result>
<result column="press" property="press"></result>
<result column="price" property="price"></result>
<result column="author_id" property="authorId"></result>
</collection>
</resultMap>
<select id="queryAuthorInfo" parameterType="java.lang.String" resultMap="bookMap">
select t1.*,t2.* from
author t1 inner join book t2 on t1.id=t2.author_id
where t1.id=#{id}
</select>
2、查询sql时使用别名。这里演示将查询book时id 更改别名为 bookId
<resultMap id="bookMap" type="com.example.demo.dto.Author">
<id column="id" property="id"></id>
<result column="name" property="name"></result>
<result column="address" property="address"></result>
<result column="phone" property="phone"></result>
<collection property="books" ofType="com.example.demo.dto.Book">
<!---这里将column值id更改为别名一致bookId-->
<id column="bookId" property="id"></id>
<result column="name" property="name"></result>
<result column="press" property="press"></result>
<result column="price" property="price"></result>
<result column="author_id" property="authorId"></result>
</collection>
</resultMap>
<select id="queryAuthorInfo" parameterType="java.lang.String" resultMap="bookMap">
<!---这里新增了t2.id as bookId-->
select t1.*,t2.id as bookId, t2.* from
author t1 inner join book t2 on t1.id=t2.author_id
where t1.id=#{id}
</select>
来源:https://www.cnblogs.com/yl97/p/15611487.html
标签:mybatis,一对多查询,resultMap
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
详解java 客户端链接不上redis解决方案
2023-11-12 10:12:15
![](https://img.aspxhome.com/file/2023/4/59204_0s.png)
Springboot 在普通类型注入Service或mapper
2023-11-29 15:26:21
java注解的全面分析
2023-11-25 11:28:50
Springcloud seata nacos环境搭建过程图解
2022-11-15 00:34:14
![](https://img.aspxhome.com/file/2023/1/61341_0s.png)
SpringMvc+Mybatis+Pagehelper分页详解
2021-08-13 14:15:11
Java 手写LRU缓存淘汰算法
2023-03-03 00:11:19
![](https://img.aspxhome.com/file/2023/0/64100_0s.jpg)
Java中SimpleDateFormat日期格式转换详解及代码示例
2023-09-04 22:13:43
![](https://img.aspxhome.com/file/2023/1/58461_0s.png)
java代码实现MD5加密及验证过程详解
2023-09-26 02:50:45
JPA Specification常用查询+排序实例
2023-11-23 04:56:32
![](https://img.aspxhome.com/file/2023/3/59423_0s.png)
深入理解Java基础之try-with-resource语法糖
2022-12-03 08:17:43
java 汉诺塔Hanoi递归、非递归(仿系统递归)和非递归规律 实现代码
2023-09-13 11:29:31
Java 字符串反转实现代码
2023-01-09 10:04:52
Java ArrayList源码深入分析
2023-06-16 16:30:26
![](https://img.aspxhome.com/file/2023/8/60918_0s.png)
深入理解C++中public、protected及private用法
2023-07-02 11:30:17
详解从零开始---用C#制作扫雷游戏
2022-06-14 13:33:12
![](https://img.aspxhome.com/file/2023/9/70399_0s.png)
java实现切割wav音频文件的方法详解【附外部jar包下载】
2021-07-08 22:29:56
Java调用wsdl接口的两种方法(axis和wsimport)
2023-06-23 14:41:22
![](https://img.aspxhome.com/file/2023/8/65338_0s.png)
android 获取上一个activity返回值的方法
2023-06-15 13:22:22
Java源码解析之object类
2023-11-05 00:46:28
MyBatis-Plus 集成动态多数据源的实现示例
2023-06-26 17:56:35