一本书只属于一个人!
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的成员属性相同
