解決mybatis一對多查詢resultMap隻返回瞭一條記錄問題
問題描述:因為領導的一個需求,需要用到使用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>
到此這篇關於mybatis一對多查詢resultMap隻返回瞭一條記錄的文章就介紹到這瞭,更多相關mybatis一對多查詢resultMap內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!
推薦閱讀:
- 關於mybatis一對一查詢一對多查詢遇到的問題
- Mybatis中resultMap的Colum和property屬性詳解
- Mybatis之@ResultMap,@Results,@Result註解的使用
- MyBatis實現兩種查詢樹形數據的方法詳解(嵌套結果集和遞歸查詢)
- mybatis如何使用註解實現一對多關聯查詢