03-MyBatis复杂查询

1、多对一查询

多个学生对应一个老师

1.1、建立数据库

student表的外键tid关联了teacher表的主键id

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) DEFAULT 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);

1.2、实体类

Student

package domain;
import lombok.Data;
@Data
public class Student {
    private int id;
    private String name;
    //一对多关系映射
    private Teacher teacher;
}

Teacher

package domain;
import lombok.Data;
@Data
public class Teacher {
    private int id;
    private String name;
}

1.3、Mapper接口

StudentMapper

package mapper;
import domain.Student;
import java.util.List;
public interface StudentMapper {
    public List<Student> getStudent();//查询所有学生
}

TeacherMapper

package mapper;
import domain.Teacher;
import java.util.List;
public interface TeacherMapper {
}

1.4、配置文件

StudentMapper.xml
按照查询嵌套

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="mapper.StudentMapper">

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

    <resultMap id="one" type="domain.Student">
          <!--javaType里面的类型为一个对象-->
        <association property="teacher" column="tid" javaType="domain.Teacher" select="getTeacher"/>
    </resultMap>

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

按照结果嵌套

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="mapper.StudentMapper">
  <select id="getStudent" resultMap="one">
      select student.id sid,student.name sname,teacher.name tname
      from student,teacher
      where student.tid=teacher.id
  </select>

    <resultMap id="one" type="domain.Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="domain.Teacher">
            <result property="name" column="tname"/>
        </association>
    </resultMap>
</mapper>

1.5、测试

  //查询学生
    @Test
    public void test01(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> students = mapper.getStudent();
        for (Student student : students) {
            System.out.println(student);
        }
        sqlSession.close();
    }

2、查询配置文件解析

resultMap中的参数：

• id：用来给结果集起名
• type：指实体类的权限命名或者别名

result中的参数

• property：指实体类中的属性
• column：数据库中表的列名

association中的参数（使用association标签属性实现一对多关系映射）

• property：实体类中的属性
• column：指该表的外键名、
• javaType：指与之相关联的实体类的权限命名或者别名
• select：指与之相对应的select语句的id名

如图

3、一对多查询

一个老师拥有多个学生

3.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) DEFAULT 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);

3.2、实体类

Student

package domain;
import lombok.Data;
@Data
public class Student {
    private int id;
    private String name;
    private int tid;
}

Teacher

package domain;
import lombok.Data;
import java.util.List;
@Data
public class Teacher {
    private int id;
    private String name;
    //一个老师拥有多个学生
    private List<Student> students;
}

3.3、Mapper接口

TeacherMapper

package mapper;
import domain.Teacher;
import org.apache.ibatis.annotations.Param;

import java.util.List;
public interface TeacherMapper {
    //查询所有老师以及老师下面的所有学生
    public Teacher getTeacher(@Param("tid")int tid);
}

StudentMapper

package mapper;
import domain.Student;
import java.util.List;
public interface StudentMapper {
}

3.4、配置文件

按结果嵌套

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="mapper.TeacherMapper">
    <select id="getTeacher" resultMap="one">
        select t.name tname,t.id tid,s.name sname,s.id sid
        from teacher t,student s
        where s.tid=t.id and t.id=#{tid}
    </select>

    <resultMap id="one" type="domain.Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <collection property="students" ofType="domain.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 Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="mapper.TeacherMapper">
    <select id="getTeacher" resultMap="one">
        select *from teacher where id=#{tid}
    </select>

    <resultMap id="one" type="domain.Teacher">
        <collection property="students" column="id" javaType="ArrayList" ofType="domain.Student" select="getStudentByTeacherId"/>
    </resultMap>

    <select id="getStudentByTeacherId" resultType="domain.Student">
        select *from student where tid=#{tid}
    </select>
</mapper>

3.5、测试

import domain.Teacher;
import mapper.TeacherMapper;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import utils.MybatisUtils;
import java.util.List;

public class junit {
    @Test
    public void one(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher(1);
        System.out.println(teacher);
        sqlSession.close();
    }
}

4、小结

1. 关联—->association【多对一】
2. 集合—->collection【一对多】
3. javaType与ofType
   1. javaType用来指定实体类中属性的类型
   2. ofType用来指定映射到List集合或者集合中的domain类型，也就是泛型中的约束类型