一、准备
1.问题
clickohuse jdbc 由 0.3.1 升级到 0.3.2-patch11 后,执行 sql,报出如下错误:
java.sql.BatchUpdateException: Code: 53. DB::Exception: Cannot convert string 2024-05-16 23:59:59.999 to type DateTime:
2.环境
clickhouse server 版本:clickhouse/clickhouse-server:23.1.2.9
创建测试表:
CREATE TABLE default.test_time
(
`id` Int64,
`datetime_1` DateTime,
`datetime64_1` DateTime64(3)
)
ENGINE = MergeTree
PRIMARY KEY id
ORDER BY id;
二、clickhouse-jdbc 0.3.1
1.clickhouse-jdbc 版本
<dependency>
<groupId>ru.yandex.clickhouse</groupId>
<artifactId>clickhouse-jdbc</artifactId>
<version>0.3.1</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
<scope>test</scope>
</dependency>
2.java 代码
@Test
public void test11() throws SQLException {
String url = "jdbc:clickhouse://127.0.0.1:8123/default";
String user = "default";
String password = null;
String sql = "SELECT id, datetime_1 FROM default.test_time WHERE datetime_1 <= ?";
LocalDateTime now = LocalDateTime.now();
Connection connection = DriverManager.getConnection(url, user, password);
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1, now);
// 生成的 sql
// 类似,SELECT id, datetime_1 FROM default.test_time WHERE datetime_1 <= '2024-05-16 23:59:59';
// 执行成功
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
Object id = resultSet.getObject(1);
Object datetime = resultSet.getObject(2);
LocalDateTime localDateTime = resultSet.getObject(2, LocalDateTime.class); // 实际对象,java.sql.Timestamp
System.out.println(String.format("%s %s %s", id, datetime, localDateTime));
}
}
结果如下:
2 2024-05-16 20:22:03.0 2024-05-16T12:22:03
1 2024-05-16 20:21:59.0 2024-05-16T12:21:59
执行可以查询出结果,注意,clickhouse-jdbc 0.3.1 版本,这里转化为 LocalDateTime,会有时区问题
3.分析 LocalDateTime 传递给 PreparedStatement
执行 preparedStatement.setObject 进入 ClickHousePreparedStatementImpl
// ClickHousePreparedStatementImpl.java
@Override
public void setObject(int parameterIndex, Object x) throws SQLException {
if (x != null) {
setBind(
parameterIndex,
ClickHousePreparedStatementParameter.fromObject(
x, dateTimeZone, dateTimeTimeZone));
} else {
setNull(parameterIndex);
}
}
ClickHousePreparedStatementParameter 格式化参数
// ClickHousePreparedStatementParameter.java
public static ClickHousePreparedStatementParameter fromObject(Object x,
TimeZone dateTimeZone, TimeZone dateTimeTimeZone)
{
if (x == null) {
return NULL_PARAM;
}
return new ClickHousePreparedStatementParameter(
ClickHouseValueFormatter.formatObject(x, dateTimeZone, dateTimeTimeZone),
ClickHouseValueFormatter.needsQuoting(x));
}
ClickHouseValueFormatter 把参数转化为 string 字符串,例,会把 LocalDateTime 转换为类似 '2024-05-16 23:59:59' 的字符串
// ClickHouseValueFormatter.java
private static final DateTimeFormatter DATE_TIME_FORMATTER =
DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
public static String formatObject(Object x, TimeZone dateTimeZone,
TimeZone dateTimeTimeZone)
{
if (x == null) {
return null;
}
if (x instanceof Byte) {
return formatInt(((Byte) x).intValue());
} else if (x instanceof String) {
return formatString((String) x);
} else if (x instanceof BigDecimal) {
return formatBigDecimal((BigDecimal) x);
} else if (x instanceof Short) {
return formatShort(((Short) x).shortValue());
} else if (x instanceof Integer) {
return formatInt(((Integer) x).intValue());
} else if (x instanceof Long) {
return formatLong(((Long) x).longValue());
} else if (x instanceof Float) {
return formatFloat(((Float) x).floatValue());
} else if (x instanceof Double) {
return formatDouble(((Double) x).doubleValue());
} else if (x instanceof byte[]) {
return formatBytes((byte[]) x);
} else if (x instanceof Date) {
return formatDate((Date) x, dateTimeZone);
} else if (x instanceof LocalDate) {
return formatLocalDate((LocalDate) x);
} else if (x instanceof Time) {
return formatTime((Time) x, dateTimeTimeZone);
} else if (x instanceof LocalTime) {
return formatLocalTime((LocalTime) x);
} else if (x instanceof OffsetTime) {
return formatOffsetTime((OffsetTime) x);
} else if (x instanceof Timestamp) {
return formatTimestamp((Timestamp) x, dateTimeTimeZone);
} else if (x instanceof LocalDateTime) {
return formatLocalDateTime((LocalDateTime) x);
} else if (x instanceof OffsetDateTime) {
return formatOffsetDateTime((OffsetDateTime) x, dateTimeTimeZone);
} else if (x instanceof ZonedDateTime) {
return formatZonedDateTime((ZonedDateTime) x, dateTimeTimeZone);
} else if (x instanceof Boolean) {
return formatBoolean(((Boolean) x).booleanValue());
} else if (x instanceof UUID) {
return formatUUID((UUID) x);
} else if (x instanceof BigInteger) {
return formatBigInteger((BigInteger) x);
} else if (x instanceof Collection) {
return ClickHouseArrayUtil.toString((Collection<?>) x, dateTimeZone, dateTimeTimeZone);
} else if (x instanceof ClickHouseBitmap) {
return formatBitmap((ClickHouseBitmap) x);
} else if (x instanceof Map) {
return formatMap((Map<?, ?>) x, dateTimeZone, dateTimeTimeZone);
} else if (x.getClass().isArray()) {
return ClickHouseArrayUtil.arrayToString(x, dateTimeZone, dateTimeTimeZone);
} else {
return String.valueOf(x);
}
}
public static String formatLocalDateTime(LocalDateTime x) {
return DATE_TIME_FORMATTER.format(x);
}
三、clickhouse-jdbc 0.3.2-patch11
1.clickhouse-jdbc 版本
<dependency>
<groupId>com.clickhouse</groupId>
<artifactId>clickhouse-jdbc</artifactId>
<version>0.3.2-patch11</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
<scope>test</scope>
</dependency>
2.java 代码
@Test
public void test21() throws SQLException {
String url = "jdbc:clickhouse://127.0.0.1:8123/default";
String user = "default";
String password = null;
String sql = "SELECT id, datetime_1 FROM default.test_time WHERE datetime_1 <= ?";
LocalDateTime now = LocalDateTime.now();
Connection connection = DriverManager.getConnection(url, user, password);
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1, now);
// 生成的 sql
// 类似,SELECT id, datetime_1 FROM default.test_time WHERE datetime_1 <= '2024-05-16 23:59:59.999';
// 执行错误
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
Object id = resultSet.getObject(1);
Object datetime = resultSet.getObject(2);
LocalDateTime localDateTime = resultSet.getObject(2, LocalDateTime.class);
System.out.println(String.format("%s %s %s", id, datetime, localDateTime));
}
}
结果报错如下:
java.sql.BatchUpdateException: Code: 53. DB::Exception: Cannot convert string 2024-05-16 20:30:39.35 to type DateTime: while executing 'FUNCTION lessOrEquals(datetime_1 : 0, '2024-05-16 20:30:39.35' : 1) -> lessOrEquals(datetime_1, '2024-05-16 20:30:39.35') UInt8 : 2'. (TYPE_MISMATCH) (version 23.1.3.5 (official build))
3.分析 LocalDateTime 传递给 PreparedStatement
执行 preparedStatement.setObject 进入 SqlBasedPreparedStatement
// SqlBasedPreparedStatement.java
@Override
public void setObject(int parameterIndex, Object x) throws SQLException {
ensureOpen();
int idx = toArrayIndex(parameterIndex);
ClickHouseValue value = templates[idx];
if (value != null) {
value.update(x);
values[idx] = value.toSqlExpression();
} else {
if (x instanceof ClickHouseValue) {
value = (ClickHouseValue) x;
templates[idx] = value;
values[idx] = value.toSqlExpression();
} else {
values[idx] = ClickHouseValues.convertToSqlExpression(x);
}
}
}
ClickHouseValues 会把参数转化为字符串,例,会把 LocalDateTime 转换为类似 '2024-05-16 23:59:59.999' 的字符串
// ClickHouseValues.java
public static final DateTimeFormatter DATETIME_FORMATTER;
static {
DATETIME_FORMATTER = (new DateTimeFormatterBuilder()).appendPattern("yyyy-MM-dd HH:mm:ss").appendFraction(ChronoField.NANO_OF_SECOND, 0, 9, true).toFormatter();
}
public static String convertToSqlExpression(Object value) {
if (value == null) {
return "NULL";
} else {
String s;
if (value instanceof String) {
s = convertToQuotedString(value);
} else if (value instanceof ClickHouseValue) {
s = ((ClickHouseValue)value).toSqlExpression();
} else if (value instanceof UUID) {
s = "" + '\'' + value + '\'';
} else if (value instanceof LocalDate) {
s = '\'' + ((LocalDate)value).format(DATE_FORMATTER) + '\'';
} else if (value instanceof LocalTime) {
s = '\'' + ((LocalTime)value).format(TIME_FORMATTER) + '\'';
} else if (value instanceof LocalDateTime) {
s = '\'' + ((LocalDateTime)value).format(DATETIME_FORMATTER) + '\'';
} else if (value instanceof OffsetDateTime) {
s = '\'' + ((OffsetDateTime)value).format(DATETIME_FORMATTER) + '\'';
} else if (value instanceof ZonedDateTime) {
s = '\'' + ((ZonedDateTime)value).format(DATETIME_FORMATTER) + '\'';
} else if (value instanceof InetAddress) {
s = '\'' + ((InetAddress)value).getHostAddress() + '\'';
} else if (value instanceof Enum) {
s = String.valueOf(((Enum)value).ordinal());
} else {
StringBuilder builder;
if (value instanceof Object[]) {
builder = (new StringBuilder()).append('[');
Object[] var3 = (Object[])value;
int var4 = var3.length;
for(int var5 = 0; var5 < var4; ++var5) {
Object o = var3[var5];
builder.append(convertToSqlExpression(o)).append(',');
}
if (builder.length() > 1) {
builder.setLength(builder.length() - 1);
}
s = builder.append(']').toString();
} else {
Iterator var7;
if (value instanceof Collection) {
builder = (new StringBuilder()).append('(');
var7 = ((Collection)value).iterator();
while(var7.hasNext()) {
Object v = var7.next();
builder.append(convertToSqlExpression(v)).append(',');
}
if (builder.length() > 1) {
builder.setLength(builder.length() - 1);
}
s = builder.append(')').toString();
} else if (value instanceof Enumeration) {
builder = (new StringBuilder()).append('(');
Enumeration<Object> v = (Enumeration)value;
while(v.hasMoreElements()) {
builder.append(convertToSqlExpression(v.nextElement())).append(',');
}
if (builder.length() > 1) {
builder.setLength(builder.length() - 1);
}
s = builder.append(')').toString();
} else if (value instanceof Map) {
builder = (new StringBuilder()).append('{');
var7 = ((Map)value).entrySet().iterator();
while(var7.hasNext()) {
Map.Entry<Object, Object> v = (Map.Entry)var7.next();
builder.append(convertToSqlExpression(v.getKey())).append(" : ").append(convertToSqlExpression(v.getValue())).append(',');
}
if (builder.length() > 1) {
builder.setLength(builder.length() - 1);
}
s = builder.append('}').toString();
} else if (value instanceof Boolean) {
s = String.valueOf((Boolean)value ? 1 : 0);
} else if (value instanceof Character) {
s = String.valueOf((Character)value);
} else if (value instanceof boolean[]) {
s = convertToString((boolean[])value);
} else if (value instanceof char[]) {
s = convertToString((char[])value);
} else if (value instanceof byte[]) {
s = convertToString((byte[])value);
} else if (value instanceof short[]) {
s = convertToString((short[])value);
} else if (value instanceof int[]) {
s = convertToString((int[])value);
} else if (value instanceof long[]) {
s = convertToString((long[])value);
} else if (value instanceof float[]) {
s = convertToString((float[])value);
} else if (value instanceof double[]) {
s = convertToString((double[])value);
} else {
s = String.valueOf(value);
}
}
}
return s;
}
}
四、解决办法
clickhouse jdbc 升级,不同版本的 clickhouse jdbc 对 java LocalDateTime 处理是不一样的。
如遇到此类问题,可以把表字段改为 DateTime64 类型
clickhouse-jdbc 0.3.1
java 类型 | clckhouse 类型 |
---|---|
LocalDateTime | DateTime |
clickhouse-jdbc 0.3.2-patch11
java 类型 | clckhouse 类型 |
---|---|
LocalDateTime | DateTime64 |