本小節(jié)將要介紹一對(duì)一卒密、一對(duì)多的查詢【技術(shù)上】。
你有可能會(huì)問(wèn)棠赛，多對(duì)多怎么查哮奇，這個(gè)問(wèn)題就不該問(wèn)膛腐！開(kāi)玩笑的，哈哈鼎俘。
其實(shí)在設(shè)計(jì)表時(shí)哲身，一般多對(duì)多的關(guān)系就已經(jīng)轉(zhuǎn)化為一對(duì)多了。
一對(duì)多
就用一個(gè)學(xué)生有多本書(shū)籍來(lái)舉例子吧!
image.png
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;
private List<Book> books;
public Student() {//無(wú)參構(gòu)造函數(shù)===>創(chuàng)建對(duì)象
}
//getter和setter略
}
public class Book {
private int bookID;
private String bookName;
private String author;
public Book() {
}
}
//getter和setter略
dao
public interface StudentMapper {
Student findMyBooks(int id);
}
mapple.xml
方式一贸伐，直接嵌套
<select id="findMyBooks" resultMap="studentMapper">
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 `student`.`id` = #{id}
</select>
<resultMap id="studentMapper" type="student">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
<collection property="books" ofType="book">
<id property="bookID" column="bookID"/>
<result property="bookName" column="bookName"/>
<result property="author" column="author"/>
</collection>
</resultMap>
方式二：resultMap復(fù)用
<select id="findMyBooks" resultMap="studentMapper">
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 `student`.`id` = #{id}
</select>
<resultMap id="bookMapper" type="book">
<id property="bookID" column="bookID"/>
<result property="bookName" column="bookName"/>
<result property="author" column="author"/>
</resultMap>
<resultMap id="studentMapper" type="student">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="age" column="age"/>
<collection property="books" resultMap="bookMapper"/>
</resultMap>
