mybatis一對多查詢resultMap只返回了一條記錄

黑夜長行發表於2021-11-27
MyBatis

問題描述：因為領導的一個需求，需要用到使用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>

相關文章