MyBatis基础入门

MyBatis基础入门

软件开发中的框架

• 框架是可被应用开发者定制的应用骨架
• 框架是一种规则，保证开发者遵循相同的方式开发程序
• 框架提倡“不要重复造轮子”，对基础功能进行封装

框架的优点

• 极大提高了开发效率
• 统一的编码规则，有利于团队管理
• 灵活配置的应用，拥有更好的维护性

SSM开发框架

• S -> Spring
• S -> Spring MVC
• M -> MyBatis

什么是MyBatis

• MyBatis是优秀的持久层框架
• MyBatis使用XML将SQL与程序解耦，便于维护
• MyBatis学习简单，执行高效，是JDBC的延展

MyBatis开发流程

1. 引入MyBatis依赖
2. 创建核心配置文件
3. 创建实体(Entity)类
4. 创建Mapper映射文件
5. 初始化SessionFactory
6. 利用SqlSession对象操作数据

MyBatis环境配置

mybatis-config.xml说明

• MyBatis采用XML格式配置数据库环境信息
• MyBatis环境配置标签
• environment包含数据库驱动、URL、用户名于密码

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="dev">
        <!--根据环境，不同的环境不同的id，dev->开发环境 prd->生产环境 -->
        <environment id="dev">
            <!--采用JDBC方式对数据库事务进行commit/rollback-->
            <transactionManager type="JDBC"></transactionManager>
            <!--数据连接方式管理数据库连接-->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/babytun?useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
        <environment id="prd">
            <!--采用JDBC方式对数据库事务进行commit/rollback-->
            <transactionManager type="JDBC"></transactionManager>
            <!--数据连接方式管理数据库连接-->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://192.168.1.1:3306/babytun?useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
</configuration>

创建SqlSession

SqlSessionFactory

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

SqlSession

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

示例代码

package com.imooc.mybatis;

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 java.io.IOException;
import java.io.Reader;
import java.sql.Connection;

/**
 * JUNIT单元测试类
 * @author CubeMonkey
 * @create 2020-10-27 10:21
 */
public class MyBatisTestor {
    @Test
    public void testSqlSessionFactory() throws IOException {
        //利用Reader加载classpath下的mybatis-config.xml核心配置文件
        Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
        //初始化SqlSessionFactory对象，同时解析mybatis-config.xml文件
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        SqlSession sqlSession = null;
        try {
            //创建SqlSession对象，SqlSession是JDBC的扩展类，用于与数据库交互
            sqlSession = sqlSessionFactory.openSession();
            //创建数据库连接（测试用）
            Connection conn = sqlSession.getConnection();
            System.out.println(conn);
        }catch (Exception e){
            e.printStackTrace();
        } finally {
            if (sqlSession != null) {
                //如果type="POOLED", 代表使用连接池，close则是将连接回收到连接池中
                //如果type="UNPOOLED",代表直连，close则会调用Connection.close()方法关闭连接
                sqlSession.close();
            }
        }
    }
}

初始化工具类MyBatisUtils

MyBatisUtils代码示例：

package com.imooc.mybatis.utils;

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 java.io.IOException;
import java.io.Reader;

/**
 * MyBatisUtils工具类，创建全局唯一的SqlSessionFactory对象
 * @author CubeMonkey
 * @create 2020-10-27 10:51
 */
public class MyBatisUtils {
    //利用static(静态)属于类不属于对象，且全局唯一
    private static SqlSessionFactory sqlSessionFactory = null;
    //利用静态代码块在初始化类时实例化sqlSessionFactory
    static{
        Reader reader = null;
        try {
            reader = Resources.getResourceAsReader("mybatis-config.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        } catch (IOException e) {
            e.printStackTrace();
            //初始化错误时，通过抛出异常ExceptionInitializerError通知调用者
            throw new ExceptionInInitializerError(e);
        }
    }

    /**
     * openSession 创建一个新的SqlSession对象
     * @return SqlSession对象
     */
    public static SqlSession openSession(){
        return sqlSessionFactory.openSession();
    }

    /**
     * 释放一个有效的SqlSession对象
     * @param sqlSession 准备释放sqlSession对象
     */
    public static void closeSession(SqlSession sqlSession){
        if (sqlSession != null){
            sqlSession.close();
        }
    }

}

MyBatisUtils工具类使用实例：

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

MyBatis数据查询

简单查询

MyBatis数据查询步骤

• 创建实体类(Entity)
• 创建Mapper Xml
• 编写SQL标签
• 开启驼峰命名映射
• 新增
• SqlSession执行select语句

示例：

1. 创建实体类

   Goods.java

   package com.imooc.mybatis.entity;
   
   /**
    * @author CubeMonkey
    * @create 2020-10-27 11:28
    */
   public class Goods {
       private Integer goodsId;//商品编号
       private String title;//标题
       private String subTitle;//子标题
       private Float originalCost;//原始价格
       private Float currentPrice;//当前价格
       private Float discount;//折扣率
       private Integer isFreeDelivery;//是否包邮，1-包邮 0-不包邮
       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;
       }
   }
   
   

2. 创建Mapper Xml

3. 编写SQL标签

   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="goods">
       <!--resultType->返回的数据类型 -->
       <select id="selectAll" resultType="com.imooc.mybatis.entity.Goods">
           select * from t_goods order by goods_id desc limit 10
       </select>
   </mapper>
   

4. 开启驼峰命名映射

   在mybatis-config.xml中的configuration下添加settings配置

   <settings>
           <!--goods_id ==> goodsId 驼峰命名转化-->
           <setting name="mapUnderscoreToCamelCase" value="true"/>
       </settings>
   

5. 新增

   在mybatis-config.xml中的configuration下添加mapper配置

   <mappers>
           <!--resource指的是映射文件的目录-->
           <mapper resource="mappers/goods.xml"></mapper>
       </mappers>
   

6. SqlSession执行select语句

   MyBatisTestor部分测试代码：

   @Test
       public void testSelectAll(){
           SqlSession session = null;
           try {
               session = MyBatisUtils.openSession();
               List<Goods> list = session.selectList("goods.selectAll");
               for (Goods goods : list) {
                   System.out.println(goods.getTitle());
               }
           }catch (Exception e){
               throw e;
           }finally {
               MyBatisUtils.closeSession(session);
           }
       }
   

SQL传参

• 单参数传参

  goods.xml

  <!--单参数传递，使用parameterType指定参数的数据类型即可，SQL#{value}提取参数-->
  <select id="selectById" parameterType="Integer" resultType="com.imooc.mybatis.entity.Goods">
      select * from t_goods where goods_id = #{value}
  </select>
  

  MyBatisTestor.java部分代码

  @Test
  public void testSelectById(){
      SqlSession session = null;
      try {
          session = MyBatisUtils.openSession();
          Goods goods = session.selectOne("goods.selectById", 1603);
          System.out.println(goods.getTitle());
      }catch (Exception e){
          throw e;
      }finally {
          MyBatisUtils.closeSession(session);
      }
  }
  

• 多参数传参

  goods.xml

  <select id="selectByPriceRange" parameterType="java.util.Map" resultType="com.imooc.mybatis.entity.Goods">
      select * from t_goods
      where
      current_price between #{min} and #{max}
      order by current_price
      limit 0, #{limit}
  </select>
  

  MyBatisTestor.java部分代码

  @Test
  public void testSelectByPriceRange(){
      SqlSession session = null;
      try {
          session = MyBatisUtils.openSession();
          Map param = new HashMap<String, Integer>();
          param.put("min", 100);
          param.put("max", 500);
          param.put("limit", 10);
          List<Goods> list = session.selectList("goods.selectByPriceRange", param);
          for (Goods goods : list) {
              System.out.println(goods.getTitle());
          }
      }catch (Exception e){
          throw e;
      }finally {
          MyBatisUtils.closeSession(session);
      }
  }
  

多表查询

示例（不用ResultMap结果映射）：

goods.xml中添加以下代码

	<!--利用LinkedHashMap保存多表关联结果
        MyBatis会将每一条记录包装为LinkedHashMap对象
        key是字段名 value是字段对应的值，字段类型根据表结构进行自动判断
        优点：易于扩展，易于使用
        缺点：太过灵活，无法进行编译时检查
    -->
    <select id="selectGoodsMap" resultType="java.util.LinkedHashMap">
        select g.*, c.category_name from t_goods g, t_category c
        where g.category_id = c.category_id
    </select>

MyBatisTestor.java部分代码

@Test
public void testSelectGoodsMap(){
    SqlSession sqlSession = null;
    sqlSession = MyBatisUtils.openSession();
    List<Map>list = sqlSession.selectList("goods.selectGoodsMap");
    for (Map map : list) {
        System.out.println(map);
    }
}

ResultMap结果映射

• ResultMap可以将查询结果映射为复杂类型的Java对象
• ResultMap使用于Java对象保存多表关联结果
• ResultMap支持对象关联查询等高级特性

tip:DTO->Data Transfer Object – 数据传输对象

使用ResultMap结果映射

编写GoodsDTO ->

package com.imooc.mybatis.dto;

import com.imooc.mybatis.entity.Category;
import com.imooc.mybatis.entity.Goods;

/**
 * @author CubeMonkey
 * @create 2020-10-27 18:50
 */
//Data Transfer Object --数据传输对象
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;
    }
}

Goods.java

package com.imooc.mybatis.entity;

/**
 * @author CubeMonkey
 * @create 2020-10-27 11:28
 */
public class Goods {
    private Integer goodsId;//商品编号
    private String title;//标题
    private String subTitle;//子标题
    private Float originalCost;//原始价格
    private Float currentPrice;//当前价格
    private Float discount;//折扣率
    private Integer isFreeDelivery;//是否包邮，1-包邮 0-不包邮
    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;
    }
}

Category.java

package com.imooc.mybatis.entity;

/**
 * @author CubeMonkey
 * @create 2020-10-27 19:12
 */
public class Category {
    private Integer categoryId;
    private String categoryName;
    private Integer parentId;
    private Integer categoryLevel;
    private Integer categoryOrder;

    public Integer getCategoryId() {
        return categoryId;
    }

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

    public String getCategoryName() {
        return categoryName;
    }

    public void setCategoryName(String categoryName) {
        this.categoryName = categoryName;
    }

    public Integer getParentId() {
        return parentId;
    }

    public void setParentId(Integer parentId) {
        this.parentId = parentId;
    }

    public Integer getCategoryLevel() {
        return categoryLevel;
    }

    public void setCategoryLevel(Integer categoryLevel) {
        this.categoryLevel = categoryLevel;
    }

    public Integer getCategoryOrder() {
        return categoryOrder;
    }

    public void setCategoryOrder(Integer categoryOrder) {
        this.categoryOrder = categoryOrder;
    }
}

在goods.xml中添加如下信息

<!--结果映射-->
    <resultMap id="rmGoods" type="com.imooc.mybatis.dto.GoodsDTO">
        <!--设置主键字段与属性映射-->
        <id property="goods.goodsId" column="goods_id"></id>
        <!--设置非主键字段与属性映射-->
        <result property="goods.title" column="title"></result>
        <result property="goods.originalCost" column="original_cost"></result>
        <result property="goods.currentPrice" column="current_price"></result>
        <result property="goods.discount" column="discount"></result>
        <result property="goods.isFreeDelivery" column="is_free_delivery"></result>
        <result property="goods.categoryId" column="category_id"></result>
        <result property="category.categoryId" column="category_id"></result>
        <result property="category.categoryName" column="category_name"></result>
        <result property="category.categoryId" column="category_id"></result>
        <result property="category.parentId" column="parent_id"></result>
        <result property="category.categoryLevel" column="category_level"></result>
        <result property="category.categoryOrder" column="category_order"></result>

        <result property="test" column="test"></result>
    </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>

MyBatis的写操作

Insert操作

在goods.xml中配置如下信息

<insert id="insert" parameterType="com.imooc.mybatis.entity.Goods">
    insert into t_goods(title, sub_title, original_cost, current_price, discount, is_free_delivery, category_id)
    values(#{title}, #{subTitle}, #{originalCost}, #{currentPrice}, #{discount}, #{isFreeDelivery}, #{categoryId})
    <!--更新goods中的id-->
    <selectKey resultType="Integer" keyProperty="goodsId" order="AFTER">
        select last_insert_id();
    </selectKey>
</insert>

MyBatisTestor.java 测试代码

@Test
    public void testInsert() {
        SqlSession session = null;
        try {
            session = MyBatisUtils.openSession();
            Goods goods = new Goods();
            goods.setTitle("测试用例标题");
            goods.setSubTitle("测试用例子标题");
            goods.setOriginalCost(1f);
            goods.setCurrentPrice(100f);
            goods.setDiscount(0.5f);
            goods.setIsFreeDelivery(1);
            goods.setCategoryId(12);
            int num = session.insert("goods.insert", goods);
            session.commit();//这个很重要，不能忘记
            if (num == 1) System.out.println("插入成功");
            else System.out.println("插入失败");
            System.out.println(goods.getGoodsId());
        } catch (Exception e){
            session.rollback();
            throw e;
        } finally {
            MyBatisUtils.closeSession(session);
        }
    }

selectKey与useGeneratedKey区别

selectKey:

<insert id="insert" parameterType="com.imooc.mybatis.entity.Goods">
   	INSERT INTO 语句
    <!--更新goods中的id-->
    <selectKey resultType="Integer" keyProperty="goodsId" order="AFTER">
        select last_insert_id();
    </selectKey>
</insert>

useGeneratedKey:

<insert id="insert" parameterType="com.imooc.mybatis.entity.Goods"
        useGeneratedKeys="true" keyProperty="goodsId" 	keyColumn="goods_id">
    INSERT INTO 语句
</insert>

二者区别 - 显示与隐式

• selectKey标签需要明确编写获取最新主键的SQL语句(不同的数据库驱动获取最新主键的SQL语句不同)
• useGeneratedKeys属性会自动根据驱动生成对应SQL语句

二者区别-应用场景不同

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

tip:

​ 不支持自增主键的数据库有：DB2， Oracle

在oracle中selectKey的用法：

<insert id="insert" parameterType="com.imooc.mybatis.entity.Goods">
   	INSERT INTO 语句
    <!--更新goods中的id-->
    <selectKey resultType="Integer" keyProperty="goodsId" order="BEFORE">
       select seq_goods.nextval as id from dual
    </selectKey>
</insert>

总结：

• selectKey标签是通用方案，使用于所有数据库，但编写麻烦
• useGeneratedKeys属性只支持“自增主键”数据库，使用简单

update操作

goods.xml:

<update id="update" parameterType="com.imooc.mybatis.entity.Goods">
    update t_goods
    set
    title = #{title},
    sub_title = #{subTitle},
    original_cost = #{originalCost},
    current_price = #{currentPrice},
    discount = #{discount},
    is_free_delivery = #{isFreeDelivery},
    category_id = #{categoryId}
    where goods_id = #{goodsId}
</update>

MyBatisTestor.java

@Test
    public void testUpdate(){
        SqlSession session = null;
        try {
            session = MyBatisUtils.openSession();
            Goods goods = session.selectOne("selectById", 739);
            goods.setTitle("更新测试商品");
            int num = session.update("goods.update", goods);
            if (num == 1) System.out.println("更新成功");
            else System.out.println("更新失败");
            System.out.println(goods.getTitle());
            session.commit();
        } catch (Exception e) {
            session.rollback();
            throw e;
        } finally {
            MyBatisUtils.closeSession(session);
        }
    }

​

delete操作

goods.xml

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

MyBatisTestor.java

@Test
    public void testDelete(){
        SqlSession session = null;
        try {
            session = MyBatisUtils.openSession();
            int num = session.delete("goods.delete", 739);
            if (num == 1) System.out.println("删除成功");
            else System.out.println("删除失败");
            session.commit();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            MyBatisUtils.closeSession(session);
        }
    }

MyBatis预防SQL注入攻击

SQL注入攻击

• SQL注入是指攻击者利用SQL漏洞，绕过系统约束，越权获取数据的攻击方式

MyBatis俩种传值方式

• ${}文本替换，未经任何处理对SQL文本替换（可以用于动态使用SQL语句，使用时绝对不能从外界输入）
• #{}预编译传值，使用预编译传值可以预防SQL注入

MyBatis工作流程