Mybatis映射文件SQL-增删查改/批量增删查改/SqlSessionBatch-SqlSessionTemplateBatch/动态拼装SQL/多表连接操作属性使用-模板

爱被打了一巴掌 2024-04-08 13:15 29阅读 0赞

增删查改/批量增删查改

SqlSessionBatch-SqlSessionTemplateBatch

package com.xch.sqlSessionTemplate;
    
    import org.apache.ibatis.session.ExecutorType;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.mybatis.spring.SqlSessionTemplate;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
    import org.springframework.context.annotation.Bean;
    import org.springframework.context.annotation.Configuration;
    
    /**
     * @author Chenghe Xu
     * @date 2022/9/5 22:58
     * 
     * 申明多个注入Bean注释，只会注入一个Bean
     * 因为注释的底层都会有@ConditionalOnMissingBean(xxx.class)，没有该Bean才创建，防止重复创建
     */
    @Configuration
    public class CreateSqlSessionTemplate {
    
        /**
         * 注入开启Batch模式的SqlSessionTemplate对象
         */
        @Bean({"sqlSessionTemplateBatch"})
        @ConditionalOnMissingBean(name = {"sqlSessionTemplateBatch"})
        @Autowired
        public SqlSessionTemplate createSqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
            return new SqlSessionTemplate(sqlSessionFactory, ExecutorType.BATCH);
        }
    
    }

package com.xch.dao.impl;
    
    import com.xch.dao.WorkDAO;
    import com.xch.mapper.WorkMapper;
    import com.xch.pojo.Work;
    import org.apache.ibatis.session.ExecutorType;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.mybatis.spring.SqlSessionTemplate;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.context.ApplicationContext;
    import org.springframework.stereotype.Repository;
    
    import javax.annotation.Resource;
    import java.util.Arrays;
    import java.util.List;
    
    /**
     * @author Chenghe Xu
     * @date 2022/9/5 20:47
     */
    @Repository
    public class WorkDAOImpl implements WorkDAO {
        
        @Resource
        ApplicationContext applicationContext;
    
        private final SqlSessionFactory sqlSessionFactory;
        private final SqlSessionTemplate sqlSessionTemplate;
        
        /**
         * SqlSessionFactory/SqlSessionTemplate默认在Spring容器中的
         * 注入开启Batch模式的SqlSessionTemplate自定义对象splSessionTemplateBatch
         *      代替原有的SqlSessionTemplate对象
         */
        @Autowired
        public WorkDAOImpl(SqlSessionFactory sqlSessionFactory, @Qualifier("sqlSessionTemplateBatch") SqlSessionTemplate sqlSessionTemplate) {
            this.sqlSessionFactory = sqlSessionFactory;
            this.sqlSessionTemplate = sqlSessionTemplate;
        }
    
        public void test(){
            System.out.println(sqlSessionFactory);
            System.out.println(sqlSessionTemplate);
            System.out.println(applicationContext.getBeansOfType(SqlSessionTemplate.class));
            System.out.println(Arrays.toString(applicationContext.getBeanNamesForType(SqlSessionTemplate.class)));
        }
        
        /**
         * SqlSession/SqlSessionTemplate异同：
         * 1、SqlSession需要手动commit和close流
         *    SqlSessionTemplate自动commit和close流
         * 2、SqlSession并发下线程不安全
         *    SqlSessionTemplate并发下线程安全
         * 3、SqlSessionTemplate是SqlSession的实现类
         *    其底层都是通过jdbc的PreparedStatement.executeBatch()执行批量操作的
         *    
         * 用法：
         * 业务逻辑中其它操作（删查改）混合，使用SqlSession
         * 业务逻辑中只有批量新增操作，使用SqlSessionTemplate
         */
        @Override
        public Integer advanceBatchWork1(List<Work> workList) {
            //try (AutoCloseable/Closeable 需要自动关闭的资源)
            //jdk1.7新特性：try ()中申明的流资源，在try语句结束后自动关闭
            //相当于简化了finally{ManualCloseable 需要手动关闭的资源}
            try (SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH)){
                WorkMapper workMapper = sqlSession.getMapper(WorkMapper.class);
                workList.forEach(workMapper::baseInsertWork);
                sqlSession.commit();
                return workList.size();
            }
            //不需要捕获catch (){rollback();}，发生异常后Spring会回滚方法的执行
        }
        
        @Override
        public Integer advanceBatchWork2(List<Work> workList) {
            //SqlSessionTemplate自动管理了提交commit和关闭流close
            WorkMapper workMapper = sqlSessionTemplate.getMapper(WorkMapper.class);
            workList.forEach(workMapper::baseInsertWork);
            return workList.size();
        }
        
    }

动态拼装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="com.xch.mapper.UserMapper">
        
        <resultMap id="BaseResultMap" type="com.xch.pojo.User">
            <id property="uId" jdbcType="VARCHAR" column="u_id"/>
            <result property="uName" jdbcType="VARCHAR" column="u_name"/>
            <result property="uAge" jdbcType="INTEGER" column="u_age"/>
            <result property="uPhone" jdbcType="CHAR" column="u_phone"/>
            <result property="uCreateTime" jdbcType="TIMESTAMP" column="u_create_time"/>
        </resultMap>
        
        <sql id="Base_Column_List">
            u_id, u_name, u_age, u_phone, u_create_time
        </sql>
    
        <sql id="Alias_Column_List">
            user.u_id, user.u_name, user.u_age, user.u_phone, user.u_create_time
        </sql>
    
    
        
        
        
        
        
        
        
        <select id="ifTest" parameterType="com.xch.pojo.User" resultMap="BaseResultMap">
            select <include refid="Base_Column_List"/>
            from dynamic_sql_user
            where 1=1
            <if test="uName neq null and uName != ''">
                and u_name=#{uName,jdbcType=VARCHAR}
            </if>
            <if test="uAge neq null and uAge >= 0 and uAge lte 160">
                and u_age=#{uAge,jdbcType=INTEGER}
            </if>
        </select>
    
        
        <select id="whereTest" parameterType="com.xch.pojo.User" resultMap="BaseResultMap">
            select <include refid="Base_Column_List"/>
            from dynamic_sql_user
            <where>
                <if test="uName neq null and uName != ''">
                    and u_name=#{uName,jdbcType=VARCHAR}
                </if>
                <if test="uAge neq null and uAge >= 0 and uAge lte 160">
                    and u_age=#{uAge,jdbcType=INTEGER}
                </if>
            </where>
        </select>
    
        
        <select id="chooseTest" parameterType="com.xch.pojo.User" resultMap="BaseResultMap">
            select <include refid="Base_Column_List"/>
            from dynamic_sql_user
            where 1=1
            <choose>
                <when test="uName neq null and uName != ''">
                    and u_name=#{uName,jdbcType=VARCHAR}
                </when>
                <when test="uAge neq null and uAge >= 0 and uAge lte 160">
                    and u_age=#{uAge,jdbcType=INTEGER}
                </when>
                <otherwise>
                    order by u_age, u_id
                </otherwise>
            </choose>
        </select>
    
        
        <update id="setTest" parameterType="com.xch.pojo.User">
            update dynamic_sql_user
            <set>
                <if test="uName neq null and uName != ''">
                    u_name=#{uName,jdbcType=VARCHAR},
                </if>
                <if test="uAge neq null and uAge >= 0 and uAge lte 160">
                    and u_age=#{uAge,jdbcType=INTEGER}
                </if>
            </set>
            where u_id=#{uId,jdbcType=VARCHAR}
        </update>
    
        
        <select id="trimTest1" parameterType="com.xch.pojo.User" resultMap="BaseResultMap">
            select <include refid="Base_Column_List"/>
            from dynamic_sql_user
            <trim prefix="where" prefixOverrides="and">
                <if test="uName neq null and uName != ''">
                    and u_name=#{uName,jdbcType=VARCHAR}
                </if>
                <if test="uAge neq null and uAge >= 0 and uAge lte 160">
                    and u_age=#{uAge,jdbcType=INTEGER}
                </if>
            </trim>
        </select>
        
        
        <update id="trimTest2" parameterType="com.xch.pojo.User">
            update dynamic_sql_user
            <trim prefix="set" suffixOverrides=",">
                <if test="uName neq null and uName != ''">
                    u_name=#{uName,jdbcType=VARCHAR},
                </if>
                <if test="uAge neq null and uAge >= 0 and uAge lte 160">
                    and u_age=#{uAge,jdbcType=INTEGER}
                </if>
            </trim>
            where u_id=#{uId,jdbcType=VARCHAR}
        </update>
        
        
        
    
        
        <resultMap id="BaseResultWithWorkMap1" type="com.xch.pojo.UserWithWork">
            <id property="uId" jdbcType="VARCHAR" column="u_id"/>
            <result property="uName" jdbcType="VARCHAR" column="u_name"/>
            <result property="uAge" jdbcType="INTEGER" column="u_age"/>
            <result property="uPhone" jdbcType="CHAR" column="u_phone"/>
            <result property="uCreateTime" jdbcType="TIMESTAMP" column="u_create_time"/>
            
            <association property="work" javaType="com.xch.pojo.Work">
                <id property="wId" jdbcType="VARCHAR" column="w_id"/>
                <result property="wDept" jdbcType="VARCHAR" column="w_dept"/>
                <result property="wPosition" jdbcType="VARCHAR" column="w_position"/>
                <result property="wSalary" jdbcType="DECIMAL" column="w_salary"/>
                <result property="wCreateTime" jdbcType="TIMESTAMP" column="w_create_time"/>
                <result property="uId" jdbcType="VARCHAR" column="u_id"/>
            </association>
        </resultMap>
        <select id="associationTest" parameterType="string" resultMap="BaseResultWithWorkMap1">
            select <include refid="Alias_Column_List"/>,<include refid="com.xch.mapper.WorkMapper.Alias_Column_List"/>
            from dynamic_sql_user user
            left join dynamic_sql_work work
            on user.u_id=work.u_id
            where work.u_id=#{uId,jdbcType=VARCHAR}
        </select>
    
        
        <resultMap id="BaseResultWithWorkMap2" type="com.xch.pojo.UserWithWork">
            <id property="uId" jdbcType="VARCHAR" column="u_id"/>
            <result property="uName" jdbcType="VARCHAR" column="u_name"/>
            <result property="uAge" jdbcType="INTEGER" column="u_age"/>
            <result property="uPhone" jdbcType="CHAR" column="u_phone"/>
            <result property="uCreateTime" jdbcType="TIMESTAMP" column="u_create_time"/>
            
            <collection property="workList" ofType="com.xch.pojo.Work">
                <id property="wId" jdbcType="VARCHAR" column="w_id"/>
                <result property="wDept" jdbcType="VARCHAR" column="w_dept"/>
                <result property="wPosition" jdbcType="VARCHAR" column="w_position"/>
                <result property="wSalary" jdbcType="DECIMAL" column="w_salary"/>
                <result property="wCreateTime" jdbcType="TIMESTAMP" column="w_create_time"/>
                <result property="uId" jdbcType="VARCHAR" column="u_id"/>
            </collection>
        </resultMap>
        <select id="collectionTest" parameterType="string" resultMap="BaseResultWithWorkMap2">
            select <include refid="Alias_Column_List"/>,<include refid="com.xch.mapper.WorkMapper.Alias_Column_List"/>
            from dynamic_sql_user user
            left join dynamic_sql_work work
            on user.u_id=work.u_id
            where work.u_id=#{uId,jdbcType=VARCHAR}
        </select>
    
        
        <resultMap id="BaseResultWithWorkMap3" type="com.xch.pojo.UserWithWork" extends="BaseResultMap">
            
            <association property="work" javaType="com.xch.pojo.Work">
                <id property="wId" jdbcType="VARCHAR" column="w_id"/>
                <result property="wDept" jdbcType="VARCHAR" column="w_dept"/>
                <result property="wPosition" jdbcType="VARCHAR" column="w_position"/>
                <result property="wSalary" jdbcType="DECIMAL" column="w_salary"/>
                <result property="wCreateTime" jdbcType="TIMESTAMP" column="w_create_time"/>
                <result property="uId" jdbcType="VARCHAR" column="u_id"/>
            </association>
        </resultMap>
        <select id="extendTest" parameterType="string" resultMap="BaseResultWithWorkMap3">
            select <include refid="Alias_Column_List"/>,<include refid="com.xch.mapper.WorkMapper.Alias_Column_List"/>
            from dynamic_sql_user user
            left join dynamic_sql_work work
            on user.u_id=work.u_id
            where work.u_id=#{uId,jdbcType=VARCHAR}
        </select>
        
        
        <resultMap id="BaseResultWithWorkMap4" type="com.xch.pojo.UserWithWork" extends="BaseResultMap">
            
            <association property="work" resultMap="com.xch.mapper.WorkMapper.BaseResultMap"/>
        </resultMap>
        <select id="resultMapTest" parameterType="string" resultMap="BaseResultWithWorkMap4">
            select <include refid="Alias_Column_List"/>,<include refid="com.xch.mapper.WorkMapper.Alias_Column_List"/>
            from dynamic_sql_user user
            left join dynamic_sql_work work
            on user.u_id=work.u_id
            where work.u_id=#{uId,jdbcType=VARCHAR}
        </select>
        
        
        <resultMap id="BaseResultWithWorkMap5" type="com.xch.pojo.UserWithWork" extends="BaseResultMap">
            
            
            
            <association property="work" column="u_id" select="com.xch.mapper.WorkMapper.baseSelectWork"/>
        </resultMap>
        <select id="selectTest" parameterType="string" resultMap="BaseResultWithWorkMap5">
            select <include refid="Base_Column_List"/>
            from dynamic_sql_user
            where u_id=#{uId,jdbcType=VARCHAR}
        </select>
        
    </mapper>

多层结构树综合查询使用比较

/**
     * 组装结构树算法工具方法(可以抽离到公共工具类中定义)
     */
    public List<LevelTreeExtra> fixLevelTreeExtra(List<LevelTreeExtra> levelTreeList) {
        // 入参判断(公共的工具方法，需要做强检验，确保健壮性/兼容性/适配性)
        Assert.notEmpty(levelTreeList, "不能组装空集合");
    //    if (ObjectUtil.isEmpty(levelTreeList)) {
    //        // 返回不可操作的空集合
    //        Collections.emptyList();
    //    }
        
        // 业务逻辑
        // 先进行JDK8新特性去重再按parentId分组
        Map<Integer, List<LevelTreeExtra>> levelTreeListMapByParentId = levelTreeList.stream()
                .distinct().collect(Collectors.groupingBy(LevelTreeExtra::getParentId));
        // 再递归(DFS)批量组装
        return fixDFS(levelTreeListMapByParentId);
    }
    
    /**
     * 递归(DFS)批量组装结果树
     */
    public List<LevelTreeExtra> fixDFS(Map<Integer, List<LevelTreeExtra>> levelTreeListMapByParentId) {
        List<LevelTreeExtra> levelTreeExtraList = levelTreeListMapByParentId.get(-1);
        if (ObjectUtil.isEmpty(levelTreeExtraList)) {
            return Collections.emptyList();
        }
        
        levelTreeExtraList.forEach(levelTreeExtra -> DFS(levelTreeListMapByParentId, levelTreeExtra));
        return levelTreeExtraList;
    }
    
    /**
     * 递归函数(写法一：参数传递/写法二：调用传递，仅适用于调用者是递归方法体类本身，才可以用this)
     */
    public void DFS(Map<Integer, List<LevelTreeExtra>> levelTreeListMapByParentId, LevelTreeExtra levelTreeExtra) {
        // 递归出口(判断的是当前状态的内容，通常判断为结束时，不再做操作)
        List<LevelTreeExtra> nextLevelTreeList = levelTreeListMapByParentId.get(levelTreeExtra.getId());
        if (ObjectUtil.isEmpty(nextLevelTreeList)) {
            return;
        }
        
        // 递归体
        levelTreeExtra.setNextLevelTreeList(nextLevelTreeList);
        nextLevelTreeList.forEach(nextLevelTree -> DFS(levelTreeListMapByParentId, nextLevelTree));
    }

其中：

1、基本数据类型可以简写(全小写)代替完整包.类名（可以通过Idea跳转测试）

2、入参有多个时，mybatis接口层用@param("xxx")标注，映射文件SQL不用写入参类型