Mybatis实现一对一、一对多关联查询

比眉伴天荒 2023-09-25 20:51 28阅读 0赞

> Mybatis实现一对一、一对多关联查询，关联查询：多个表联合查询，只查询一次，通过resultMap里面的`<association>`、`<collection>`标签配置一对一、一对多。
> 
> 涉及的sql文件、idea工程文件在CSDN资源：[Mybatis实现一对一、一对多关联查询资源-CSDN文库][Mybatis_-CSDN]

#### Mybatis实现一对一、一对多关联查询 ####

*   *   *   *  pom.xml
             *  mybatis-config.xml
             *  database.properties
             *  MybatisUtil.java
             *  Teacher.java
             *  Student.java
             *  Classes.java
             *  classMapper.java
             *  classMapper.xml
             *  MapperTest.java

本篇文章涉及的一对一、一对多关系

*  班级classes、班主任teacher是一对一的关系
 *  班级classes、学生student是一对多的关系

![image-20230326141952875][]

使用IDEA快速构建一个MyBatis工程

![image-20230331151146829][]

##### pom.xml #####

<?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
    
        <groupId>com</groupId>
        <artifactId>sun</artifactId>
        <version>1.0-SNAPSHOT</version>
        <name>mybatisTest</name>
    
        <properties>
            <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
            <maven.compiler.target>1.8</maven.compiler.target>
            <maven.compiler.source>1.8</maven.compiler.source>
            <junit.version>5.8.2</junit.version>
        </properties>
    
        <dependencies>
            <dependency>
                <groupId>org.mybatis</groupId>
                <artifactId>mybatis</artifactId>
                <version>3.2.2</version>
            </dependency>
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>8.0.30</version>
            </dependency>
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <version>1.18.24</version>
            </dependency>
            <dependency>
                <groupId>log4j</groupId>
                <artifactId>log4j</artifactId>
                <version>1.2.17</version>
            </dependency>
            <dependency>
                <groupId>org.apache.logging.log4j</groupId>
                <artifactId>log4j-core</artifactId>
                <version>2.13.3</version>
            </dependency>
            <dependency>
                <groupId>org.junit.jupiter</groupId>
                <artifactId>junit-jupiter-api</artifactId>
                <version>${junit.version}</version>
                <scope>test</scope>
            </dependency>
            <dependency>
                <groupId>org.junit.jupiter</groupId>
                <artifactId>junit-jupiter-engine</artifactId>
                <version>${junit.version}</version>
                <scope>test</scope>
            </dependency>
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>4.13.2</version>
                <scope>test</scope>
            </dependency>
        </dependencies>
        <build>
            <resources>
                <resource>
                    
                    <directory>src/main/java</directory>
                    <includes>
                        
                        
                        <include>**/*.properties</include>
                        <include>**/*.xml</include>
                    </includes>
                    
                    <filtering>false</filtering>
                </resource>
                <resource>
                    <directory>src/main/resources</directory>
                </resource>
            </resources>
        </build>
    </project>

##### 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="database.properties"/>
    
        
        <settings>
            <setting name="logImpl" value="LOG4J" />
        </settings>
    
        <environments default="development">
            <environment id="development">
                
                <transactionManager type="JDBC"></transactionManager>
                
                <dataSource type="POOLED">
                    <property name="driver" value="${driver}"/>
                    <property name="url" value="${url}"/>
                    <property name="username" value="${user}"/>
                    <property name="password" value="${password}"/>
                </dataSource>
            </environment>
        </environments>
    
        
        <mappers>
            <mapper resource="com/sun/mapper/ClassesMapper.xml"/>
        </mappers>
    </configuration>

##### database.properties #####

driver=com.mysql.cj.jdbc.Driver
    url=jdbc:mysql://127.0.0.1:3306/mybatis?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
    user=root
    password=root

##### MybatisUtil.java #####

package com.sun.utils;
    
    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;
    
    import java.io.InputStream;
    
    /**
     * @author JumperSun
     * @date 2023-03-26-14:46
     */
    public class MyBatisUtil {
        
        private static SqlSessionFactory factory;
    
        static {
        
            try {
        
                InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
                factory = new SqlSessionFactoryBuilder().build(is);
            } catch (Exception e) {
        
                e.printStackTrace();
            }
        }
    
        public static SqlSession createSqlSession(){
        
            return factory.openSession(false); // true为自动提交事务
        }
    
        public static void closeSqlSession(SqlSession sqlSession){
        
            if(null != sqlSession)
                sqlSession.close();
        }
    }

##### Teacher.java #####

package com.sun.pojo;
    
    import lombok.Data;
    
    /**
     * @author JumperSun
     * @date 2023-03-26-14:51
     */
    @Data
    public class Teacher {
        
        private int id;
        private String name;
    }

##### Student.java #####

package com.sun.pojo;
    
    import lombok.Data;
    
    /**
     * @author JumperSun
     * @date 2023-03-26-14:53
     */
    @Data
    public class Student {
        
        private int id;
        private String name;
    }

##### Classes.java #####

package com.sun.pojo;
    
    import lombok.Data;
    
    import java.util.List;
    
    /**
     * @author JumperSun
     * @date 2023-03-26-14:52
     */
    @Data
    public class Classes {
        
        private int id;
        private String name;
        private Teacher teacher;
        private List<Student> studentList;
    }

##### classMapper.java #####

package com.sun.mapper;
    
    import com.sun.pojo.Classes;
    import org.apache.ibatis.annotations.Param;
    
    import java.util.List;
    
    /**
     * @author JumperSun
     * @date 2023-03-26-14:54
     */
    public interface ClassesMapper {
        
        List<Classes> getClassesTeacher(@Param("id") int id);
    
        List<Classes> getClassesTeacherStudentList(@Param("id") int id);
    }

##### classMapper.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.sun.mapper.ClassesMapper">
    
        
        <select id="getClassesTeacher" parameterType="int" resultMap="ClassesResultMap">
            select * from classes c,teacher t where c.teacher_id=t.t_id and c.c_id=#{id}
        </select>
    
        <resultMap type="com.sun.pojo.Classes" id="ClassesResultMap">
            
            <id property="id" column="c_id"/>
            <result property="name" column="c_name"/>
            <association property="teacher" javaType="com.sun.pojo.Teacher">
                <id property="id" column="t_id"/>
                <result property="name" column="t_name"/>
            </association>
        </resultMap>
    
        
        <select id="getClassesTeacherStudentList" parameterType="int" resultMap="ClassesResultMap2">
            select * from classes c,teacher t,student s where c.teacher_id=t.t_id and c.c_id=s.class_id and c.c_id=#{id}
        </select>
    
        <resultMap type="com.sun.pojo.Classes" id="ClassesResultMap2">
            <id property="id" column="c_id"/>
            <result property="name" column="c_name"/>
            <association property="teacher" javaType="com.sun.pojo.Teacher">
                <id property="id" column="t_id"/>
                <result property="name" column="t_name"/>
            </association>
            <collection property="studentList" ofType="com.sun.pojo.Student">
                <id property="id" column="s_id"/>
                <result property="name" column="s_name"/>
            </collection>
        </resultMap>
    
    </mapper>

##### MapperTest.java #####

import com.sun.mapper.ClassesMapper;
    import com.sun.pojo.Classes;
    import com.sun.utils.MyBatisUtil;
    import org.apache.ibatis.session.SqlSession;
    import org.junit.Test;
    
    import java.util.List;
    
    /**
     * @author JumperSun
     * @date 2023-03-26-15:10
     */
    public class MapperTest {
        
        /**
         * 测试班级与班主任老师一对一的关联关系
         */
        @Test
        public void test1() {
        
            SqlSession sqlSession = MyBatisUtil.createSqlSession();
            ClassesMapper classesMapper = sqlSession.getMapper(ClassesMapper.class);
            List<Classes> classes = classesMapper.getClassesTeacher(1);
            for (Classes Classes : classes) {
        
                System.out.println(classes);
            }
    
        }
    
        /**
         * 测试班级与学生一对多关联关系
         */
        @Test
        public void test2() {
        
            SqlSession sqlSession = MyBatisUtil.createSqlSession();
            ClassesMapper classesMapper = sqlSession.getMapper(ClassesMapper.class);
            List<Classes> classes = classesMapper.getClassesTeacherStudentList(1);
            for (Classes Classes : classes) {
        
                System.out.println(classes);
            }
    
        }
    }

test1:

![image-20230331151047498][]

test2:

![image-20230331151113226][]

总结：一对一关联查询需要在resultMap里面配置association，一对多关联查询需要在resultMap里面配置collection；  
List classes = classesMapper.getClassesTeacherStudentList(1);  
for (Classes Classes : classes) \{  
System.out.println(classes);  
\}

}

test1:
    
    [外链图片转存中...(img-qon5qYiR-1680248446105)]
    
    test2:
    
    [外链图片转存中...(img-DZayGx8q-1680248446107)]
    
    总结：一对一关联查询需要在resultMap里面配置association，一对多关联查询需要在resultMap里面配置collection；

[Mybatis_-CSDN]: https://download.csdn.net/download/qq_51808107/87636021
[image-20230326141952875]: https://sunmingtypora.oss-cn-qingdao.aliyuncs.com/image-20230326141952875.png
[image-20230331151146829]: https://sunmingtypora.oss-cn-qingdao.aliyuncs.com/image-20230331151146829.png
[image-20230331151047498]: https://img-blog.csdnimg.cn/img_convert/5f639929845dff6db816eb844a79b5d6.png
[image-20230331151113226]: https://img-blog.csdnimg.cn/img_convert/94a9b334a55c9371d6c2c59bb285fac0.png