Mybatis知识小汇（9）——复杂查询环境搭建 (多对一处理)

复杂查询环境搭建

多对一处理（比如多个学生对应一个老师）

sql:

CREATE TABLE teacher(
	id INT PRIMARY KEY,
	NAME VARBINARY(20) DEFAULT NULL
)ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO teacher VALUES(1,'卓中');
CREATE TABLE student(
	id INT PRIMARY KEY,
	NAME VARBINARY(20) DEFAULT NULL,
	tid INT,
	CONSTRAINT ftid FOREIGN KEY(tid) REFERENCES teacher(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO student VALUES(1,'张三' ,1),(2,'李四' ,1),(3,'王五' ,1),(4,'田七' ,1);

测试环境搭建

1. 导入lombok

2. 实体类Teacher、Student

   @Data
   @AllArgsConstructor
   @NoArgsConstructor
   public class Student {
       private int id;
       private String name;
       //注意这里是Teacher对象
       private Teacher teacher;
   }
   
   @Data
   @AllArgsConstructor
   @NoArgsConstructor
   public class Teacher {
       private int id;
       private String name;
   }
   

3. 接口TeacherMapper、StudentMapper

4. 可以通过注解或者xml文件进行测试，看环境是否搭建成

按照查询嵌套处理

1. StudentMapper

   public interface StudentMapper {
       //查询返回学生list
       List<Student> getStudentList();
   }
   

2. StudentMapper.xml (记得在在核心配置中注册)

   ​ 思路：

   • 首先查询所有的学生
   • 再根据所有的学生表中的tid查找对应的老师（子查询）

   <?xml version="1.0" encoding="UTF8" ?>
   <!DOCTYPE mapper
           PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
           "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
   <mapper namespace="com.wjq.dao.StudentMapper">
       <select id="getStudentList" resultMap="StudentTeacher">
           select * from student
       </select>
       <resultMap id="StudentTeacher" type="Student">
   <!--        因为Student对象的第三个属性为Teacher类型，因此不能再用resultMap中的普通标签(result、id)-->
   <!--        要用复杂的处理例如collection集合（一对多，一个老师对应多个学生）  association对象（在多对一中使用，可以理解为多个学生关联一个老师）-->
   <!--                 对象中的字段        字段的类型         映射到数据库中的字段名    嵌套查询的id-->
           <association property="teacher" javaType="Teacher" column="tid" select="getTeacher"/>
       </resultMap>
       <select id="getTeacher" resultType="Teacher">
           select * from teacher where id = #{tid};
       </select>
   </mapper>
   

3. 测试

   @Test
   public void getStudentList(){
       SqlSession sqlSession = MybatisUtils.getSqlSession();
       StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
       List<Student> students = mapper.getStudentList();
       for (Student student : students) {
           System.out.println(student);
       }
       sqlSession.close();
   }
   

按照结果嵌套处理

1. StudentMapper

   public interface StudentMapper {
       //查询返回学生list
       List<Student> getStudentList2();
   }
   

2. StudentMapper.xml (记得在在核心配置中注册)

   ​ 思路：

   • 就直接连表查询

   <?xml version="1.0" encoding="UTF8" ?>
   <!DOCTYPE mapper
           PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
           "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
   <mapper namespace="com.wjq.dao.StudentMapper">
       <select id="getStudentList2" resultMap="studentTeacher2">
           select s.id sid, s.name sname, t.name tname from student s, teacher t where s.tid = t.id;
       </select>
       <resultMap id="studentTeacher2" type="student">
           <result property="id" column="sid"/>
           <result property="name" column="sname"/>
           <!--复杂字段用association      javaType指定属性的类型-->
           <association property="teacher" javaType="Teacher">
               <!--Teacher表中的属性-->
               <result property="name" column="tname"/>
           </association>
       </resultMap>
   </mapper>
   

3. 测试

   @Test
   public void getStudentList2(){
       SqlSession sqlSession = MybatisUtils.getSqlSession();
       StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
       List<Student> students = mapper.getStudentList2();
       for (Student student : students) {
           System.out.println(student);
       }
       sqlSession.close();
   }
   

上一节—>使用lombok

如有不对的地方欢迎指出，共同进步！