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

您所在的位置：网站首页 › mysql一对一一对多 › MyBatis实现一对一，一对多关联查询

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

2024-01-31 12:30| 来源: 网络整理| 查看: 265

文章目录准备数据库表结构与数据新建Spring Boot项目在pom.xml文件中添加如下依赖在application.yml文件中进行数据库和mybatis配置新建老师类Teacher新建班级类Classes新建学生类Student新建ClassesMapper.xml映射文件Application类测试类测试结果

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项目

在这里插入图片描述

在pom.xml文件中添加如下依赖 org.springframework.boot spring-boot-starter-web org.mybatis.spring.boot mybatis-spring-boot-starter 2.1.0 mysql mysql-connector-java runtime org.projectlombok lombok true org.springframework.boot spring-boot-starter-test test 在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 studentList; } 新建学生类Student @Data public class Student { private int id; private String name; } 新建ClassesMapper.xml映射文件 select * from classes c,teacher t where c.teacher_id=t.t_id and c.c_id=#{id} 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} 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 = 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 = 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)

【本文地址】

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

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

今日新闻

推荐新闻