MyBatis转换器-时间与字符串

道鸽拉斯

已于 2024-03-20 19:40:23 修改

阅读量183

点赞数 3

分类专栏： MyBatis 文章标签： mybatis

于 2024-03-20 19:37:03 首次发布

本文链接：https://blog.csdn.net/yearsdy/article/details/136886516

版权

MyBatis 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

背景：java字段类型为String，数据库类型为timestamp，需要统一处理时区

package xx.xx.xx;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeException;

import java.sql.*;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.ZoneOffset;
import java.time.format.DateTimeFormatter;

@Slf4j
public class TimestampToStringTypeHandler extends BaseTypeHandler<String> {
    private static final DateTimeFormatter LDT_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
    private static final DateTimeFormatter LDT_MILLIS_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS");
    private static final ZoneId UTC = ZoneId.of("UTC");

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType) throws SQLException {
        // UTC时间写入
        LocalDateTime tm;
        try {
            if (parameter.length() == 19) {
                tm = LocalDateTime.parse(parameter, LDT_FORMATTER);
            } else if (parameter.length() == 23) {
                tm = LocalDateTime.parse(parameter, LDT_MILLIS_FORMATTER);
            } else {
                throw new TypeException("Error for parameter #" + i + " with JdbcType " + jdbcType + " . " +
                        "Cause: formatter unsupported, parameter=" + parameter);
            }
            ps.setTimestamp(i, new Timestamp(tm.toInstant(ZoneOffset.UTC).toEpochMilli()));
        } catch (Exception e) {
            throw new TypeException("Error for parameter #" + i + " with JdbcType " + jdbcType + " . " +
                    "Try parse timestamp for this parameter. " +
                    "Cause: " + e, e);
        }
    }

    @Override
    public String getNullableResult(ResultSet rs, String columnName) throws SQLException {
        Timestamp date = null;
        try {
            date = rs.getTimestamp(columnName);
        } catch (Exception e) {
            log.warn("unexpected: getTimestamp from ResultSet error, val={}", rs.getString(columnName), e);
        }
        return date == null ? null : LocalDateTime.ofInstant(date.toInstant(), UTC).format(LDT_FORMATTER);
    }

    @Override
    public String getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        Timestamp date = null;
        try {
            date = rs.getTimestamp(columnIndex);
        } catch (Exception e) {
            log.warn("unexpected: getTimestamp from ResultSet error, val={}", rs.getString(columnIndex), e);
        }
        return date == null ? null : LocalDateTime.ofInstant(date.toInstant(), UTC).format(LDT_FORMATTER);
    }

    @Override
    public String getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        Timestamp date = null;
        try {
            date = cs.getTimestamp(columnIndex);
        } catch (Exception e) {
            log.warn("unexpected: getTimestamp from ResultSet error, val={}", cs.getString(columnIndex), e);
        }
        return date == null ? null : LocalDateTime.ofInstant(date.toInstant(), UTC).format(LDT_FORMATTER);
    }
}

xml配置，xml报错注意MyBatis的标签顺序

  <typeHandlers>
    <typeHandler handler="xx.xx.xx.TimestampToStringTypeHandler"
                 javaType="java.lang.String" jdbcType="TIMESTAMP"/>
  </typeHandlers>