MyBatis实现一对一，一对多关联查询

小灰灰 2020-06-13 04:36 1105阅读 0赞

MyBatis如何实现一对一，一对多查询的？

可以通过关联查询实现。关联查询是几个表联合查询，只查询一次，通过在resultMap里面的association，collection节点配置一对一，一对多的类就可以完成

具体实现如下

## 准备数据库表结构与数据 ##

班级classes与老师teacher是一对一的关系

班级classes与学生student是一对多的关系

SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    -- Table structure for classes
    -- ----------------------------
    DROP TABLE IF EXISTS `classes`;
    CREATE TABLE `classes`  (
      `c_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '班级id',
      `c_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '班级名',
      `teacher_id` int(11) NULL DEFAULT NULL COMMENT '老师id',
      PRIMARY KEY (`c_id`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic COMMENT '班级表';
    
    -- ----------------------------
    -- Records of classes
    -- ----------------------------
    INSERT INTO `classes` VALUES (1, '高三2班', 1);
    INSERT INTO `classes` VALUES (2, '高一4班', 2);
    
    -- ----------------------------
    -- Table structure for teacher
    -- ----------------------------
    DROP TABLE IF EXISTS `teacher`;
    CREATE TABLE `teacher`  (
      `t_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '老师id',
      `t_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '老师姓名',
      PRIMARY KEY (`t_id`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic COMMENT '老师表';
    
    -- ----------------------------
    -- Records of teacher
    -- ----------------------------
    INSERT INTO `teacher` VALUES (1, '老师张三');
    INSERT INTO `teacher` VALUES (2, '老师李四');
    
    SET NAMES utf8mb4;
    SET FOREIGN_KEY_CHECKS = 0;
    
    -- ----------------------------
    -- Table structure for student
    -- ----------------------------
    DROP TABLE IF EXISTS `student`;
    CREATE TABLE `student`  (
      `s_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生id',
      `s_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学生姓名',
      `class_id` int(11) NULL DEFAULT NULL COMMENT '班级id',
      PRIMARY KEY (`s_id`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic COMMENT '学生表';
    
    -- ----------------------------
    -- Records of student
    -- ----------------------------
    INSERT INTO `student` VALUES (1, '学生小明', 1);
    INSERT INTO `student` VALUES (2, '学生小陈', 1);
    INSERT INTO `student` VALUES (3, '学生小张', 2);
    
    SET FOREIGN_KEY_CHECKS = 1;

数据库准备工作完成后，使用IDEA新建一个Spring Boot项目，工程目录如下

## 新建Spring Boot项目 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly90aGlua3dvbi5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70]

## 在pom.xml文件中添加如下依赖 ##

<dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.1.0</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>

## 在application.yml文件中进行数据库和mybatis配置 ##

# spring配置
    spring:
      # 数据库配置
      datasource:
        type: com.zaxxer.hikari.HikariDataSource
        url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&allowMultiQueries=true&useSSL=false&tinyInt1isBit=false&serverTimezone=GMT%2B8
        driverClassName: com.mysql.cj.jdbc.Driver
        username: root
        password: root
    
    # mybatis配置
    mybatis:
      # 给实体类配置别名
      type-aliases-package: com.jourwon.*.pojo
      # 加载mybatis的mapper配置文件
      mapper-locations: classpath:mybatis/mapper/**/*Mapper.xml
      configuration:
        # 开发环境控制台打印sql语句
        log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
        # 开启驼峰规则自动映射字段属性值；如字段为user_name的可以映射到userName属性中
        map-underscore-to-camel-case: true
        # 设置sql执行超时时间,以秒为单位的全局sql超时时间设置,当超出了设置的超时时间时,会抛出SQLTimeoutException
        default-statement-timeout: 30
        # 解决查询返回结果含null没有对应字段值问题
        call-setters-on-nulls: true

## 新建老师类Teacher ##

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

## 新建班级类Classes ##

@Data
    public class Classes {
    
        private int id;
    
        private String name;
    
        private Teacher teacher;
    
        private List<Student> studentList;
    
    
    }

## 新建学生类Student ##

@Data
    public class Student {
    
        private int id;
    
        private String name;
    
    }

## 新建ClassesMapper.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.jourwon.mybatis.mapper.ClassesMapper">
    
        
        <select id="listClasses" 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.jourwon.mybatis.pojo.Classes" id="ClassesResultMap">
            
            <id property="id" column="c_id"/>
            <result property="name" column="c_name"/>
            <association property="teacher" javaType="com.jourwon.mybatis.pojo.Teacher">
                <id property="id" column="t_id"/>
                <result property="name" column="t_name"/>
            </association>
        </resultMap>
    
        
        <select id="listClasses2" 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.jourwon.mybatis.pojo.Classes" id="ClassesResultMap2">
            <id property="id" column="c_id"/>
            <result property="name" column="c_name"/>
            <association property="teacher" javaType="com.jourwon.mybatis.pojo.Teacher">
                <id property="id" column="t_id"/>
                <result property="name" column="t_name"/>
            </association>
            <collection property="studentList" ofType="com.jourwon.mybatis.pojo.Student">
                <id property="id" column="s_id"/>
                <result property="name" column="s_name"/>
            </collection>
        </resultMap>
    
    
    </mapper>

## Application类 ##

@MapperScan(basePackages = {"com.jourwon.mybatis.**.mapper"})
    @SpringBootApplication
    public class Application {
    
        public static void main(String[] args) {
            SpringApplication.run(Application.class, args);
        }
    
    }

## 测试类 ##

@RunWith(SpringRunner.class)
    @SpringBootTest
    public class ApplicationTests {
    
        @Autowired
        private ClassesMapper classesMapper;
    
        /** * Description: 测试班级与老师一对一关联关系 * * @author JourWon * @date 2019/9/24 23:18 * @param * @return void */
        @Test
        public void test01() {
    
            List<Classes> classes = classesMapper.listClasses(1);
            for (Classes aClass : classes) {
                System.out.println(classes);
            }
    
        }
    
        /** * Description: 测试班级与学生一对多关联关系 * * @author JourWon * @date 2019/9/24 23:19 * @param * @return void */
        @Test
        public void test02() {
    
            List<Classes> classes = classesMapper.listClasses2(1);
            for (Classes aClass : classes) {
                System.out.println(classes);
            }
    
        }
    
    }

## 测试结果 ##

test01()测试结果输出

==>  Preparing: select * from classes c,teacher t where c.teacher_id=t.t_id and c.c_id=? 
    ==> Parameters: 1(Integer)
    <==    Columns: c_id, c_name, teacher_id, t_id, t_name
    <==        Row: 1, 高三2班, 1, 1, 老师张三
    <==      Total: 1
    Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@140d1230]
    [Classes(id=1, name=高三2班, teacher=Teacher(id=1, name=老师张三), studentList=null)]

test02()测试结果输出

==>  Preparing: 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=? 
    ==> Parameters: 1(Integer)
    <==    Columns: c_id, c_name, teacher_id, t_id, t_name, s_id, s_name, class_id
    <==        Row: 1, 高三2班, 1, 1, 老师张三, 1, 学生小明, 1
    <==        Row: 1, 高三2班, 1, 1, 老师张三, 2, 学生小陈, 1
    <==      Total: 2
    Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@140d1230]
    [Classes(id=1, name=高三2班, teacher=Teacher(id=1, name=老师张三), studentList=[Student(id=1, name=学生小明), Student(id=2, name=学生小陈)])]

所以，一对一关联查询需要在resultMap里面配置association，一对多关联查询需要在resultMap里面配置collection节点

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly90aGlua3dvbi5ibG9nLmNzZG4ubmV0_size_16_color_FFFFFF_t_70]: /images/1592022957884.png