一对一查询
MyBatis 实现 “一对一查询” 的两种方法：扩展类及resultMap，扩展类就是写一个扩展类来映射查询结果，不方便直接舍弃。
先准备sql语句。
CREATE DATABASE IF NOT EXISTS `MyBatisDemo`;
USE `MyBatisDemo`;
DROP TABLE IF EXISTS `studentCard`;
CREATE TABLE `studentCard`
(
`cardId` int(10) NOT NULL,
`cardInfo` varchar(50) NOT NULL,
PRIMARY KEY (`cardId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `studentCard` values (1000, '张三是清华大学计算机学院的');
insert into `studentCard` values (1001, '李四是北京大学管理学院的');
insert into `studentCard` values (1002, '颜群是复旦大学经管学院的');
insert into `studentCard` values (1003, '王五是西南交通大学数学学院的');
insert into `studentCard` values (1004, '赵六是电子科技大学英语学院的');
insert into `studentCard` values (1005, '孙琪是中国科学技术大学计算机学院的');
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`
(
`stuNo` int(10) NOT NULL,
`stuName` varchar(50) NOT NULL,
`stuAge` tinyint,
`graName` varchar(50) NOT NULL,
`cardId` int(10) NOT NULL,
PRIMARY KEY (`stuNo`),
KEY `student_cardId` (`cardId`),
CONSTRAINT `student_cardId` FOREIGN KEY (cardId) REFERENCES studentCard(cardId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `student` values (31, '张三', 23, '就业班', 1000);
insert into `student` values (32, '李四', 24, '初级', 1001);
insert into `student` values (33, '颜群', 28, '就业班', 1002);
insert into `student` values (34, '王五', 25, '初级', 1003);
insert into `student` values (36, '赵六', 26, '中级', 1004);
insert into `student` values (37, '孙琪', 27, '中级', 1005);
使用 resultMap 实现一对一查询 要实现学生和学生证之间的一对一查询，将学生和学生证设计成两个类， 然后在学生类中添加一个学生证类型的成员变量（当然，也可以反过来）。
实体类
public class StudentCard{
//学生证号
private int cardId;
//学生证的相关信息
private String cardInfo;
//getter、setter
public int getCardId() {
return this.cardId;
}
public void setCardId(int cardId) {
this.cardId = cardId;
}
public String getCardInfo() {
return this.cardInfo;
}
public void setCardInfo(String cardInfo) {
this.cardInfo = cardInfo;
}
}
public class Student{
private int stuNo;
private String stuName;
private int stuAge;
//学生证
private StudentCard card ;
...
//setter、getter
public StudentCard getCard() {
return this.card;
}
public void setCard(StudentCard card) {
this.card = card;
}
}
SQL 映射文件StudentMapper.xml
select标签中 resultMap属性是自己写的结果集映射，属性值对应resultMap标签中的id属性值，必须一致。 resultMap的一个作用，就是用来解决实体类属性名和表字段名不一致的问题。resultMap 的另一个作用，就是用来处理关联查询。 association标签中 property="card"是学生类中的一个属性名，javaType="StudentCard"是实体类
<!-- 用resultMap，实现学生表和学生证表的一对一查询 -->
<select id="queryStudentAndCardByStuNoWithResultMap"
parameterType="int" resultMap="student_card_map">
select s.*,c.* from student s
inner join studentCard c
on s.cardId = c.cardId
where s.stuNo = #{stuNo}
</select>
<resultMap type="Student" id="student_card_map">
<!-- property：实体类的属性名称 column：字段名称 -->
<id property="stuNo" column="stuNo"/>
<result property="stuName" column="stuName"/>
<result property="stuAge" column="stuAge"/>
<result property="graName" column="graName"/>
<!-- association: 就是用来一对一查询，关联级联属性-->
<association property="card" javaType="StudentCard">
<id property="cardId" column="cardId"/>
<result property="cardInfo" column="cardInfo"/>
</association>
</resultMap>
代理接口StudentMapper.java
public interface StudentMapper{
//方法名和xml中的id属性值必须一致
Student queryStudentAndCardByStuNoWithResultMap(int stuNo);
}
测试
@Test
public void queryStudentAndCardByStuNoWithResultMap()
throws IOException{
…
SqlSession session = sessionFactory.openSession();
StudentMapper studentMapper = session.getMapper(StudentMapper.class);
Student student = studentMapper.queryStudentAndCardByStuNoWithResultMap(32);
…
}
以上，就是使用resultMap中的association子标签实现一对一关联查询的具体步骤。
一对多查询
sql
CREATE DATABASE IF NOT EXISTS `MyBatisDemo`;
USE `MyBatisDemo`;
DROP TABLE IF EXISTS `studentClass`;
CREATE TABLE `studentClass`
(
`classId` int(10) NOT NULL,
`className` varchar(50) NOT NULL,
PRIMARY KEY (`classId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `studentClass` values (1, 'Java 班');
insert into `studentClass` values (2, 'IOS 班');
insert into `studentClass` values (3, 'HTML5 班');
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`
(
`stuNo` int(10) NOT NULL,
`stuName` varchar(50) NOT NULL,
`stuAge` tinyint,
`graName` varchar(50) NOT NULL,
`classId` int(10) NOT NULL,
PRIMARY KEY (`stuNo`),
KEY `student_classId` (`classId`),
CONSTRAINT `student_classId` FOREIGN KEY (classId) REFERENCES studentClass(classId)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `student` values (31, '张三', 23, '就业班', 1);
insert into `student` values (32, '李四', 24, '初级', 2);
insert into `student` values (33, '颜群', 28, '就业班', 3);
insert into `student` values (34, '王五', 25, '初级', 1);
insert into `student` values (36, '赵六', 26, '中级', 2);
insert into `student` values (37, '孙琪', 27, '中级', 3);
实体类
public class StudentClass{
// 班级id
private int classId ;
// 班级名称
private String className;
//setter、getter
public int getClassId() {
return this.classId;
}
public void setClassId(int classId) {
this.classId = classId;
}
public String getClassName() {
return this.className;
}
public void setClassName(String className) {
this.className = className;
}
}
public class StudentClass{
private int classId ;
private String className;
// 班级中的学生信息
private List<Student> students ;
//setter、getter
public int getClassId() {
return this.classId;
}
public void setClassId(int classId) {
this.classId = classId;
}
public String getClassName() {
return this.className;
}
public void setClassName(String className) {
this.className = className;
}
public List<Student> getStudents() {
return this.students;
}
public void setStudents(List<Student> students) {
this.students = students;
}
}
SQL 映射文件 StudentMapper.xml
通过select执行一对多的查询 SQL，并将查询结果通过resultMap映射到 StudentClass 类中的各个属性中：普通类型通过id、result映射，List 类型的属性 students 通过collection映射，并通过 ofType 指定 List 中元素的类型。
<select id="queryClassAndStudentsByClassId" parameterType="int" resultMap="classAndStudentMap">
select s.*,sc.* from student s
inner join studentClass sc
on s.classid=sc.classid
where sc.classid = #{classId}
</select>
<resultMap type="studentClass" id="classAndStudentMap">
<id property="classId" column="classId" />
<result property="className" column="className" />
<collection property="students" ofType="student">
<id property="stuNo" column="stuNo" />
<result property="stuName" column="stuName" />
<result property="stuAge" column="stuAge" />
<result property="graName" column="graName" />
</collection>
</resultMap>
动态代理接口 StudentMapper.java
<select id="queryClassAndStudentsByClassId" parameterType="int" resultMap="classAndStudentMap">
select s.*,sc.* from student s
inner join studentClass sc
on s.classid=sc.classid
where sc.classid = #{classId}
</select>
<resultMap type="studentClass" id="classAndStudentMap">
<id property="classId" column="classId" />
<result property="className" column="className" />
<collection property="students" ofType="student">
<id property="stuNo" column="stuNo" />
<result property="stuName" column="stuName" />
<result property="stuAge" column="stuAge" />
<result property="graName" column="graName" />
</collection>
</resultMap>
测试
@Test
public void queryClassAndStudentsByClassId() throws IOException{
…
IStudentMapper studentMapper = session.getMapper(IStudentMapper.class);
StudentClass stuclass = studentMapper.queryClassAndStudentsByClassId(1);
…
}
多对多查询
多对多查询，它的本质就是两个一对多的组合，同样它所使用的标签元素也是 collection
sql
CREATE DATABASE IF NOT EXISTS `MyBatisDemo`;
USE `MyBatisDemo`;
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`
(
`courseNo` int(10) NOT NULL,
`courseName` varchar(50) NOT NULL,
PRIMARY KEY (`courseNo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `course` values (1001, 'Java基础');
insert into `course` values (1002, 'python');
insert into `course` values (1003, 'Web前端');
insert into `course` values (1004, 'C++');
insert into `course` values (1005, 'C语言');
insert into `course` values (1006, 'Oracle');
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`
(
`stuNo` int(10) NOT NULL,
`stuName` varchar(50) NOT NULL,
`stuAge` tinyint,
`graName` varchar(50) NOT NULL,
PRIMARY KEY (`stuNo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `student` values (31, '张三', 23, '就业班');
insert into `student` values (32, '李四', 24, '初级');
insert into `student` values (33, '颜群', 28, '就业班');
insert into `student` values (34, '王五', 25, '初级');
insert into `student` values (35, '赵六', 26, '中级');
insert into `student` values (36, '孙琪', 27, '中级');
DROP TABLE IF EXISTS `studentCourse`;
CREATE TABLE `studentCourse`
(
`stuNo` int(10) NOT NULL,
`courseNo` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `studentCourse` values (31,1001);
insert into `studentCourse` values (31,1002);
insert into `studentCourse` values (32,1001);
insert into `studentCourse` values (32,1003);
insert into `studentCourse` values (33,1004);
insert into `studentCourse` values (33,1003);
insert into `studentCourse` values (34,1005);
insert into `studentCourse` values (34,1004);
insert into `studentCourse` values (35,1006);
insert into `studentCourse` values (35,1002);
insert into `studentCourse` values (36,1006);
insert into `studentCourse` values (36,1005);
实体类
public class Student{
...
private int stuNo;
private String stuName;
//在学生类Student中保留课程信息
private List<Course> courses;
public List<Course> getCourses() {
return this.courses;
}
public void setCourses(List<Course> courses) {
this.courses = courses;
}
...
public class Course{
private int courseNo;
private String courseName;
//在课程类Course类中保留学生信息
private List<Student> students;
public int getCourseNo() {
return this.courseNo;
}
public void setCourseNo(int courseNo) {
this.courseNo = courseNo;
}
public String getCourseName() {
return this.courseName;
}
public void setCourseName(String courseName) {
this.courseName = courseName;
}
public List<Student> getStudents() {
return this.students;
}
public void setStudents(List<Student> students) {
this.students = students;
}
@Override
public String toString() {
return "课程编号:"+this.courseNo+"\t课程名称:"+this.courseName;
}
...
}
}
SQL 映射文件 StudentMapper.xml
<!-- 多对多的查询 -->
<select id="queryStudentsAndCourses" resultMap="studentsAndCoursesMap">
select s.*,sc.*,c.* from student s left join studentCourse sc on s.stuNo=sc.stuNo
left join course c on c.courseNo=sc.courseNo
</select>
<resultMap type="student" id="studentsAndCoursesMap">
<id property="stuNo" column="stuNo" />
<result property="stuName" column="stuName" />
<result property="stuAge" column="stuAge" />
<result property="graName" column="graName" />
<collection property="courses" ofType="Course">
<id property="courseNo" column="courseNo" />
<result property="courseName" column="courseName" />
</collection>
</resultMap>
动态代理接口 IStudentMapper.java
public interface IStudentMapper{
…
List<Student> queryStudentsAndCourses();
}
测试
@Test
public void testQueryStudentByStuNoWithConverter() throws IOException{
String resource = "mybatis-config.xml";
Reader reader = Resources.getResourceAsReader(resource);
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);
SqlSession session = sessionFactory.openSession();
IStudentMapper studentMapper = session.getMapper(IStudentMapper.class);
List<Student> students = studentMapper.queryStudentsAndCourses();
for (Student student : students) {
System.out.println(student+"\n"+student.getCourses());
}
session.close();
}
延迟加载
现在需要通过延迟加载来实现：员工表多对一关联查询部门表，要求默认只查询员工表， 只有当需要时再查询部门表，即延迟加载查询部门表。 首先要开启延迟加载的相关配置,在mybatis-config.xml文件中的settings标签中添加下面的配置。
<!-- 将延迟加载设置为true（可省略，因为默认值就是true）-->
<setting name="lazyLoadingEnabled" value="true"/>
<!-- 将立即加载设置为false -->
<setting name="aggressiveLazyLoading" value="false"/>
然后再编写mapper.xml文件
//DepartmentMapper.xml 中的查询标签
<select id="queryDepartment" parameterType="int" resultType="department">
select * from department where dNo=#{dNo}
</select>
//EmployeeMapper.xml 中的查询标签以及
<!-- 实现多对一下的延迟加载 -->
<resultMap id="map" type="Employee" autoMapping="true">
<id column="eNo" property="eNo"/>
<!--
select:通过 namespace+id 指定延迟加载执行的SQL语句
column:给方法传递的字段值
-->
<association property="department" javaType="Department" column="dNo"
select="com.test.dao.DepartmentMapper.queryDepartment"/>
</resultMap>
<select id="queryEmployeeByEno" resultMap="map">
select * from employee where eNo=#{eNo}
</select>
测试
当不执行查询部门时就只会查询员工信息如下：
==> Preparing: select * from employee where eNo=?
==> Parameters: 20100101(String)
<== Columns: eNo, dNo, jNo, eName, cardId, sex, phone, email
<== Row: 20100101, 0001, 1001, 令狐冲, 321456987989674563, 男, 16678890788, 0101@126.com
<== Total: 1
当需要查询部门信息时System.out.println(employee)就会出现如下效果：
==> Preparing: select * from employee where eNo=?
==> Parameters: 20100101(String)
<== Columns: eNo, dNo, jNo, eName, cardId, sex, phone, email
<== Row: 20100101, 0001, 1001, 令狐冲, 321456987989674563, 男, 16678890788, 0101@126.com
<== Total: 1
==> Preparing: select * from department where dNo=?
==> Parameters: 1(Integer)
<== Columns: dNo, dName, mgrNo
<== Row: 0001, 技术部, 20100101
<== Total: 1
Employee{eNo='20100101', dNo='null', jNo='1001', eName='令狐冲', cardId='321456987989674563', sex='男', phone='16678890788', email='0101@126.com', department='Department{dNo='0001', dName='技术部', mgrNo='20100101', jobs=null}'}
@Test
public void queryEmployeeByEno() throws Exception{
// 获取接口对象
EmployeeMapper eMapper = MybatisUtil.getSqlSession().getMapper(EmployeeMapper.class);
// 调用方法
Employee employee = eMapper.queryEmployeeByEno("20100101");
// System.out.println(employee);
}
