oracle和db2通用批量新增、更新的方法（基于merge into）

已于 2022-08-08 10:50:45 修改
阅读量1.6k 收藏
点赞数
文章标签： oracle mybatis 数据库
于 2022-08-04 19:57:56 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_46176823/article/details/126165633
版权

注意点：

此种方法是通过反射，拼接完整的merge into语句，然后通过mybatis去执行sql。
这种方法在更新时需要注意，传入的实体类对于属性值为null的，也会将原来对应的字段值修改为null。所以使用此种方法批量更新，需要将所有的值都塞入实体类。
oracle，需要主键自增的字段必须要有序列和触发器。其次，无论是db2还是oracle都需要配合@TableId注解来指定主键，实体类中多余的属性需要用@TableField（exist=flase）来排除。

附上源码，仅供参考：

import com.cmbchina.cc.mc.aptms.infrastructure.helper.McCmsDbSysParmCacheHelper;
import com.cmbchina.cc.mc.aptms.infrastructure.repository.db2.BatchMapper;
import com.cmbchina.cc.mc.aptms.infrastructure.util.SqlTemplateUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import java.util.List;

/**
 * @author LGQ
 * @Description 通用批量操作方法
 * @date 2022/8/2 19:04
 */
@Service
@Slf4j
public class CommonBatchService {

    @Value("${batchSize:1000}")
    private int batchSize;

    @Autowired
    private BatchMapper batchMapper;

    @Autowired
    private McCmsDbSysParmCacheHelper mcCmsDbSysParmCacheHelper;


    public <T> Boolean batchSaveOrUpdate(List<T> sources, Class<T> clazz) {
        return batchSaveOrUpdate(sources, clazz, batchSize);
    }

    public <T> Boolean batchSaveOrUpdate(List<T> sources, Class<T> clazz, int size) {
        if (CollectionUtils.isEmpty(sources)) {
            return false;
        }
        try {
            Boolean isOracleDataSource = mcCmsDbSysParmCacheHelper.isChangeDatabase();
            int sourceSize = sources.size();
            for (int i = 0; i < sourceSize; i = i + size) {
                List<T> subList;
                if (i + size > sourceSize) {
                    subList = sources.subList(i, sourceSize);
                } else {
                    subList = sources.subList(i, i + size);
                }
                String batchSaveOrUpdateSql = SqlTemplateUtils.batchSaveOrUpdateSql(subList, clazz, isOracleDataSource);
                batchMapper.mergeInto(batchSaveOrUpdateSql);
            }
        } catch (Exception e) {
            log.error(e.toString());
            return false;
        }
        return true;
    }

}


import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Update;

/**
 * @author LGQ
 * @date 2022/8/2 19:02
 */
@Mapper
public interface BatchMapper extends BaseMapper {

    @Update("${sql}")
    void mergeInto(@Param("sql") String sql);

}


import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.cmbchina.cc.mc.aptms.enums.MCSysErrorCodeType;
import lombok.extern.slf4j.Slf4j;

import java.lang.reflect.Field;
import java.util.List;

/**
 * @author LGQ
 * @date 2022/8/1 10:44
 */
@Slf4j
public class SqlTemplateUtils {

    public static <T> String batchSaveOrUpdateSql(List<T> sources, Class<T> clazz, Boolean isOracleDataSource) {
        if (sources.size() < 1) {
            return null;
        }
        StringBuffer finalSqlTemplate = new StringBuffer("MERGE INTO ");
        finalSqlTemplate.append(StringUtils.getTableName(clazz)).append(" u USING ( ");
        StringBuffer tableIdName = new StringBuffer("");
        StringBuffer basicSql = getBasicSql(sources, clazz, tableIdName, isOracleDataSource);
        if (StringUtils.isEmptyString(tableIdName.toString())) {
            throw new RuntimeException(MCSysErrorCodeType.UN_CAUGHT_TABLE_ID.getErrorMessage());
        }
        finalSqlTemplate.append(basicSql).append(" ) t on (").append(String.format("u.%s = t.%s) ", tableIdName, tableIdName));
        StringBuffer lastSql = getLastSql(clazz, isOracleDataSource);
        return finalSqlTemplate.append(lastSql).toString();
    }


    private static <T> StringBuffer getBasicSql(List<T> list, Class<T> clazz, StringBuffer tableIdName, Boolean isOracleDataSource) {
        StringBuffer basicSql = new StringBuffer("");
        Field[] declaredFields = clazz.getDeclaredFields();
        int fieldsLength = declaredFields.length;
        String tableIdValue = "";
        for (int j = 0; j < list.size(); ) {
            basicSql.append("SELECT ");
            Object o = list.get(j);
            for (int i = 0; i < fieldsLength; ) {
                Field field = declaredFields[i];
                TableField tableField = field.getAnnotation(TableField.class);
                if (null != tableField && !tableField.exist()) {
                	i++;
                    continue;
                }
                TableId tableId = field.getAnnotation(TableId.class);
                String tableFieldValue = getColumnByField(field);
                if (null != tableId) {
                    tableIdValue = tableFieldValue;
                }
                if (StringUtils.isEmpty(tableFieldValue)) {
                    throw new RuntimeException(MCSysErrorCodeType.UN_CAUGHT_COLUMNS.getErrorMessage());
                }
                field.setAccessible(true);
                try {
                    String simpleName = field.getType().getSimpleName();
                    Object fieldValue = field.get(o);
                    if (null == fieldValue) {
                        if (!isOracleDataSource && null != tableId) {
                            basicSql.append("-1 AS ").append(tableFieldValue);
                        } else {
                            basicSql.append("NULL AS ").append(tableFieldValue);
                        }
                    } else if ("String".equals(simpleName)) {
                        basicSql.append(String.format("'%s'", fieldValue)).append(" AS ").append(tableFieldValue);
                    } else if ("Timestamp".equals(simpleName)) {
                        basicSql.append(String.format("TIMESTAMP'%s'", fieldValue)).append(" AS ").append(tableFieldValue);
                    } else if ("Date".equals(simpleName)) {
                        if (isOracleDataSource) {
                            basicSql.append(String.format("TO_DATE('%s','yyyy-mm-dd hh24:mi:ss')", fieldValue)).append(" AS ").append(tableFieldValue);
                        } else {
                            basicSql.append(String.format("'%s'", fieldValue)).append(" AS ").append(tableFieldValue);
                        }
                    } else {
                        basicSql.append(fieldValue).append(" AS ").append(tableFieldValue);
                    }
                    if (++i < fieldsLength) {
                        basicSql.append(", ");
                    }
                } catch (IllegalAccessException e) {
                    log.error(e.toString());
                }
            }
            if (isOracleDataSource) {
                basicSql.append(" FROM DUAL");
            } else {
                basicSql.append(" FROM sysibm.sysdummy1");
            }
            if (++j < list.size()) {
                basicSql.append(" UNION ALL ");
            }
        }
        tableIdName.append(tableIdValue);
        return basicSql;
    }


    private static <T> StringBuffer getLastSql(Class<T> clazz, Boolean isOracleDataSource) {
        StringBuffer matchedBuf = new StringBuffer(" WHEN MATCHED THEN UPDATE SET ");
        String whereSql = "";
        StringBuffer notMatchedBuf = new StringBuffer(" WHEN NOT MATCHED THEN INSERT ");
        StringBuffer columns = new StringBuffer("( ");
        StringBuffer values = new StringBuffer("VALUES ( ");
        Field[] declaredFields = clazz.getDeclaredFields();
        for (int i = 0; i < declaredFields.length; ) {
            Field field = declaredFields[i];
            TableField tableField = field.getAnnotation(TableField.class);
            if (null != tableField && !tableField.exist()) {
            	i++;
                continue;
            }
            TableId tableId = field.getAnnotation(TableId.class);
            String tableFieldVal = getColumnByField(field);
            if (null != tableId) {
                if (!isOracleDataSource) {
                    i++;
                    continue;
                }
                whereSql = String.format(" WHERE u.%s = t.%s ", tableFieldVal, tableFieldVal);
                columns.append(tableFieldVal).append(", ");
                values.append(String.format("t.%s", tableFieldVal)).append(", ");
                i++;
                continue;
            }
            matchedBuf.append(String.format("u.%s = t.%s", tableFieldVal, tableFieldVal));
            columns.append(tableFieldVal);
            values.append(String.format("t.%s", tableFieldVal));
            if (++i < declaredFields.length) {
                matchedBuf.append(", ");
                columns.append(", ");
                values.append(", ");
            } else {
                columns.append(") ");
                values.append(")");
            }
        }
        matchedBuf.append(whereSql).append(notMatchedBuf).append(columns).append(values);
        return matchedBuf;
    }

    /**
     * @description 通过属性获取字段名
     * @author LGQ
     * @date 2022/8/4 9:40
     */
    private static String getColumnByField(Field field) {
        String fieldName = field.getName();
        String[] split = fieldName.split("");
        StringBuffer buffer = new StringBuffer("");
        for (String s : split) {
            if (s.equals(s.toUpperCase())) {
                buffer.append("_").append(s.toUpperCase());
            } else {
                buffer.append(s.toUpperCase());
            }
        }
        return buffer.toString();
    }

}


import com.baomidou.mybatisplus.annotation.TableName;

/**
 * @date 2020/09/18
 **/
public class StringUtils {

    private StringUtils() {

    }

    public static <T> String getTableName(Class<T> clazz) {
        TableName tableName = clazz.getAnnotation(TableName.class);
        if (null == tableName || isEmptyString(tableName.value())) {
            throw new RuntimeException("未查询到表名");
        }
        return tableName.value();
    }

    public static boolean isEmpty(Object str) {
        return str == null || "".equals(str);
    }

    public static boolean isNotEmpty(Object str) {
        return str != null && !"".equals(str);
    }

    /**
     * @description null转空字符串
     * @author LGQ
     * @date 2022/6/28 8:47
     */
    public static String nullToString(String s) {
        return isEmptyString(s) ? "" : s;
    }

    public static boolean isEmptyString(String s) {
        if (s == null) {
            return true;
        }
        return "".equals(s.trim());
    }


    /**
     * 计算字符串在给定字符串中是否至少出现了几次
     *
     * @param src          字符串
     * @param toFind       要查找的字符串
     * @param atLeastCount 至少出现的次数
     * @return boolean
     */
    public static boolean occurAtLeastCount(String src, String toFind, int atLeastCount) {
        if (isEmptyString(src) || isEmptyString(toFind) || atLeastCount < 1) {
            return false;
        }
        int index = 0;
        int count = 0;
        while ((index = src.indexOf(toFind, index)) != -1) {
            count++;
            if (count >= atLeastCount) {
                return true;
            }
            index = index + toFind.length();
        }
        return false;
    }
    
    public static boolean isBlank(String string) {
		int strLen;
		if (string == null || (strLen = string.length()) == 0)
			return true;
		for (int i = 0; i < strLen; i++)
			if (!Character.isWhitespace(string.charAt(i)))
				return false;

		return true;
	}
    
    public static String trim(String string) {
		return string != null ? string.trim() : null;
	}
}
宋马尧
关注
oracel和db2 mybatis批量更新
dcm19920115的博客
03-02 1688
网上有很多关于mybatis批量更新的例子，但基本都是针对mysql或oracle的，针对db2的少之又少； 一、oracle+mybatis执行批量更新： mapper.xml: <update id="doupdateBatchResUpStatusById" parameterType="java.util.List"> <foreach collec...
Merge into 实现匹配到就更新，匹配不到就插入
weixin_44662615的博客
08-14 1009
Merge into 实现匹配到就更新，匹配不到就插入 merge into 目标表 a using 源表 b on a.id=b.id when matched then update set a.要修改的字段=b.要匹配的字段,a.要修改的字段='常量' when not matched then insert(要修改的字段,要修改的字段,要修改的字段,要修改的字段) values(b.匹配的字段,b.匹配的字段,匹配的值，匹配的值)； 例如: MERGE INTO student a U
DB2AS400 批量更新
zhengzixiang666的博客
04-23 1187
一、oracle+mybatis执行批量更新： mapper.xml: <update id="doupdateBatchResUpStatusById" parameterType="java.util.List"> <foreach collection="list" item="item" index="index" open="begin" close...
关于DB2批量更新数据方式的思考
hulua2010的专栏
05-25 8587
由于负责公司的系统的运维与开发，为实现系统之间的SOA数据共享，少不了要进行数据库表啊什么之类的数据共享。做JAVA的，除了数据库方面的sql任务，自然而然能够想到利用javaapplication解决一部分数据更新任务，尤其是涉及到还要生成excel报表之类的附加功能，更是需要java相关的插件生成更加方便。 那么就需要自己写一些利用java（JDBC）链接DB2，并进行增删改查的功能。那么基
db2带序列批量插入以及批量更新
mkdeluffy的博客
03-13 5432
db2带序列的批量插入,mybatis写法 &lt;insert id="sdfsdf" parameterType="java.util.List"&gt; INSERT INTO TABLE_A(ID,NAME,AGE) SELECT SEQ_TABLE_A.nextval,A.* FROM( &lt;foreach collection='list' item='item' se...
db2存储过程批量插入数据_DB2使用存储过程插入数据
weixin_39639049的博客
12-19 2101
要求：插入一张表(TESTMV)中三个字段(ID, GROUPID【组id】, USERID【用户id】)，为了产生多个组，每个组多个人的数据，进行统计每个组多少人数据用代码如下：delete testmv;create procdedure insertdb()language SQLbegindeclare v_num int;declare v_num1 int;declare v_num2...
merge into 带where条件
我的博客
12-07 5466
                                      merge into using 基本语法 merge into
MERGE INTO 目标表 USING 数据源表 ON的用法（代码优化）
lz_N_one的博客
06-30 2887
存储过程中merge into 一般用于增量插入数据，如果是源表全量数据插入目标表常规认为insert into 比merge into 效率更高， 但是数据源表的数据来源是需要查询大量关联表时然后全量录入目标表时，merge into 后面的匹配条件on（0=1) 可以使插入数据效率更高； 具体的执行效率可以看完成同样事物Oracle执行时间的长短； Oracle 9i引入的功能），其语法如下： MERGE INTO a_crt_repay_loan t --增量切片 USING (SELECT p_da
使用BULK COLLECT, MERGE 语句提高sql执行效率
01-17
详细介绍了使用 BULK COLLECT 进行批量操作 提高sql的执行效率 使用MERGE INTO USING 一条sql搞定 新增和修改 使用connect by 进行递归树查询
达梦数据库怎么利用MERGE INTO进行批量数据的insert or update
最新发布
05-26
MERGE INTO语句是Oracle和DB2数据库中常用的语句，但达梦数据库并不支持该语句。如果要实现类似的批量数据插入或更新操作，可以使用以下两种方法： 1. 使用多条INSERT或UPDATE语句 可以使用多条INSERT或UPDATE语句...
DB2数据库批量导insert工具
03-04
为了弥补DB2一些客户端无法批量导出insert语句备份数据，特为此做了此工具，本工具可以批量导出DB2数据库的insert语句，可以更方便，而且已经过大量实践验证测试。慢慢体验吧！！！呵呵...
批量插入数据库
11-16
批量插入数据库
批量更新两种方法实践
05-03
NULL 博文链接：https://yuhuiblog695685688425687986842568269.iteye.com/blog/2425744
批量更新历史数据 每10000提交一次
03-18
NULL 博文链接：https://dolphin-ygj.iteye.com/blog/673547
DB2数据库自动导出数据INSERT SQL脚本工具
06-13
DB2数据库自动导出数据INSERT SQL脚本工具
Python 向db2数据库中批量插入大量数据（1w条左右）
程序员雅痞的博客
09-17 4171
网上关于python操作db2的文章很少，特此记录。 目录1. 最终解决方案2. 探索1：load & import3. 探索2：to_sql4. 探索3: excutemany(sql,param)5. 遇到的bug 环境 Python 3.8.6 ibm_db_sa 0.3.7 ibm-db 3.0.4 sqlalchemy 1.4.7 1. 最终解决方案 采用拼接字符串的方法，把要批量插入的数据拼接成insert into table values (),...,(),()这种形式。 .
db2存储过程批量插入数据_DB2存储过程批量提交（续）
weixin_39747615的博客
01-12 2030
DB2存储过程批量提交(续)之前的帖子因为结了，所以就没有人去看了~于是乎就新开了个帖子！问题一：在不考虑性能损失的情况下，DB2存储过程分批提交可以尝试触发器调用存储过程提交的方法，下面是一个例子。此过程基本逻辑如下：当对表有INSERT操作时，触发存储过程COMMIT_TRIGGER，由COMMIT_TRIGGER调用存储过程COMMIT_A，在COMMIT_A中查询表的记录数，用记录数模每次...
db2存储过程批量插入数据_存储过程 插入大量数据
weixin_36410316的博客
12-29 1559
CREATE TABLE aaa (`id` int(10) NOT NULL AUTO_INCREMENT,`title` varchar(200) NOT NULL,`cid` int(10) NOT NULL,`resume` text NOT NULL,`releasetime` int(10) NOT NULL,`uid` int(10) NOT NULL,`type` tinyint...
SQL语句批量增改数据的SQL语句写法（DB2、MySql）
weixin_44090537的博客
11-11 2193
DB2 merge语句 MERGE INTO SCHEMA1.TABLE1 AS A USING SCHEMA2.TABLE2 AS B ON A.XXX = B.YYY WHEN MATCHED THEN UPDATE SET A.AAA = B.AAA, A.BBB = B.BBB, A.CCC = B.CCC ... WHEN NOT MATCHED THEN INSERT VALUES (B.AAA,B.BBB,B.CCC...省略) 注意： 判断条件是ON后面的条件 MySql ON D
Oracle MERGE INTO操作详解
"Oracle MERGE INTO语句是一种强大的SQL操作，它允许在单个语句中执行更新、插入和删除操作。这个功能自Oracle 9i版本开始引入，并在Oracle 10g及后续版本中得到了增强。MERGE INTO主要用于合并两个数据源，通常是一...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

宋马尧

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值