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