Mybatis:通过on duplicate key update实现批量插入或更新-阿里云开发者社区

开发者社区> 琦彦> 正文

Mybatis:通过on duplicate key update实现批量插入或更新

简介: Mybatis:通过on duplicate key update实现批量插入或更新
+关注继续查看


批量的saveOrupdate:

    1. 使用要点:
      (1) 表要求必须有主键或唯一索引才能起效果,否则insert或update无效;
      (2)  注意语法on duplicate key update后面应为需要更新字段,不需要更新的字段不用罗列;
      (3) 相较于replace into(insert加强版,不存在时insert,存在时先delete后insert)虽然也能达到批量更新目的,但因为删除和添加需要重复维护索引,所以大批量比on duplicate key update性能要差,小量可忽略,自选为主。
    2. foreach中()后面不要有空格,如果报错
    org.springframework.jdbc.BadSqlGrammarException: 
    ### Error updating database.  
    Cause: java.sql.SQLSyntaxErrorException: 
    You have an error in your SQL syntax; 
    check the manual that corresponds to
     your MySQL server version for the right syntax to use near '
    1. image.gif除了检查,是否缺少或者多余,还要检查是否有多余空格
    <!--批量的插入 or  更新的操作-->
      <insert id="batchInsertStaff">
        insert into xxxTable (
           merchant_id, app_id, department_id,
          `name`, mobile, token,
          created_at, updated_at)
        values
        <foreach collection="list" item="item" separator="," >
          (
          #{item.merchantId,jdbcType=INTEGER},#{item.appId,jdbcType=INTEGER},
           #{item.departmentId},
          #{item.name},#{item.mobile},#{item.token},
          NOW(),NOW()
          )
        </foreach>
        on duplicate key update department_id = values(department_id),updated_at = values(updated_at)
      </insert>

    image.gif

    单条的saveOrupdate:

    1.根据selectkey判断查询的count值是否为1,然后再进行新增或更新

    <insert id="insertOrUpdateOneUserInfo">
            <selectKey keyProperty="count" resultType="int" order="BEFORE">
                select count(*) as count from `${tableName}` where userid = #{user.userid}
            </selectKey>
            <!-- 如果大于0则更新 -->
            <if test="count>0">
                UPDATE  `${tableName}`
                <set >
                    <if test="user.appId != null" >
                        `app_id` = #{user.appId},
                    </if>
                    <if test="user.yunid != null" >
                        `yunid` = #{user.yunid},
                    </if>
                    <if test="user.qr_scene != null" >
                        `qr_scene` = #{user.qr_scene},
                    </if>
                    <if test="user.openid != null" >
                        `openid` = #{user.openid},
                    </if>
                    <if test="user.qr_scene_str != null" >
                        `qr_scene_str` = #{user.qr_scene_str},
                    </if>
                    <if test="user.nickname != null" >
                        `nickname` = #{user.nickname},
                    </if>
                    <if test="user.language != null" >
                        `language` = #{user.language},
                    </if>
                    <if test="user.city != null" >
                        `city` = #{user.city},
                    </if>
                    <if test="user.country != null" >
                        `country` = #{user.country},
                    </if>
                    <if test="user.remark != null" >
                        `remark` = #{user.remark},
                    </if>
                    <if test="user.headimgurl != null" >
                        `headimgurl` = #{user.headimgurl},
                    </if>
                    <if test="user.province != null" >
                        `province` = #{user.province},
                    </if>
                    <if test="user.tagIdList != null" >
                        `tagid_list` = #{user.tagIdList},
                    </if>
                    <if test="user.subscribe_scene != null" >
                        `subscribe_scene` = #{user.subscribe_scene},
                    </if>
                    <if test="user.unionid != null" >
                        `unionid` = #{user.unionid},
                    </if>
                    <if test="user.subscribe != null" >
                        `subscribe` = #{user.subscribe},
                    </if>
                    <if test="user.groupid != null" >
                        `groupid` = #{user.groupid},
                    </if>
                    <if test="user.subTime != null" >
                        `subscribe_time` = #{user.subTime},
                    </if>
                    <if test="user.sexStr != null" >
                        `sex` = #{user.sexStr},
                    </if>
                    <if test="user.updatedAt != null" >
                        `updated_at` = #{user.updatedAt},
                    </if>
                </set>
                WHERE userid = #{user.userid}
            </if>
            <!-- 如果等于0则保存 -->
            <if test="count==0">
                INSERT IGNORE INTO `${tableName}`
                (
                <include refid="wx_temp_params" />
                )
                values
                (
                #{user.appId},#{user.yunid},#{user.userid},#{user.point},#{user.qr_scene},
                #{user.openid},#{user.qr_scene_str},#{user.nickname},#{user.language},#{user.city},
                #{user.country},#{user.remark},#{user.headimgurl},#{user.province},#{user.tagIdList},
                #{user.subscribe_scene},#{user.unionid},#{user.subscribe},#{user.groupid},#{user.subTime},
                #{user.sexStr},#{user.createdAt},#{user.updatedAt}
                )
            </if>
        </insert>

    image.gif

    2.根据相应的唯一主键来判断是否新增或更新  [对事务支持较好]

    INSERT INTO 
    user(userid,sex,age) 
    VALUES('oCCtTxOz28457LUISKyOq4r94DYE','男',18) 
    ON DUPLICATE KEY UPDATE sex=VALUES(sex),age=VALUES(age)

    image.gif

    ON DUPLICATE KEY UPDATE 附带更新条件

    1.  单条语句--不为空进行更新

    在update后面要判断参数是否为空,如果不为空进行更新,为空的就不进行更新了

    insert into sys_hk_goods
        (
        `sku_code`,
        `product_code`,
        `sku_name`,
        `uom`,
        `wrapping`,
        `weight`,
        `length`,
        `width`,
        `height`,
        `logistics_package`,
        `package_material`,
        `picture_url`
        )
        values
        (
        #{skuCode},
        #{productCode},
        #{skuName},
        #{uom},
        #{wrapping},
        #{weight},
        #{length},
        #{width},
        #{height},
        #{logisticsPackage},
        #{packageMaterial},
        #{pictureUrl}
        )
        ON DUPLICATE KEY UPDATE
        <trim prefix="" suffixOverrides=",">
            <if test="productCode != null">`product_code` = #{productCode}, </if>
            <if test="skuName != null">`sku_name` = #{skuName}, </if>
            <if test="uom != null">`uom` = #{uom}, </if>
            <if test="wrapping != null">`wrapping` = #{wrapping}, </if>
            <if test="weight != null">`weight` = #{weight}, </if>
            <if test="length != null">`length` = #{length}, </if>
            <if test="width != null">`width` = #{width}, </if>
            <if test="height != null">`height` = #{height}, </if>
            <if test="logisticsPackage != null">`logistics_package` = #{logisticsPackage}, </if>
            <if test="packageMaterial != null">`package_material` = #{packageMaterial}, </if>
            <if test="pictureUrl != null">`picture_url` = #{pictureUrl}</if>
        </trim>
    </insert>

    image.gif

    之所以使用了trim, 是因为当最后一个pictureUrl为空的时候,会报异常,因为不走最后一条数据了,多了一个”,”,

    处理方式可以用mybatis中的trim进行判断

    2.  批量操作--不为空进行更新

    <!--批量插入or更新-->
    <insert id="batchSaveOrUpdateSimulatorInfo">
        insert into dm_simulator_info
        (
        simulator_id, simulator_name,
        simulator_state,simulator_type,
        simcontorler_name, simcontorler_id,
        create_time,update_time
        )
        values
        <foreach collection="list" item="item" separator=",">
            (
            #{item.simulatorId,jdbcType=VARCHAR}, #{item.simulatorName,jdbcType=VARCHAR},
            #{item.simulatorState,jdbcType=INTEGER},#{item.simulatorType,jdbcType=INTEGER},
            #{item.simcontorlerName,jdbcType=VARCHAR}, #{item.simcontorlerId,jdbcType=VARCHAR},
            NOW(),NOW()
            )
        </foreach>
        on duplicate key update
        <trim prefix="" suffixOverrides=",">
            <foreach collection="list" item="item" separator=",">
                <if test="item.simulatorName != null">
                    simulator_name = #{item.simulatorName,jdbcType=VARCHAR},
                </if>
                <if test="item.simulatorType != null">
                    simulator_type = #{item.simulatorType,jdbcType=VARCHAR},
                </if>
                <if test="item.simulatorState != null">
                    simulator_state = #{item.simulatorState,jdbcType=INTEGER},
                </if>
                <if test="item.simcontorlerName != null">
                    simcontorler_name = #{item.simcontorlerName,jdbcType=VARCHAR},
                </if>
                <if test="item.simcontorlerId != null">
                    simcontorler_id = #{item.simcontorlerId,jdbcType=VARCHAR},
                </if>
                update_time =NOW()
            </foreach>
        </trim>
    </insert>

    image.gif

    因为是批量, 所以ON DUPLICATE KEY UPDATE 附带的更新条件要使用foreach,  打印出来的SQL如下

    INSERT INTO dm_simulator_info (
        simulator_id,
        simulator_name,
        simulator_state,
        simulator_type,
        simcontorler_name,
        simcontorler_id,
        create_time,
        update_time
    )
    VALUES
        (?, ?, ?,?, ?, ?, NOW(), NOW()),
        (?, ?, ?,?, ?, ?, NOW(), NOW()),
        (?, ?, ?,?, ?, ?, NOW(), NOW()),
        (?, ?, ?,?, ?, ?, NOW(), NOW()),
        (?, ?, ?,?, ?, ?, NOW(), NOW()),
        (?, ?, ?,?, ?, ?, NOW(), NOW()) 
    ON DUPLICATE KEY UPDATE 
        simulator_name = ?, simulator_state = ?, simcontorler_name = ?, simcontorler_id = ?, update_time = NOW(),
        simulator_name = ?, simulator_state = ?, simcontorler_name = ?, simcontorler_id = ?, update_time = NOW(),
        simulator_name = ?, simulator_state = ?, simcontorler_name = ?, simcontorler_id = ?, update_time = NOW(),
        simulator_name = ?, simulator_state = ?, simcontorler_name = ?, simcontorler_id = ?, update_time = NOW(),
        simulator_name = ?, simulator_state = ?, simcontorler_name = ?, simcontorler_id = ?, update_time = NOW(),
        simulator_name = ?, simulator_state = ?, simcontorler_name = ?, simcontorler_id = ?, update_time = NOW()

    image.gif

    如果不使用foreach, 则会提示如下异常信息

    Caused by: org.apache.ibatis.binding.BindingException: Parameter 'simulatorName' not found. Available parameters are [arg0, collection, list]
        at org.apache.ibatis.binding.MapperMethod$ParamMap.get(MapperMethod.java:212)
        at org.apache.ibatis.scripting.xmltags.DynamicContext$ContextAccessor.getProperty(DynamicContext.java:120)
        at org.apache.ibatis.ognl.OgnlRuntime.getProperty(OgnlRuntime.java:3338)
        at org.apache.ibatis.ognl.ASTProperty.getValueBody(ASTProperty.java:121)
        at org.apache.ibatis.ognl.SimpleNode.evaluateGetValueBody(SimpleNode.java:212)
        at org.apache.ibatis.ognl.SimpleNode.getValue(SimpleNode.java:258)
        at org.apache.ibatis.ognl.ASTNotEq.getValueBody(ASTNotEq.java:50)
        at org.apache.ibatis.ognl.SimpleNode.evaluateGetValueBody(SimpleNode.java:212)
        at org.apache.ibatis.ognl.SimpleNode.getValue(SimpleNode.java:258)
        at org.apache.ibatis.ognl.Ognl.getValue(Ognl.java:560)
        at org.apache.ibatis.ognl.Ognl.getValue(Ognl.java:524)
        at org.apache.ibatis.scripting.xmltags.OgnlCache.getValue(OgnlCache.java:46)
        at org.apache.ibatis.scripting.xmltags.ExpressionEvaluator.evaluateBoolean(ExpressionEvaluator.java:32)
        at org.apache.ibatis.scripting.xmltags.IfSqlNode.apply(IfSqlNode.java:34)
        at org.apache.ibatis.scripting.xmltags.MixedSqlNode.lambda$apply$0(MixedSqlNode.java:32)
        at java.util.ArrayList.forEach(ArrayList.java:1257)
        at org.apache.ibatis.scripting.xmltags.MixedSqlNode.apply(MixedSqlNode.java:32)
        at org.apache.ibatis.scripting.xmltags.TrimSqlNode.apply(TrimSqlNode.java:55)
        at org.apache.ibatis.scripting.xmltags.MixedSqlNode.lambda$apply$0(MixedSqlNode.java:32)
        at java.util.ArrayList.forEach(ArrayList.java:1257)
        at org.apache.ibatis.scripting.xmltags.MixedSqlNode.apply(MixedSqlNode.java:32)
        at org.apache.ibatis.scripting.xmltags.DynamicSqlSource.getBoundSql(DynamicSqlSource.java:39)
        at org.apache.ibatis.mapping.MappedStatement.getBoundSql(MappedStatement.java:305)
        at org.apache.ibatis.executor.statement.BaseStatementHandler.<init>(BaseStatementHandler.java:64)
        at org.apache.ibatis.executor.statement.PreparedStatementHandler.<init>(PreparedStatementHandler.java:41)
        at org.apache.ibatis.executor.statement.RoutingStatementHandler.<init>(RoutingStatementHandler.java:46)
        at org.apache.ibatis.session.Configuration.newStatementHandler(Configuration.java:636)
        at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:48)
        at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
        at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
        at sun.reflect.GeneratedMethodAccessor201.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
        at com.sun.proxy.$Proxy240.update(Unknown Source)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197)
        at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:184)
        at sun.reflect.GeneratedMethodAccessor200.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426)
        ... 30 common frames omitted

    image.gif

    3.  insert ... on duplicate key update column=IF(条件,值1,值2 )

    需求:  更新操作,先将columnB更新为新值,然后根据if条件(columnB更新后的值)做判断更新columnA

    INSERT INTO tbl (columnA,columnB,columnC) VALUES (1,2,3) ON DUPLICATE KEY UPDATE columnA=IF(columnB>0,1,columnA)

    image.gif

    IF 函数用法--类似JAVA中的三元表达式

    语法: IF(expr1,expr2,expr3)

    函数用法说明:如果 expr1 是 TRUE (expr1 <> 0 and expr1 <> NULL) ,则 IF() 的返回值为 expr2 ; 否则返回值则为 expr3 。 IF() 的返回值为数字值或字符串值,具体情况视其所在语境而定

    INSERT INTO dm_simulator_info (
        simulator_id,
        simulator_name,
        simulator_state,
        simulator_type,
        simcontorler_name,
        simcontorler_id,
        create_time,
        update_time
    )
    VALUES
        ("2222", "wwwwww", 2,"2222", "2222", "2222", NOW(), NOW())
    ON DUPLICATE KEY UPDATE 
        simulator_name = "wwwwww", simulator_state = 2, simcontorler_name = IF(simulator_state>0,"FFFFF","YYYY")

    image.gif


    版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

    相关文章
    可以通过基类实现的几种功能。vs2008 .net 2.0
        我很懒,很不喜欢写重复的代码,一行重复的都不想写,所以当同一段代码要写第二遍的时候,我就会去想避免的方法。OO的特性之一 —— 继承 —— 可以帮很大的忙。     这里只说UI部分,先看一个类的图。
    746 0
    SharePoint自动化系列——通过PowerShell创建SharePoint Lists
    转载请注明出自天外归云的博客园:http://www.cnblogs.com/LanTianYou/ 代码如下(保存到本地ps1文件中,右键run with PowerShell即可): Add-PSSnapin microsoft.
    633 0
    +关注
    琦彦
    孤独的技术是没有价值的
    71
    文章
    1
    问答
    文章排行榜
    最热
    最新
    相关电子书
    更多
    文娱运维技术
    立即下载
    《SaaS模式云原生数据仓库应用场景实践》
    立即下载
    《看见新力量:二》电子书
    立即下载