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

刺骨的言语ヽ痛彻心扉 2023-06-30 09:00 41阅读 0赞

**目录**

批量的saveOrupdate:

单条的saveOrupdate:

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

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

ON DUPLICATE KEY UPDATE 附带更新条件

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

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

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

--------------------

# 批量的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 ' 
>     
>     除了检查，是否缺少或者多余，还要检查是否有多余空格

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

# 单条的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>
            
            <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>
            
            <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>

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

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

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

> **之所以使用了trim, 是因为当最后一个pictureUrl为空的时候，会报异常，因为不走最后一条数据了，多了一个”，”，**
> 
> **处理方式可以用mybatis中的trim进行判断**

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

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

> 因为是批量, 所以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()

> 如果不使用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

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

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