一、多对一(查询student表中学生所对应的老师)
-
学生表 student
-
老师表 teacher
-
实体类 Student
public class Student {
private int id;
private String name;
private Teacher teacher;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Teacher getTeacher() {
return teacher;
}
public void setTeacher(Teacher teacher) {
this.teacher = teacher;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", teacher=" + teacher +
'}';
}
- 实体类 Teacher
public class Teacher {
private int id;
private String name;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "Teacher{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
}
- 接口 StudentMapper
public interface StudentMapper {
public List<Student> getStudent();
}
- 接口 TeacherMapper
public interface TeacherMapper {
Teacher getTeacher(@Param("tid") String id);
}
- 接口配置文件 StudentMapper.xml
使用两种方式实现
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xxw.dao.StudentMapper">
<!--方式一:-->
<!--复杂的属性,需要单独处理:
association:对象属性
collection:集合
javaType:指定属性的类型
ofType:集合中的泛型信息,使用ofType获取-->
<resultMap id="StudentTeacher" type="student">
<result property="id" column="id"/>
<result property="name" column="name"/>
<association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>
<select id="getStudent" resultMap="StudentTeacher">
select * from mybatis_xxw.student
</select>
<select id="getTeacher" resultType="Teacher">
select *
from mybatis_xxw.teacher
where id=#{id}
</select>
<!--==========================================================================================-->
<!--方式二:按照结果嵌套查询-->
<select id="getStudent2" resultMap="ST">
select s.id sid,s.name sname,t.id tid
from student s,teacher t
where s.id = t.id
</select>
<resultMap id="ST" type="student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<association property="teacher" javaType="Teacher">
<result property="name" column="tname"/>
</association>
</resultMap>
</mapper>
- 测试类
public class UserMapperTest {
@Test
//方式一
public void getStudent(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> studentList = mapper.getStudent();
for (Student student : studentList) {
System.out.println(student);
}
sqlSession.close();
}
@Test
//方式二
public void getStudent2(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> studentList = mapper.getStudent();
for (Student student : studentList) {
System.out.println(student);
}
sqlSession.close();
}
}
结果如图所示:
二、一对多(查询teacher表中老师教的学生)
- 实体类 Teacher
public class Teacher {
private int id;
private String name;
//一个老师拥有多名学生
private List<Student> students;
public Teacher() {
}
public Teacher(int id, String name, List<Student> students) {
this.id = id;
this.name = name;
this.students = students;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public List<Student> getStudents() {
return students;
}
public void setStudents(List<Student> students) {
this.students = students;
}
@Override
public String toString() {
return "Teacher{" +
"id=" + id +
", name='" + name + '\'' +
", students=" + students +
'}';
}
- 实体类 Student
public class Student {
private int id;
private String name;
private int tid;
public Student() {
}
public Student(int id, String name, int tid) {
this.id = id;
this.name = name;
this.tid = tid;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getTid() {
return tid;
}
public void setTid(int tid) {
this.tid = tid;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", tid=" + tid +
'}';
}
}
- 接口 TeacherMapper
public interface TeacherMapper {
Teacher getTeacher(@Param("tid") int id);
}
- 接口配置文件 TeacherMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.xxw.dao.TeacherMapper">
<!--按结果嵌套查询-->
<select id="getTeacher" resultMap="ST">
select s.id sid,s.name sname,t.id tid,t.name tname
from teacher t,student s
where s.tid = tid and t.id = #{tid}
</select>
<!--javaType:指定属性的类型
ofType:集合中的泛型信息,使用ofType获取-->
<resultMap id="ST" type="Teacher">
<result 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>
- 测试类
public class UserMapperTest {
@Test
public void getTeacher(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher(1);
System.out.println(teacher);
sqlSession.close();
}
}
结果如图所示:
注意点:
配置文件中涉及到的一些配置:
association:对象属性 【多对一】
collection:集合 【一对多】
javaType:指定属性的类型
ofType:集合中的泛型信息,使用ofType获取
- 数据表student
CREATE TABLE `student` (
`id` int(10) NOT NULL,
`name` varchar(30) DEFAULT NULL,
`tid` int(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fktid` (`tid`),
CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- 数据表teacher
CREATE TABLE `teacher` (
`id` int(10) NOT NULL,
`name` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;