Got SQLException when insert entity with attribute type java.time.LocalDateTime

[sunnyfun888]

Environment

• OS version: NA
• JDK version: 1.8
• ClickHouse Server version: 21.1.2.15
• ClickHouse Native JDBC version: 2.5.3
• (Optional) Spark version: N/A
• (Optional) Other components' version: N/A

Error logs

Caused by: java.sql.SQLException: Exception processing value 2021-02-02T13:59:24.103 for column: update_time
    at com.github.housepower.jdbc.data.Block.appendRow(Block.java:97)
    at com.github.housepower.jdbc.statement.ClickHousePreparedInsertStatement.addParameters(ClickHousePreparedInsertStatement.java:162)
    at com.github.housepower.jdbc.statement.ClickHousePreparedInsertStatement.executeUpdate(ClickHousePreparedInsertStatement.java:80)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
    at org.springframework.jdbc.core.JdbcTemplate.lambda$batchUpdate$2(JdbcTemplate.java:959)
    at org.springframework.jdbc.core.JdbcTemplate$$Lambda$1194/1975555493.doInPreparedStatement(Unknown Source)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617)
    ... 97 more
Caused by: java.lang.ClassCastException: java.time.LocalDateTime cannot be cast to java.time.ZonedDateTime
    at com.github.housepower.jdbc.data.type.complex.DataTypeDateTime.serializeBinary(DataTypeDateTime.java:115)
    at com.github.housepower.jdbc.data.Column.write(Column.java:31)
    at com.github.housepower.jdbc.data.ColumnNullable.write(ColumnNullable.java:40)
    at com.github.housepower.jdbc.data.Block.appendRow(Block.java:93)

Steps to reproduce

Create an entity class with attribute type java.time.LocalDateTime, then try to use spring JdbcTemplate to do the insert.

Other descriptions

It seems DataTypeDateTime.java is trying to cast any object to java.time.ZonedDateTime, then Caused the java.lang.ClassCastException.

[pan3793]

Thanks for report. We need refactor our tests to cover more cases

[sunnyfun888]

I tried to fix the issue and here is the code for reference:

In DataTypeDateTime.java, change the method public void serializeBinary(Object data, BinarySerializer serializer) throws SQLException, IOException to:

    private ZoneId zid = ZoneId.systemDefault();

    @Override
    public void serializeBinary(Object data, BinarySerializer serializer) throws SQLException, IOException {
        ZonedDateTime zdt = null;

        if (data instanceof LocalDateTime) {
            zdt = ((LocalDateTime) data).atZone(zid);
        } else if (data instanceof ZonedDateTime) {
            zdt = (ZonedDateTime) data;
        } 

        if (zdt != null) {
            serializer.writeInt((int) DateTimeUtil.toEpochSecond(zdt));
        }
    }

In class AbstractPreparedStatement.java, change the method private boolean assembleSimpleParameter(StringBuilder queryBuilder, Object parameter) to:

    protected ZoneId zid = ZoneId.systemDefault();

    private boolean assembleSimpleParameter(StringBuilder queryBuilder, Object parameter) {
        if (parameter == null) {
            return assembleWithoutQuotedParameter(queryBuilder, "Null");
        } else if (parameter instanceof Number) {
            return assembleWithoutQuotedParameter(queryBuilder, parameter);
        } else if (parameter instanceof String) {
            return assembleQuotedParameter(queryBuilder, String.valueOf(parameter));
        } else if (parameter instanceof LocalDate) {
            return assembleQuotedParameter(queryBuilder, dateFmt.format((LocalDate) parameter));
        } else if (parameter instanceof ZonedDateTime) {
            return assembleQuotedParameter(queryBuilder, timestampFmt.format((ZonedDateTime) parameter));
        } else if (parameter instanceof LocalDateTime) {
            return assembleQuotedParameter(queryBuilder, timestampFmt.format(((LocalDateTime) parameter).atZone(zid)));
        }
        return false;
    }

This will fix the insert and update issue for LocalDateTime, but not consider other date types eg. java.util.Date.

[pan3793]

It works, but I'm afraid we can't do it in this way. We are refactoring to make all IDataTypes clean and move all type cast stuffs to JDBC relative classes.

[pan3793]

close by #316