快捷導(dǎo)航

解決mybatis一對(duì)多查詢r(jià)esultMap只返回了一條記錄問題

更新時(shí)間：2021年11月27日 12:27:51 作者：黑夜長行

小編接到領(lǐng)導(dǎo)一個(gè)任務(wù)需求，需要用到使用resultMap相關(guān)知識(shí)，在這小編記錄下這個(gè)問題的解決方法，對(duì)mybatis一對(duì)多查詢r(jià)esultMap項(xiàng)目知識(shí)感興趣的朋友一起看看吧

問題描述：因?yàn)轭I(lǐng)導(dǎo)的一個(gè)需求，需要用到使用resultMap，很久沒使用了，結(jié)果就除了點(diǎn)意外。就記錄下這個(gè)問題
準(zhǔn)備兩個(gè)類：author(作者)和book（書），數(shù)據(jù)庫創(chuàng)建對(duì)應(yīng)的author->book一對(duì)多的數(shù)據(jù)

@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執(zhí)行查看結(jié)果：

{
    "code": "200",
    "msg": "成功",
    "data": {
        "id": 1,
        "name": "法外狂徒張三",
        "phone": null,
        "address": null,
        "books": [
            {
                "id": 1,
                "name": "法外狂徒張三",
                "press": "人民出版社",
                "price": 10.00,
                "authorId": 1
            }
        ]
    }
}

發(fā)現(xiàn)問題：本來author對(duì)應(yīng)book有兩條記錄，結(jié)果books里面只返回了一條記錄。
問題原因：2張表的主鍵都叫id，所以導(dǎo)致結(jié)果不能正確展示。
解決方法：1、主鍵使用不用的字段名。2、查詢sql時(shí)使用別名
1、主鍵使用不用的字段名，涉及到更改數(shù)據(jù)庫，只需要更改其中一個(gè)即可。這里演示將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,數(shù)據(jù)庫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時(shí)使用別名。這里演示將查詢book時(shí)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>

到此這篇關(guān)于mybatis一對(duì)多查詢r(jià)esultMap只返回了一條記錄的文章就介紹到這了,更多相關(guān)mybatis一對(duì)多查詢r(jià)esultMap內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: