Mybatis学习笔记（九）：复杂查询

9、复杂查询

学习视频地址：复杂查询

9.1、环境搭建

1、创建表和插入语句

CREATE TABLE `teacher` (
  `id` int(10) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT into teacher(id,`name`) VALUES (1,'秦老师')

CREATE TABLE `student` (
  `id` int(10) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `tid` int(10) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fktid` (`tid`),
  CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT into student (id,`name`,tid) VALUES (1,'小明',1);
INSERT into student (id,`name`,tid) VALUES (2,'小红',1);
INSERT into student (id,`name`,tid) VALUES (3,'小张',1);
INSERT into student (id,`name`,tid) VALUES (4,'小王',1);
INSERT into student (id,`name`,tid) VALUES (5,'水水',1);

2、导入Lombok

我以前的博客中有教程

3、新建Teacher，Student实体类

4、新建Mapper接口

5、新建Mapper.xml文件

6、在核心配置文件中绑定Mapper.xml

<!--扫描包下的Mapper文件-->
<mappers>
    <package name="com.shui.dao"/>
</mappers>

6、测试

9.2、多对一的处理

任务：查询所有的学生和对应老师的信息

SQL实现：

SELECT s.id,s.name as 学生,t.name as 老师 
from student as s,teacher as t 
where s.tid=t.id

Mybatis实现：

1、实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
    private int id;
    private String name;

    //每个学生关联一个老师
    private Teacher teacher;
}

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

2、接口

public interface StudentMapper {

    //查询所有的学生和对应老师的信息
    public List<Student> getStudent();

    public List<Student> getStudent2();
}

3、Mapper.xml

按照查询嵌套处理

<select id="getStudent" resultMap="studentTeacher">
    select * from student
</select>

<resultMap id="studentTeacher" type="student">
    <result property="id" column="id"/>
    <result property="name" column="name"/>
    <!--复杂的属性，我们需要单独处理。 对象：association 集合：collection-->
    <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>

<select id="getTeacher" resultType="Teacher">
    select * from teacher where id =#{id}
</select>

按照结果嵌套处理

   <!--按照结果嵌套处理-->
    <select id="getStudent2" resultMap="StudentTeacher2">
        SELECT s.id,s.name as 学生,t.name as 老师
        from student as s,teacher as t
        where s.tid=t.id
    </select>

    <resultMap id="StudentTeacher2" type="Student">
        <result property="id" column="id"/>
        <result property="name" column="学生"/>
        <association property="teacher" javaType="Teacher">
            <result property="name" column="老师"/>
        </association>
    </resultMap>

4.测试

9.3、一对多处理

SQL实现：

SELECT s.id,s.name as 学生,t.name as 老师 
from student as s,teacher as t 
where s.tid=t.id and t.id=1

Mybatis实现：

1、实体类

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

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

2、接口

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

3、Mapper.xml

按照查询嵌套处理

<select id="getTeacher2" resultMap="TeacherStudent2">
    select * from teacher where id = #{tid}
</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=#{tid}
</select>

按照结果嵌套处理

<select id="getTeacher" resultMap="TeacherStudent">
    SELECT s.id as sid,s.name as 学生,t.name as 老师,t.id tid
    from student as s,teacher as t
    where s.tid=t.id and t.id=#{tid}
</select>

<resultMap id="TeacherStudent" type="Teacher">
    <result property="id" column="tid"/>
    <result property="name" column="老师"/>
    <!--复杂的属性，我们需要单独处理。
         对象：association   集合：collection
        javaType="" 指定属性的类型
        集合中泛型的信息，我们用 ofType 获取-->
    <collection property="students" ofType="Student">
        <result property="id" column="sid"/>
        <result property="name" column="学生"/>
        <result property="tid" column="tid"/>
    </collection>
</resultMap>

4.测试