mybatis一对一/一对多的关联/级联

卫三123

已于 2022-05-30 15:11:27 修改

阅读量134

点赞数

于 2022-05-25 22:28:59 首次发布

本文链接：https://blog.csdn.net/weixin_46683845/article/details/124933538

版权

mybatis 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

一、数据表结构

在这里插入图片描述

二、代码实现

2.1一对多关联查询
在这里插入图片描述

①一对多关联查询测试

 /**
     * 需求:已知课程id，查询课程的信息以及所有报了该课程的学生的信息
     */
    @Test
    public void oneToMany(){
//        获取Sqlsession
        SqlSession session = MybatisUtil.createSession();
//        通过反射获取对应的接口类
        CourseDao mapper = session.getMapper(CourseDao.class);
//        调用接口中与映射文件同名的类
        CourseDto courseDto = mapper.oneToMany1(1);
//        获取DTO类中的学生集合属性
        List<Student> students = courseDto.getStudents();
        for (Student student : students) {
            System.out.println(student);
        }
        System.out.println("========================================");
        System.out.println(courseDto);
    }

②映射文件

<!--    一对多关联查询-->
    <resultMap id="oneToManyMap1" type="com.sct.dto.CourseDto">
        <id property="courseId" column="course_id" javaType="Integer" jdbcType="VARCHAR"></id>
        <result property="courseName" column="course_name" javaType="String" jdbcType="VARCHAR"></result>
        <collection property="students" column="student_id" javaType="list" ofType="com.sct.pojo.Student">
            <id property="studentId" column="student_id"></id>
            <result property="studentName" column="student_name"></result>
            <result property="courseId" column="course_id"></result>
        </collection>
    </resultMap>
    
    <select id="oneToMany1" parameterType="int" resultMap="oneToManyMap1">
        select * from course c left join student s on c.course_id=s.course_id where c.course_id=#{courseId}
    </select>

注意要将映射文件注册到mybatis-config.xml的配置文件中

<!--    映射器  可以写配置文件的路径，也可以写接口的路径-->
    <mappers>
        <mapper resource="mapper/goodsMapper.xml"/>
        <mapper resource="mapper/adminMapper.xml"/>
        <mapper resource="mapper/sctMapper/courseMapper.xml"/>
<!--        <mapper class="com.xs.dao.GoodsMapper"></mapper>-->
    </mappers>

③对应的接口中的方法

CourseDto oneToMany1(int courseId);

2.2一对多级联查询
①测试（其实就是改了一下调用的方法）

 /**
     * 需求:已知课程id，查询课程的信息以及所有报了该课程的学生的信息
     */
    @Test
    public void oneToMany(){
//        获取Sqlsession
        SqlSession session = MybatisUtil.createSession();
//        通过反射获取对应的接口类
        CourseDao mapper = session.getMapper(CourseDao.class);
//        调用接口中与映射文件同名的类  关联查询
//        CourseDto courseDto = mapper.oneToMany1(1);
//        级联查询
        CourseDto courseDto = mapper.oneToMany2(1);
//        获取DTO类中的学生集合属性
        List<Student> students = courseDto.getStudents();
        for (Student student : students) {
            System.out.println(student);
        }
        System.out.println("========================================");
        System.out.println(courseDto);
    }

②映射文件

注意：这里的<collection />是单标签

<!--    一对多级联查询-->
    <resultMap id="oneToManyMap2" type="com.sct.dto.CourseDto">
        <id property="courseId" column="course_id"></id>
        <result property="courseName" column="course_name"></result>
        <collection property="students" javaType="list"
                    ofType="com.sct.pojo.Student" column="course_id" select="com.sct.dao.CourseDao.selectByCourseId" />
    </resultMap>

    <select id="oneToMany2" parameterType="int" resultMap="oneToManyMap2">
        select * from course where course_id=#{courseId}
    </select>

<!-- 
		1.selectByCourseId这个sql语句不会被Java调用，所以不写该接口的参数
		2.这里使用的是resultType，不是resultMap ，故需要依赖框架本身对其进行下划线转小驼峰设置
 -->
    <select id="selectByCourseId" parameterType="int" resultType="com.sct.pojo.Student">
        select * from student where course_id=#{courseId}
    </select>

③接口中对应的方法

    CourseDto oneToMany2(int courseId);

④下滑线转小驼峰

<configuration>
<!--    引入外部的配置文件，一遍下文数据源引入-->
    <properties resource="jdbc.properties"/>
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
 </configuration>

注意，配置文件中属性比如properties、settings的摆放有顺序。

在这里插入图片描述
2.2一对一关联/级联查询
①测试及需求

/**
     * 需求:根据学生id查询学生信息以及学生所报的课程的信息
     */
    @Test
    public void oneToOne(){
        SqlSession session = MybatisUtil.createSession();
        StudentDao mapper = session.getMapper(StudentDao.class);
//        StudentDto studentDto = mapper.selectById(1);
        StudentDto studentDto = mapper.selectById2(2);
        System.out.println(studentDto);
    }

②配置文件

<?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.sct.dao.StudentDao">

<!--    一对一关联查询-->
    <resultMap id="studentCourseMap" type="com.sct.dto.StudentDto">
        <id property="studentId" column="student_id" javaType="int" jdbcType="VARCHAR"></id>
        <result property="studentName" column="student_name" javaType="string" jdbcType="VARCHAR"></result>
        <result property="courseId" column="course_id"></result>
        <association property="course">
            <id property="courseId" column="course_id"></id>
            <result property="courseName" column="course_name"></result>
        </association>
    </resultMap>
    <select id="selectById" resultMap="studentCourseMap">
        select * from student s left join course c on s.course_id = c.course_id  where student_id=#{studentId}
    </select>

<!--    一对一级联查询：这里引用的course的配置文件中根据course_id查询课程信息的查询方法-->
    <resultMap id="studentCourseMap2" type="com.sct.dto.StudentDto">
        <id property="studentId" column="student_id" javaType="int" jdbcType="VARCHAR"></id>
        <result property="studentName" column="student_name" javaType="string" jdbcType="VARCHAR"></result>
        <result property="courseId" column="course_id"></result>
        <association property="course" javaType="com.sct.pojo.Course" column="course_id" select="com.sct.dao.CourseDao.selectByCoursesId" />
    </resultMap>

    <select id="selectById2" resultMap="studentCourseMap2">
        select * from student where student_id=#{studentId}
    </select>
</mapper>

③StudentDao.java

//    关联查询
    StudentDto selectById(int id);
//    级联查询
    StudentDto selectById2(int id);

注意这里依然需要将映射文件添加到mybatis-config.xml的配置文件中

<!--    映射器  可以写配置文件的路径，也可以写接口的路径-->
    <mappers>
        <mapper resource="mapper/goodsMapper.xml"/>
        <mapper resource="mapper/adminMapper.xml"/>
        <mapper resource="mapper/sctMapper/courseMapper.xml"/>
        <mapper resource="mapper/sctMapper/studentMapper.xml"/>
<!--        <mapper class="com.xs.dao.GoodsMapper"></mapper>-->
    </mappers>

三、一些配置文件
3.1mybatis配置文件

<?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="jdbc.properties"/>
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
<!--    多环境变量  default="development"不可少 代表要使用的环境是什么，根据id来指定-->
    <environments default="development">
<!--        单环境变量，可以设置多个，id代表当前环境变量的标识-->
        <environment id="development">
<!--            事务管理器，管理事务的方式是JDBC-->
            <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="mapper/goodsMapper.xml"/>
        <mapper resource="mapper/adminMapper.xml"/>
        <mapper resource="mapper/sctMapper/courseMapper.xml"/>
        <mapper resource="mapper/sctMapper/studentMapper.xml"/>
<!--        <mapper class="com.xs.dao.GoodsMapper"></mapper>-->
    </mappers>


</configuration>

3.2jdbc.properties

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

注意：要导入依赖

 <dependencies>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.3.15</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <!-- https://mvnrepository.com/artifact/javax.annotation/javax.annotation-api -->
        <dependency>
            <groupId>javax.annotation</groupId>
            <artifactId>javax.annotation-api</artifactId>
            <version>1.3.2</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.aspectj/aspectjweaver -->
        <dependency>
        <groupId>org.aspectj</groupId>
        <artifactId>aspectjweaver</artifactId>
        <version>1.9.8</version>
    </dependency>
        <!-- https://mvnrepository.com/artifact/javax.servlet/javax.servlet-api -->
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <version>4.0.1</version>
            <scope>provided</scope>
        </dependency>

        <!-- https://mvnrepository.com/artifact/cglib/cglib -->
        <dependency>
            <groupId>cglib</groupId>
            <artifactId>cglib</artifactId>
            <version>3.3.0</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.48</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.3.19</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/commons-fileupload/commons-fileupload -->
        <dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>1.3.3</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/ch.qos.logback/logback-classic -->
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <version>1.3.0-alpha14</version>
            <scope>test</scope>
        </dependency>
        <!-- https://mvnrepository.com/artifact/com.fasterxml.jackson.core/jackson-databind -->
        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-databind</artifactId>
            <version>2.13.2.2</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.12</version>
            <scope>provided</scope>
        </dependency>

		   <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.9</version>
        </dependency>
    </dependencies>