【TDengine 使用环境】
测试
【TDengine 版本】
3.3.6 / maven taos-jdbcdriver驱动3.7.4/3.8.3/3.9.0 都试验过了
【描述业务影响】
jdbc-ws连接方式，columnDataExecuteBatch/ executeBatch方法批量插入的时候全部都是
java.sql.SQLException: TDengine ERROR (0x230): (0x230):Stmt cache error
java.sql.SQLException: TDengine ERROR (0x230): (0x230):Stmt cache error
at com.taosdata.jdbc.TSDBError.createSQLException(TSDBError.java:102)
at com.taosdata.jdbc.ws.AbsWSPreparedStatement.executeBatchImpl(AbsWSPreparedStatement.java:1129)
at com.taosdata.jdbc.ws.AbsWSPreparedStatement.columnDataExecuteBatch(AbsWSPreparedStatement.java:1144)
官网chat对话问了找不到原因，文档目前没有找到
addBatch + executeBatch：
public void newBatchInsertStmt2One(List dataList) throws SQLException {
String sql = "INSERT INTO ? USING st_cm_monitor_data " +
"TAGS(?,?,?,?,?,?,?,?) " +
“VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)”;
List<MonitorDataEntity> list = dataList.stream().map(this::convert).collect(Collectors.toList());
Map<String, List<MonitorDataEntity>> tableGroup = list.stream()
.collect(Collectors.groupingBy(MonitorDataEntity::getMonitorCommonCode));
Properties props = new Properties();
props.setProperty(TSDBDriver.PROPERTY_KEY_USER, username);
props.setProperty(TSDBDriver.PROPERTY_KEY_PASSWORD, password);
props.setProperty("stmtBindMode", "traditional");
try (Connection conn = DriverManager.getConnection(url, props)) {
for (Map.Entry<String, List<MonitorDataEntity>> entry : tableGroup.entrySet()) {
String tbName = entry.getKey();
List<MonitorDataEntity> itemList = entry.getValue();
if (itemList.isEmpty()) continue;
try (TSWSPreparedStatement stmt =
conn.prepareStatement(sql).unwrap(TSWSPreparedStatement.class)) {
stmt.setTableName(tbName);
for (MonitorDataEntity item : itemList) {
// ---- 标签：索引从 1 开始 ----
stmt.setTagString(0, item.getMonitorCommonCode());
stmt.setTagLong(1, item.getStationId());
stmt.setTagString(2, item.getDeviceLargeType());
stmt.setTagString(3, item.getDeviceTypeCode());
stmt.setTagInt(4, item.getMonitorType());
stmt.setTagString(5, item.getMonitorTypeCode());
stmt.setTagLong(6, item.getDeviceId());
stmt.setTagLong(7, item.getMonitorDeviceId());
// ---- VALUES 列：索引从 1 开始 ----
stmt.setTimestamp(1, new Timestamp(item.getCollectionTime().getTime()));
stmt.setBigDecimal(2, item.getCurrentValue());
stmt.setBigDecimal(3, item.getMinusValue());
stmt.setInt(4, item.getDataSource());
stmt.setInt(5, item.getDataStatus());
stmt.setInt(6, item.getDataType());
stmt.setString(7, item.getAncestors());
stmt.setString(8, item.getDeptAncestors());
stmt.setLong(9, item.getDeptId());
stmt.setString(10, item.getDeptName());
stmt.setString(11, item.getDeviceCode());
stmt.setString(12, item.getDeviceName());
stmt.setString(13, item.getDeviceLargeTypeName());
stmt.setLong(14, item.getLineId());
stmt.setString(15, item.getLineName());
stmt.setString(16, item.getMonitorDescription());
stmt.setString(17, item.getMonitorTypeName());
stmt.setString(18, item.getRegisterNumber());
stmt.setString(19, item.getUnit());
stmt.setTimestamp(20, new Timestamp(item.getCreateTime().getTime()));
stmt.setInt(21, item.getCreateBy());
stmt.addBatch(); // ← 标准 JDBC 行模式
}
stmt.executeBatch(); // ← 标准 JDBC 批量执行
}
}
}
}
columeData方式:
public void newBatchInsertStmt2(List dataList) throws SQLException{
String sql = "INSERT INTO ? USING st_cm_monitor_data_bak " +
"TAGS(?,?,?,?,?,?,?,?) " +
“VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)”;
List<MonitorDataEntity> list = dataList.stream().map(this::convert).collect(Collectors.toList());
// 分组key建议直接是子表名称，这里先用你原有分组逻辑
Map<String, List> tableGroup = list.stream()
.collect(Collectors.groupingBy(MonitorDataEntity::getMonitorCommonCode));
Properties props = new Properties();
props.setProperty(TSDBDriver.PROPERTY_KEY_USER, username);
props.setProperty(TSDBDriver.PROPERTY_KEY_PASSWORD, password);
try (Connection conn = DriverManager.getConnection(url, props)) {
// 遍历每一个子表分组
for (Map.Entry<String, List<MonitorDataEntity>> entry : tableGroup.entrySet()) {
String tbName = entry.getKey();
List<MonitorDataEntity> itemList = entry.getValue();
if (itemList.isEmpty()) continue;
// 每个子表独立创建stmt，try自动关闭，彻底清除缓冲区残留
try (TSWSPreparedStatement stmt = conn.prepareStatement(sql).unwrap(TSWSPreparedStatement.class)) {
// 先固定子表名，全程不变
stmt.setTableName(tbName);
MonitorDataEntity itemCase = itemList.get(0);
// 逐个TAG单值赋值（官方原生存在的方法，不会爆红）
stmt.setTagString(0, itemCase.getMonitorCommonCode());
stmt.setTagLong(1, itemCase.getStationId());
stmt.setTagString(2, itemCase.getDeviceLargeType());
stmt.setTagString(3, itemCase.getDeviceTypeCode());
stmt.setTagInt(4, itemCase.getMonitorType());
stmt.setTagString(5, itemCase.getMonitorTypeCode());
stmt.setTagLong(6, itemCase.getDeviceId());
stmt.setTagLong(7, itemCase.getMonitorDeviceId());
List<Long> list0 = new ArrayList<>();
List<Double> list1 = new ArrayList<>();
List<Double> list2 = new ArrayList<>();
List<Integer> list3 = new ArrayList<>();
List<Integer> list4 = new ArrayList<>();
List<Integer> list5 = new ArrayList<>();
List<String> list6 = new ArrayList<>();
List<String> list7 = new ArrayList<>();
List<Long> list8 = new ArrayList<>();
List<String> list9 = new ArrayList<>();
List<String> list10 = new ArrayList<>();
List<String> list11 = new ArrayList<>();
List<String> list12 = new ArrayList<>();
List<Long> list13 = new ArrayList<>();
List<String> list14 = new ArrayList<>();
List<String> list15 = new ArrayList<>();
List<String> list16 = new ArrayList<>();
List<String> list17 = new ArrayList<>();
List<String> list18 = new ArrayList<>();
List<Long> list19 = new ArrayList<>();
List<Integer> list20 = new ArrayList<>();
for (MonitorDataEntity item : itemList) {
list0.add(item.getCollectionTime().getTime());
list1.add(item.getCurrentValue() != null ? item.getCurrentValue().doubleValue() : null);
list2.add(item.getMinusValue() != null ? item.getMinusValue().doubleValue() : null);
list3.add(item.getDataSource());
list4.add(item.getDataStatus());
list5.add(item.getDataType());
list6.add(item.getAncestors());
list7.add(item.getDeptAncestors());
list8.add(item.getDeptId());
list9.add(item.getDeptName());
list10.add(item.getDeviceCode());
list11.add(item.getDeviceName());
list12.add(item.getDeviceLargeTypeName());
list13.add(item.getLineId());
list14.add(item.getLineName());
list15.add(item.getMonitorDescription());
list16.add(item.getMonitorTypeName());
list17.add(item.getRegisterNumber());
list18.add(item.getUnit());
list19.add(item.getCreateTime().getTime());
list20.add(item.getCreateBy());
// 单行加入批次
}
stmt.setTimestamp(0, list0);
stmt.setDouble( 1, list1);
stmt.setDouble( 2, list2);
stmt.setInt( 3, list3);
stmt.setInt( 4, list4);
stmt.setInt( 5, list5);
stmt.setString( 6, list6, 64);
stmt.setString( 7, list7, 256);
stmt.setLong( 8, list8);
stmt.setString( 9, list9, 128);
stmt.setString( 10, list10, 128);
stmt.setString( 11, list11, 256);
stmt.setString( 12, list12, 128);
stmt.setLong( 13, list13);
stmt.setString( 14, list14, 128);
stmt.setString( 15, list15, 512);
stmt.setString( 16, list16, 128);
stmt.setString( 17, list17, 32);
stmt.setString( 18, list18, 16);
stmt.setTimestamp( 19, list19);
stmt.setInt( 20, list20);
stmt.columnDataAddBatch();
// 当前子表所有行一次性提交
stmt.columnDataExecuteBatch();
}
}
}
}
均报错