【MyBatis】XML版快速入门——练习题

妖狐艹你老母 2024-03-22 12:16 32阅读 0赞

**目录**

1、准备工作

1.1、导入依赖（mybatis启动器和mysql驱动）

1.2.、yml中配置MyBatis的配置

3、编写domain、mapper、xml

2、数据库准备

2.1、类型表

2.2、老师表

2.3、班级表

2.4、城市表

2.5、学生表

2.6、课程表

2.7、中间表

3、练习题

3.1、练习1

3.2、练习2

3.3、练习3

3.4、练习4

3.5、练习5

3.6、优化

4、总结

## 1、准备工作 ##

### 1.1、导入依赖（mybatis启动器和mysql驱动） ###

<dependency>
                <groupId>org.mybatis.spring.boot</groupId>
                <artifactId>mybatis-spring-boot-starter</artifactId>
                <version>1.3.2</version>
            </dependency>
    
            
            <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

![b161d2f0ed4e4cd99d12778897eeb939.png][]

### 3、编写domain、mapper、xml ###

![880617f9d64b4eb39284e5a0636d9419.png][]

@Mapper作用与 Mapper 用于告诉sprigng框架此接口的实现类由Mybatis负责创建，并将其实现类对象存储到spring容器中。

![d996afe13c214b29a7c64714ce617ed7.png][]编写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

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

### 3.2、练习2 ###

*  查询所有的班级，同时
    
     *  查询班级对应的老师
     *  查询班级学生的总人数
     *  查询班级包含的学生详情

CourseMapper

@Mapper
    public interface CourseMapper {
    
    
        public List<Course> selectAll();
    
    }

CourseMapper.xml

StudentMapper.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.StudentMapper">
    
        <select id="countByCourseId" resultType="long">
            select count(1) from tb_student s
                inner join tb_student_course sc on s.s_id = sc.s_id
                where sc.c_id = #{courseId}
        </select>
    
        <select id="selectAllByCourseId" resultType="student">
            select * from tb_student s
                inner join tb_student_course sc on s.s_id = sc.s_id
                where sc.c_id = #{courseId}
        </select>
        
    </mapper>

### 3.3、练习3 ###

*  查询所有的学生，同时
    
     *  查询学生所属班级
     *  查询学生省市县信息
     *  查询学生所学课程

StudentMapper

@Mapper
    public interface StudentMapper {
       
        /**
         * 查询所有
         * @return
         */
        List<Student> selectAll();
    }

StudentMapper.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.StudentMapper">
    
        <resultMap id="studentResultMap" type="student">
            <result property="sid" column="s_id"></result>
            <result property="cid" column="c_id"></result>
            <result property="provinceId" column="province_id"></result>
            <result property="cityId" column="city_id"></result>
            <result property="countyId" column="county_id"></result>
    
            <association property="classes" column="c_id" select="com.czxy.mapper.ClassesMapper.selectById"></association>
    
            <association property="province" column="province_id" select="com.czxy.mapper.CityMapper.selectById"></association>
            <association property="city" column="city_id" select="com.czxy.mapper.CityMapper.selectById"></association>
            <association property="county" column="county_id" select="com.czxy.mapper.CityMapper.selectById"></association>
    
            <collection property="courseList" column="s_id" select="com.czxy.mapper.CourseMapper.selectAllByStudentId"></collection>
        </resultMap>
    
        <select id="selectAll" resultMap="studentResultMap">
            select * from tb_student
        </select>
    
    </mapper>

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.czxy.mapper.ClassesMapper">
    
        <select id="selectById" resultType="classes">
            select * from tb_class where cid = #{classesId}
        </select>
    
    </mapper>

CityMapper.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.CityMapper">
    
        <select id="selectById" resultType="city">
            select * from tb_city where c_id = #{cid}
        </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">
    
        <select id="selectAllByStudentId" resultType="course">
            select c.* from tb_course c
            inner join tb_student_course sc on c.c_id = sc.c_id
            where sc.s_id = #{studentId}
        </select>
    
    </mapper>

### 3.4、练习4 ###

*  查询所有课程，同时
    
     *  查询学生学习该课程总人数
     *  查询学生学习该课程详情

CourseMapper

@Mapper
    public interface CourseMapper {
    
        public List<Course> selectAll();
    }

CourseMapper.xml

StudentMapper.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.StudentMapper">
    
        <select id="countByCourseId" resultType="long">
            select count(1) from tb_student s
                inner join tb_student_course sc on s.s_id = sc.s_id
                where sc.c_id = #{courseId}
        </select>
    
        <select id="selectAllByCourseId" resultType="student">
            select * from tb_student s
                inner join tb_student_course sc on s.s_id = sc.s_id
                where sc.c_id = #{courseId}
        </select>
        
    </mapper>

### 3.5、练习5 ###

*  创建或修改表，完成老师与课程关联关系
    
     *  不同的老师可以教授不同的课程
     *  不同的课程可以由不同的老师教授

### 3.6、优化 ###

*  优化：2.1、2.2、2.3、2.4，该`查询所有`为`指定若干`

## `4、总结` ##

**@Param作用**：给参数命名 sql语句使用\#\{\}或$\{\}取出

**mybatis.mapper-locations:classpath:mapper/\*.xml**：扫描mapper mapper接口与mapper xml

才能绑定

**mybatis.type-aliases-package**：扫描自定义POJO 映射不用全类名 可以直接写类名

**mybatis.configuration.map-underscore-to-camel-case**：开启自动驼峰命名转换

**<ResultMap>**

ResultMap：自定义结果集映射规则，自定义某个[JavaBean][]的封装规则。

type：返回的类型

id：指定主键列的封装规则（主键也可以使用result来定义）

property：指定对应的javaBean属性

column：指定哪一列

**<association>**

association：配置一对一关联

property：指定对应的javaBean属性

column：指定哪一列

select：dao层进行查询的方法

**<collection>**

collection：一对多关联

property：指定对应的javaBean属性

column：指定哪一列

select：dao层进行查询的方法

**select、update、delete的parameterType**

传递参数的类型

**<foreach>**

foeach是mybatis的动态标签 一般用来批量操作

collection：传入的集合 【必选】

item：集合中元素迭代时的别名 【必选】

open：代码开始符号 一般时(和close)合用 常用in(),values()时 【可选】

separator：元素之间的分隔符 一般用，【可选】

close：代码关闭符号 ) 【可选】

[b161d2f0ed4e4cd99d12778897eeb939.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/2fc1e9ddca2d40b395783ccfeb195219.png
[880617f9d64b4eb39284e5a0636d9419.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/ad98f361b9fe4a6a86f23db6c11b76cd.png
[d996afe13c214b29a7c64714ce617ed7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/9e2c3f4242df4ebdbfb3358b4e330692.png
[JavaBean]: https://so.csdn.net/so/search?q=JavaBean&spm=1001.2101.3001.7020