數(shù)據(jù)庫統(tǒng)一采用datatime格式矩桂，該格式是純粹字符串，不包含時(shí)區(qū)內(nèi)容

mybatis采用自定義的DateTypeHandler來處理包含時(shí)區(qū)查詢和結(jié)果集都弹。

自定義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()));
    }

    //將數(shù)據(jù)庫中的類型轉(zhuǎn)換成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)){
            //從本地緩存獲取電站時(shí)區(qū),轉(zhuǎn)化為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;
    }

    //將數(shù)據(jù)庫中的類型轉(zhuǎn)換成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)){
            //從本地緩存獲取電站時(shí)區(qū),轉(zhuǎn)化為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;
    }
    //將數(shù)據(jù)庫中的類型轉(zhuǎn)換成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)){
            //從本地緩存獲取電站時(shí)區(qū),轉(zhuǎn)化為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中,否則不會(huì)生效

@Bean
    public ConfigurationCustomizer configurationCustomizer() {
        return configuration -> {
            //代碼增強(qiáng)哮翘，實(shí)現(xiàn)插入數(shù)據(jù)庫和返回?cái)?shù)據(jù)的時(shí)候bigDecimal末尾0去除
            TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
            typeHandlerRegistry.register(Date.class, new DateStringTypeHandler());
            typeHandlerRegistry.register(JdbcType.DATE, new DateStringTypeHandler());
        };
    }

時(shí)區(qū)信息放入本地緩存

@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;
    }
}