架构
对于老师而言就是一对多
环境搭建
数据库
CREATE TABLE `student`(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(20) DEFAULT NULL
)ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO student VALUES (1,"李老师");
CREATE TABLE `student` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(20) DEFAULT NULL ,
`tid` INT(4) NOT NULL,
KEY `fk_tid` (`tid`),
CONSTRAINT fk_tid FOREIGN KEY(`tid`) REFERENCES `teacher`(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO student VALUES
(1,"小红",1),
(2,"小绿",1),
(3,"小灯",1),
(4,"小勿",1),
(5,"小闯",1);
实体类
Student
package pojo;
import lombok.Data;
@Data
public class Student {
private int id;
private String name;
private int tid;
}
Teacher
package pojo;
import lombok.Data;
import java.util.List;
@Data
public class Teacher {
private int id;
private String name;
private List<Student> Students;
}
接口
StudentMapper
package dao;
import pojo.Student;
import java.util.List;
public interface StudentMapper {
}
TeacherMapper
package dao;
import pojo.Teacher;
public interface TeacherMapper {
Teacher getTeacher(int id);
Teacher getTeacher2(int id);
}
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="dao.StudentMapper">
</mapper>
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="dao.TeacherMapper">
<select id="getTeacher" resultMap="TeacherStudent">
select s.id sid,s.name sname,t.name tname,t.id,tid from student s,teacher t where s.tid=t.id and tid=#{id};
</select>
<resultMap id="TeacherStudent" type="Teacher">
<result column="tid" property="id" />
<result column="tname" property="name" />
<collection property="Students" ofType="Student">
<result property="id" column="sid" />
<result property="name" column="sname" />
<result property="tid" column="tid" />
</collection>
</resultMap>
<select id="getTeacher2" resultMap="TeacherStudent2">
select * from teacher where id=#{id};
</select>
<resultMap id="TeacherStudent2" type="Teacher">
<collection property="Students" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId" column="id" />
</resultMap>
<select id="getStudentByTeacherId" resultType="Student">
select * from student where tid=#{id};
</select>
</mapper>
按结果集查询
<?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="dao.TeacherMapper">
<select id="getTeacher" resultMap="TeacherStudent">
select s.id sid,s.name sname,t.name tname,t.id,tid from student s,teacher t where s.tid=t.id and tid=#{id};
</select>
<resultMap id="TeacherStudent" type="Teacher">
<result column="tid" property="id" />
<result column="tname" property="name" />
<collection property="Students" ofType="Student">
<result property="id" column="sid" />
<result property="name" column="sname" />
<result property="tid" column="tid" />
</collection>
</resultMap>
</mapper>
这种方式就是相对于结果集做处理来的
子查询
<?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="dao.TeacherMapper">
<select id="getTeacher2" resultMap="TeacherStudent2">
select * from teacher where id=#{id};
</select>
<resultMap id="TeacherStudent2" type="Teacher">
<collection property="Students" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId" column="id" />
</resultMap>
<select id="getStudentByTeacherId" resultType="Student">
select * from student where tid=#{id};
</select>
</mapper>