資料庫設計
我們用學生和老師的例子來說明：
一對多：一個老師有多個學生，轉換為程式設計語言即為一個老師物件下有一個學生物件集合。
多對一：多個學生有同一個老師，學生物件裡有一個屬性關聯著老師。
建立學生、教師表
CREATE TABLE `teacher`(
`id` int(20) not null,
`name` VARCHAR(30) DEFAULT NULL,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `teacher`(`id`,`name`) VALUES(1,'hresh');
CREATE TABLE `student`(
`id` int(20) not null,
`name` VARCHAR(30) DEFAULT null,
`tid` int(20) default null,
PRIMARY KEY (`id`),
KEY `ftid` (`tid`),
CONSTRAINT `ftid` FOREIGN KEY (`tid`) REFERENCES `teacher`(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `student`(`id`,`name`,`tid`) VALUES(1,'張三',1);
INSERT INTO `student`(`id`,`name`,`tid`) VALUES(2,'李四',1);
INSERT INTO `student`(`id`,`name`,`tid`) VALUES(3,'王武',1);
INSERT INTO `student`(`id`,`name`,`tid`) VALUES(4,'張散散',1);
複製程式碼多對一處理
1、Student
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
private int id;
private String name;
private Teacher teacher;
}
複製程式碼Teacher
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
private int id;
private String name;
}
複製程式碼2、編寫 Mapper 介面
StudentMapper.java 定義了兩個查詢方法，基於不同的實現方式。
public interface StudentMapper {
List<Student> getStudent();
List<Student> getStudent2();
}
複製程式碼public interface TeacherMapper {
}
複製程式碼3、編寫 Mapper 介面對應的配置檔案
關於 StudentMapper.xml 的書寫有兩種方式。
按查詢巢狀處理
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.msdn.dao.StudentMapper">
<select id="getStudent" resultMap="studentTeacher">
select * from student
</select>
<resultMap id="studentTeacher" type="student">
<id property="id" column="id" />
<result property="name" column="name" />
<!--複雜的屬性需要單獨處理，物件：association；集合：collection-->
<!--association關聯屬性 property屬性名 javaType屬性型別 column在多的一方的表（即學生表）中的列名-->
<association property="teacher" javaType="Teacher" column="tid" select="getTeacher" />
</resultMap>
<!--
這裡傳遞過來的id，只有一個屬性的時候，下面可以寫任何值
association中column多引數配置：
column="{key=value,key=value}"
其實就是鍵值對的形式，key是傳給下個sql的取值名稱，value是片段一中sql查詢的欄位名。
-->
<select id="getTeacher" resultType="teacher">
select * from teacher where id = #{tid}
</select>
</mapper>
複製程式碼關於 getTeacher 查詢中的 tid，是隨便命名的，如果該查詢 SQL 有多個查詢條件，則必須一致，如下所示：
<resultMap id="StudentTeacher" type="Student">
<!--association關聯屬性 property屬性名 javaType屬性型別 column在多的一方的表中的列名-->
<association property="teacher" column="{id=tid,name=tid}" javaType="Teacher" select="getTeacher"/>
</resultMap>
<!--
這裡傳遞過來的id，只有一個屬性的時候，下面可以寫任何值
association中column多引數配置：
column="{key=value,key=value}"
其實就是鍵值對的形式，key是傳給下個sql的取值名稱，value是片段一中sql查詢的欄位名。
-->
<select id="getTeacher" resultType="teacher">
select * from teacher where id = #{id} and name = #{name}
</select>
複製程式碼測試程式碼
@Test
public void getStudent(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
List<Student> students = studentMapper.getStudent();
for (Student student : students) {
System.out.println(student);
}
sqlSession.close();
}
複製程式碼執行結果為：
Student(id=1, name=張三, teacher=Teacher(id=1, name=hresh))
Student(id=2, name=李四, teacher=Teacher(id=1, name=hresh))
Student(id=3, name=王武, teacher=Teacher(id=1, name=hresh))
Student(id=4, name=張散散, teacher=Teacher(id=1, name=hresh))
複製程式碼按結果巢狀處理
StudentMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.msdn.dao.StudentMapper">
<select id="getStudent2" resultMap="studentMap">
select s.id sid,s.name sname,t.id ttid,t.name tname
from student s,teacher t
where s.tid=t.id;
</select>
<resultMap id="studentMap" type="student">
<id property="id" column="sid" />
<result property="name" column="sname" />
<!--關聯物件property 關聯物件在Student實體類中的屬性-->
<association property="teacher" javaType="Teacher" >
<id property="id" column="ttid" />
<result property="name" column="tname" />
</association>
</resultMap>
</mapper>
複製程式碼測試程式碼
@Test
public void getStudent2(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
List<Student> students = studentMapper.getStudent2();
for (Student student : students) {
System.out.println(student);
}
sqlSession.close();
}
複製程式碼上述兩種方式的執行效果是一致的，選擇哪種方式根據個人理解，覺得哪種簡單就使用哪種。
一對多的處理
1、實體類
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
private int id;
private String name;
private int tid;
}
複製程式碼@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
private int id;
private String name;
private List<Student> students;
}
複製程式碼2、Mapper 介面
public interface TeacherMapper {
Teacher getTeacherById(@Param("id") int id);
Teacher getTeacherById2(@Param("id") int id);
}
複製程式碼3、TeacherMapper.xml
同多對一處理一樣，這裡也有兩種實現方式。
按查詢巢狀處理
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.msdn.dao.TeacherMapper">
<select id="getTeacherById2" resultMap="TeacherStudent2">
select * from mybatis.teacher where id = #{id}
</select>
<resultMap id="TeacherStudent2" type="Teacher">
<id property="id" column="id" />
<result property="name" column="name" />
<collection property="students" column="id" javaType="ArrayList" ofType="Student" select="getStudents" />
</resultMap>
<select id="getStudents" resultType="Student">
select * from mybatis.student where tid = #{tid}
</select>
</mapper>
複製程式碼測試
@Test
public void getTeacher(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = teacherMapper.getTeacherById2(1);
System.out.println(teacher);
sqlSession.close();
}
複製程式碼執行結果為：
Teacher(id=1, name=hresh, students=[Student(id=1, name=張三, tid=1), Student(id=2, name=李四, tid=1), Student(id=3, name=王武, tid=1), Student(id=4, name=張散散, tid=1)])
複製程式碼按結果巢狀處理
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.msdn.dao.TeacherMapper">
<select id="getTeacherById" resultMap="TeacherStudent">
select s.id sid,s.name sname,t.id tid,t.name tname
from mybatis.student s,mybatis.teacher t
where s.tid = t.id and t.id =#{id}
</select>
<resultMap id="TeacherStudent" type="Teacher">
<id property="id" column="tid" />
<result property="name" column="tname" />
<collection property="students" ofType="Student" >
<result property="id" column="sid" />
<result property="name" column="sname" />
<result property="tid" column="tid" />
</collection>
</resultMap>
</mapper>
複製程式碼測試
@Test
public void getTeacher(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = teacherMapper.getTeacherById(1);
System.out.println(teacher);
sqlSession.close();
}
複製程式碼總結
- 聯-association 用於一對一和多對一
- 集合-collection 用於一對多的關係
- JavaType 和 ofType 都是用來指定物件型別的
- JavaType 是用來指定 pojo 中屬性的型別
- ofType 指定的是對映到 list 集合屬性中 pojo 的型別。