Mybatis中多对一的查询

数据库的搭建

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

搭建idea中Mybatis的结构体

• Mybatis的配置文件

		<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--引入外部配置文件-->
    <properties resource="config.properties"></properties>
<!--启用日志-->
    <settings>
        <setting name="logImpl" value="LOG4J"/>
    </settings>
<!--    给实体类起别名-->
    <typeAliases>
        <package name="com.ibm.pojo"/>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <package name="com.ibm.dao"/>
    </mappers>
</configuration>

• 创建对应的实体类

package com.ibm.pojo;

import lombok.Data;

/**
* @author 郑朝文;
* @Description
* @creat 2021-03-16--18:47;
*/
@Data
public class Student {

   private int id;
   private String name;
   private int tid;
}


package com.ibm.pojo;

import lombok.Data;

import java.util.List;

/**
* @author 郑朝文;
* @Description
* @creat 2021-03-16--18:46;
*/
@Data
public class Teacher {

   private int id;
   private String name;
   private List<Student> students;
}

• 创建实体类对应的Mapper

   	package com.ibm.dao;

import com.ibm.pojo.Student;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
* @author 郑朝文;
* @Description
* @creat 2021-03-16--18:47;
*/
public interface StudentMapper {

}


package com.ibm.dao;

import com.ibm.pojo.Teacher;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;


/**
* @author 郑朝文;
* @Description
* @creat 2021-03-16--18:47;
*/
public interface TeacherMapper {

   Teacher getTeacherById(@Param("tid" )int id);
}

• 创建对Mapper对应的xml文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
       PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
       "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ibm.dao.TeacherMapper">
   <!--       写复杂sql 语句 -->
   <select id="getTeacherById" resultMap="TeacherStudent">
       select t.id tid, t.name tname, s.id sid, s.name sname
       from teacher t
                join student s on t.id = s.tid
       where s.tid = t.id;
   </select>
   <!--    结果集映射 一对多的映射结果 -->
   <resultMap id="TeacherStudent" type="teacher">
       <result property="name" column="tname"/>
       <result property="id" column="tid"/>
       <!--        使用集合来进行结果集映射 字段为集合时使用 collection
         ofType 这个设置的是泛型中的类型  -->
       <collection property="students" ofType="student">
           <result property="id" column="sid"/>
           <result property="name" column="sname"/>
       </collection>
   </resultMap>
</mapper>

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
       PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
       "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ibm.dao.StudentMapper">

</mapper>

• 一个教师对应的多个学生，在教师类中定义了一个学生的集合，当属性是集合的时候使用< collection> 标签来进行结果集映射，collection中的属性ofType的值为泛型中的类型，之后再在里面进行嵌套结果映射。