多表查询

多对一

实体：学生、老师

关系：学生多对一老师

实体与接口

实体(需要导入lombok依赖)

@Data @NoArgsConstructor @AllArgsConstructor @ToString public class Student { int id; String name; /** * 多个学生可以是同一个老师，即多对一 * Teacher teacher可以通过teacher类获取id * 简单的来说teacher.id要作为一个外键对象 */ private Teacher teacher; } @Data @NoArgsConstructor @AllArgsConstructor @ToString public class Teacher { int id; String name; }

接口

/** * 实现增删改查的功能 * @author DELL */ public interface StudentMapper { /** * 多表查询 */ public List<Student> getStudent(); } /** * 老师表 */ public interface TeacherMapper { /** * 简单的查询 */ public List<Teacher> getTeacher(); }

方法1（子查询）

思路

1、查询所有的学生信息 2、根据查询出来的学生的tid，寻找对应的老师

映射文件（重点关注）

<select id="getStudent" resultMap="StudentTeacher"> select * from student </select> <!-- 这里的student需要去核心配置文件中设置别名--> <resultMap id="StudentTeacher" type="student"> <result property="id" column="id"/> <result property="name" column="name"/> <!-- 通过表象看本质，实质上就是解决数据库字段和属性名不一致的问题，用外键teacher作为一个对象，对象的类型javaType（主体）是teacher，由select=getTeacher查询得出的结果--> <!-- 复杂的属性，需要单独处理 对象：association 集合：collection--> <!-- javaType 为 student类中的 Teacher teacher--> <association property="teacher" column="tid" javaType="teacher" select="getTeacher"></association> </resultMap> <select id="getTeacher" resultType="teacher"> select * from teacher where id = #{id} </select>

方法2（通过结果进行映射）

映射文件（重点关注）

<!--第二种方法--> <select id="getStudent" resultMap="StudentTeacher"> select student.id as sid,student.name as sname, teacher.name as tname from student,teacher where student.tid = teacher.id </select> <resultMap id="StudentTeacher" type="student"> <!-- proper为属性名 column为字段--> <result property="id" column="sid"/> <result property="name" column="sname"/> <!-- 关联对象property 关联对象在Student实体类中的属性，把teacher类中name关联给tname--> <association property="teacher" javaType="teacher" > <result property="name" column="tname"/> </association> </resultMap>

通用测试方法

public class MyBatisUtils { private static String resource = "mybatis-config.xml"; private static InputStream inputStream; private static SqlSessionFactory sqlSessionFactory; static { try { inputStream = Resources.getResourceAsStream(resource); sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); } catch (IOException e) { e.printStackTrace(); } } public static SqlSession getSession(){ SqlSession session = sqlSessionFactory.openSession(); return session; } }

一对多

实体：学生、老师

关系：老师一对多学生

实体与接口

实体

public class Student { int id; String name; int tid; public Student(){ } public Student(int id, String name, int tid){ this.id = id; this.name=name; this.tid = tid; } 。。get/set，toString方法 } public class Teacher { int id; String name; /** * 一个老师拥有多个学生 */ List<Student> students; public Teacher() { } public Teacher(int id, String name, List<Student> students) { this.id = id; this.name = name; this.students = students; } ....set/get方法，toString方法 }

接口

public interface StudentMapper { /** * 多表查询 */ public List<Student> getStudent(); } public interface TeacherMapper { /** * 简单的查询 * 查询所有的老师 */ public List<Teacher> getTeacher(); /** *简单的查询单条记录 * 获取指定老师，及老师下的所有学生 */ public Teacher selectTeacherById(int n); }

方法1

映射文件（需要重点关注）

<!--===================按结果查询--> <select id="selectTeacherById" resultMap="TeacherAndStudent"> select teacher.id as tid, teacher.name as tname,student.id as sid, student.name as sname from teacher, student where teacher.id=student.tid and teacher.id= #{id} </select> <resultMap id="TeacherAndStudent" type="teacher"> <result property="name" column="tname"/> <!-- 取集合里面的每一个值--> <collection property="students" ofType="student"> <result property="id" column="sid"></result> <result property="name" column="sname"></result> <result property="tid" column="tid"></result> </collection> </resultMap>

方法2（连表查询）

<select id="selectTeacherById" resultMap="TeacherAndStudent"> # 根据老师的id查询老师的记录 select * from teacher where id = #{id} </select> <resultMap id="TeacherAndStudent" type="Teacher"> <!-- 取一个集合,从集合里面取值--> <collection property="students" javaType="ArrayList" ofType="student" column="id" select="getStudentByTeacherId"></collection> </resultMap> <select id="getStudentByTeacherId" resultType="student"> # 通过老师的id查询这个老师下所有学生 select * from student where tid = #{id} </select>

通用测试方法

public class MybatisTest { //获取日志 Logger logger = Logger.getLogger(MybatisTest.class); @Test public void selectTeacherById02(){ SqlSession session = MybatisUtils.getSession(); TeacherMapper mapper = session.getMapper(TeacherMapper.class); Teacher teacher = mapper.selectTeacherById(1); System.out.println(teacher.getName()); System.out.println(teacher.getStudents()); } }

扩展：

避免慢索引

必定面试问题

mysql引擎innoDB底层原理索引索引优化