通过JDBC写入CnosDB成功但executeUpdate返回-1而非行数的问题

问题描述 #

通过JDBC向CnosDB写入数据，数据已成功写入数据库，但executeUpdate接口返回的不是预期的受影响行数而是-1。相关代码、日志及数据查询结果如下：

代码步骤 #

1. 创建数据库：

boolean rs002 = stmt.execute("CREATE DATABASE tc_between WITH TTL '100000d';");
assertFalse(rs002);

2. 创建表：

boolean rs004 = stmt001.execute("CREATE TABLE IF NOT EXISTS m2(f0 BIGINT UNSIGNED, f1 BIGINT, TAGS(t0, t1));");
assertFalse(rs004);

3. 插入数据：

int rs005 = stmt001.executeUpdate("INSERT m2(TIME, t0, f0, t1, f1) VALUES(CAST (1672301798050000000 AS TIMESTAMP), 'Ig.UZ', 531136669299148225, 'nA4C3DH~B ', 9223372036854775807),(CAST (1672301798060000000 AS TIMESTAMP), '263356943', 1040920791041719924, '', -9223372036854775807),(CAST (1672301798070000000 AS TIMESTAMP), '1040920791041719924', 442061994865016078, 'gE1B7c.', 0);");
System.out.println(rs005);
int rs006 = stmt001.executeUpdate("INSERT m2(TIME, t0, f0, t1, f1) VALUES(CAST (3031647407609562138 AS TIMESTAMP), '1D75h', 4166390262642105876, '7ua', 0.0),(CAST (1079616064603730664 AS TIMESTAMP), '}', 7806435932478031652, 'EA26qy', 23.456), (CAST (263356943 AS TIMESTAMP), '0.6287658423307444', 5466573340614276155, ',JC51Fh', -23.456), (CAST (1742494251700243812 AS TIMESTAMP), '#f^uE1B7KruC7BFz', 196790207, 'aF', 0.123);");
assertEquals(4, rs006);
int rs007 = stmt001.executeUpdate("INSERT m2(TIME, t0, f0, t1, f1) VALUES(CAST (3584132160280509277 AS TIMESTAMP), '', 4132058214182166915, 'V*1lE/', -0.123);");
assertEquals(1, rs007);
ResultSet rs008 = stmt001.executeQuery("SELECT m2.f0 FROM m2 WHERE CAST(0 AS STRING) BETWEEN (CAST( starts_with(m2.t0, m2.t1) AS STRING)) AND (m2.t1);");
rs008.next();
System.out.println(rs008.getString(1));
stmt001.close();
conn001.close();

错误日志 #

[ERROR]   AppTest.test_Jdbc_betweensql:66 expected:<4> but was:<-1>

数据查询结果 #

tc_between ❯ select * from m2;
+-------------------------------+---------------------+------------+---------------------+----------------------+
| time                          | t0                  | t1         | f0                  | f1                   |
+-------------------------------+---------------------+------------+---------------------+----------------------+
| 2025-03-20T18:10:51.700243812 | #f^uE1B7KruC7BFz    | aF         | 196790207           | 0                    |
| 2004-03-18T13:21:04.603730664 | }                   | EA26qy     | 7806435932478031652 | 23                   |
| 2066-01-25T12:16:47.609562138 | 1D75h               | 7ua        | 4166390262642105876 | 0                    |
| 2022-12-29T08:16:38.070       | 1040920791041719924 | gE1B7c.    | 442061994865016078  | 0                    |
| 1970-01-01T00:00:00.263356943 | 0.6287658423307444  | ,JC51Fh    | 5466573340614276155 | -23                  |
| 2022-12-29T08:16:38.050       | Ig.UZ               | nA4C3DH~B  | 531136669299148225  | 9223372036854775807  |
| 2022-12-29T08:16:38.060       | 263356943           |            | 1040920791041719924 | -9223372036854775807 |
+-------------------------------+---------------------+------------+---------------------+----------------------+
Query took 0.100 seconds.

原因分析 #

这是CnosDB JDBC驱动的设计特性导致的：

• 时序数据库的写入通常采用批量、异步落盘的处理模式，驱动无法即时获取精确的插入行数；
• 当前版本的CnosDB JDBC驱动未实现返回INSERT语句受影响行数的逻辑，因此统一返回-1。

从查询结果来看，所有数据都已成功写入，说明写入操作本身是正常的，只是驱动没有返回预期的行数数值。

验证方案 #

如果需要验证插入的行数，可以在写入后执行COUNT查询来确认，示例代码如下：

// 在插入完成后执行计数查询
ResultSet countResult = stmt001.executeQuery("SELECT COUNT(*) FROM m2");
countResult.next();
int actualRowCount = countResult.getInt(1);
// 根据实际插入的总数据量进行断言
// assertEquals(8, actualRowCount);

内容的提问来源于stack exchange，提问作者Baker X