Web后端开发---Mybatis【4】

良辰美景好时光

已于 2024-01-12 13:07:01 修改

阅读量797

点赞数 26

分类专栏： mybatis 文章标签： mybatis java

于 2023-12-12 09:00:04 首次发布

本文链接：https://blog.csdn.net/qq_45740503/article/details/134925634

版权

mybatis 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

Mybatis

在这里插入图片描述

1.Mybatis入门

1.1 快速入门

create table user
(
    id     int unsigned primary key auto_increment comment 'ID',
    name   varchar(100) comment '姓名',
    age    tinyint unsigned comment '年龄',
    gender tinyint unsigned comment '性别, 1:男, 2:女',
    phone  varchar(11) comment '手机号'
) comment '用户表';

insert into user(id, name, age, gender, phone)
VALUES (null, '白眉鹰王', 55, '1', '18800000000'),
       (null, '金毛狮王', 45, '1', '18800000001'),
       (null, '青翼蝠王', 38, '1', '18800000002'),
       (null, '紫衫龙王', 42, '2', '18800000003'),
       (null, '光明左使', 37, '1', '18800000004'),
       (null, '光明右使', 48, '1', '18800000005');

在这里插入图片描述

1.2 JDBC介绍

在这里插入图片描述

    @Test
    public void testJdbc() throws Exception {
        //1.注册驱动
        //2.获取连接对象
        //3.获取执行Sql的对象statement,执行Sql,返回结果
        //4.封装结果数据
        //5.释放资源

        //1.注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        //2.获取连接对象
        String url = "jdbc:mysql://localhost:3306/mybatis";
        String username = "root";
        String password = "123456";
        Connection connection = DriverManager.getConnection(url, username, password);

        //3.获取执行Sql的对象statement,执行Sql,返回结果
        String sql = "select * from user";
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sql);

        //4.封装结果数据
        List<User> userList = new ArrayList<>();
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            short age = resultSet.getShort("age");
            short gender = resultSet.getShort("gender");
            String phone = resultSet.getString("phone");

            User user = new User(id, name, age, gender, phone);
            userList.add(user);
        }

        //5.释放资源
        statement.close();
        connection.close();

        userList.stream().forEach(user -> {
            System.out.println(user);
        });

    }

在这里插入图片描述

1.3 数据库连接池

在这里插入图片描述

官方地址: https://github.com/alibaba/druid/tree/master/druid-spring-boot-starter

在这里插入图片描述

1.4 lombok

在这里插入图片描述

        <!--lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

2.Mybatis基础操作–增删改查

环境准备

在这里插入图片描述

-- 员工管理
create table emp
(
    id          int unsigned primary key auto_increment comment 'ID',
    username    varchar(20)      not null unique comment '用户名',
    password    varchar(32) default '123456' comment '密码',
    name        varchar(10)      not null comment '姓名',
    gender      tinyint unsigned not null comment '性别, 说明: 1 男, 2 女',
    image       varchar(300) comment '图像',
    job         tinyint unsigned comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管, 5 咨询师',
    entrydate   date comment '入职时间',
    dept_id     int unsigned comment '部门ID',
    create_time datetime         not null comment '创建时间',
    update_time datetime         not null comment '修改时间'
) comment '员工表';

INSERT INTO emp
(id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time)
VALUES (1, 'jinyong', '123456', '金庸', 1, '1.jpg', 4, '2000-01-01', 2, now(), now()),
       (2, 'zhangwuji', '123456', '张无忌', 1, '2.jpg', 2, '2015-01-01', 2, now(), now()),
       (3, 'yangxiao', '123456', '杨逍', 1, '3.jpg', 2, '2008-05-01', 2, now(), now()),
       (4, 'weiyixiao', '123456', '韦一笑', 1, '4.jpg', 2, '2007-01-01', 2, now(), now()),
       (5, 'changyuchun', '123456', '常遇春', 1, '5.jpg', 2, '2012-12-05', 2, now(), now()),
       (6, 'xiaozhao', '123456', '小昭', 2, '6.jpg', 3, '2013-09-05', 1, now(), now()),
       (7, 'jixiaofu', '123456', '纪晓芙', 2, '7.jpg', 1, '2005-08-01', 1, now(), now()),
       (8, 'zhouzhiruo', '123456', '周芷若', 2, '8.jpg', 1, '2014-11-09', 1, now(), now()),
       (9, 'dingminjun', '123456', '丁敏君', 2, '9.jpg', 1, '2011-03-11', 1, now(), now()),
       (10, 'zhaomin', '123456', '赵敏', 2, '10.jpg', 1, '2013-09-05', 1, now(), now()),
       (11, 'luzhangke', '123456', '鹿杖客', 1, '11.jpg', 5, '2007-02-01', 3, now(), now()),
       (12, 'hebiweng', '123456', '鹤笔翁', 1, '12.jpg', 5, '2008-08-18', 3, now(), now()),
       (13, 'fangdongbai', '123456', '方东白', 1, '13.jpg', 5, '2012-11-01', 3, now(), now()),
       (14, 'zhangsanfeng', '123456', '张三丰', 1, '14.jpg', 2, '2002-08-01', 2, now(), now()),
       (15, 'yulianzhou', '123456', '俞莲舟', 1, '15.jpg', 2, '2011-05-01', 2, now(), now()),
       (16, 'songyuanqiao', '123456', '宋远桥', 1, '16.jpg', 2, '2010-01-01', 2, now(), now()),
       (17, 'chenyouliang', '123456', '陈友谅', 1, '17.jpg', NULL, '2015-03-21', NULL, now(), now());

在这里插入图片描述

删除

在这里插入图片描述

预编译SQL–防止SQL注入–#{} --安全性更高

在这里插入图片描述

新增–主键返回

在这里插入图片描述

更新

在这里插入图片描述

根据ID查询

在这里插入图片描述

条件查询

在这里插入图片描述

数据准备

-- 员工管理
create table emp
(
    id          int unsigned primary key auto_increment comment 'ID',
    username    varchar(20)      not null unique comment '用户名',
    password    varchar(32) default '123456' comment '密码',
    name        varchar(10)      not null comment '姓名',
    gender      tinyint unsigned not null comment '性别, 说明: 1 男, 2 女',
    image       varchar(300) comment '图像',
    job         tinyint unsigned comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管, 5 咨询师',
    entrydate   date comment '入职时间',
    dept_id     int unsigned comment '部门ID',
    create_time datetime         not null comment '创建时间',
    update_time datetime         not null comment '修改时间'
) comment '员工表';

INSERT INTO emp
(id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time)
VALUES (1, 'jinyong', '123456', '金庸', 1, '1.jpg', 4, '2000-01-01', 2, now(), now()),
       (2, 'zhangwuji', '123456', '张无忌', 1, '2.jpg', 2, '2015-01-01', 2, now(), now()),
       (3, 'yangxiao', '123456', '杨逍', 1, '3.jpg', 2, '2008-05-01', 2, now(), now()),
       (4, 'weiyixiao', '123456', '韦一笑', 1, '4.jpg', 2, '2007-01-01', 2, now(), now()),
       (5, 'changyuchun', '123456', '常遇春', 1, '5.jpg', 2, '2012-12-05', 2, now(), now()),
       (6, 'xiaozhao', '123456', '小昭', 2, '6.jpg', 3, '2013-09-05', 1, now(), now()),
       (7, 'jixiaofu', '123456', '纪晓芙', 2, '7.jpg', 1, '2005-08-01', 1, now(), now()),
       (8, 'zhouzhiruo', '123456', '周芷若', 2, '8.jpg', 1, '2014-11-09', 1, now(), now()),
       (9, 'dingminjun', '123456', '丁敏君', 2, '9.jpg', 1, '2011-03-11', 1, now(), now()),
       (10, 'zhaomin', '123456', '赵敏', 2, '10.jpg', 1, '2013-09-05', 1, now(), now()),
       (11, 'luzhangke', '123456', '鹿杖客', 1, '11.jpg', 5, '2007-02-01', 3, now(), now()),
       (12, 'hebiweng', '123456', '鹤笔翁', 1, '12.jpg', 5, '2008-08-18', 3, now(), now()),
       (13, 'fangdongbai', '123456', '方东白', 1, '13.jpg', 5, '2012-11-01', 3, now(), now()),
       (14, 'zhangsanfeng', '123456', '张三丰', 1, '14.jpg', 2, '2002-08-01', 2, now(), now()),
       (15, 'yulianzhou', '123456', '俞莲舟', 1, '15.jpg', 2, '2011-05-01', 2, now(), now()),
       (16, 'songyuanqiao', '123456', '宋远桥', 1, '16.jpg', 2, '2010-01-01', 2, now(), now()),
       (17, 'chenyouliang', '123456', '陈友谅', 1, '17.jpg', NULL, '2015-03-21', NULL, now(), now());

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>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.5</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.itcast</groupId>
    <artifactId>springboot-mybatis-crud</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>springboot-mybatis-crud</name>
    <description>springboot-mybatis-crud</description>
    <properties>
        <java.version>11</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <!--druid连接池-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.8</version>
        </dependency>

        <!--lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

application.properties

#配置数据库的连接信息 - 四要素
#驱动类名称
spring.datasource.druid.driver-class-name=com.mysql.cj.jdbc.Driver
#数据库连接的url
spring.datasource.druid.url=jdbc:mysql://localhost:3306/mybatis
#连接数据库的用户名
spring.datasource.druid.username=root
#连接数据库的密码
spring.datasource.druid.password=123456

#配置mybatis输出日志的位置,输出控制台
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

#开启mybatis的驼峰命名自动映射开关 a_cloumn ----->aCloumn
mybatis.configuration.map-underscore-to-camel-case=true

Emp.java

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.time.LocalDate;
import java.time.LocalDateTime;

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Emp {
    private Integer id;  //ID
    private String username; //用户名
    private String password; //密码
    private String name;//姓名
    private short gender;//性别,1 男,2 女
    private String image;//图像url
    private short job;//职位,说明:1 班主任,2 讲师,3 学生主管,4 教研主管,5 咨询师
    private LocalDate entrydate;//入职日期
    private Integer deptId;//部门ID
    private LocalDateTime createTime;//创建时间
    private LocalDateTime updateTime;//修改时间
}

EmpMapper.java

package com.itcast.mapper;

import com.itcast.pojo.Emp;
import org.apache.ibatis.annotations.*;

import java.time.LocalDate;
import java.util.List;

@Mapper
public interface EmpMapper {

    //根据ID删除数据
    //delete from emp where id = 17
    @Delete("delete from emp where id = #{id}")
    public void deleteEmp(Integer id);
    //public int deleteEmp(Integer id);

    //新增员工
    @Options(useGeneratedKeys = true, keyProperty = "id") //会自动将生成的主键值,赋值给emp对象的id属性
    @Insert("insert into emp(username, name, gender, image, job, entrydate, dept_id, create_time, update_time)" +
            "VALUES (#{username}, #{name}, #{gender}, #{image}, #{job}, #{entrydate}, #{deptId}, #{createTime}, #{updateTime})")
    public void insert(Emp emp);

    //更新员工
    @Update("update emp set username = #{username},name = #{name},gender = #{gender},image = #{image}," +
            "               job = #{job},entrydate = #{entrydate},dept_id = #{deptId},update_time = #{updateTime} where id = #{id}")
    public void update(Emp emp);

    //根据ID查询员工
    @Select("select * from emp where id = #{id}")
    public Emp getById1(Integer id);

    //方案一:给字段起别名,让别名与实体类属性一致
    //根据ID查询员工
    @Select("select id,username,password,name, gender, image, job, entrydate, " +
            "dept_id deptId, create_time createTime, update_time updateTime from emp where id = #{id}")
    public Emp getById2(Integer id);

    //方案二:通过@Result,@Result注解手动映射封装
    @Results({
            @Result(column = "dept_id", property = "deptId"),
            @Result(column = "create_time", property = "createTime"),
            @Result(column = "update_time", property = "updateTime")
    })
    @Select("select * from emp where id = #{id}")
    public Emp getById3(Integer id);

    //方案三:开启mybatis的驼峰命名自动映射开关 -- a_cloumn ----->aCloumn
    @Select("select * from emp where id = #{id}")
    public Emp getById(Integer id);

    //条件查询员工信息
    @Select("select * from emp where name like '%${name}%' and gender = #{gender} " +
            "and entrydate between #{begin} and #{end} order by update_time desc")
    public List<Emp> list1(String name, Short gender, LocalDate begin, LocalDate end);

    //条件查询员工信息
    @Select("select * from emp where name like concat('%',#{name},'%') and gender = #{gender} " +
            "and entrydate between #{begin} and #{end} order by update_time desc")
    public List<Emp> list(String name, Short gender, LocalDate begin, LocalDate end);
}

SpringbootMybatisCrudApplicationTests.java

import com.itcast.mapper.EmpMapper;
import com.itcast.pojo.Emp;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;

@SpringBootTest
class SpringbootMybatisCrudApplicationTests {

    @Autowired
    private EmpMapper empMapper;

    @Test
    public void testDelete(){
//        int delete = empMapper.deleteEmp(16);
//        System.out.println(delete);
        empMapper.deleteEmp(18);
    }

    @Test
    public void testInsert(){
        //构造员工对象
        Emp emp = new Emp();
        emp.setUsername("Tom");
        emp.setName("汤姆");
        emp.setImage("1.jpg");
        emp.setGender((short) 1);
        emp.setJob((short) 1);
        emp.setEntrydate(LocalDate.of(2000, 1, 1));
        emp.setCreateTime(LocalDateTime.now());
        emp.setUpdateTime(LocalDateTime.now());
        emp.setDeptId(1);

        //执行新增员工信息操作
        empMapper.insert(emp);
        System.out.println(emp.getId());
    }

    @Test
    public void testUpdate(){
        //构造员工对象
        Emp emp = new Emp();
        emp.setId(19);
        emp.setUsername("Tom1");
        emp.setName("汤姆1");
        emp.setImage("1.jpg");
        emp.setGender((short) 1);
        emp.setJob((short) 1);
        emp.setEntrydate(LocalDate.of(2000, 1, 1));
        emp.setUpdateTime(LocalDateTime.now());
        emp.setDeptId(1);

        //执行更新员工信息操作
        empMapper.update(emp);
    }

    //根据ID查询员工
    @Test
    public void testGetById(){
        Emp emp = empMapper.getById(19);
        System.out.println(emp);
    }

    //根据条件查询员工
    @Test
    public void testList(){
        List<Emp> empList =empMapper.list("张",(short) 1,LocalDate.of(2010,1,1),LocalDate.of(2020,1,1));
        System.out.println(empList);
    }

}

3.XML映射文件

在这里插入图片描述

4.Mybatis动态SQL

在这里插入图片描述

Mybatis动态SQL–if

在这里插入图片描述

<?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.itcast.mapper.EmpMapper">
    <!--resultType:单条记录所封装的类型-->
    <select id="list" resultType="com.itcast.pojo.Emp">
        select *
        from emp
        <where>
            <if test="name!=null">
                name like concat('%', #{name}, '%')
            </if>
            <if test="gender!=null">
                and gender = #{gender}
            </if>
            <if test="begin!=null and end!=null">
                and entrydate between #{begin} and #{end}
            </if>
        </where>
        order by update_time desc
    </select>
</mapper>

@Mapper
public interface EmpMapper {
    //动态条件查询员工信息
    public List<Emp> list(String name, Short gender, LocalDate begin, LocalDate end);
}

    //根据条件查询员工
    @Test
    public void testList(){
        //List<Emp> empList =empMapper.list("张",(short) 1,LocalDate.of(2010,1,1),LocalDate.of(2020,1,1));
        List<Emp> empList =empMapper.list(null,(short) 1,LocalDate.of(2010,1,1),LocalDate.of(2020,1,1));
        empList.stream().forEach(emp -> {
            System.out.println(emp);
        });
    }

在这里插入图片描述

    <!--动态更新员工信息-->
    <update id="update">
        update emp
        <set>
            <if test="username!=null">username = #{username},</if>
            <if test="name!=null">name = #{name},</if>
            <if test="gender!=null">gender = #{gender},</if>
            <if test="image!=null">image = #{image},</if>
            <if test="job!=null">job = #{job},</if>
            <if test="entrydate!=null">entrydate = #{entrydate},</if>
            <if test="deptId!=null">dept_id = #{deptId},</if>
            <if test="updateTime!=null">update_time = #{updateTime}</if>
        </set>
        where id = #{id}
    </update>

    //动态更新员工
    public void update(Emp emp);

    //动态更新员工–更新ID为19的员工 username更新为Tom111,name更新为汤姆111,gender更新为2
    @Test
    public void testUpdate(){
        //构造员工对象
        Emp emp = new Emp();
        emp.setId(19);
        emp.setUsername("Tom222");
        emp.setName("汤姆222");
        //emp.setImage("1.jpg");
        emp.setGender((short) 2);
        emp.setJob((short) 1);
        emp.setEntrydate(LocalDate.of(2000, 1, 1));
        emp.setUpdateTime(LocalDateTime.now());
        //emp.setDeptId(1);

        //执行更新员工信息操作
        empMapper.update(emp);
    }

在这里插入图片描述

Mybatis动态SQL–foreach

在这里插入图片描述

    <!--批量删除员工 (18,19,20)-->
    <!--
        collection:遍历的集合
        item:遍历出来的元素
        separator:分隔符
        open:遍历开始前拼接的SQL片段
        close:遍历结束后拼接的SQL片段
    -->
    <delete id="deleteByIds">
        delete from emp where id in
        <foreach collection="ids" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
    </delete>

    //批量删除员工
    public void deleteByIds(List<Integer> ids);

    //批量删除员工 - 13,14,15
    @Test
    public void testDeleteByIds(){
        List<Integer> ids = Arrays.asList(13,14,15);
        empMapper.deleteByIds(ids);
    }

Mybatis动态SQL–sql\include

在这里插入图片描述

    <sql id="commonselect">
        select id,
               username,
               name,
               gender,
               image,
               job,
               entrydate,
               dept_id,
               create_time,
               update_time
        from emp
    </sql>

    <!--resultType:单条记录所封装的类型-->
    <select id="list" resultType="com.itcast.pojo.Emp">
        <include refid="commonselect"/>
        <where>
            <if test="name!=null">
                name like concat('%', #{name}, '%')
            </if>
            <if test="gender!=null">
                and gender = #{gender}
            </if>
            <if test="begin!=null and end!=null">
                and entrydate between #{begin} and #{end}
            </if>
        </where>
        order by update_time desc
    </select>

    //动态条件查询员工信息
    public List<Emp> list(String name, Short gender, LocalDate begin, LocalDate end);

    //根据条件查询员工
    @Test
    public void testList(){
        //List<Emp> empList =empMapper.list("张",(short) 1,LocalDate.of(2010,1,1),LocalDate.of(2020,1,1));
        List<Emp> empList =empMapper.list(null,(short) 1,LocalDate.of(2010,1,1),LocalDate.of(2020,1,1));
        empList.stream().forEach(emp -> {
            System.out.println(emp);
        });
    }

在这里插入图片描述

总结

mybatis动态Sql语句学习

if:用于判断条件是否成立。使用test属性进行条件判断，如果条件为true，则拼接SQL。

where:where元素只会在子元素有内容的情况下才插入where子句。而且会自动去除子句的开头的AND或OR.

set:动态地在行首插入set关键字，并会删掉额外的逗号。(用在update语句中)

foreach:
    collection:集合名称
    item:集合遍历出来的元素/项
    separator:每一次遍历使用的分隔符
    open:遍历开始前拼接的片段
    close:遍历结束后拼接的片段

sql:定义可重用的SQL片段。

include:通过属性refid，指定包含的sql片段。