1、准备工作
1.1、导入依赖（mybatis启动器和mysql驱动）
<!-- mybatis启动器 --> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.3.2</version> </dependency> <!-- mysql驱动 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>${mysql.version}</version> </dependency>
1.2.、yml中配置MyBatis的配置
server: port: 8888 spring: application: name: tanhua-test-xml datasource: driver-class-name: com.mysql.jdbc.Driver url: jdbc:mysql://127.0.0.1:3306/test2?useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true&useSSL=false username: root password: 123456 mybatis: mapper-locations: classpath:mapper/*.xml type-aliases-package: com.czxy.domain configuration: map-underscore-to-camel-case: true logging: level: com.czxy.mapper: debug
3、编写domain、mapper、xml
@Mapper作用与 Mapper 用于告诉sprigng框架此接口的实现类由Mybatis负责创建，并将其实现类对象存储到spring容器中。
编写xml namespace对应你的mapper
2、数据库准备
2.1、类型表
CREATE TABLE `tb_teacher_type` ( `type_id` INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `content` VARCHAR(50) COMMENT '类型' ); INSERT INTO `tb_teacher_type`(`type_id`,`content`) VALUES (1,'授课老师'); INSERT INTO `tb_teacher_type`(`type_id`,`content`) VALUES (2,'助理老师'); INSERT INTO `tb_teacher_type`(`type_id`,`content`) VALUES (3,'辅导员老师');
2.2、老师表
CREATE TABLE `tb_teacher` ( `tid` INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `tname` VARCHAR(50) DEFAULT NULL COMMENT '老师姓名', `type` INT(11) DEFAULT NULL COMMENT '老师类型ID' ); INSERT INTO `tb_teacher`(`tid`,`tname`,`type`) VALUES (1,'梁桐老师',1); INSERT INTO `tb_teacher`(`tid`,`tname`,`type`) VALUES (2,'马坤老师',2); INSERT INTO `tb_teacher`(`tid`,`tname`,`type`) VALUES (3,'仲燕老师',3); INSERT INTO `tb_teacher`(`tid`,`tname`,`type`) VALUES (4,'袁新奇老师',1); INSERT INTO `tb_teacher`(`tid`,`tname`,`type`) VALUES (5,'任林达老师',2); INSERT INTO `tb_teacher`(`tid`,`tname`,`type`) VALUES (6,'王珊珊老师',3);
2.3、班级表
CREATE TABLE `tb_class` ( `cid` INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, `cname` VARCHAR(50) DEFAULT NULL COMMENT '班级名称', `teacher1_id` INT(11) DEFAULT NULL COMMENT '授课老师', `teacher2_id` INT(11) DEFAULT NULL COMMENT '助理老师', `teacher3_id` INT(11) DEFAULT NULL COMMENT '辅导员老师' ); INSERT INTO `tb_class`(`cid`,`cname`,`teacher1_id`,`teacher2_id`,`teacher3_id`) VALUES (1,'Java56',1,2,3); INSERT INTO `tb_class`(`cid`,`cname`,`teacher1_id`,`teacher2_id`,`teacher3_id`) VALUES (2,'Java78',1,2,3); INSERT INTO `tb_class`(`cid`,`cname`,`teacher1_id`,`teacher2_id`,`teacher3_id`) VALUES (3,'Java12',4,5,6); INSERT INTO `tb_class`(`cid`,`cname`,`teacher1_id`,`teacher2_id`,`teacher3_id`) VALUES (4,'Java34',4,5,6);
2.4、城市表
CREATE TABLE tb_city( c_id VARCHAR(32) PRIMARY KEY COMMENT '城市ID', city_name VARCHAR(20) COMMENT '城市名称' , parent_id VARCHAR(32) COMMENT '父ID' ); INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('320000','江苏省','0'); INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('140000','山西省','0'); INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('130000','河北省','0'); INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('320100','南京市','320000'); INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('320102','玄武区','320100'); INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('320103','白下区','320100'); INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('321300','宿迁市','320000'); INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('321322','沭阳县','321300'); INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('321323','泗阳县','321300'); INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('140100','太原市','140000'); INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('140106','迎泽区','140100'); INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('140108','尖草坪区','140100'); INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('140800','运城市','140000'); INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('140823','闻喜县','140800'); INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('140828','夏 县','140800'); INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('130100','石家庄市','130000'); INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('130127','高邑县','130100'); INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('130185','鹿泉市','130100'); INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('131000','廊坊市','130000'); INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('131003','广阳区','131000'); INSERT INTO tb_city(c_id,city_name,parent_id) VALUES('131022','固安县','131000');
2.5、学生表
CREATE TABLE `tb_student` ( `s_id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学生ID', `sname` VARCHAR(50) DEFAULT NULL COMMENT '姓名', `age` INT(11) DEFAULT NULL COMMENT '年龄', `birthday` DATETIME DEFAULT NULL COMMENT '生日', `gender` CHAR(1) DEFAULT NULL COMMENT '性别', `c_id` INT DEFAULT NULL COMMENT '班级ID', `province_id` VARCHAR(32) DEFAULT NULL COMMENT '省ID', `city_id` VARCHAR(32) DEFAULT NULL COMMENT '市ID', `county_id` VARCHAR(32) DEFAULT NULL COMMENT '县ID' ); INSERT INTO `tb_student`(`s_id`,`sname`,`age`,`birthday`,`gender`,`c_id`,`province_id`,`city_id`,`county_id`) VALUES (1,'赵三33',21,'2001-01-17 00:00:00','1',1,'320000','321300','321322'); INSERT INTO `tb_student`(`s_id`,`sname`,`age`,`birthday`,`gender`,`c_id`,`province_id`,`city_id`,`county_id`) VALUES (2,'钱四444',1900,'2001-05-16 00:00:00','1',2,'320000','321300','321322'); INSERT INTO `tb_student`(`s_id`,`sname`,`age`,`birthday`,`gender`,`c_id`,`province_id`,`city_id`,`county_id`) VALUES (3,'孙五56',189,'2022-03-15 00:00:00','0',1,'320000','321300','321322'); INSERT INTO `tb_student`(`s_id`,`sname`,`age`,`birthday`,`gender`,`c_id`,`province_id`,`city_id`,`county_id`) VALUES (4,'张三',20,'2020-12-21 00:00:00','0',2,'320000','321300','321322'); INSERT INTO `tb_student`(`s_id`,`sname`,`age`,`birthday`,`gender`,`c_id`,`province_id`,`city_id`,`county_id`) VALUES (5,'xxx',18,'2020-12-21 00:00:00','0',2,'140000','140800','140823'); INSERT INTO `tb_student`(`s_id`,`sname`,`age`,`birthday`,`gender`,`c_id`,`province_id`,`city_id`,`county_id`) VALUES (6,'123',18,'2020-11-01 00:00:00','0',3,'130000','130100','130127'); INSERT INTO `tb_student`(`s_id`,`sname`,`age`,`birthday`,`gender`,`c_id`,`province_id`,`city_id`,`county_id`) VALUES (7,'xx',18,'2020-11-02 00:00:00','0',1,'130000','131000','131003');
2.6、课程表
CREATE TABLE `tb_course` ( `c_id` INT NOT NULL PRIMARY KEY COMMENT '课程ID', `cname` VARCHAR(50) DEFAULT NULL COMMENT '课程名称', `desc` VARCHAR(100) DEFAULT NULL COMMENT '课程描述' ); INSERT INTO `tb_course`(`c_id`,`cname`,`desc`) VALUES (1,'Java基础','JavaSE所有课程'); INSERT INTO `tb_course`(`c_id`,`cname`,`desc`) VALUES (2,'JavaWeb','Java Web 所有课程'); INSERT INTO `tb_course`(`c_id`,`cname`,`desc`) VALUES (3,'SSM','Spring Mvc、Spring、MyBatis所有课程');
2.7、中间表
CREATE TABLE `tb_student_course` ( `s_id` INT NOT NULL COMMENT '学生ID', `c_id` INT NOT NULL COMMENT '课程ID', `score` DOUBLE DEFAULT NULL, PRIMARY KEY (`s_id`,`c_id`) ); INSERT INTO tb_student_course(s_id,c_id,score) VALUES(1,1,100); INSERT INTO tb_student_course(s_id,c_id,score) VALUES(1,2,95); INSERT INTO tb_student_course(s_id,c_id,score) VALUES(1,3,NULL); INSERT INTO tb_student_course(s_id,c_id,score) VALUES(2,1,100); INSERT INTO tb_student_course(s_id,c_id,score) VALUES(2,2,95); INSERT INTO tb_student_course(s_id,c_id,score) VALUES(2,3,100); INSERT INTO tb_student_course(s_id,c_id,score) VALUES(3,1,80); INSERT INTO tb_student_course(s_id,c_id,score) VALUES(3,2,NULL); INSERT INTO tb_student_course(s_id,c_id,score) VALUES(3,3,90);
3、练习题
3.1、练习1
- 查询所有的老师，同时
- 查询老师对应的类型
- 查询老师所教授的课程
TeacherMapper：
@Mapper public interface TeacherMapper { /** * 查询所有 * @return */ List<Teacher> selectAll(); }
TeacherMapper.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.czxy.mapper.TeacherMapper"> <resultMap id="teacherResultMap" type="teacher"> <result property="type" column="type"></result> <result property="tid" column="tid"></result> <association property="teacherType" column="type" select="com.czxy.mapper.TeacherTypeMapper.selectById"></association> <collection property="courseList" column="tid" select="com.czxy.mapper.CourseMapper.selectCourseIdTeacher"></collection> </resultMap> <select id="selectAll" resultMap="teacherResultMap"> select * from tb_teacher </select> </mapper>
TeacherTypeMapper.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.czxy.mapper.TeacherTypeMapper"> <select id="selectById" resultType="teacherType"> select * from tb_teacher_type where type_id = #{typeId} </select> </mapper>
CourseMapper.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.czxy.mapper.CourseMapper"> <resultMap id="courseResultMap" type="course"> <result property = "cid" column = "c_id"></result> <association property="total" column="c_id" select="com.czxy.mapper.StudentMapper.countByCourseId"></association> <collection property="studentList" column="c_id" select="com.czxy.mapper.StudentMapper.selectAllByCourseId"></collection> </resultMap> <select id="selectCourseIdTeacher" resultMap="courseResultMap"> select c.* from tb_course c inner join tb_course_teacher ct on ct.c_id = c.c_id where ct.t_id = #{teacherId} </select> </mapper>
