数据库设计
我们用学生和老师的例子来说明：
一对多：一个老师有多个学生，转换为程序设计语言即为一个老师对象下有一个学生对象集合。
多对一：多个学生有同一个老师，学生对象里有一个属性关联着老师。
创建学生、教师表
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();
}
总结
1、 联-association 用于一对一和多对一
2、 集合-collection 用于一对多的关系
3、 JavaType 和 ofType 都是用来指定对象类型的
- JavaType 是用来指定 pojo 中属性的类型
- ofType 指定的是映射到 list 集合属性中 pojo 的类型。