Mybatis支持实体类LocalDateTime格式化转化解析

Mybatis生成的映射实体类对应的日期类型是Date类型，我们应该改成LocatDateTime等Java8新出的安全日期类型，但是这样实体类在mybatis执行映射解析上跟数据库的类型是匹配不上的，会报类型格式不匹配等问题。

解决方法：统计设置日期类型转换，执行查询获取的日期需要映射为LocatDateTime类型，执行新增更新等操作时将LocatDateTime日期对象转成与数据库相匹配的类型。代码如下：

import com.common.constant.DateConstant;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.type.baseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.time.LocalDateTime;
import java.time.ZoneId;


@Component
//定义转换器支持的JAVA类型
@MappedTypes(LocalDateTime.class)
//定义转换器支持的数据库类型
@MappedJdbcTypes(value = JdbcType.TIMESTAMP, includeNullJdbcType = true)
public class CustomLocalDateTimeTypeHandler extends baseTypeHandler {

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, LocalDateTime parameter, JdbcType jdbcType)
            throws SQLException {
        if (parameter != null) {
            // ps.setString(i, DateConstant.FORMAT_YYYY_MM_DD_HH_MM_SS.format(parameter));
            // 不用解析为字符串，不然与数据库TIMESTAMP类型不匹配
            // setDate(),sql.Date只能精确到年月日，采用Timestamp时间戳方式
            ps.setTimestamp(i, Timestamp.valueOf(parameter));
        }
    }

    @Override
    public LocalDateTime getNullableResult(ResultSet rs, String columnName) throws SQLException {
        String target = rs.getString(columnName);
        if (StringUtils.isBlank(target)) {
            return null;
        }
        // 日期值后面多了.0，去掉
        target = target.substring(0, target.length() - 2);
        return LocalDateTime.parse(target, DateConstant.FORMAT_YYYY_MM_DD_HH_MM_SS);
    }

    @Override
    public LocalDateTime getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        String target = rs.getString(columnIndex);
        if (StringUtils.isBlank(target)) {
            return null;
        }
        // 日期值后面多了.0，去掉
        target = target.substring(0, target.length() - 2);
        return LocalDateTime.parse(target, DateConstant.FORMAT_YYYY_MM_DD_HH_MM_SS);
    }

    @Override
    public LocalDateTime getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        String target = cs.getString(columnIndex);
        if (StringUtils.isBlank(target)) {
            return null;
        }
        // 日期值后面多了.0，去掉
        target = target.substring(0, target.length() - 2);
        return LocalDateTime.parse(target, DateConstant.FORMAT_YYYY_MM_DD_HH_MM_SS);
    }
}

import org.apache.ibatis.type.TypeHandlerRegistry;
import org.springframework.context.annotation.Configuration;
import tk.mybatis.mapper.autoconfigure.ConfigurationCustomizer;


@Configuration
public class MybatisConfigurationCustomizer implements ConfigurationCustomizer {

    @Override
    public void customize(org.apache.ibatis.session.Configuration configuration) {
        TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
        typeHandlerRegistry.register(CustomLocalDateTimeTypeHandler.class);
    }
}

import java.time.format.DateTimeFormatter;


public interface DateConstant {

    
    DateTimeFormatter FORMAT_YYYY_MM_DD = DateTimeFormatter.ofPattern(YYYY_MM_DD);
    DateTimeFormatter FORMAT_YYYY_MM_DD_HH_MM_SS = DateTimeFormatter.ofPattern(YYYY_MM_DD_HH_MM_SS);

}

这样与数据库映射实体类的日期属性改为LocatDateTime类型时，也可以正常执行Mybatis操作，代码实现主要也是监听TIMESTAMP类型的sql字段进行解析转化，如果是获取则转化为LocatDateTime日期类，如果是更新则解析成java.sql包下可与数据库类型匹配的时间戳类型。

另外，如下是统一按日期格式json序列化LocatDateTime日期类型的代码：

import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.datatype.jsr310.deser.LocalDateDeserializer;
import com.fasterxml.jackson.datatype.jsr310.deser.LocalDateTimeDeserializer;
import com.fasterxml.jackson.datatype.jsr310.ser.LocalDateSerializer;
import com.fasterxml.jackson.datatype.jsr310.ser.LocalDateTimeSerializer;
import com.common.constant.DateConstant;
import org.springframework.boot.autoconfigure.jackson.Jackson2ObjectMapperBuilderCustomizer;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.http.converter.json.Jackson2ObjectMapperBuilder;

import java.time.LocalDate;
import java.time.LocalDateTime;


@Configuration
public class ObjectMapperConfiguration {

    private static ObjectMapper MAPPER;

    public static ObjectMapper getMapper() {
        if (MAPPER != null) {
            return MAPPER;
        }
        return new ObjectMapper();
    }

    @Bean
    @Primary
    public ObjectMapper jacksonObjectMapper(Jackson2ObjectMapperBuilder builder) {
        MAPPER = builder.createXmlMapper(false).build();
        return MAPPER;
    }

    @Bean
    public Jackson2ObjectMapperBuilderCustomizer enumCustomizer() {
        return builder -> builder
                .deserializerByType(LocalDateTime.class,
                        new LocalDateTimeDeserializer(DateConstant.FORMAT_YYYY_MM_DD_HH_MM_SS))
                .deserializerByType(LocalDate.class,
                        new LocalDateDeserializer(DateConstant.FORMAT_YYYY_MM_DD_HH_MM_SS))
                .serializerByType(LocalDateTime.class,
                        new LocalDateTimeSerializer(DateConstant.FORMAT_YYYY_MM_DD_HH_MM_SS))
                .serializerByType(LocalDate.class, new LocalDateSerializer(DateConstant.FORMAT_YYYY_MM_DD_HH_MM_SS))
                ;
    }
}