MyBatis环境配置与基础使用

学习工具

IDE: VS Code
VS Code插件：Extension Pack for Java
包管理：Maven

创建项目

打开vscode，点击创建java项目
选择使用maven创建项目
选择No Archetype
输入group id
输入artifact id
选择项目存储位置即可

MyBatis环境配置

配置文件名称自定义，但必须使用XML格式，如：mybatis-config.xml
MyBatis采用XML格式配置数据库环境信息
MyBatis环境配置标签<environment>
environment包含数据库驱动、URL、用户名与密码

<!--配置环境，不同的环境不同的id-->
<environment id="dev">
  <!--采用JDBC方式对数据库事务进行commit/rollback-->
  <transactonManager type="JDBC"></transactonManager>
  <!-- 采用连接池方式管理数据库连接-->
  <dataSource type="POOLED">
    <property name="driver" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql//localhost:3306/db" />
    <property name="username" value="root" />  
    <property name="password" value="root" />
  </dataSource>
</environment>

在前面创建的项目中配置依赖

<!-- 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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>cn.chengaofeng</groupId>
    <artifactId>mybatis</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>17</maven.compiler.source>
        <maven.compiler.target>17</maven.compiler.target>
    </properties>

    <dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.1</version>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.16</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.27</version>
        </dependency>
    </dependencies>
</project>

安装Mysql数据库

本文使用docker-compose来创建和管理本地数据库，需要安装docker和docker-compose，如果觉得麻烦，可以本地直接安装Mysql数据库
在根目录中新建docker-compose.yaml文件

version: "3.4"

services:
  db:
    image: mysql:8.0
    restart: always
    command:
      - --default-authentication-plugin=caching_sha2_password
      - --character-set-server=utf8mb4
      - --collation-server=utf8mb4_general_ci
      - --explicit_defaults_for_timestamp=true
    volumes:
    # 这里创建如果失败，要给当前目录下的data/mysql和data/logs目录777权限
      - ./data/mysql:/var/lib/mysql
      - ./data/logs:/var/lib/mysql/logs
    # 这里我的端口映射是3307:3306，因为我本地已经有一个mysql服务了
    ports:
      - "3307:3306"
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "127.0.0.1", "--silent"]
      interval: 3s
      retries: 5
      start_period: 30s
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: mybatis

使用docker-compose up -d 创建并启动，使用docker-compose down 卸载，使用docker-compose start 启动，使用docker-compose stop停止

项目中配置`mybatis-config.xml`

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3307/mybatis?useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
</configuration>

使用工具将测试数据导入

mybatis-demo.sql 下载并使用工具导入

代码中使用

SqlSessionFactory

SqlSessionFactory是MyBatis的核心对象
用于初如化MyBatis，创建SqlSession对象
保证SqlSessionFactory在应用中全局唯一

SqlSession

SqlSession是MyBatis操作数据库的核心对象
SqlSession使用JDBC方式与数据库交互
SqlSession对象提供了数据表CRUD对应方法

测试创建数据库连接

package cn.chengaofeng;

import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

public class MybatisTest {
    @Test
    public void testSqlSessionFactory() throws IOException {
        // 读取xml配置文件
        Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
        // 初始化SqlSessionFactory对象，同时解析配置文件
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        /**
         * 在Java中，try-with-resources 是一个自动管理资源的语句，它可以确保在语句结束时自动关闭实现了 AutoCloseable 接口的资源。
         * 这对于需要关闭的资源，如文件、数据库连接等，是非常有用的。
         * SqlSession 实现了 AutoCloseable 接口，因此可以使用 try-with-resources 来简化资源管理。
         */
        // SqlSession sqlSession = null;
        // try {
        //     // 获取SqlSession对象
        //     sqlSession = sqlSessionFactory.openSession();
        //     // 创建数据库连接测试
        //     Connection connection = sqlSession.getConnection();
        //     System.out.println(connection);
        // } catch (Exception e) {
        //     e.printStackTrace();
        // } finally {
        //     // 关闭SqlSession对象
        //     if (sqlSession != null) {
        //         // 如果type==="POOLED"，则会将连接放回连接池，否则会调用connection.close()方法关闭连接
        //         sqlSession.close();
        //     }
        // }
        // 使用try-with-resources简化资源管理
        try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
            // 创建数据库连接测试
            Connection connection = sqlSession.getConnection();
            System.out.println(connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

创建工具类保证SqlSessionFactory全局唯一

src/main/java/cn/chengaofeng/utils/MyBatisUtils.java

package cn.chengaofeng.utils;

import java.io.IOException;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class MyBatisUtils {
    private  static SqlSessionFactory sqlSessionFactory = null;

    static {
        try {
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("mybatis-config.xml"));
        } catch (IOException e) {
            // 初始化错误时，通过抛出异常通知调用者
            throw new ExceptionInInitializerError(e);
        }
    }

    public static SqlSession openSession() {
        return sqlSessionFactory.openSession();
    }

    public static void closeSession(SqlSession sqlSession) {
        if (sqlSession != null) {
            sqlSession.close();
        }
    }
}

src/test/java/cn/chengaofeng/MybatisTest.java

package cn.chengaofeng;

import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import cn.chengaofeng.utils.MyBatisUtils;

public class MybatisTest {
    @Test
    public void testMyBatisUtils() throws Exception {
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.openSession();
            Connection connection = sqlSession.getConnection();
            System.out.println(connection);
        } catch (Exception e) {
            throw e;
        } finally {
            MyBatisUtils.closeSession(sqlSession);
        }
    }
}

运行结果

MyBatis数据查询步骤

创建实体类（Entity）
src/main/java/cn/chengaofeng/entity/Goods.java

package cn.chengaofeng.entity;

public class Goods {
    private Integer goodsId;
    private String title;
    private String subTitle;
    private Float originalCost;
    private Float currentPrice;
    private Float discount;
    private Integer isFreeDelivery;
    private Integer categoryId;

    public Integer getGoodsId() {
        return goodsId;
    }

    public void setGoodsId(Integer goodsId) {
        this.goodsId = goodsId;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getSubTitle() {
        return subTitle;
    }

    public void setSubTitle(String subTitle) {
        this.subTitle = subTitle;
    }

    public Float getOriginalCost() {
        return originalCost;
    }

    public void setOriginalCost(Float originalCost) {
        this.originalCost = originalCost;
    }

    public Float getCurrentPrice() {
        return currentPrice;
    }

    public void setCurrentPrice(Float currentPrice) {
        this.currentPrice = currentPrice;
    }

    public Float getDiscount() {
        return discount;
    }

    public void setDiscount(Float discount) {
        this.discount = discount;
    }

    public Integer getIsFreeDelivery() {
        return isFreeDelivery;
    }

    public void setIsFreeDelivery(Integer isFreeDelivery) {
        this.isFreeDelivery = isFreeDelivery;
    }

    public Integer getCategoryId() {
        return categoryId;
    }

    public void setCategoryId(Integer categoryId) {
        this.categoryId = categoryId;
    }
}

创建Mapper XML，编写<select>SQL标签
src/main/resources/mappers/goods.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="cn.chengaofeng.mapper.GoodsMapper">
    <select id="selectGoods" resultType="cn.chengaofeng.entity.Goods">
        select * from t_goods order by goods_id desc limit 10
    </select>
</mapper>

新增<mapper>，开启驼峰命名映射
src/main/resources/mybatis-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3307/mybatis?useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="mappers/goods.xml"/>
    </mappers>
</configuration>

SqlSession执行select语句
src/test/java/cn/chengaofeng/MybatisTest.java

package cn.chengaofeng;

import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import cn.chengaofeng.entity.Goods;
import cn.chengaofeng.utils.MyBatisUtils;

public class MybatisTest {
    @Test
    public void testSelectAll () throws  Exception {
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.openSession();
            List<Goods> list = sqlSession.selectList("cn.chengaofeng.mapper.GoodsMapper.selectGoods");
            list.forEach(System.out::println);
        } catch (Exception e) {
            throw e;
        } finally {
            MyBatisUtils.closeSession(sqlSession);
        }
    }
}

MyBatis SQL传参

单个参数传参，使用parameterType指定参数的数据类型即可，SQL中#{value}提取参数
src/main/resources/mappers/goods.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="cn.chengaofeng.mapper.GoodsMapper">
    <select id="selectGoodsById" parameterType="Integer" resultType="cn.chengaofeng.entity.Goods">
        select * from t_goods where goods_id = #{value}
    </select>
</mapper>

src/test/java/cn/chengaofeng/MybatisTest.java

    @Test
    public void testGetGoodsById () throws Exception {
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.openSession();
            Goods goods = sqlSession.selectOne("cn.chengaofeng.mapper.GoodsMapper.selectGoodsById", 739);
            System.out.println(goods);
        } catch (Exception e) {
            throw e;
        } finally {
            MyBatisUtils.closeSession(sqlSession);
        }
    }

多个参数传参，使用parameterType指定Map接口，SQL中#{key}提取参数
src/main/resources/mappers/goods.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="cn.chengaofeng.mapper.GoodsMapper">
    <select id="selectGoodsBetweenPrice" parameterType="java.util.Map" resultType="cn.chengaofeng.entity.Goods">
        select * from t_goods where current_price between #{min} and #{max} 
        order by current_price limit 0,#{limit}
    </select>
</mapper>

src/test/java/cn/chengaofeng/MybatisTest.java

    @Test
    public  void  testGetGoodsByMap () throws Exception {
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.openSession();
            Map<String, Number> params = new HashMap<>();
            // 通过map传递参数, key为xml中的参数名，xml中写几个就得传几个
            params.put("min", 700);
            params.put("max", 800);
            params.put("limit", 10);
            List<Goods> goods = sqlSession.selectList("cn.chengaofeng.mapper.GoodsMapper.selectGoodsBetweenPrice", params);
            System.out.println(goods);
        } catch (Exception e) {
            throw e;
        } finally {
            MyBatisUtils.closeSession(sqlSession);
        }
    }

多表关联查询

通过resultMap做结果映射，Entity与表结构相对应，多表时使用DTO来映射
src/main/resources/mappers/goods.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="cn.chengaofeng.mapper.GoodsMapper">
    <!--结果映射-->
    <resultMap id="rmGoods" type="cn.chengaofeng.dto.GoodsDTO">
        <!--设置字段与属性映射-->
        <id property="goods.goodsId" column="goods_id"/>
        <result property="goods.title" column="title"/>
        <result property="goods.originalCost" column="original_cost"/>
        <result property="goods.discount" column="discount"/>
        <result property="goods.isFreeDelivery" column="is_free_delivery"/>
        <result property="goods.categoryId" column="category_id"/>
        <result property="category.categoryId" column="category_id"/>
        <result property="category.categoryName" column="category_name"/>
        <result property="category.parentId" column="parent_id"/>
        <result property="category.categoryLevel" column="category_level"/>
        <result property="category.categoryOrder" column="category_order"/>
        <result property="test" column="test"/>
    </resultMap>
    <select id="selectGoodsDTO" resultMap="rmGoods">
        select g.*, c.*, '1' as test from t_goods g, t_category c
        where g.category_id = c.category_id
    </select>
</mapper>

DTO(Data Transfer Object)：数据传输对象，用于封装业务数据，传输数据。
src/main/java/cn/chengaofeng/dto/GoodsDTO.java

package cn.chengaofeng.dto;

import cn.chengaofeng.entity.Category;
import cn.chengaofeng.entity.Goods;

public class GoodsDTO {
    private Goods goods = new Goods();
    private Category category = new Category();
    private String test;

    public Goods getGoods() {
        return goods;
    }

    public void setGoods(Goods goods) {
        this.goods = goods;
    }

    public Category getCategory() {
        return category;
    }

    public void setCategory(Category category) {
        this.category = category;
    }

    public String getTest() {
        return test;
    }

    public void setTest(String test) {
        this.test = test;
    }
}

src/test/java/cn/chengaofeng/MybatisTest.java

    @Test
    public void testGetGoodsDTO() throws Exception {
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.openSession();
            List<GoodsDTO> list = sqlSession.selectList("cn.chengaofeng.mapper.GoodsMapper.selectGoodsDTO");
            System.out.println(list);
        } catch (Exception e) {
            throw e;
        } finally {
            MyBatisUtils.closeSession(sqlSession);
        }
    }

MyBatis的插入操作

数据库事务

数据库事务是保证数据操作完整性的基础
客户端 -> 写操作记录到事务日志 -> commit -> 提交后写入数据表 -> 清空事务日志
客户端 -> 写操作增记录到事务日志 -> 失败回滚不会写入数据表

MyBatis写操作包含三种

1.插入 insert

src/main/resources/mappers/goods.xml

    <insert id="insertGoods" parameterType="cn.chengaofeng.entity.Goods">
        insert into t_goods(title, original_cost, current_price, discount, is_free_delivery, category_id)
        values(#{title}, #{originalCost}, #{currentPrice}, #{discount}, #{isFreeDelivery}, #{categoryId})
        <!--获取自增主键-->
        <selectKey keyProperty="goodsId" order="AFTER" resultType="Integer">
            <!-- last_insert_id 是mysql自带的函数，用于获取当前连接最后产生的id -->
            <!-- 它的范围是当前连接中，所以没有并发性问题，也不会出现主健的混乱 -->
            select last_insert_id()
        </selectKey>
    </insert>

src/test/java/cn/chengaofeng/MybatisTest.java

    @Test
    public void testInsertGood() throws Exception {
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.openSession();
            Goods goods = new Goods();
            goods.setTitle("测试商品");
            goods.setOriginalCost(100.0f);
            goods.setCurrentPrice(100.0f);
            goods.setDiscount(1.0f);
            goods.setIsFreeDelivery(1);
            goods.setCategoryId(1);
            sqlSession.insert("cn.chengaofeng.mapper.GoodsMapper.insertGoods", goods);
            sqlSession.commit();
            System.out.println(goods.getGoodsId());
        } catch (Exception e) {
            if (sqlSession != null) {
                sqlSession.rollback();
            }
            throw e;
        } finally {
            MyBatisUtils.closeSession(sqlSession);
        }
    }

2.更新 update

    <update id="updateGoods" parameterType="cn.chengaofeng.entity.Goods">
        update t_goods 
        set 
            title = #{title}, 
            original_cost = #{originalCost}, 
            current_price = #{currentPrice},
            discount = #{discount}, 
            is_free_delivery = #{isFreeDelivery}, 
            category_id = #{categoryId}
        where goods_id = #{goodsId}
    </update>

    @Test
    public void testUpdateGoods() throws Exception {
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.openSession();
            Goods goods = sqlSession.selectOne("cn.chengaofeng.mapper.GoodsMapper.selectGoodsById", 1742);
            goods.setTitle("更新测试商品2");
            sqlSession.update("cn.chengaofeng.mapper.GoodsMapper.updateGoods", goods);
            sqlSession.commit();
        } catch (Exception e) {
            if (sqlSession != null) {
                sqlSession.rollback();
            }
            throw e;
        } finally {
            MyBatisUtils.closeSession(sqlSession);
        }
    }

3.删除 delete

    <delete id="deleteGoods" parameterType="Integer">
        delete from t_goods where goods_id = #{value}
    </delete>

    @Test
    public void testDeleteGoods() throws Exception {
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.openSession();
            sqlSession.delete("cn.chengaofeng.mapper.GoodsMapper.deleteGoods", 1742);
            sqlSession.commit();
        } catch (Exception e) {
            if (sqlSession != null) {
                sqlSession.rollback();
            }
            throw e;
        } finally {
            MyBatisUtils.closeSession(sqlSession);
        }
    }

selectKey和useGeneratedKeys的区别

    <insert id="insertGoods" parameterType="cn.chengaofeng.entity.Goods" useGenaratedKeys="true" keyProperty="goodsId" keyColumn="goods_id">
        insert into t_goods(title, original_cost, current_price, discount, is_free_delivery, category_id)
        values(#{title}, #{originalCost}, #{currentPrice}, #{discount}, #{isFreeDelivery}, #{categoryId})
    </insert>

二者区别-显示与隐示

selectKey标签需要明确编写获取最新主键的SQL语句，不同数据库可能语句不相同；
useGeneratedKeys属性会自动根据驱动生成对应的SQL语句，不用手写，不同数据库自动适配。

二者区别-应用场景不同

selectKey适用于所有的关系型数据库
useGeneratedKeys只支持“自增主键”类型的数据库
Oracle只能用selectKey序列

总结

selectKey是通用方案，适用于所有数据库，但编写麻烦
useGeneratedKeys属性不通用，但使用简单，只要数据库支持，推荐使用

MyBatis预防SQL注入攻击

sql注入是指攻击者利用SQL漏洞，绕过系统约束，越权获取数据的攻击方式。一般就是文本拼接。

通过拼接条件，让查询条件失效，会将所有数据返回
select * from a where name = '' or 1=1 or 1=''

MyBatis两种传值方式

${} 文本替换，未经任何处理对SQL文本替换
#{} 预编译传值，使用预编译传值可以预防SQL注入，直接将整个输入当成文本并转义处理

在一些场景下需要用到${} 来进行SQL拼接，但需要注意的时，必须要确保外界是不能输入的

MyBatis工作流程

创建应用
新建Mybatis-config.xml：配置环境，声明mapper、配置全局配置项
使用SqlSessionFactory创建SqlSession
通过SqlSession执行mapper.xml定义的sql
写操作注意事务的回滚、提交
关闭SqlSession

分享

学习工具

创建项目

MyBatis环境配置

在前面创建的项目中配置依赖

安装Mysql数据库

项目中配置mybatis-config.xml

使用工具将测试数据导入

代码中使用

SqlSessionFactory

SqlSession

测试创建数据库连接

创建工具类保证SqlSessionFactory全局唯一

MyBatis数据查询步骤

MyBatis SQL传参

多表关联查询

MyBatis的插入操作

数据库事务

MyBatis写操作包含三种

1.插入 insert

2.更新 update

3.删除 delete

selectKey和useGeneratedKeys的区别

二者区别-显示与隐示

二者区别-应用场景不同

总结

MyBatis预防SQL注入攻击

MyBatis两种传值方式

MyBatis工作流程

评论

项目中配置`mybatis-config.xml`