十五、MyBatis进阶

作者: 东方奇迹 | 来源:发表于2022-07-30 17:31 被阅读0次

一、日志

截屏2022-07-22 下午10.21.46.png

截屏2022-07-22 下午10.22.08.png

截屏2022-07-22 下午10.27.01.png

logback.xml

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

二、动态SQL

截屏2022-07-28 15.57.26.png

    <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() throws Exception {
        SqlSession sqlSession = null;
        try {
            sqlSession = MybatisUtils.openSession();
            HashMap param = new HashMap();
            param.put("categoryId",44);
            param.put("currentPrice",500);

            List<Goods> list = sqlSession.selectList("goods.dynamicSQL",param);
            for (Goods goods : list) {
                System.out.println(goods);
            }
        } catch (Exception e) {
            throw e;
        } finally {
            MybatisUtils.closeSession(sqlSession);
        }
    }

三、二级缓存

截屏2022-07-28 16.41.50.png

截屏2022-07-28 16.41.25.png

截屏2022-07-28 16.43.26.png

截屏2022-07-28 17.06.56.png

四、对象关联查询

1、一对多

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



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

    @Test
    public void testSelectOneToMany() throws Exception {
        SqlSession sqlSession = null;
        try {
            sqlSession = MybatisUtils.openSession();
            List<Goods> list = sqlSession.selectList("goods.selectOneToMany");
            for (Goods goods : list) {
                System.out.println(goods);
            }
        } catch (Exception e) {
            throw e;
        } finally {
            MybatisUtils.closeSession(sqlSession);
        }
    }

2、多对一

<mapper namespace="goodsDetail">
    <resultMap id="rmGoodsDetail" type="com.imooc.mybatis.entity.GoodsDetail">
        <id property="gdId" column="gd_id"/>
        <result property="goodsId" column="goods_id"/>
        <association property="goods" select="goods.selectById" column="goods_id"/>
    </resultMap>
    <select id="selectManyToOne" resultMap="rmGoodsDetail">
        select * from t_goods_detail limit 0,1
    </select>
</mapper>

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

    @Test
    public void testSelectMonyToOne() throws Exception {
        SqlSession sqlSession = null;

        try {
            sqlSession = MybatisUtils.openSession();
            List<GoodsDetail> list = sqlSession.selectList("goodsDetail.selectManyToOne");

            for (GoodsDetail goodsDetail : list) {
                System.out.println(goodsDetail);
            }
        } catch (Exception e) {
            throw e;
        } finally {
            MybatisUtils.closeSession(sqlSession);
        }
    }

五、分页插件PageHelper

截屏2022-07-29 15.35.48.png

截屏2022-07-29 15.37.07.png

        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.1.10</version>
        </dependency>

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

    <select id="selectPage" resultType="com.imooc.mybatis.entity.Goods">
        select * from t_goods where current_price &lt; 1000
    </select>

    @Test
    public void testSelectPage() throws Exception {
        SqlSession sqlSession = null;

        try {
            sqlSession = MybatisUtils.openSession();
            PageHelper.startPage(2,10);
            Page<Goods> page = (Page)sqlSession.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 goods : list) {
                System.out.println(goods);
            }
        } catch (Exception e) {
            throw e;
        } finally {
            MybatisUtils.closeSession(sqlSession);
        }
    }

六、Mybatis整合C3P0连接池

        <dependency>
            <groupId>com.mchange</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.5.4</version>
        </dependency>

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();
    }
}

<?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">-->
            <dataSource type="com.imooc.mybatis.datasource.C3P0DataSourceFactory">
<!--                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>-->
                <property name="driverClass" value="com.mysql.cj.jdbc.Driver"/>
<!--                <property name="url" value="jdbc:mysql://localhost:3306/babytun?useUnicode=true&amp;characterEncoding=UTF-8"/>-->
                <property name="jdbcUrl" value="jdbc:mysql://localhost:3306/babytun?useUnicode=true&amp;characterEncoding=UTF-8"/>
<!--                <property name="username" value="root"/>-->
                <property name="user" value="root"/>
                <property name="password" value="htxx1234"/>
                <property name="initialPoolSize" value="5"/>
                <property name="maxPoolSize" value="20"/>
                <property name="minPoolSize" value="5"/>
            </dataSource>
        </environment>
    </environments>

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

七、Mybatis整合Druid连接池

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.14</version>
        </dependency>

DruidDataSourceFactory.java类

public class DruidDataSourceFactory extends UnpooledDataSourceFactory {
    public DruidDataSourceFactory() {
        this.dataSource = new DruidDataSource();
    }

    @Override
    public DataSource getDataSource() {
        try {
            ((DruidDataSource)this.dataSource).init();//初始化Druid数据源
        }catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return this.dataSource;
    }
}

<?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>
        <!--开启驼峰命名转换form_id -> formId-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
    <environments default="dev">
        <!--开发环境配置-->
        <environment id="dev">
            <!--事务管理器采用JDBC方式-->
            <transactionManager type="JDBC"></transactionManager>
            <!--利用Mybatis自带连接池管理连接-->
            <!--<dataSource type="POOLED">-->
            <!--Mybatis与Druid的整合-->
            <dataSource type="com.imooc.oa.datasource.DruidDataSourceFactory">
                <!--JDBC连接属性-->
                <property name="driverClassName" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/imooc-oa?useSSL=false&amp;useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=Asia/Shanghai&amp;allowPublicKeyRetrieval=true"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mappers/test.xml"/>
    </mappers>
</configuration>

八、Mybatis的批处理

批量插入

    <insert id="batchInsert" parameterType="java.util.List">
        insert into 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>
    @Test
    public void testBatchInsert() throws Exception {
        SqlSession sqlSession = null;

        try {
            long st = new Date().getTime();
            sqlSession = MybatisUtils.openSession();
            List list = new ArrayList();
            for (int i = 0; i <10000; i++) {
                Goods goods = new Goods();
                goods.setTitle("测试商品");
                goods.setSubTitle("测试子标题");
                goods.setOriginalCost(200f);
                goods.setCurrentPrice(100f);
                goods.setDiscount(0.5f);
                goods.setIsFreeDelivery(1);
                goods.setCategoryId(43);
                list.add(goods);
            }
            sqlSession.insert("goods.batchInsert", list);
            sqlSession.commit();
            long et = new Date().getTime();
            System.out.println("执行时间：" + (et -st) + "毫秒");
            
        } catch (Exception e) {
            throw e;
        } finally {
            MybatisUtils.closeSession(sqlSession);
        }
    }

批量删除

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

    @Test
    public void testBatchDelete() {
        SqlSession session = null;
        try {
            long st = new Date().getTime();
            session = MybatisUtils.openSession();

            System.out.println("session" + session);

            List list = new ArrayList();
            list.add("1920");
            list.add("1921");
            list.add("1922");

            session.delete("goods.batchDelete",list);
            session.commit();
            long et = new Date().getTime();
            System.out.println(et - st);
        } catch (Exception e) {
            e.printStackTrace();
            if (session != null) {
                session.rollback();
            }
        } finally {
            MybatisUtils.closeSession(session);
        }
    }

九、Mybatis注解开发

截屏2022-07-30 下午3.15.59.png

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="dev">
        <environment id="dev">
            <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"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <!--<mapper class="com.imooc.mybatis.dao.GoodsDao"/>-->
        <package name="com.imooc.mybatis.dao"/>
    </mappers>
</configuration>

GoodsDao.java接口

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

    @Insert("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})")
    @SelectKey(statement = "select last_insert_id()" ,before = false, keyProperty = "goodsId", resultType = Integer.class)
    public Integer insert(Goods goods);

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

MybatisTestor.java测试类

public class MybatisTestor {
    @Test
    public void testSelectByPriceRange() throws Exception {
        SqlSession sqlSession = null;

        try {
            sqlSession = MybatisUtils.openSession();
            GoodsDao goodsDao = sqlSession.getMapper(GoodsDao.class);
            List<Goods> list = goodsDao.selectByPriceRange(100F, 500F, 20);
            System.out.println(list.size());
        } catch (Exception e) {
            throw e;
        } finally {
            MybatisUtils.closeSession(sqlSession);
        }
    }

    @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);

            GoodsDao goodsDao = session.getMapper(GoodsDao.class);
            Integer num = goodsDao.insert(goods);
            session.commit();

            System.out.println(goods.getGoodsId());

        } catch (Exception e) {
            e.printStackTrace();
            if (session != null) {
                session.rollback();
            }
        } finally {
            MybatisUtils.closeSession(session);
        }
    }

    @Test
    public void testSelectAll() throws Exception {
        SqlSession sqlSession = null;

        try {
            sqlSession = MybatisUtils.openSession();
            GoodsDao goodsDao = sqlSession.getMapper(GoodsDao.class);
            List<GoodsDTO> list = goodsDao.selectAll();
            System.out.println(list.size());
        } catch (Exception e) {
            throw e;
        } finally {
            MybatisUtils.closeSession(sqlSession);
        }
    }
}

十五、MyBatis进阶

一、日志

二、动态SQL

三、二级缓存

四、对象关联查询

1、一对多

2、多对一

五、分页插件PageHelper

六、Mybatis整合C3P0连接池

七、Mybatis整合Druid连接池

八、Mybatis的批处理

九、Mybatis注解开发

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读