【MyBatis从入门到入土】6 - 关联、集合_<== total: 1 student(id=1, name=小明, teacher=teache-CSDN博客

本文链接：https://blog.csdn.net/Cap220590/article/details/107182790

11 关联、集合

在MySQL中，我们经常需要处理一对多的情况，使用MyBatis中的高级映射就能够很好地处理这种情况。

11.1 数据库中创建测试表

创建学生表student和教师表teacher，并插入测试数据，在这里学生和老师是多对一的关系：
在这里插入图片描述

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

11.2 创建新的模块

在resources下创建配置文件mybatis-config.xml

<?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="db.properties" />

    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
    </settings>

    <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>
        <mapper resource="com/cap/dao/TeacherMapping.xml" ></mapper>
        <mapper resource="com/cap/dao/StudentMapping.xml" ></mapper>
    </mappers>

</configuration>

这里需要引用到db.properties，里面存放连接数据库的相关信息

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis
username=root
password=mysql

编写工具类，通过SqlSessionFactoryBuilder读取mybatis-config.xml配置文件后，构建SqlSessionFactory，再编写一个获取·SqlSession·的方法

public class MyBatisUtils {
    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            String resource = "mybatis-config.xml";
            InputStream is = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession();
    }
}

创建实体类Student和Teacher

public class Student {
    private int id;
    private String name;
    //关联老师的id
    private Teacher teacher;
}

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

编写dao层的相关接口

public interface TeacherMapping {

}

public interface StudentMapping {

}

在resources下创建同样的层级包结构，编写相应的Mapping.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="com.cap.dao.StudentMapping">

</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="com.cap.dao.TeacherMapping">

</mapper>

整个的层级结构如下
在这里插入图片描述 6. 简单测试一下，利用注解在TeacherMapping接口编写一个通过id查询Teacher的方法

public interface TeacherMapping {
    @Select("SELECT * FROM teacher WHERE id = #{id}")
    Teacher getTeacherById(int id);
}

编写测试方法

public class Test {
    @org.junit.Test
    public void test(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        TeacherMapping mapper = sqlSession.getMapper(TeacherMapping.class);

        Teacher teacher = mapper.getTeacherById(1);
        System.out.println(teacher);

        sqlSession.close();
    }
}
	/**
	Teacher{id=1, name='秦老师'}
	*/

11.3 关联的嵌套 Select 查询

现在我们需要查询所有学生的信息，并且还要查询对应的老师的信息

public interface StudentMapping {
    /**
     * <p>获取学生的信息，以及对应的老师的信息</p>
     * @return
     */
    List<Student> getStudent();
}

查询出来的结大概是这样子：
在这里插入图片描述首先我们来讲解一下第一个思路——嵌套SELECT查询：
先查询所有学生的信息，再根据学生的tid，去寻找对应的老师

    <!--
    思路：
        1. 查询所有的学生信息
        2. 根据查询出来的学生的id，寻找对应的老师
    -->
    <select id="getStudent" resultMap="StudentTeacher">
        SELECT * FROM student;
    </select>

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

    <select id="getTeacher" resultType="teacher">
        SELECT * FROM teacher WHERE id = #{tid};
    </select>

resultMap结果集映射其他都能明白，关键在于如何处理Student中含有的Teacher对象，这里我们需要使用<association>标签来处理，它将查询得到的字段tid对应到Teacher对象上，再通过引用另一个SELECT查询，得到该tid所对应的Teacher对象。
在这里插入图片描述
注意type和javaType，他们用了别名student和teacher，分别代表实体类Student和Teacher，这个可以在mybatis-config.xml中配置

    <!-- 给pojo包取别名，默认为类名（首字母变小写，其他不变） -->
    <typeAliases>
        <package name="com.cap.pojo"/>
    </typeAliases>

编写一个测试方法

    @org.junit.Test
    public void test1(){
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        StudentMapping mapper = sqlSession.getMapper(StudentMapping.class);
        List<Student> list = mapper.getStudent();

        for(Student student : list){
            System.out.println(student);
        }

        sqlSession.close();
    }

查询结果如下：

Student{id=1, name='小明', teacher=Teacher{id=1, name='秦老师'}}
Student{id=2, name='小红', teacher=Teacher{id=1, name='秦老师'}}
Student{id=3, name='小张', teacher=Teacher{id=1, name='秦老师'}}
Student{id=4, name='小李', teacher=Teacher{id=1, name='秦老师'}}
Student{id=5, name='小王', teacher=Teacher{id=1, name='秦老师'}}

11.4 关联的嵌套结果映射

    <resultMap id="StudentTeacher" type="student" >
        <result property="id" column="sid" />
        <result property="name" column="sname" />
        <association property="teacher" javaType="teacher">
            <result property="name" column="tname" />
        </association>
    </resultMap>

测试方法得到：

Student{id=1, name='小明', teacher=Teacher{id=0, name='秦老师'}}
Student{id=2, name='小红', teacher=Teacher{id=0, name='秦老师'}}
Student{id=3, name='小张', teacher=Teacher{id=0, name='秦老师'}}
Student{id=4, name='小李', teacher=Teacher{id=0, name='秦老师'}}
Student{id=5, name='小王', teacher=Teacher{id=0, name='秦老师'}}

11.5 集合的嵌套结果映射

集合和关联非常相似，也是有嵌套 Select 查询和嵌套结果映射，这里只举嵌套结果映射的例子。
首先我们先修改实体类

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

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

在接口TeacherMapping中添加一个方法，用于查询指定id的Teacher

public interface TeacherMapping {
    Teacher getTeacherById(@Param("tid") int tid);
}

<mapper namespace="com.cap.dao.TeacherMapping">
    <!-- 按结果嵌套 -->
    <select id="getTeacherById" resultMap="TeacherStudent">
        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="TeacherStudent" type="teacher">
        <result property="id" column="tid" />
        <result property="name" column="tname" />
        <!--
        复杂的属性，我们需要单独处理
            对象：association
            集合：collection
            javaType : 指定属性的类型
            集合中的泛型信息，使用ofType获取
         -->
        <collection property="students" ofType="com.cap.pojo.Student">
            <result property="id" column="sid" />
            <result property="name" column="sname" />
            <result property="tid" column="tid" />
        </collection>
    </resultMap>

</mapper>