mybatis 升级，导致获得 clickhouse 时间错误

一、准备

1.问题

由于组件需要安全整改，进行了 mybatis 升级，发现用 mybatis 查出的时间是 0 时区的时间，clickhouse 存储的数据是正确的，为 8 时区时间。

2.环境

clickhouse server 版本：clickhouse/clickhouse-server:23.1.2.9
clickhouse jdbc 版本：0.3.1

创建 clickouhse 测试表：

CREATE TABLE `default`.`student`
(

    `id` Int64,
    `datetime_1` DateTime,
    `datetime64_1` DateTime64(3)
)
ENGINE = MergeTree
PRIMARY KEY id
ORDER BY id;

java 代码

@ToString
public class Student {

    private Integer id;

    private LocalDateTime datetime1;

    private LocalDateTime datetime641;
}

public interface StudentDao {

    List<Student> findAll();
}

测试代码：

@Test
public void testSelect() throws IOException {
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

    SqlSession openSession = sqlSessionFactory.openSession();
    StudentDao studentDao = openSession.getMapper(StudentDao.class);

    List<Student> list = studentDao.findAll();

    for (Student student : list) {
        System.out.println(student);
    }

    openSession.close();
}

mybatis 配置

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="ru.yandex.clickhouse.ClickHouseDriver"/>
                <property name="url" value="jdbc:clickhouse://127.0.0.1:8123/default"/>
                <property name="username" value="default"/>
                <property name="password" value=""/>
            </dataSource>
        </environment>
    </environments>

    <!--
    <mappers>
        包扫描，dao接口名与sql映射文件名相同，且放在同一包下
        <package name="com.example.dao"/>
    </mappers>
    -->

    <mappers>
        <mapper resource="mapper/studentDao.xml"/>
    </mappers>

</configuration>

mapper 文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.dao.StudentDao">

    <resultMap id="StudentResultMap" type="com.example.bean.Student">
        <id property="id" column="id" jdbcType="NUMERIC"/>
        <result property="datetime1" column="datetime_1" jdbcType="TIMESTAMP"/>
        <result property="datetime641" column="datetime64_1" jdbcType="TIMESTAMP"/>
    </resultMap>

    <select id="findAll" resultMap="StudentResultMap">
        SELECT * FROM student
    </select>

</mapper>

二、mybatis 3.4.6

1.maven 依赖

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.4.6</version>
</dependency>

<dependency>
    <groupId>ru.yandex.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.3.1</version>
</dependency>

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.28</version>
    <scope>provided</scope>
</dependency>

<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
    <scope>test</scope>
</dependency>

执行测试类，结果：

Student(id=1, datetime1=2024-05-16T20:21:59, datetime641=2024-05-16T20:21:59)
Student(id=2, datetime1=2024-05-16T20:22:03, datetime641=2024-05-16T20:22:03)

2.分析结果

mybatis 的 PreparedStatementHandler#query 方法，进行调用 jdbc 查询数据，然后处理数据

// PreparedStatementHandler.java
@Override
public <E> List<E> query(Statement statement, ResultHandler resultHandler) throws SQLException {
  PreparedStatement ps = (PreparedStatement) statement;
  ps.execute();
  return resultSetHandler.<E> handleResultSets(ps);
}

DefaultResultSetHandler#getRowValue 处理每行数据，根据 LocalDateTime 类型，使用 LocalDateTimeTypeHandler 处理对应列的字段

// DefaultResultSetHandler.java
private Object getRowValue(ResultSetWrapper rsw, ResultMap resultMap) throws SQLException {
  final ResultLoaderMap lazyLoader = new ResultLoaderMap();
  Object rowValue = createResultObject(rsw, resultMap, lazyLoader, null);
  if (rowValue != null && !hasTypeHandlerForResultObject(rsw, resultMap.getType())) {
    final MetaObject metaObject = configuration.newMetaObject(rowValue);
    boolean foundValues = this.useConstructorMappings;
    if (shouldApplyAutomaticMappings(resultMap, false)) {
      foundValues = applyAutomaticMappings(rsw, resultMap, metaObject, null) || foundValues;
    }
    foundValues = applyPropertyMappings(rsw, resultMap, metaObject, lazyLoader, null) || foundValues;
    foundValues = lazyLoader.size() > 0 || foundValues;
    rowValue = foundValues || configuration.isReturnInstanceForEmptyRow() ? rowValue : null;
  }
  return rowValue;
}

private Object getPropertyMappingValue(ResultSet rs, MetaObject metaResultObject, ResultMapping propertyMapping, ResultLoaderMap lazyLoader, String columnPrefix)
    throws SQLException {
  if (propertyMapping.getNestedQueryId() != null) {
    return getNestedQueryMappingValue(rs, metaResultObject, propertyMapping, lazyLoader, columnPrefix);
  } else if (propertyMapping.getResultSet() != null) {
    addPendingChildRelation(rs, metaResultObject, propertyMapping);   // TODO is that OK?
    return DEFERED;
  } else {
    final TypeHandler<?> typeHandler = propertyMapping.getTypeHandler();
    final String column = prependPrefix(propertyMapping.getColumn(), columnPrefix);
    return typeHandler.getResult(rs, column);
  }
}

LocalDateTimeTypeHandler#getNullableResult 方法，可以看出 mybatis 使用 rs.getTimestamp(columnName)，得到 Timestamp 的对象，再转化为 LocalDateTime

// LocalDateTimeTypeHandler.java
@Override
public LocalDateTime getNullableResult(ResultSet rs, String columnName) throws SQLException {
  Timestamp timestamp = rs.getTimestamp(columnName);
  return getLocalDateTime(timestamp);
}

private static LocalDateTime getLocalDateTime(Timestamp timestamp) {
  if (timestamp != null) {
    return timestamp.toLocalDateTime();
  }
  return null;
}

三、mybatis 3.5.9

1.maven 依赖

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.9</version>
</dependency>

<dependency>
    <groupId>ru.yandex.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.3.1</version>
</dependency>

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.28</version>
    <scope>provided</scope>
</dependency>

<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
    <scope>test</scope>
</dependency>

执行测试类，结果：

Student(id=1, datetime1=2024-05-16T12:21:59, datetime641=2024-05-16T12:21:59)
Student(id=2, datetime1=2024-05-16T12:22:03, datetime641=2024-05-16T12:22:03)

2.分析结果

LocalDateTimeTypeHandler#getNullableResult 方法，可以看出 mybatis 使用 rs.getObject(columnName, LocalDateTime.class)，得到 LocalDateTime 的对象。
在 clickhouse jdbc 0.3.1 版本中，rs.getObject(columnName, LocalDateTime.class) 得到的是 0 时区的时间，可将 clickhouse jdbc 升级到 0.3.2-patch11 解决问题

// LocalDateTimeTypeHandler.java
@Override
public LocalDateTime getNullableResult(ResultSet rs, String columnName) throws SQLException {
  return rs.getObject(columnName, LocalDateTime.class);
}

四、解决问题

可将 clickhouse jdbc 升级到 0.3.2-patch11 解决问题

另：mybatis 3.5.1 (2019.4.7)，由 rs.getTimestamp(columnName) 改为 rs.getObject(columnName, LocalDateTime.class)，是为了支持 JSR310，使用全新的时间API。