java · 2024-05-16 0

clickhouse jdbc 升级，sql 参数有 LocalDateTime，执行查询失败分析

一、准备

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

标签：

您可能还喜欢...

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注