MyBatis

Yang大约 17 分钟

ORM 框架，用于简化数据库操作

框架是可被应用开发者定制的应用骨架
是一种规则，保证开发者遵循相同的方式开发程序
提倡 不要重复造轮子 ，对基础功能进行封装
极大他搞了开发效率
统一的编码规则，利于团队管理
灵活配置的应用，拥有更好的维护性
SSM：spring + spring mvc + mybatis

MyBatis 是优秀的持久层框架

使用 XML 将 SQL 与程序解耦，便于维护
学习简单，执行高效，是 JDBC 的延伸

开发流程

引入 MyBatis 依赖
创建核心配置文件
创建实体（Entity）类
创建 Mapper 映射文件
初始化 SessionFactory
利用 SqlSession 对象操作数据

环境配置

采用 XML 格式配置数据库信息
- 默认配置文件：mybatis-config.xml：
环境配置标签：<environment>：包含数据库驱动、URL、用户名、密码

<?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>
        <!-- goods_id ==> goodsId 驼峰命名转换 -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    <!--启用Pagehelper分页插件-->
    <plugins>
        <plugin interceptor="com.github.pagehelper.PageInterceptor">
            <!--设置数据库类型-->
            <property name="helperDialect" value="mysql"/>
            <!--分页合理化-->
            <property name="reasonable" value="true"/>
        </plugin>
    </plugins>

    <!--设置默认指向的数据库-->
    <environments default="dev">
        <!--配置环境，不同的环境不同的id名字-->
        <environment id="dev">
            <!-- 采用JDBC方式对数据库事务进行commit/rollback -->
            <transactionManager type="JDBC"></transactionManager>
            <!--采用连接池方式管理数据库连接-->
            <!--<dataSource type="POOLED">-->
          	<!-- com.imooc.mybatis.datasource.C3P0DataSourceFactory -->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="jdbcUrl"
                          value="jdbc:mysql://localhost:3306/babytun?useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="user" value="root"/>
                <property name="password" value="root"/>
                <property name="initialPoolSize" value="5"/>
                <property name="maxPoolSize" value="20"/>
                <property name="minPoolSize" value="5"/>
                <!--...-->
            </dataSource>
        </environment>
        <environment id="prd">
            <!-- 采用JDBC方式对数据库事务进行commit/rollback -->
            <transactionManager type="JDBC"></transactionManager>
            <!--采用连接池方式管理数据库连接-->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url"
                          value="jdbc:mysql://192.168.1.155:3306/babytun?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"/>
        <mapper resource="mappers/goods_detail.xml"/>
    </mappers>
</configuration>

SqlSessionFactory

是 MyBatis 的核心对象

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

SqlSession

是 MyBatis 操作数据库的核心对象

使用 JDBC 方式与数据库交互
提供了对数据表 CURD 对应方法

// 测试 MyBatis 连接
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;

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);
        System.out.println("SessionFactory 加载成功");
        SqlSession sqlSession = null;
        try {
            // 创建 SqlSession 对象，SqlSession 是 JDBC 拓展类，用于与数据库交互
            sqlSession = sqlSessionFactory.openSession();
            // ...
        } 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 对象
 */
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();
            // 初始化错误时，通过抛出异常通知调用者
            throw new ExceptionInInitializerError(e);
        }
    }

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

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

// 测试 MyBatisUtils
package com.imooc.mybatis.utils;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import static org.junit.Assert.*;

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

数据查询

操作流程

创建实体类（Entity）

新增 entity 包
新建 Goods 类

// Goods.java
package com.imooc.mybatis.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

resources 文件夹下新增 mappers 目录
新建 goods.xml 文件
设置命名空间
编写 <select> 标签，设定 id 和 resultType

<!-- goods.xml -->
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="goods">
    <select id="selectAll" resultType="com.imooc.mybatis.entity.Goods">
        select *
        from t_goods
        order by goods_id desc
        limit 10
    </select>
</mapper>

开启驼峰命名映射

<configuration> 标签下新增 setting 设置项 mapUnderscoreToCamelCase

<!-- mybatis-config.xml -->
<settings>
  <setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>

新增 mapper

<configuration> 标签下新增 mappers -> mapper项

<!-- mybatis-config.xml -->
<mappers>
  <mapper resource="mappers/goods.xml"></mapper>
</mappers>

执行 select 语句

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

带参数查询

单个参数

使用 parameterType 指定参数的数据类型即可，SQL 中 #{value} 提取参数

<!-- goods.xml -->
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="goods">
    <select id="selectById" resultType="com.imooc.mybatis.entity.Goods" parameterType="Integer">
        select *
        from t_goods
        where goods_id = #{value}
    </select>
</mapper>

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

多个参数

使用 parameterType 指定 Map 接口，SQL 中 #{key} 提取参数

<!-- goods.xml -->
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="goods">
    <select id="selectByPriceRange" resultType="com.imooc.mybatis.entity.Goods" parameterType="java.util.Map">
        select *
        from t_goods
        where current_price between #{min} and #{max}
        order by current_price
        limit 0,#{limit}
    </select>
</mapper>

// MyBatisUtilsTest
@Test
public void testSelectByPriceRange() {
  SqlSession session = null;
  try {
    session = MyBatisUtils.openSession();
    Map param = new HashMap();
    param.put("min", 1000);
    param.put("max", 2000);
    param.put("limit", 15);
    List<Goods> list = session.selectList("goods.selectByPriceRange", param);
    for (Goods g : list) {
      System.out.println(g.getTitle());
    }
  } catch (Exception e) {
    e.printStackTrace();
  } finally {
    MyBatisUtils.closeSession(session);
  }
}

多表关联查询

利用 LinkedHashMap 保存多表关联结果

MyBatis 会将每一条记录包装为 LinkedHashMap 对象
key 是字段名，value 是字段值，字段类型根据表结构进行自动判断
优点：易于拓展，易于使用
缺点：太过灵活，无法进行编译时检查

<!-- goods.xml -->
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="goods">
    <select id="selectGoodMap" resultType="java.util.LinkedHashMap">
        select g.*, c.category_name
        from t_goods g,
             t_category c
        where g.category_id = c.category_id
    </select>
</mapper>

// MyBatisUtilsTest
@Test
public void testSelectGoodMap() {
  SqlSession session = null;
  try {
    session = MyBatisUtils.openSession();
    List<Map> list = session.selectList("goods.selectGoodMap");
    for (Map map : list) {
      System.out.println(map);
    }
  } catch (Exception e) {
    e.printStackTrace();
  } finally {
    MyBatisUtils.closeSession(session);
  }
}

ResultMap 结果映射

可以将查询结果映射为复杂类型的 Java 对象
适用于 Java 对象保存多表关联结果
支持对象关联查询等高级特性
新建 com.imooc.mybatis.dto 包，用于保存数据和传递，里面的类是对原始实体类的拓展

<!-- goods.xml -->
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="goods">
    <resultMap id="rmGoods" type="com.imooc.mybatis.dto.GoodsDTO">
        <id property="goods.goodsId" column="goods_id"></id>
        <id property="goods.title" column="title"></id>
        <id property="goods.subTitle" column="sub_title"></id>
        <id property="goods.originalCost" column="original_cost"></id>
        <id property="goods.currentPrice" column="current_price"></id>
        <id property="goods.discount" column="discount"></id>
        <id property="goods.isFreeDelivery" column="is_free_delivery"></id>
        <id property="goods.categoryId" column="category_id"></id>
        <id property="categoryName" column="category_name"></id>
        <id property="test" column="test"></id>
    </resultMap>
    <select id="selectGoodsDTO" resultMap="rmGoods">
        select g.*, c.category_name, '1' as test
        from t_goods g,
             t_category c
        where g.category_id = c.category_id
    </select>
</mapper>

// GoodsDTO.java
package com.imooc.mybatis.dto;

import com.imooc.mybatis.entity.Goods;

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

    public Goods getGoods() {
        return goods;
    }

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

    public String getCategoryName() {
        return categoryName;
    }

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

    public String getTest() {
        return test;
    }

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

// MyBatisUtilsTest
@Test
public void testSelectGoodsDTO() {
  SqlSession session = null;
  try {
    session = MyBatisUtils.openSession();
    List<GoodsDTO> list = session.selectList("goods.selectGoodsDTO");
    for (GoodsDTO g : list) {
      System.out.println(g.getGoods().getTitle());
    }
  } catch (Exception e) {
    e.printStackTrace();
  } finally {
    MyBatisUtils.closeSession(session);
  }
}

数据写入

插入(insert)

<!-- goods.xml -->
<insert id="insert" parameterType="com.imooc.mybatis.entity.Goods">
  insert into `babytun`.`t_goods`( `title`, `sub_title`, `original_cost`,
  `current_price`,`discount`,`is_free_delivery`,`category_id` ) value ( #{title},
  #{subTitle},#{originalCost}, #{currentPrice}, #{discount}, #{isFreeDelivery}, #{categoryId})
  <selectKey resultType="int" keyProperty="goodsId" order="AFTER">
    <!-- 当前连接中最后产生的 id 号 -->
    select last_insert_id()
  </selectKey>
</insert>

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

selectKey 标签需要明确编写获取最新主键的 SQL 语句
- 适用于所有的关系型数据库，但编写玛法
useGeneratedKeys 属性会自动根据驱动生成对应的 SQL 语句
- 会根据不同的数据库变化
- 只支持 自增主键 类型的数据库

// MyBatisUtilsTest
@Test
public void testInsert() {
  SqlSession session = null;
  try {
    session = MyBatisUtils.openSession();
    Goods goods = new Goods();
    goods.setTitle("固态硬盘");
    goods.setSubTitle("最新固态，稳定不掉速");
    goods.setOriginalCost(500f);
    goods.setCurrentPrice(250f);
    goods.setDiscount(0.5f);
    goods.setIsFreeDelivery(1);
    goods.setCategoryId(32);
    int num = session.insert("goods.insert", goods);
    System.out.println(goods.getGoodsId());
    session.commit();
  } catch (Exception e) {
    if (session != null) {
      session.rollback();
    }
    e.printStackTrace();
  } finally {
    MyBatisUtils.closeSession(session);
  }
}

更新(update)

<!-- goods.xml -->
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="goods">
    <update id="update" parameterType="com.imooc.mybatis.entity.Goods">
        update `babytun`.`t_goods`
        set `title`=#{title},
            `sub_title`=#{subTitle},
            `category_id`=#{categoryId}
        where `goods_id` = #{goodsId}
    </update>
</mapper>

// MyBatisUtilsTest
@Test
public void testUpdate() {
  SqlSession session = null;
  try {
    session = MyBatisUtils.openSession();
    Map param = new HashMap();
    param.put("title", "希捷固态硬盘");
    param.put("subTitle", "希捷最新固态，稳定不掉速");
    param.put("categoryId", 70);
    param.put("goodsId", 2679);
    session.update("goods.update", param);
    session.commit();
  } catch (Exception e) {
    if (session != null) {
      session.rollback();
    }
    e.printStackTrace();
  } finally {
    MyBatisUtils.closeSession(session);
  }
}

// 或者更新所有字段时
try {
  session = MyBatisUtils.openSession();
  Goods goods = session.selectOne("goods.selectById", 739);
  goods.setTitle("测试商品");
  session.update("goods.update", goods);
  session.commit();
}

删除(delete)

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="goods">
    <delete id="delete" parameterType="Integer">
        delete
        from t_goods
        where goods_id = #{value}
    </delete>
</mapper>

// MyBatisUtilsTest
@Test
public void testDelete() {
  SqlSession session = null;
  try {
    session = MyBatisUtils.openSession();
    session.delete("goods.delete", 2679);
    session.commit();
  } catch (Exception e) {
    if (session != null) {
      session.rollback();
    }
    e.printStackTrace();
  } finally {
    MyBatisUtils.closeSession(session);
  }
}

动态 SQL

指根据参数数据动态组织 SQL 的技术

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="goods">
    <select id="dynamicSQL" resultType="com.imooc.mybatis.entity.Goods" parameterType="java.util.Map">
        select *
        from t_goods
        <where>
            <if test="categoryId!=null">
                and category_id=#{categoryId}
            </if>
            <if test="currentPrice!=null">
                and current_price=#{currentPrice}
            </if>
        </where>
    </select>
</mapper>

// MyBatisUtilsTest
@Test
public void testDynamicSQL() {
  SqlSession session = null;
  try {
    session = MyBatisUtils.openSession();
    Map param = new HashMap();
    param.put("categoryId", 16);
    param.put("currentPrice", 99f);
    List<Goods> list = session.selectList("goods.dynamicSQL", param);
    for (Goods g : list) {
      System.out.println(g);
    }
  } catch (Exception e) {
    e.printStackTrace();
  } finally {
    MyBatisUtils.closeSession(session);
  }
}

二级缓存

缓存范围

一级缓存默认开启，缓存范围 SqlSession 会话
二级缓存手动开启，属于范围 Mapper Namespace

运行规则

二级缓存开启后默认所有查询操作均使用缓存
写操作 commit 提交时对该 namespace 缓存强制清空，保证数据一致性
配置 useCache=false 可以不用缓存
- <select useCache=false>
配置 flushCache=true 代表强制清空缓存
- <insert flushCache=true>

<!-- goods.xml -->
<mappers>
  <!--
	eviction：缓存清空策略，当缓存对象数量达到上线后，自动触发对应算法对缓存对象清除
			LRU：最近最久未使用（移除嘴上时间不被使用的对象）
			FIFO：先进先出（按对象进入缓存的顺序来移除它们）
			SOFT：软引用（移除基于垃圾收集器状态和软引用规则的对象）
			WEAK：弱引用（更积极的移除基于垃圾收集器状态和弱引用规则的对象）
	flushInterval：刷新间隔(自动清空缓存),单位毫秒
	size：缓存存储上限，用于保存对象或集合（一个集合算一个对象）的数量上限
	readOnly：设置为true，代表返回只读缓存，每次从缓存取出的是缓存对象本身，这种执行效率更高
						 设置为false，代表每次取出的是缓存对象的副本，每一次取出的对象都是不同的，这种安全性较高
	-->
	<cache eviction="LRU" flushInterval="600000" size="512" readOnly="true" />
</mappers>

多表级联查询

一对多

<!-- goods_detail.xml -->
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="goodsDetail">
    <select id="selectByGoodsId" parameterType="Integer" resultType="com.imooc.mybatis.entity.GoodsDetail">
        select *
        from t_goods_detail
        where goods_id = #{value}
    </select>
</mapper>

<!-- goods.xml -->
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="goods">
    <!--
        resultMap 可用于说明一对多或者多对一的映射逻辑
        id 是 resultMap 属性引用的标志
        type 指向 One 实体（Goods）
    -->
    <resultMap id="rmGoods1" type="com.imooc.mybatis.entity.Goods">
        <!-- 映射 goods 对象的主键到 goods_id 字段 -->
        <id property="goodsId" column="goods_id"></id>

        <!--
            collection 的含义是，在查询语句得到结果后，对所有 Goods 对象遍历得到 goods_id 字段值，
            并带入到 goodsDetail 命名空间的 findByGoodsId 的 SQL 中执行查询，
            将得到的商品详情集合赋值给 goodsDetail List 对象
        -->
        <collection property="goodsDetail" select="goodsDetail.selectByGoodsId" column="goods_id"></collection>
    </resultMap>
    <select id="selectOneToMany" resultMap="rmGoods1">
        select *
        from t_goods
        limit 0,10
    </select>
</mapper>

// GoodsDetail.java
package com.imooc.mybatis.entity;

public class GoodsDetail {
    private Integer gdId;
    private Integer goodsId;
    private String gdPicUrl;
    private Integer gdOrder;

    public Integer getGdId() {
        return gdId;
    }

    public void setGdId(Integer gdId) {
        this.gdId = gdId;
    }

    public Integer getGoodsId() {
        return goodsId;
    }

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

    public String getGdPicUrl() {
        return gdPicUrl;
    }

    public void setGdPicUrl(String gdPicUrl) {
        this.gdPicUrl = gdPicUrl;
    }

    public Integer getGdOrder() {
        return gdOrder;
    }

    public void setGdOrder(Integer gdOrder) {
        this.gdOrder = gdOrder;
    }
}

// MyBatisUtilsTest
@Test
public void testSelectOneToMany() {
  SqlSession session = null;
  try {
    session = MyBatisUtils.openSession();
    List<Goods> list = session.selectList("goods.selectOneToMany");
    for (Goods g : list) {
      System.out.println(g);
    }
  } catch (Exception e) {
    e.printStackTrace();
  } finally {
    MyBatisUtils.closeSession(session);
  }
}

多对一

<!-- goods_detail.xml -->
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="goodsDetail">
    <resultMap id="rmGoodsDetail" type="com.imooc.mybatis.entity.GoodsDetail">
        <id property="gdId" column="gd_id"></id>
        <!-- MyBatis默认策略，goodsId 会变为 null，需要显式的映射 -->
        <result property="goodsId" column="goods_id"></result>
        <association property="goods" column="goods_id" select="goods.selectById"></association>
    </resultMap>
    <select id="selectManyToOne" resultMap="rmGoodsDetail">
        select *
        from t_goods_detail
        limit 0,10
    </select>
</mapper>

// GoodsDetail.java
package com.imooc.mybatis.entity;

public class GoodsDetail {
    private Integer gdId;
    private Integer goodsId;
    private String gdPicUrl;
    private Integer gdOrder;
    private Goods goods;

    public Goods getGoods() {
        return goods;
    }

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

    public Integer getGdId() {
        return gdId;
    }

    public void setGdId(Integer gdId) {
        this.gdId = gdId;
    }

    public Integer getGoodsId() {
        return goodsId;
    }

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

    public String getGdPicUrl() {
        return gdPicUrl;
    }

    public void setGdPicUrl(String gdPicUrl) {
        this.gdPicUrl = gdPicUrl;
    }

    public Integer getGdOrder() {
        return gdOrder;
    }

    public void setGdOrder(Integer gdOrder) {
        this.gdOrder = gdOrder;
    }
}

// MyBatisUtilsTest
@Test
public void testSelectManyToOne() {
  SqlSession session = null;
  try {
    session = MyBatisUtils.openSession();
    List<GoodsDetail> list = session.selectList("goodsDetail.selectManyToOne");
    for (GoodsDetail gd : list) {
      System.out.println(gd);
    }
  } catch (Exception e) {
    e.printStackTrace();
  } finally {
    MyBatisUtils.closeSession(session);
  }
}

分页插件 PageHelper

使用流程

引入

maven 引入 PageHelper 与 jsqlparser

<!-- pom.xml -->
<dependency>
  <groupId>com.github.pagehelper</groupId>
  <artifactId>pagehelper</artifactId>
  <version>5.1.10</version>
</dependency>
<dependency>
  <groupId>com.github.jsqlparser</groupId>
  <artifactId>jsqlparser</artifactId>
  <version>2.0</version>
</dependency>

配置

Mybatis-config.xml 增加 Plugin 配置

<!-- 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>
    <plugins>
        <!-- 启用 PageHelper 分页插件 -->
        <plugin interceptor="com.github.pagehelper.PageInterceptor">
            <!-- 设置数据库类型 -->
            <property name="helperDialect" value="mysql"/>
            <!-- 分页合理化 -->
            <property name="reasonable" value="true"/>
        </plugin>
    </plugins>
</configuration>

实现

代码中使用 PageHelper.startPage() 自动分页

// MyBatisUtilsTest
@Test
public void testSelectPage() {
  SqlSession session = null;
  try {
    session = MyBatisUtils.openSession();
    // startPage 方法会自动将下一次查询进行分页 (pageNum, pageSize)
    PageHelper.startPage(2, 10);
    Page<Goods> page = (Page) session.selectList("goods.selectPage");
    System.out.println("总页数：" + page.getPages());
    System.out.println("总记录数：" + page.getTotal());
    System.out.println("开始行号：" + page.getStartRow());
    System.out.println("结束行号：" + page.getEndRow());
    System.out.println("当前页码：" + page.getPageNum());
    // 当前页数据
    List<Goods> list = page.getResult();
    for (Goods g : list) {
      System.out.println(g.getTitle());
    }
  } catch (Exception e) {
    e.printStackTrace();
  } finally {
    MyBatisUtils.closeSession(session);
  }
}

预防 SQL 注入攻击

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

传值方式（MyBatis）

${}

文本替换，未经任何处理对 SQL 文本替换

#{}

**（推荐）**预编译传值，使用预编译传值可以预防 SQL 注入

日志管理

日志文件是用于记录系统操作事件的记录文件或文件集合
日志保存历史数据，是诊断问题以及理解系统活动的重要一句

日志门面

提供统一的日志调用接口

SLF4j

Simple Logging Facade For Java

Apache Commons-Loging

日志实现

log4j

logback

<!-- pom.xml -->
<dependency>
  <groupId>ch.qos.logback</groupId>
  <artifactId>logback-classic</artifactId>
  <version>1.2.3</version>
</dependency>

<!-- resources/logback.xml -->
<?xml version="1.0" encoding="UTF-8" ?>
<configuration>
    <appender name="console" class="ch.qos.logback.core.ConsoleAppender">
        <encoder>
            <pattern>[%thread] %d{HH:mm:ss.SSS} %-5level %logger{36} - %msg%n</pattern>
        </encoder>
    </appender>
  	<!--
    日志输出级别（优先级从高到低）：
       error：错误 - 系统的故障日志
       warn：警告 - 存在风险或使用不当的日志
       info：一般性消息
       debug：程序内部用于调试信息
       trace：程序运行的跟踪信息
		-->
    <root level="debug" >
        <appender-ref ref="console" />
    </root>
</configuration>

Java.util.logging(jul)

不同数据库的分页

MySQL

select * from table limit 10,20;

Oracle

select t3. from(
  select t2*, rownum as row num from(
    select from table order by id asc
  ) t2 where rownum <= 20
) t3
where t2 row num > 11

SQL Server 2000

select top 3 * from table where id not in ( select top 15 id from table )

SQL Server 2012+

select from table order by id offset 4 rows fetch next 5 rows only

整合 C3P0 连接池

<!-- pom.xml -->
<dependency>
  <groupId>com.mchange</groupId>
  <artifactId>c3p0</artifactId>
  <version>0.9.5.4</version>
</dependency>

<!-- 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">
        <environment id="dev">
            <transactionManager type="JDBC"/>
            <dataSource type="com.imooc.mybatis.datasource.C3P0DataSourceFactory">
                <property name="driverClass" value="com.mysql.jdbc.Driver"/>
                <property name="jdbcUrl"
                          value="jdbc:mysql://localhost:3306/babytun?useUnicode=true&amp;useSSL=false&amp;characterEncoding=UTF-8"/>
                <property name="user" value="root"/>
                <property name="password" value="11160328"/>
                <property name="initialPoolSize" value="5"/>
                <property name="maxPoolSize" value="20"/>
                <property name="minPoolSize" value="5"/>
            </dataSource>
        </environment>
    </environments>
</configuration>

// C3P0DataSourceFactory.java
package com.imooc.mybatis.datasource;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.ibatis.datasource.unpooled.UnpooledDataSourceFactory;

/**
 * C3P0 与 MyBatis 兼容使用的数据源工厂类
 */
public class C3P0DataSourceFactory extends UnpooledDataSourceFactory {
    public C3P0DataSourceFactory() {
        this.dataSource = new ComboPooledDataSource();
    }
}

批处理

插入

无法获得插入数据的 id
批量生成 SQL 太长，可能会被服务器拒绝

<!-- goods.xml -->
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="goods">
    <insert id="batchInsert" parameterType="java.util.List">
        insert into `babytun`.`t_goods`( `title`, `sub_title`, `original_cost`,
        `current_price`,`discount`,`is_free_delivery`,`category_id` ) values
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.title},#{item.subTitle},#{item.originalCost},#{item.currentPrice},#{item.discount},#{item.isFreeDelivery},#{item.categoryId})
        </foreach>
    </insert>
</mapper>

// MyBatisUtilsTest
@Test
public void testBatchInsert() {
  SqlSession session = null;
  try {
    long st = new Date().getTime();
    session = MyBatisUtils.openSession();
    List<Goods> list = new ArrayList<>();
    for (int i = 0; i < 10000; i++) {
      Goods goods = new Goods();
      goods.setTitle("测试商品 " + i);
      goods.setSubTitle("测试子标题 " + i);
      goods.setOriginalCost(200f);
      goods.setCurrentPrice(100f);
      goods.setDiscount(0.5f);
      goods.setIsFreeDelivery(1);
      goods.setCategoryId(43);
      list.add(goods);
    }
    session.insert("goods.batchInsert", list);
    session.commit();
    long et = new Date().getTime();
    System.out.println("执行时间：" + (et - st) + " 毫秒");
  } catch (Exception e) {
    if (session != null) {
      session.rollback();
    }
    e.printStackTrace();
  } finally {
    MyBatisUtils.closeSession(session);
  }
}

删除

<!-- goods.xml -->
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="goods">
    <delete id="batchDelete" parameterType="java.util.List">
        delete from t_goods where goods_id in
        <foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
            #{item}
        </foreach>
    </delete>
</mapper>

// MyBatisUtilsTest
@Test
public void testBatchDelete() {
  SqlSession session = null;
  try {
    session = MyBatisUtils.openSession();
    List<Integer> list = new ArrayList<>();
    for (int i = 2682; i <= 12682; i++) {
      list.add(i);
    }
    session.delete("goods.batchDelete", list);
    session.commit();
  } catch (Exception e) {
    if (session != null) {
      session.rollback();
    }
    e.printStackTrace();
  } finally {
    MyBatisUtils.closeSession(session);
  }
}

注解开发

注解	对应 XML	说明
`@Insert`	`<insert>`	新增 SQL
`@Update`	`<update>`	更新 SQL
`@Delete`	`<delete>`	删除 SQL
`@Select`	`<select>`	查询 SQL
`@Param`	`--`	参数映射
`@Results`	`<resultMap>`	结果映射
`@Result`	`<id><result>`	字段映射

配置

<!-- 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>
    <mappers>

        <!-- 写法一 -->
        <!-- <mapper class="com.imooc.mybatis.dto.GoodsDTO"></mapper> -->

        <!-- 写法二（对包下所有接口进行扫描） -->
        <package name="com.imooc.mybatis.dao"/>

    </mappers>
</configuration>

查询

// GoodsDAO.java
package com.imooc.mybatis.dao;

import com.imooc.mybatis.entity.Goods;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface GoodsDAO {
    @Select("select * from t_goods where current_price between #{min} and #{max} order by current_price limit 0,#{limit}")
    public List<Goods> selectByPriceRange(@Param("min") Float min, @Param("max") Float max, @Param("limit") Integer limit);
}

// MyBatisUtilsTest
@Test
public void testMyBatisUtils() {
  SqlSession session = null;
  try {
    session = MyBatisUtils.openSession();
    GoodsDAO goodsDAO = session.getMapper(GoodsDAO.class);
    List<Goods> list = goodsDAO.selectByPriceRange(100f, 500f, 20);
    System.out.println(list.size());
  } catch (Exception e) {
    e.printStackTrace();
  } finally {
    MyBatisUtils.closeSession(session);
  }
}

插入

// GoodsDAO.java
package com.imooc.mybatis.dao;

import com.imooc.mybatis.entity.Goods;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.SelectKey;

import java.util.List;

public interface GoodsDAO {
    @Insert("insert into `babytun`.`t_goods`( `title`, `sub_title`, `original_cost`, `current_price`,`discount`,`is_free_delivery`,`category_id` ) value ( #{title}, #{subTitle},#{originalCost}, #{currentPrice}, #{discount}, #{isFreeDelivery}, #{categoryId})")
    @SelectKey(statement = "select last_insert_id()", keyProperty = "goodsId", before = false, resultType = Integer.class)
    public int insert(Goods goods);
}

// MyBatisUtilsTest
@Test
public void testInsert() {
  SqlSession session = null;
  try {
    session = MyBatisUtils.openSession();
    Goods goods = new Goods();
    goods.setTitle("1测试商品 ");
    goods.setSubTitle("1测试子标题");
    goods.setOriginalCost(200f);
    goods.setCurrentPrice(100f);
    goods.setDiscount(0.5f);
    goods.setIsFreeDelivery(1);
    goods.setCategoryId(43);
    GoodsDAO goodsDAO = session.getMapper(GoodsDAO.class);
    int num = goodsDAO.insert(goods);
    session.commit();
    System.out.println(goods.getGoodsId());
  } catch (Exception e) {
    if (session != null) {
      session.rollback();
    }
    e.printStackTrace();
  } finally {
    MyBatisUtils.closeSession(session);
  }
}

结果映射

// GoodsDAO.java
package com.imooc.mybatis.dao;

import com.imooc.mybatis.dto.GoodsDTO;
import com.imooc.mybatis.entity.Goods;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface GoodsDAO {
    @Select("select * from t_goods")
    //<resultMap>
    @Results({
            //<id>
            @Result(column = "goods_id", property = "goodsId", id = true),
            //<result>
            @Result(column = "title", property = "title"),
            @Result(column = "current_price", property = "currentPrice")
    })
    public List<GoodsDTO> selectAll();
}

// MyBatisUtilsTest
@Test
public void testSelectAll() {
  SqlSession session = null;
  try {
    session = MyBatisUtils.openSession();
    GoodsDAO goodsDAO = session.getMapper(GoodsDAO.class);
    List<GoodsDTO> list = goodsDAO.selectAll();
    System.out.println(list.size());
  } catch (Exception e) {
    e.printStackTrace();
  } finally {
    MyBatisUtils.closeSession(session);
  }
}

xml 实现 mapper 接口

namespace 与包名类名一致
id 与方法名对应
parameterType 与方法参数类型对应
resultType 与方法返回类型对应

<!-- employee.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.imooc.oa.dao.EmployeeDao">
    <select id="selectById" parameterType="Long" resultType="com.imooc.oa.entity.Employee">
        select *
        from adm_employee
        where employee_id = #{value}
    </select>
</mapper>

// 接口 Employee.java
package com.imooc.oa.dao;
import com.imooc.oa.entity.Employee;

public interface EmployeeDao {
    public Employee selectById(Long employeeId);
}

// 实体类 Employee.java
package com.imooc.oa.entity;

public class Employee {
    private Long employeeId;
    private String name;
    private Long departmentId;
    private String title;
    private Integer level;

    public Long getEmployeeId() {
        return employeeId;
    }

    public void setEmployeeId(Long employeeId) {
        this.employeeId = employeeId;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Long getDepartmentId() {
        return departmentId;
    }

    public void setDepartmentId(Long departmentId) {
        this.departmentId = departmentId;
    }

    public String getTitle() {
        return title;
    }

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

    public Integer getLevel() {
        return level;
    }

    public void setLevel(Integer level) {
        this.level = level;
    }
}

// Service
package com.imooc.oa.services;

import com.imooc.oa.dao.EmployeeDao;
import com.imooc.oa.entity.Employee;
import com.imooc.oa.utils.MybatisUtils;

public class EmployeeService {
    public Employee selectById(Long employeeId) {
        return (Employee) MybatisUtils.executeQuery(sqlSession -> {
            EmployeeDao employeeDao = sqlSession.getMapper(EmployeeDao.class);
            return employeeDao.selectById(employeeId);
        });
    }
}