2021-01-22 sql批量插入

sqllist.add(mess);
//拼接大sql
String sqlno = "insert into " + dimension + "(" + columnNames + ")" + " values ";
//循环拿sql批量插入
String sqlS ="";
if(sqllist.size()>0 && null != sqllist){
    if((sqllist.size()%10000==0 && sqllist.size()>0) || (i==data.size()) ){
        for(int s=0;s<10000;s++){
            sqlS = sqlS+sqllist.get(s);
        }
        String sqlss = sqlno+sqlS.substring(0,sqlS.length()-1);
        System.out.println("打印sql语句拼接第"+i+"条时批量插入"+sqlss);
        PreparedStatement preparedStatement = (PreparedStatement) connection.prepareStatement(sqlss);
        preparedStatement.execute();
        //清空数据
        sqllist.remove(sqllist);
    }
}

dimension 为表名

//获取拼接信息
mess = getMess(split1,split2,mysqlkey,mongoValue,data,mysqlType,i,mess);

//动态获取拼接信息

/**
 * mysql 拼接获取值 20210121
 * @param split1
 * @param split2
 * @param mysqlkey
 * @param mongoValue
 * @param data
 * @param mysqlType
 * @param i
 * @param mess
 * @return
 */

public String getMess(String[] split1,String[] split2,String mysqlkey,String mongoValue,List<Map<String, Object>> data,String mysqlType,int i,String mess){
    //从第二个值开始拼接
    for (int j = 0; j < split1.length; j++) {
        mysqlkey = split1[j];
        //根据字段名取出的mongodb实际值
        mongoValue = data.get(i).get(mysqlkey) + "";
        mysqlType = split2[j];
        //如果mysqlType为decimal,不能为null转为0
        if (mysqlType.contains("BigDecimal")) {
            if (org.apache.commons.lang3.StringUtils.isNotBlank(mongoValue)) {
                mess = mess + mongoValue + ",";
            } else {
                mess = mess + 0 + ",";
            }
        } else if (mysqlType.contains("Timestamp")) {
            //转换时间差
        } else if (mysqlType.contains("Integer")) {
            //Integer类型的转化
            //序列字段
            if (org.apache.commons.lang3.StringUtils.isNotEmpty(mongoValue)) {
                mess = mess + mongoValue + ",";
            } else {
                mess = mess + null + ",";
            }
        } else if (mysqlkey.contains("update_time")) {
            mess = mess + " Now() " + ",";
        } else {
            if (StringUtils.isEmpty(mongoValue)) {
                mongoValue = "";
            }
            mess = mess + "'" + mongoValue + "'" + ",";
        }
    }
    return mess;
}

map = jdbcUtils.getMessage(dimension, connection);

/**
 * 根据表名获取字段信息 ok
 * @param table
 * @throws SQLException
 */
public static Map<String,Object> getMessage(String table,Connection connection) throws SQLException {
    Map<String, Object> map = new HashMap<String, Object>();
    //连接数据库查询每个表的字段名
    String columnNames = "";
    //连接数据库查询每个字段的类型
    String columnTypes = "";
    //增加判断判断表名是否存在于mysql中
    DatabaseMetaData meta = connection.getMetaData();
    ResultSet set = meta.getTables(null, null, table, null);
    if (set.next()) {
        //表存在的情况
        String sql = "select * from " + table;
        PreparedStatement stmt;
        stmt = connection.prepareStatement(sql);
        //System.out.println("dayin------"+sql);
        ResultSet rs = stmt.executeQuery(sql);
        ResultSetMetaData data = rs.getMetaData();
        if (null != data) {
            //不需要id从2开始
            int a = 2;
            for (int i = a; i <= data.getColumnCount(); i++) {
                // 获得指定列的列名
                String columnName = data.getColumnName(i);
                //拼接字段名
                columnNames = columnNames + "," + columnName;
                // 获得指定列的数据类型名
                //String columnTypeName = data.getColumnTypeName(i);
                // 对应数据类型的类
                String columnClassName = data.getColumnClassName(i);
                //拼接字段名
                columnTypes = columnTypes + "," + columnClassName;
            }
            //System.out.println("获得最终的默认的列的字段拼接:" + columnNames);
            //System.out.println("获得最终的默认的列的字段个数data.getColumnCount():" + data.getColumnCount());
            map.put("columnNames", columnNames.substring(1, columnNames.length()));
            map.put("columnTypes", columnTypes.substring(1, columnTypes.length()));
            map.put("size", data.getColumnCount());
            //System.out.println("获得最终的信息:" + map);
        }
    }else {
        //表不存在 打印提示并返回不执行插入
        map = null;
    }
    return map;
}