4 - 关联查询~一对一

一本书只属于一个人!
table

create table `student`(
`id` int auto_increment primary key,
`name` varchar(30) not null,
`age` int
)auto_increment=10000;

create table `book`
(`id` int auto_increment primary key,
`bookName` varchar(20),
`author` varchar(10),
`studentID` int references `student`(`id`)
)auto_increment=10000;

model

public class Student {
    private int id;
    private String name;
    private int age;

    public Student(){}
    //getter setter略
}

public class Book {

    private int bookID;
    private String bookName;
    private String author;

    private Student student;

    public Book() {
    }
   //getter setter略
}

dao

public interface StudentMapper {
    Book findBook(int bookID);
}

mapper.xml
方式一：嵌套

    <resultMap id="bookMapper" type="book">
        <id column="bookID" property="bookID"/>
        <result column="bookName" property="bookName"/>
        <result column="author" property="author"/>
        <association property="student" javaType="student">
            <id column="id" property="id"/>
            <result column="name" property="name"/>
            <result column="age" property="age"/>
        </association>
    </resultMap>

    <select id="findBook" resultMap="bookMapper">
        select `student`.`id` `id`,
       `student`.`name` `name`,
       `student`.`age` `age`,
       `book`.`id` `bookID`,
       `book`.`bookName` `bookName`,
       `book`.`author` `author`
        from `student`,`book`
        where `student`.`id` = `book`.`studentID`
        and `book`.`id` = #{bookID};
    </select>

方式二:复用resultMapper

    <select id="findBook" resultMap="bookMapper">
        select `student`.`id` `id`,
       `student`.`name` `name`,
       `student`.`age` `age`,
       `book`.`id` `bookID`,
       `book`.`bookName` `bookName`,
       `book`.`author` `author`
        from `student`,`book`
        where `student`.`id` = `book`.`studentID`
        and `book`.`id` = #{bookID};
    </select>

   <resultMap id="studentMapper" type="student">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="age" property="age"/>
    </resultMap>

    <resultMap id="bookMapper" type="book">
        <id column="bookID" property="bookID"/>
        <result column="bookName" property="bookName"/>
        <result column="author" property="author"/>
        <association property="student" resultMap="studentMapper"/>
    </resultMap>

综合3、4关联查询两篇文章，总结一下
1.明确实体之间的关系，优化范式，构建表
2.依据业务，构建model
3.编写关联查询语句
首先编写简单的model开始，建议使用resutlMap来映射，因为当SQL复杂时，嵌套很容易出错。然后依据一对一association或一对多collection来创建复杂实体。最后编写关联查询语句，注意返回字段的名称。
需要注意两点:

resultMap中的column属性必须和数据库返回字段相同
resultMap中的property属性必须和Model的成员属性相同

4 - 关联查询~一对一

推荐阅读更多精彩内容