springboot-mybatits数据库，ecache缓存操作，批量操作，事物操作

古城微笑少年丶 2022-04-17 06:09 373阅读 0赞

# springboot-mybatits数据库，ecache缓存操作，批量操作，事物操作 #

### 文章目录 ###

*  springboot-mybatits数据库，ecache缓存操作，批量操作，事物操作
 *   *  1.mybatits，数据库ecache缓存操作
     *  2.mybatits，数据库批量操作：增删查改
     *  3.mybatits，数据库事物操作

代码下载： [https://github.com/2010yhh/springBoot-demos/tree/master/springboot-static][https_github.com_2010yhh_springBoot-demos_tree_master_springboot-static]  
**环境**

> idea2018，jdk1.8，
> 
> springboot版本：1.5.9.RELEASE

## 1.mybatits，数据库ecache缓存操作 ##

**ecache配置：**

<?xml version="1.0" encoding="UTF-8"?>
    <ehcache updateCheck="false" name="shiroCache">
    	<diskStore path="java.io.tmpdir"/>
    	<defaultCache
    			maxElementsInMemory="10000"
    			eternal="false"
    			timeToIdleSeconds="120"
    			timeToLiveSeconds="120"
    			overflowToDisk="false"
    			diskPersistent="false"
    			diskExpiryThreadIntervalSeconds="120"
    	/>
    	
    	<cache name="user"
    		   maxEntriesLocalHeap="2000"
    		   eternal="false"
    		   timeToIdleSeconds="60"
    		   timeToLiveSeconds="120"
    		   overflowToDisk="false"
    		   statistics="true">
    	</cache>
    </ehcache>

**使用：**

@Service
    @CacheConfig(cacheNames = {"user"})
    public class UserServiceImpl implements UserService {
        @Autowired
        UserMapper userMapper;
    
        @Cacheable(key = "#userName")
        @Override
        public List<User> findByUserName(String userName, String passWord) {
            UserExample example = new UserExample();
            example.createCriteria().andUserNameEqualTo(userName).andPassWordEqualTo(passWord);
            return userMapper.selectByExample(example);
        }
    
        @CachePut(key = "#user.userId")
        @Override
        @Transactional(isolation = Isolation.DEFAULT, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
        public void addUser(User user) {
            //测试事物特性
            userMapper.insertSelective(user);
             //userMapper.insertSelective(user);
        }
    
    }

**测试时，设置缓存30s,（timeToIdleSeconds=“30”  
timeToLiveSeconds=“30”）在缓存有效期内查询：从日志可看出，只查询数据库一次。**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3loaHloaHloaHloaA_size_16_color_FFFFFF_t_70]

## 2.mybatits，数据库批量操作：增删查改 ##

**配置：mapper的sql语句**

<insert id="batchAddUser" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="userId">
            insert into sys_user (user_id, user_name, real_name,
            pass_word, telephone, email,
            photo, create_time, update_time,
            status)
            values
            <foreach collection="list" item="item" index="index"
                     separator=",">
                (#{item.userId,jdbcType=INTEGER}, #{item.userName,jdbcType=VARCHAR}, #{item.realName,jdbcType=VARCHAR},
                #{item.passWord,jdbcType=VARCHAR}, #{item.telephone,jdbcType=VARCHAR}, #{item.email,jdbcType=VARCHAR},
                #{item.photo,jdbcType=VARCHAR}, #{item.createTime,jdbcType=TIMESTAMP},
                #{item.updateTime,jdbcType=TIMESTAMP},
                #{item.status,jdbcType=INTEGER})
            </foreach>
        </insert>
        
        <delete id="batchDeleteUser" parameterType="java.util.List">
            delete from sys_user where user_id in
            <foreach collection="list" index="index" item="item" open="("
                     separator="," close=")">
                #{item}
            </foreach>
        </delete>
        
        <select id="batchFindUser" resultMap="BaseResultMap" parameterType="java.util.List">
            select
            <include refid="Base_Column_List"/>
            from sys_user
            where user_name in
            <foreach collection="list" item="index" separator="," open="("
                     close=")">
                #{index}
            </foreach>
        </select>
        
        <update id="batchUpdate" parameterType="java.util.List">
    
            <foreach collection="list" separator=";" item="item" open="" close="">
                update sys_user
                <set>
                    <if test="#{item.userName} != null">
                        user_name = #{item.userName},
                    </if>
                    <if test="#{item.realName} != null">
                        real_name = #{item.realName},
                    </if>
                    <if test="#{item.passWord} != null">
                        pass_word = #{item.passWord},
                    </if>
                    <if test="#{item.telephone} != null">
                        telephone = #{item.telephone},
                    </if>
                    <if test="#{item.email} != null">
                        email = #{item.email,jdbcType=VARCHAR},
                    </if>
                    <if test="#{item.photo} != null">
                        photo = #{item.photo},
                    </if>
                    <if test="#{item.createTime}!= null">
                        create_time = #{item.createTime},
                    </if>
                    <if test="#{item.updateTime}!= null">
                        update_time = #{item.updateTime},
                    </if>
                    <if test="#{item.status} != null">
                        status = #{item.status},
                    </if>
                </set>
                where user_id = #{item.userId}
            </foreach>
        </update>
        
        <update id="batchUpdateUser" parameterType="java.util.List">
            update sys_user
            <trim prefix="set" suffixOverrides=",">
                <trim prefix="user_name =case" suffix="end,">
                    <foreach collection="list" item="item">
                        <if test="item.userName!=null">
                            when user_id=#{item.userId} then #{item.userName}
                        </if>
                    </foreach>
                </trim>
            <trim prefix="real_name =case" suffix="end,">
                <foreach collection="list" item="item">
                    <if test="item.realName!=null">
                        when user_id=#{item.userId} then #{item.realName}
                    </if>
                </foreach>
            </trim>
                <trim prefix="pass_word =case" suffix="end,">
                    <foreach collection="list" item="item">
                        <if test="item.passWord!=null">
                            when user_id=#{item.userId} then #{item.passWord}
                        </if>
                    </foreach>
                </trim>
                <trim prefix="telephone=case" suffix="end,">
                    <foreach collection="list" item="item">
                        <if test="item.telephone!=null">
                            when user_id=#{item.userId} then #{item.telephone}
                        </if>
                    </foreach>
                </trim>
                <trim prefix="email =case" suffix="end,">
                    <foreach collection="list" item="item">
                        <if test="item.email!=null">
                            when user_id=#{item.userId} then #{item.email}
                        </if>
                    </foreach>
                </trim>
                <trim prefix="photo =case" suffix="end,">
                    <foreach collection="list" item="item">
                        <if test="item.photo!=null">
                            when user_id=#{item.userId} then #{item.photo}
                        </if>
                    </foreach>
                </trim>
                <trim prefix="create_time=case" suffix="end,">
                    <foreach collection="list" item="item">
                        <if test="item.createTime!=null">
                            when user_id=#{item.userId} then #{item.createTime}
                        </if>
                    </foreach>
                </trim>
                <trim prefix="update_time =case" suffix="end,">
                    <foreach collection="list" item="item">
                        <if test="item.updateTime!=null">
                            when user_id=#{item.userId} then #{item.updateTime}
                        </if>
                    </foreach>
                </trim>
                <trim prefix="status=case" suffix="end,">
                    <foreach collection="list" item="item">
                        <if test="item.status!=null">
                            when user_id=#{item.userId} then #{item.status}
                        </if>
                    </foreach>
                </trim>
            </trim>
            <where>
                <foreach collection="list" separator="or" item="item">
                 user_id= #{item.userId}
                </foreach>
            </where>
        </update>

**2.1批量查询：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3loaHloaHloaHloaA_size_16_color_FFFFFF_t_70 1]

**2.2批量插入：**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3loaHloaHloaHloaA_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3loaHloaHloaHloaA_size_16_color_FFFFFF_t_70 3]  
**2.3批量更新：**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3loaHloaHloaHloaA_size_16_color_FFFFFF_t_70 4]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3loaHloaHloaHloaA_size_16_color_FFFFFF_t_70 5]

**2.4批量删除：**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3loaHloaHloaHloaA_size_16_color_FFFFFF_t_70 6]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3loaHloaHloaHloaA_size_16_color_FFFFFF_t_70 7]

## 3.mybatits，数据库事物操作 ##

>     * 声明式事务管理： 建立在AOP之上的。其本质是对方法前后进行拦截，
>      * 然后在目标方法开始之前创建或者加入一个事务，在执行完目标方法之后根据执行情况提交或者回滚事务。
>      * 声明式事务管理不需要入侵代码，通过@Transactional就可以进行事务操作，更快捷而且简单
>      * 隔离级别
>      * 隔离级别是指若干个并发的事务之间的隔离程度，与我们开发时候主要相关的场景包括：脏读取、重复读、幻读
>      */
>     
>     /**
>      * 传播行为
>      * 所谓事务的传播行为是指，如果在开始当前事务之前，
>      * 一个事务上下文已经存在，此时有若干选项可以指定一个事务性方法的执行行为
>      */

@CacheEvict(key = "#id")
        @Override
        @Transactional(isolation = Isolation.DEFAULT, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
        public void deleteUser(int userId) {
            //测试事物特性
            //userMapper.insertSelective(user);
            userMapper.deleteByPrimaryKey(userId);
        }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3loaHloaHloaHloaA_size_16_color_FFFFFF_t_70 8]

[https_github.com_2010yhh_springBoot-demos_tree_master_springboot-static]: https://github.com/2010yhh/springBoot-demos/tree/master/springboot-static
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3loaHloaHloaHloaA_size_16_color_FFFFFF_t_70]: /images/20220417/64fd678af63943aa91fbcd0537366c6f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3loaHloaHloaHloaA_size_16_color_FFFFFF_t_70 1]: /images/20220417/2105ff83a813405dbf96108296cac020.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3loaHloaHloaHloaA_size_16_color_FFFFFF_t_70 2]: /images/20220417/f0377b16c80e41d9b8190a5ab831ac5b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3loaHloaHloaHloaA_size_16_color_FFFFFF_t_70 3]: /images/20220417/83d53a3a222e41f7bdeacfe775ff22cc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3loaHloaHloaHloaA_size_16_color_FFFFFF_t_70 4]: /images/20220417/0d41912beb3e4ef8893f0fd7a0f17ebd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3loaHloaHloaHloaA_size_16_color_FFFFFF_t_70 5]: /images/20220417/a5b91fbeaafd4321a8b06559247f2724.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3loaHloaHloaHloaA_size_16_color_FFFFFF_t_70 6]: /images/20220417/c71298cb0e3b407a8567e4b65d8eb0bb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3loaHloaHloaHloaA_size_16_color_FFFFFF_t_70 7]: /images/20220417/26c93f4e3c0845cea4dc88c5a32281ff.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3loaHloaHloaHloaA_size_16_color_FFFFFF_t_70 8]: /images/20220417/128e7fa0f83f4a7ea058f265a98cce32.png