MyBatis

---

一、MyBatis是什么？

二、单元测试和JUnit4

二、MyBatis环境配置

<?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.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="123456"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="org/mybatis/example/BlogMapper.xml"/>
    </mappers>
</configuration>

三、SqlSessionFactory

四、MyBatis数据查询步骤

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

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">
    <select id="selectAll" resultType="com.imooc.mybatis.entity.Goods">
        select * from  t_goods order by goods_id desc limit 10
    </select>
</mapper>

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.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="123456"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mappers/goods.xml"/>
    </mappers>
</configuration>

1.SQL传参

<?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">
    <select id="selectAll" resultType="com.imooc.mybatis.entity.Goods">
        select * from  t_goods order by goods_id desc limit 10
    </select>

    <select id="selectById" parameterType="Integer" resultType="com.imooc.mybatis.entity.Goods">
        select * from t_goods where goods_id=#{value}
    </select>

    <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>   
</mapper>

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

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

2.多表关联查询

<mapper namespace="goods">
    <select id="selectAll" resultType="com.imooc.mybatis.entity.Goods">
        select * from  t_goods order by goods_id desc limit 10
    </select>

    <select id="selectById" parameterType="Integer" resultType="com.imooc.mybatis.entity.Goods">
        select * from t_goods where goods_id=#{value}
    </select>

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

    <select id="selectGoodsMap" resultType="java.util.LinkedHashMap">
        select g.*,c.category_name,'1' as test from t_goods g,t_category c
        where g.category_id = c.category_id
    </select>
</mapper>

@Test
    public void testSelectGoodsMap(){
        SqlSession session = null;
        try {
            session=MyBatisUtils.openSession();
            List<Map> goods = session.selectList("goods.selectGoodsMap");
            for(Map map:goods){
                System.out.println(map);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }

3.ResultMap结果映射

<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.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>

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

4.MyBatis数据写入

1.insert 新增

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

<insert id="insert" parameterType="com.imooc.mybatis.entity.Goods">
        INSERT  INTO t_goods(title, sub_title, original_cost, current_price, discount, is_free_delivery)
        VALUE (#{title},#{subTitle},#{originalCost},#{currentPrice},#{discount},#{isFreeDelivery})
        <selectKey resultType="Integer" keyProperty="goodsId" order="AFTER">
            select last_insert_id()
        </selectKey>
</insert>

2.useGeneratedKeys属性

<insert id="insert" parameterType="com.imooc.mybatis.entity.Goods" useGeneratedKeys="true" keyProperty="goodsId" keyColumn="goods_id" >
        INSERT  INTO t_goods(title, sub_title, original_cost, current_price, discount, is_free_delivery)
        VALUE (#{title},#{subTitle},#{originalCost},#{currentPrice},#{discount},#{isFreeDelivery})
        <!--<selectKey resultType="Integer" keyProperty="goodsId" order="AFTER">
            select last_insert_id()
        </selectKey>-->
</insert>

3.更新和删除操作

更新

删除

@Test
    public void testUpdate(){
        SqlSession session = null;
        try {
            session=MyBatisUtils.openSession();
            Goods goods = session.selectOne("goods.selectById", 739);
            goods.setTitle("更新测试商品");
            int num = session.update("goods.update", goods);
            session.commit();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Test
    public void testDelete(){
        SqlSession session = null;
        try {
            session=MyBatisUtils.openSession();
            int num = session.delete("goods.delete", 739);
            System.out.println(num);
            session.commit();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

<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>
    
    <delete id="delete" parameterType="Integer">
        delete from t_goods where goods_id=#{value}
    </delete>

4.SQL注入攻击

5.MyBatis工作流程

五、MyBatis高级特性

1.日志管理

2.动态SQL

<select id="dynamicSQL" parameterType="java.util.Map" resultType="com.imooc.mybatis.entity.Goods">
        select * from t_goods
        <where>
            <if test="categoryId != null">
                and category_id = #{categoryId}
            </if>
            <if test="currentPrice != null">
                and current_price &lt; #{currentPrice}
            </if>
        </where>
    </select>

@Test
    public void testDynamicSQL(){
        SqlSession session = null;
        try {
            session=MyBatisUtils.openSession();
            Map param = new HashMap();
            param.put("categoryId", 44);
            param.put("currentPrice",100);
            List<Goods> list = session.selectList("goods.dynamicSQL", param);
            for(Goods p:list){
                System.out.println(p.getCurrentPrice());
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }

3.MyBatis二级缓存

<!--开启了二级缓存-->
	 <!--开启了二级缓存
        eviction是缓存的清除策略,当缓存对象数量达到上限后,自动触发对应算法对缓存对象清除
            1.LRU – 最近最久未使用:移除最长时间不被使用的对象。
            O1 O2 O3 O4 .. O512
            14 99 83 1     893
            2.FIFO – 先进先出:按对象进入缓存的顺序来移除它们。
            3.SOFT – 软引用:移除基于垃圾收集器状态和软引用规则的对象。
            4.WEAK – 弱引用:更积极的移除基于垃圾收集器状态和弱引用规则的对象。
         
         flushInterval 代表间隔多长时间自动清空缓存，单位毫秒，600000毫秒 = 10分钟   
         size 缓存存储上限，用于保存对象或者集合（1个集合算1个对象）的数量上限
         readOnly 设置为true，代表返回只读缓存，每次从缓存取出的是缓存对象本身，这种执行效率较高
         				设置为false，代表每次取出的是缓存对象的“副本”，每一次取出的对象都是不同的，这种安全性较高
 -->
    <cache eviction="LRU" flushInterval="600000" size="512" readOnly="true"></cache>

4.多表级联查询

1.OneToMany

goods.xml

<!--
       resultMap可用于说明一对多或者多对一的映射逻辑
       id 是resultMap属性引用的标志
       type 指向One的实体(Goods)
   -->
   <resultMap id="rmGoods1" type="com.imooc.mybatis.entity.Goods">
       <!-- 映射goods对象的主键到goods_id字段 -->
       <id column="goods_id" property="goodsId"></id>
       <!--
       collection的含义是,在
       select * from t_goods limit 0,1 得到结果后,对所有Goods对象遍历得到goods_id字段值,
       并代入到goodsDetail命名空间的selectByGoodsId的SQL中执行查询,
       将得到的"商品详情"集合赋值给goodsDetails List对象.
   -->
       <collection property="goodsDetailList" select="goodsDetail.selectByGoodsId" column="goods_id"></collection>
   </resultMap>
   <select id="selectOneToMany" resultMap="rmGoods1">
       select * from t_goods limit 0,1;
   </select>

goods_detail.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="goodsDetail">
    <select id="selectByGoodsId" parameterType="Integer" resultType="com.imooc.mybatis.entity.GoodsDetail">
        select * from t_goods_detail where goods_id = #{value}
    </select>
</mapper>

@Test
    public void testOneToMany(){
        SqlSession session = null;
        try {
            session=MyBatisUtils.openSession();
            List<Goods> list = session.selectList("goods.selectOneToMany");
            for(Goods goods:list){
                System.out.println(goods.getTitle()+":"+goods.getGoodsDetailList().size());
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }

5.分页插件PageHelper

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>
    <!-- pagehelper配置 -->
    <plugins>
        <plugin interceptor="com.github.pagehelper.PageInterceptor">
            <property name="helperDialect" value="mysql"/>
            <property name="reasonable" value="true"/>
        </plugin>
    </plugins>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.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="123456"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mappers/goods.xml"/>
        <mapper resource="mappers/goods_detail.xml"/>
    </mappers>
</configuration>

@Test
    public void testSelectPage(){
        SqlSession session = null;
        try {
            session=MyBatisUtils.openSession();
            PageHelper.startPage(2,10);
            Page<Goods> page = (Page) session.selectList("goods.selectPage");
            System.out.println(page.getPages());
            List<Goods> result = page.getResult();
            for(Goods p:result){
                System.out.println(p.getTitle());
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }

6.Mabatis注解开发