接口得到数据批量写入表不关心增减字段

 利用JdbcTemplate 批量操作数据

import com.alibaba.fastjson.JSONArray;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @Author 
 * @Date 2021/5/20
 * @Description: TODO
 */
public class BatchInsertOrUpdateServiceImpl {
    protected static final Logger LOGGER = LoggerFactory.getLogger(BatchInsertOrUpdateUtil.class);

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 每20分钟执行一次  0 0/20 * * * ?
     */
    @Scheduled(cron = "0 0/1 * * * ?")
    void batchInsertOrUpdate() throws ParseException {
        //表命名空间
        String tableSpace = "";
        //表名
        String table = "";
        //主键
        String primarykey = "";
        //接口得到的数据
        JSONArray array = getList();
        //用于批量操作
        StringBuilder sqlUpdate = new StringBuilder();
        StringBuilder sqlInsert = new StringBuilder();

        List<Object[]> batchArgsInsert = new ArrayList<Object[]>();
        List<Object[]> batchArgsUpdate = new ArrayList<Object[]>();
        //循环得到所有的key ，有返回对象字段不固定的情况
        Set keySet = new TreeSet();
        for (int i = 0; i < array.size(); i++) {
            Map map = (Map) array.get(i);
            Iterator<Map.Entry<String, Object>> entries = map.entrySet().iterator();
            while (entries.hasNext()) {
                Map.Entry<String, Object> entry = entries.next();
                keySet.add(entry.getKey());
            }
        }
        String keyInsert = "";
        String keyInsertocc = "";
        String fieldUpdate = "";
        Iterator iterator = keySet.iterator();
        while (iterator.hasNext()) {
            Object fieldName = iterator.next();
            keyInsert = keyInsert + fieldName + ",";
            keyInsertocc = keyInsertocc + "?,";
            fieldUpdate = fieldUpdate + fieldName + "=?,";
        }
        //拼接insert语句
        sqlInsert.append(" insert into ");
        if (!StringUtils.isEmpty(tableSpace)) {
            sqlInsert.append("\"" + tableSpace.trim() + "\"" + ".");
        }
        sqlInsert.append(table + "(");
        if (!StringUtils.isEmpty(keyInsert) && keyInsert.endsWith(",")) {
            keyInsert = keyInsert.substring(0, keyInsert.length() - 1);
            sqlInsert.append(keyInsert);
        }
        sqlInsert.append(") values ( ");
        if (!StringUtils.isEmpty(keyInsertocc) && keyInsertocc.endsWith(",")) {
            keyInsertocc = keyInsertocc.substring(0, keyInsertocc.length() - 1);
            sqlInsert.append(keyInsertocc);
        }
        sqlInsert.append(" ) ");

        //拼接update语句
        sqlUpdate.append(" update  ");
        if (!StringUtils.isEmpty(tableSpace)) {
            sqlUpdate.append("\"" + tableSpace.trim() + "\"" + ".");
        }
        sqlUpdate.append(table);
        sqlUpdate.append(" set ");
        if (!StringUtils.isEmpty(fieldUpdate) && fieldUpdate.endsWith(",")) {
            fieldUpdate = fieldUpdate.substring(0, fieldUpdate.length() - 1);
            sqlUpdate.append(fieldUpdate);
        }
        sqlUpdate.append(" where " + primarykey + "=? ");

        //循环得到当前请求返回的所有对象
        for (int i = 0; i < array.size(); i++) {
            Map map = (Map) array.get(i);
            List paramsObj = new ArrayList();
            boolean isExist = false;
            //定义一个新的有序treemap存放，补缺少的字段
            Map treemap = new TreeMap();
            //根据keyset比较单个对象的key,没有补value
            Iterator iteratorkey = keySet.iterator();
            while (iteratorkey.hasNext()) {
                String fieldName = (String) iteratorkey.next();
                //循环每个对象的字段属性
                treemap.put(fieldName, map.get(fieldName));
            }
            //
            if (!CollectionUtils.isEmpty(treemap)) {
                //执行插入或更新
                Object primarykeyValue = "";
                Iterator<Map.Entry<String, Object>> entries = treemap.entrySet().iterator();
                //循环每个对象的字段属性
                while (entries.hasNext()) {
                    Map.Entry<String, Object> entry = entries.next();
                    String key = entry.getKey();
                    Object value = "";
                    if (!StringUtils.isEmpty(entry.getValue())) {
                        value = String.valueOf(entry.getValue());
                        if (value != null) {
                            boolean dateFlag = isDatePatten(value);
                            if (dateFlag) {
                                SimpleDateFormat dateParser = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
                                Date date = dateParser.parse((String) value);
                                value = date;
                            }
                        }
                    } else {
                        value = null;
                    }
                    if (primarykey.equalsIgnoreCase(key)) {
                        isExist = queryExist(tableSpace, table, primarykey, value);
                        if (isExist) {
                            primarykeyValue = value;
                        }
                    }
                    paramsObj.add(value);
                }
                //这条记录存在进行更新
                if (isExist && !StringUtils.isEmpty(primarykeyValue)) {
                    paramsObj.add(primarykeyValue);
                    batchArgsUpdate.add(paramsObj.toArray());
                } else {
                    batchArgsInsert.add(paramsObj.toArray());
                }

            }
        }
        if (!CollectionUtils.isEmpty(batchArgsInsert)) {
            jdbcTemplate.batchUpdate(sqlInsert.toString(), batchArgsInsert);
            LOGGER.info("执行批量插入");
        }
        if (!CollectionUtils.isEmpty(batchArgsUpdate)) {
            jdbcTemplate.batchUpdate(sqlUpdate.toString(), batchArgsUpdate);
            LOGGER.info("执行批量更新");
        }
    }

    /**
     * 校验唯一性
     * @param tableSpace
     * @param table
     * @param primarykey
     * @param value
     * @return
     */
    private boolean queryExist(String tableSpace, String table, String primarykey, Object value) {
        return false;
    }

    /**
     * 接口数据
     * @return
     */
    private JSONArray getList() {
        JSONArray jsonArray = new JSONArray();
        return jsonArray;
    }

    /**
     * 判断是否时间格式
     * @param value
     * @return
     */
    private boolean isDatePatten(Object value) {
        SimpleDateFormat dateParser = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
        try {
            dateParser.parse((String) value);
            return true;
        } catch (Exception e) {
            return false;
        }
    }
}