MyBatis一对多映射简单查询案例（嵌套Mapper映射文件中的sql语句）

一、案例描述

书本类别表和书本信息表，查询书本类别表中的某一记录，连带查询出所有该类别书本的信息。

二、数据库表格

书本类别表（booktypeid,booktypename）

书本信息表（bookid,booktypeid,bookname...）

三、java代码

1.书本类别

BookType.java

package com.yh.entity;

import java.util.List;

public class BookType {
    private int bookTypeId;
    private String bookTypeName;
    private List<Book> books;

    public int getBookTypeId() {
        return bookTypeId;
    }

    public void setBookTypeId(int bookTypeId) {
        this.bookTypeId = bookTypeId;
    }

    public String getBookTypeName() {
        return bookTypeName;
    }

    public void setBookTypeName(String bookTypeName) {
        this.bookTypeName = bookTypeName;
    }

    public List<Book> getBooks() {
        return books;
    }

    public void setBooks(List<Book> books) {
        this.books = books;
    }

}

BookTypeMapper.xml

<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yh.mybatis.mapper.BookTypeMapper">

    <select id="findTypeById" parameterType="int"
        resultMap="bookTypeMap">
        select *from booktype where booktypeid = #{id}
    </select>

    <resultMap type="BookType" id="bookTypeMap">
        <id property="bookTypeId" column="booktypeid"></id>
        <result property="bookTypeName" column="booktypename"></result>
        <collection property="books" column="booktypeid"
            select="com.yh.mybatis.mapper.BookMapper.findBookInfosByType" ofType="Book"></collection>
    </resultMap>
</mapper>

BookTypeMapper.java

package com.yh.mybatis.mapper;

import com.yh.entity.BookType;

public interface BookTypeMapper {
    BookType findTypeById(int id);
}

2.书本信息

Book.java（省略getter()、setter()方法）

package com.yh.entity;

import java.sql.Timestamp;
import java.sql.Date;

import com.yh.dao.DBAddRecord;

public class Book {

    // 自动赋值
    private int bookId;
    private int bookTypeId;

    private int sellerId;
    private String bookName;
    private String press;
    private Date publishDate;
    private short bookSize;
    private short bookVersion;
    private String author;
    private String translator;
    private String isbn;
    private float price;
    private int pageNumber;
    private String introduction;
    private float marketPrice;
    private float memberPrice;
}

BookMapper.xml

<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.yh.mybatis.mapper.BookMapper">
    
    <select id="findBookInfosByType" parameterType="int"
        resultType="Book">
        select bookname,dealamount,price,press,imgurl,bookid
        from book where booktypeid = #{id}
    </select>

</mapper>

BookMapper.java

package com.yh.mybatis.mapper;

import java.util.List;

import com.yh.entity.Book;

public interface BookMapper {
    List<Book> findBookInfosByType(String type);
}

3.测试代码

@ResponseBody
@RequestMapping(value="/byType",produces = "application/json; charset=utf-8")
public String byType(
        @RequestParam(value="type") String type){
    this.init();
    BookTypeMapper btm = sqlSession.getMapper(BookTypeMapper.class);
    BookType books = btm.findTypeById(Integer.valueOf(type));
    this.destroy();
    return JSON.toJSONString(books);
}