无废话mybatis七(批量操作)

作者: 毛仑上保罗先生 | 来源:发表于2018-11-07 21:38 被阅读0次

无废话mybatis七(批量操作)
mybatis for mysql数据库批量操作(insert
Mybatis开发总结
mybatis+oracle 批量操作
Mybatis 批量操作
Mybatis (批量操作)
mybatis 批量操作
mybatis 批量操作
mybatis 批量操作
mybatis批量插入，control中传入参数为

废话少说,直接上菜，此爲mysql的批量添加
UserMapper.java


    public void insertBulk(@Param("sysUsers") List<SysUser> list);

UserMapper.xml

<insert id="insertBulk" parameterType="java.util.List">
        INSERT INTO sys_user (user_name, user_password, user_email, user_info, create_time)
        VALUES
        <foreach collection="sysUsers" item="item" index="index" separator=",">
           ( 
                #{item.userName,jdbcType=VARCHAR},
                #{item.userPassword,jdbcType=VARCHAR},
                #{item.userEmail,jdbcType=VARCHAR},
                #{item.userInfo,jdbcType=VARCHAR},
                #{item.createTime,jdbcType=TIMESTAMP}
            )
        </foreach>
    </insert>

UserMapperTest.java

@Test
    public void testInsertBulk(){
        SqlSession sqlSession = getSqlSession();
        try {
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            List<SysUser> sysUsers=new ArrayList<SysUser>();
            for (int i=0;i<10;i++) {
                SysUser sysUser = new SysUser();
                sysUser.setUserName(UUID.randomUUID().toString());
                sysUser.setUserPassword(UUID.randomUUID().toString());
                sysUser.setUserEmail( UUID.randomUUID()+"@163.com");
                sysUser.setUserInfo(UUID.randomUUID().toString());
                sysUser.setCreateTime(new Date());
                sysUsers.add(sysUser); 
            }
            userMapper.insertBulk(sysUsers);
        } finally {
            sqlSession.commit();
            sqlSession.close();
        }
    }

執行發現

[DEBUG] 2018-11-07 21:31:18,920 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)
==>  Preparing: INSERT INTO sys_user (user_name, user_password, user_email, user_info, create_time) VALUES ( ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ? ) 
[DEBUG] 2018-11-07 21:31:18,954 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)
==> Parameters: 9afa2569-350b-4eb2-a444-42bb08c9b290(String), 5df15ff4-9337-4446-919c-3b9902c0d8a9(String), 56879c3f-b25b-4a87-9036-784d2d51a0ba@163.com(String), e4bd2427-f1b0-4a96-8f15-9fbc6ab834ff(String), 2018-11-07 21:31:18.704(Timestamp), 4bfef836-2f05-4690-8f26-fd9703ea29c8(String), 20d006f2-36ed-4801-80a9-33b78ae63458(String), fa71be49-0db9-4ada-91ad-21c87d7a39de@163.com(String), c7dc9543-006d-4dc9-9694-8330af12feac(String), 2018-11-07 21:31:18.704(Timestamp), e81d67e9-e5e6-4802-b553-b4ab2139cabd(String), 927d24a5-0356-4b35-be96-5b1e32379697(String), c1c020a4-9fd5-457c-af60-eb771767849c@163.com(String), 50e5fb07-6cf9-45bd-8836-e8ed046db674(String), 2018-11-07 21:31:18.704(Timestamp), dc7640b1-970a-4bd6-ad1d-fa8860eb8ee3(String), 64ad6a23-ff2c-4d62-9015-1c77a8a5f7e4(String), 59a7c870-c929-4aa7-8108-87e72323f52e@163.com(String), 5793e5e4-61c8-4548-88b6-dd9a1e0e09b0(String), 2018-11-07 21:31:18.704(Timestamp), ec16d4a5-c5f9-4a7e-8b8e-4b68ac6a43f2(String), e8652b0f-51eb-49b8-8566-33c7c42e903c(String), d8bcb612-330d-4c61-b423-c55c41285da6@163.com(String), c0fb4aa5-6412-4c40-bd86-1315ad581bbe(String), 2018-11-07 21:31:18.704(Timestamp), 42ce7b6e-6f95-4a63-b274-80e3398c46c2(String), 218c4d19-207b-4eed-bc86-84d07ad0f87c(String), f1078020-f384-49e9-aa49-04edefdfca35@163.com(String), c59807f0-63b1-4522-a25f-1dcd27064114(String), 2018-11-07 21:31:18.704(Timestamp), 28eb4cd6-2b6e-4f7a-b42e-28486eff9e70(String), df6e83ab-312d-4956-9275-17e6e075cb09(String), adb67320-e961-4cbd-a35c-1814d9760d71@163.com(String), 077c4474-f6e1-4f79-ae19-d864cfcbefad(String), 2018-11-07 21:31:18.704(Timestamp), 227bf52e-db7f-463d-a8b8-086332b2b6a6(String), 9cd73c49-52b9-444e-92ef-be784c067a2d(String), 82b439bf-9036-429e-8c22-aa87c00786d6@163.com(String), 68d9db65-e7b1-41ac-ac95-4dcb0fd55848(String), 2018-11-07 21:31:18.704(Timestamp), 1948e74b-20b8-40e0-826e-e8df7e777bd9(String), aeae58a4-951d-4517-bca7-d13f3fede77c(String), 476d4fb9-3911-4203-ad82-46946dc1ea4b@163.com(String), 1df6b26c-d2d1-4eb8-9312-c9514228a82c(String), 2018-11-07 21:31:18.704(Timestamp), 100883b3-f5fd-47f2-8695-6bf6c5e312f6(String), 9892f00e-55ae-4a3c-b025-a306ad92b2ec(String), 0693fd52-267e-4b2e-bbfb-97dc9dfbb0f8@163.com(String), 652b41d9-b5e6-4255-a34c-13301534d495(String), 2018-11-07 21:31:18.704(Timestamp)
[DEBUG] 2018-11-07 21:31:19,019 method:org.apache.ibatis.logging.jdbc.BaseJdbcLogger.debug(BaseJdbcLogger.java:159)
<==    Updates: 10

成功，看console中的message應該已經知道sql語句是怎麽寫的了，重點說下xml中的寫法

图片.png

foreach：循環

collection：集合，這裏我們用@Param("sysUsers")指定為sysUsers，如果沒有指定默認為list ，這個是一個關鍵點，需要特別注意，另外如果集合爲空將會報錯，所以在傳參數的時候一定要確保有有值, 如果没有你懂怎么处理的..

item: 項

index：項索引

separator：執行完用什麽結尾，如果是最後結尾會自動去除

####################################################################
批量返回自增主键
前面章节已经有提过,返回自增主键两种方式,我这里的话采用的是在insert标签中增加useGeneratedKeys="true" keyProperty="id"

<insert id="insertBulk" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO sys_user (user_name, user_password, user_email, user_info,
        create_time)
        VALUES
        <foreach collection="list" item="item" index="index" separator=",">
            (
                #{item.userName,jdbcType=VARCHAR},
                #{item.userPassword,jdbcType=VARCHAR},
                #{item.userEmail,jdbcType=VARCHAR},
                #{item.userInfo,jdbcType=VARCHAR},
                #{item.createTime,jdbcType=TIMESTAMP}
            )
        </foreach>
    </insert>

@Test
    public void testInsertBulk() {
        SqlSession sqlSession = getSqlSession();
        try {

            List<SysUser> sysUsers = new ArrayList<SysUser>();
            for (int i = 0; i < 10; i++) {
                SysUser sysUser = new SysUser();
                sysUser.setUserName(UUID.randomUUID().toString());
                sysUser.setUserPassword(UUID.randomUUID().toString());
                sysUser.setUserEmail(UUID.randomUUID() + "@163.com");
                sysUser.setUserInfo(UUID.randomUUID().toString());
                sysUser.setCreateTime(new Date());
                sysUsers.add(sysUser);
            }
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            int reulst= userMapper.insertBulk(sysUsers);
            System.out.println(reulst);
            System.out.println(sysUsers);
        } finally {
            sqlSession.commit();
            sqlSession.close();
        }
    }

我将上面的insertBulk接口稍微改动了下, 将返回值改成了int, 运行发现返回的reulst是10 , 证明我们传入list的10条数据都已经存入成功了, 然后我们在吧sysUsers打印出来(记住是传入的参数), 我们会发现id已经有值了, 另外需要特别注意, 我这里测试的时候发现, 如果将方法参数用@Param("sysUsers")指定就没有办法返回主键, 必须要用默认的list才行

image.png

批量更新

<update id="update" parameterType="java.util.List">
        update _audit
        <foreach collection="list" item="item" index="index" open="" close="" separator=";">
            <set>
                <if test="item.bankCode != null and item.bankCode != ''">
                    bank_code = #{item.bankCode},
                </if>
                <if test="item.bankName != null and item.bankName != ''">
                    bank_name = #{item.bankName},
                </if>
                <if test="item.userName != null and item.userName != ''">
                    user_name = #{item.userName},
                </if>
                <if test="item.createTime != null">
                    create_time = #{item.createTime},
                </if>
                <if test="item.updateTime != null">
                    update_time = #{item.updateTime},
                </if>
                <if test="item.agentName != null and item.agentName != ''">
                    agent_name = #{item.agentName},
                </if>
                <if test="item.payoutStatus != null">
                    payout_status = #{item.payoutStatus},
                </if>
            </set>
            where 1=1
            <if test="item.Id!=null">
                and
                id=#{item.Id}
            </if>
        </foreach>
    </update>

批量查询

<select id="pageListBybatcheNo" parameterType="java.util.List"
        resultType="com.domain.Audit"
        flushCache="true">
        select * from audit
        where id in
        <foreach collection="list" item="item" index="index" open="("close=")" separator=",">
            #{item.Id}
        </foreach>
                   and (bank_code is null or bank_code='')
    </select>

无废话mybatis七(批量操作)
废话少说,直接上菜，此爲mysql的批量添加UserMapper.java UserMapper.xml Use...
mybatis for mysql数据库批量操作(insert
批量添加批量修改 in/or 操作优化(mysql) mybatis 实现 in操作优化
Mybatis开发总结
Mybatis批量操作 Mybatis大于小于号使用 mybatis在xml中不能直接使用> <等符号，需要用> ...
mybatis+oracle 批量操作
在mybatis中，对于oracle的批量操作有点特殊，用到了就记一下。批量插入批量插入（带序列）批量插入（...
Mybatis 批量操作
Mybatis (批量操作)
1.批量插入 forearch的参数可以是array，list，map这三种 2.批量删除 3.批量更新通过一条...
mybatis 批量操作
一些时候需要将一些数据批量的进行操作，比如：根据ids批量查询、更新、删除、将一个集合的数据插入数据库。简单的操作...
mybatis 批量操作
大纲本文旨在分享一下mybatis批量操作的实现方式批量插入批量更新批量插入或更新同时分享一个对代码生成...
mybatis 批量操作
mybatis批量插入，control中传入参数为
关于mybatis批量问题，见之前文章mybatis批量插入、批量更新和批量删除mybatis批量新增，插入的数据...