数据库统一采用datatime格式，该格式是纯粹字符串，不包含时区内容

mybatis采用自定义的DateTypeHandler来处理包含时区查询和结果集。

自定义DateStringTypeHandler

public class DateStringTypeHandler extends DateTypeHandler {

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, Date parameter, JdbcType jdbcType) throws SQLException {
        ps.setTimestamp(i, new Timestamp(parameter.getTime()));
    }

    //将数据库中的类型转换成java类型
    @Override
    public Date getNullableResult(ResultSet resultSet, String columnName) throws SQLException {
        String dateStr = resultSet.getString(columnName);
        String stationId = resultSet.getString("station_id");
        if(!StringUtils.isEmpty(stationId)){
            //从本地缓存获取电站时区,转化为date
            Cache<String,String> cache =SpringUtil.getBean("localCache");
            String timeZone = cache.getIfPresent(stationId);
            ZonedDateTime zonedDateTime = DateUtil.parseToZoneDateTimeDefault(dateStr,timeZone);
            return Date.from(zonedDateTime.toInstant());
        }else {
            Timestamp sqlTimestamp = resultSet.getTimestamp(columnName);
            if (sqlTimestamp != null) {
                return new Date(sqlTimestamp.getTime());
            }
        }
        return null;
    }

    //将数据库中的类型转换成java类型
    @Override
    public Date getNullableResult(ResultSet resultSet, int columnIndex) throws SQLException {
        String dateStr = resultSet.getString(columnIndex);
        String stationId = resultSet.getString("station_id");
        if(!StringUtils.isEmpty(stationId)){
            //从本地缓存获取电站时区,转化为date
            Cache<String,String> cache =SpringUtil.getBean("localCache");
            String timeZone = cache.getIfPresent(stationId);
            ZonedDateTime zonedDateTime = DateUtil.parseToZoneDateTimeDefault(dateStr,timeZone);
            return Date.from(zonedDateTime.toInstant());
        }else {
            Timestamp sqlTimestamp = resultSet.getTimestamp(columnIndex);
            if (sqlTimestamp != null) {
                return new Date(sqlTimestamp.getTime());
            }
        }
        return null;
    }
    //将数据库中的类型转换成java类型
    @Override
    public Date getNullableResult(CallableStatement resultSet, int columnIndex) throws SQLException {
        String dateStr = resultSet.getString(columnIndex);
        String stationId = resultSet.getString("station_id");
        if(!StringUtils.isEmpty(stationId)){
            //从本地缓存获取电站时区,转化为date
            Cache<String,String> cache =SpringUtil.getBean("localCache");
            String timeZone = cache.getIfPresent(stationId);
            ZonedDateTime zonedDateTime = DateUtil.parseToZoneDateTimeDefault(dateStr,timeZone);
            return Date.from(zonedDateTime.toInstant());
        }else {
            Timestamp sqlTimestamp = resultSet.getTimestamp(columnIndex);
            if (sqlTimestamp != null) {
                return new Date(sqlTimestamp.getTime());
            }
        }
        return null;
    }

将自定义的TypeHandler加入mybatis的TypeHandlerRegistry中,否则不会生效

@Bean
    public ConfigurationCustomizer configurationCustomizer() {
        return configuration -> {
            //代码增强，实现插入数据库和返回数据的时候bigDecimal末尾0去除
            TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
            typeHandlerRegistry.register(Date.class, new DateStringTypeHandler());
            typeHandlerRegistry.register(JdbcType.DATE, new DateStringTypeHandler());
        };
    }

时区信息放入本地缓存

@Configuration
@Slf4j
public class LocalCacheConfig {

    @Autowired
    StationConfigMapper stationConfigMapper;

    @Bean("localCache")
    public Cache<String, String> localCache() {
        Cache<String, String> cache = CacheBuilder.newBuilder().maximumSize(100)
                .expireAfterWrite(60, TimeUnit.SECONDS)
                .refreshAfterWrite(30, TimeUnit.SECONDS).build(new CacheLoader<String, String>() {
                    @Override
                    public String load(String key) throws Exception {
                        log.info("start load station's timezone into local cache!");
                        StationConfigEntity stationConfig = stationConfigMapper.findByStationId(key);
                        if(stationConfig!=null){
                            return stationConfig.getTimeZone();
                        }
                        return null;
                    }
                });
        List<StationConfigEntity> stationConfigEntities= stationConfigMapper.findAll();
        Map<String, String> map = stationConfigEntities.parallelStream().collect(Collectors.toMap(StationConfigEntity::getStationId, v -> v.getTimeZone()));
        cache.putAll(map);
        return cache;
    }
}