MyBatis之复杂查询

1. 数据表准备

CREATE TABLE IF NOT EXISTS `teacher`(
	`id` INT NOT NULL,
	`name` VARCHAR(10),
  PRIMARY KEY(`id`)
)ENGINE= INNODB DEFAULT CHARSET=utf8;

INSERT INTO `teacher` VALUES(1,'张老师');

CREATE TABLE IF NOT EXISTS `student`(
	`id` INT NOT NULL,
	`name` VARCHAR(20) DEFAULT NULL,
	`tid` INT DEFAULT NULL,
	PRIMARY KEY(`id`),
	CONSTRAINT fk_student_teacher FOREIGN KEY(tid) REFERENCES teacher(id)
)ENGINE= INNODB DEFAULT CHARSET=utf8;

INSERT INTO `student` VALUES(1,'张三',1);
INSERT INTO `student` VALUES(2,'李四',1);
INSERT INTO `student` VALUES(3,'王五',1);

2. 多对一（关联：association）

案例：多个学生关联一个老师

实体类

@Data
public class Student {
    private int id;
    private String name;
    private Teacher teacher;
}

@Data
public class Teacher {
    private int id;
    private String name;
}

接口

public interface StudentMapper {
    //查询学生下关联的老师
    List<Student> getStudentList2();
    //查询学生下关联的老师
    List<Student> getStudentList3();

}

按查询嵌套处理

 <select id="getStudentList2" resultMap="StudentTeacher">
        select * from student
    </select>
    <resultMap id="StudentTeacher" type="student">
       <id property="id" column="id" />
        <result property="name" column="name"/>
        <association property="teacher" column="tid" javaType="teacher" select="getTeacher" />
    </resultMap>
    <select id="getTeacher" resultType="teacher">
        select * from teacher where id = #{tid}
    </select>

按结果嵌套处理

<select id="getStudentList3" resultMap="StudentTeacher2">
        select s.id,s.name,t.name from student s,teacher t where s.tid = t.id;
    </select>
    <resultMap id="StudentTeacher2" type="student">
        <id property="id" column="id" />
        <result property="name" column="name"/>
        <association property="teacher" column="teacher" javaType="teacher">
            <result property="name" column="name" />
        </association>
    </resultMap>

测试

@Test
    public void test2() {
        SqlSession sqlSession = null;
        try {
            //1.获取session对象
            sqlSession= MybatisUtils.getSqlSession();
            //2.获取Dao对象
            StudentMapper studentDao = sqlSession.getMapper(StudentMapper.class);
            List<Student> studentList = studentDao.getStudentList2();
            for (Student student : studentList) {
                System.out.println(student);
            }
        } catch (Exception e){
            e.printStackTrace();
        }finally {
            //3.关闭sqlSession
            if (sqlSession != null){
                sqlSession.close();
            }
        }
    }

3. 一对多（集合：collection）

案例：一个老师拥有多个学生

实体类

@Data
public class Student {
    private int id;
    private String name;
    private int tid;
}

@Data
public class Teacher {
    private int id;
    private String name;
    private List<Student> students;
}

接口

public interface TeacherMapper {
    Teacher getTeacher(@Param("tid") int id);
    Teacher getTeacher2(@Param("tid") int id);
}

按查询嵌套处理

<!--按查询嵌套处理-->
    <select id="getTeacher" resultMap="teacherStudent">
        select * from teacher where id = #{tid};
    </select>
    <resultMap id="teacherStudent" type="teacher">
        <result property="id" column="id" />
        <result property="name" column="name" />
        <collection property="students" column="id" javaType="ArrayList" ofType="student" select="getStudent" />
    </resultMap>
    <select id="getStudent" resultType="student">
        select * from student where tid = #{tid};
    </select>

按结果嵌套处理

<!--按结果嵌套处理-->
    <select id="getTeacher2" resultMap="teacherStudent2">
        select s.id sid,s.name sname,t.name tname,t.id tid
        from student s,teacher t
        where s.tid = t.id
        and t.id = #{tid}
    </select>
    <resultMap id="teacherStudent2" type="teacher">
        <id property="id" column="tid"/>
        <result property="name" column="tname"/>
        <collection property="students" ofType="student">
            <id property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>

4. 小结

小结

1. 关联- association【多对一】
2. 集合- collection【一对多】
3. javaType & ofType
   1.JavaType 用来指定实体类中属性的类型
   2. ofType用来指定映射到List或者集合中的pojo类型，泛型中的约束类型!

注意点:

• 保证SQL的可读性，尽量保证通俗易懂
• 注意一对多和多对一中，属性名和字段的问题!
• 如果问题不好排查错误，可以使用日志，建议使用Log4j