批量拼sql的一个方法

最新推荐文章于 2024-08-05 21:02:05 发布

初猿

最新推荐文章于 2024-08-05 21:02:05 发布

阅读量106

点赞数

文章标签： sql windows linux

本文链接：https://blog.csdn.net/qq_42525457/article/details/134297266

版权

文章介绍了BatchOprateUtils类，用于在Java中处理批量保存数据，通过@Transactional注解保证事务安全。方法区分了当数据量超过3000条时采用分批插入，否则一次性插入，同时处理了@TableField注解的特殊字段处理。

摘要由CSDN通过智能技术生成

@Component
public class BatchOprateUtils {

@Resource
private MigrateMapper migrateMapper;


@Transactional
public <T> void BatchSaveModelInfo(List<T> saveDataList, String newTableName,Class<T> tClass) {

    if (CollectionUtils.isEmpty(saveDataList)) {
        return;
    }
    boolean flag = saveDataList.size() > 3000;
    int num = 0;
    Field[] declaredFields = tClass.getDeclaredFields();
    //将被@TableField注解修饰，且其属性exist = false的字段过滤掉
    declaredFields = Arrays.stream(declaredFields).filter(f -> !fieldIsDecorateByTableField(f)).collect(Collectors.toList()).toArray(new Field[]{});
    StringBuilder sql = new StringBuilder("insert into ");
    sql.append(newTableName).append(" ");
    // 获取每一个字段的属性
    List<String> fileNameList = Arrays.stream(declaredFields).map(item -> StrUtil.toUnderlineCase(item.getName())).collect(Collectors.toList());
    sql.append(" ( ").append(org.apache.commons.lang.StringUtils.join(fileNameList, ",")).append(" ) ");
    sql.append(" values ");

    if (flag) {
        // 分批插入数据
        String prefix = sql.toString();
        for (T dataRecord : saveDataList) {
            if (num >= 3000) {
                sql.deleteCharAt(sql.length() - 1).append(" ;");
                // 执行sql
                migrateMapper.insertBatchData(sql.toString());
                num = 0;
                sql = new StringBuilder(prefix);

            }
            // 获取每一个字段的值
            sql.append(" ( ");
            for (Field field : declaredFields) {
                field.setAccessible(true);
                Object val = null;
                try {
                    val = field.get(dataRecord);
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
                if (val == null) {
                    if ("id".equals(field.getName())) {
                        sql.append("'").append(IdWorker.getId() + "").append("'").append(",");
                    } else {
                        sql.append("null").append(",");
                    }

                } else {
                    if (field.getType() == Date.class) {
                        val = DateUtil.formatDateTime((Date) val);
                    }
                    sql.append("'").append(val).append("'").append(",");
                }
            }
            sql.deleteCharAt(sql.length() - 1).append(" ),");
            num++;
        }
        sql.deleteCharAt(sql.length() - 1).append(" ; ");
        // 执行sql
        migrateMapper.insertBatchData(sql.toString());
    } else {
        // 一次性插入
        for (T dataRecord : saveDataList) {
            // 获取每一个字段的值
            sql.append(" ( ");
            for (Field field : declaredFields) {
                field.setAccessible(true);
                Object val = null;
                try {
                    val = field.get(dataRecord);
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                }
                if (val == null) {
                    if ("id".equals(field.getName())) {
                        sql.append("'").append(IdWorker.getId() + "").append("'").append(",");
                    } else {
                        sql.append("null").append(",");
                    }
                } else {
                    if (field.getType() == Date.class) {
                        val = DateUtil.formatDateTime((Date) val);
                    }
                    sql.append("'").append(val).append("'").append(",");
                }

            }
            sql.deleteCharAt(sql.length() - 1).append(" ),");
        }
        sql.deleteCharAt(sql.length() - 1).append(" ; ");
        // 执行sql
        migrateMapper.insertBatchData(sql.toString());
    }
}



public boolean fieldIsDecorateByTableField(Field field) {
    boolean result = false;
    TableField annotation = field.getAnnotation(TableField.class);
    if (annotation != null && annotation.exist() == false) {
        result = true;
    }
    return result;
}

}