如何将org.joda.time.DateTime以字符串写入PostgreSQL JSONB列？

向PostgreSQL的JSONB列写入对象时，lastModified字段（类型为org.joda.time.DateTime）被序列化为包含大量内部属性的复杂结构：

{
  "id": "custom_ 6ef7181d-6afd-4632-b867-523c9d690af4",
  "name": "bla",
  "tenantId": "test_tenant",
  "description": null,
  "lastModified": {
    "era": 1,
    "year": 2022,
    "zone": {
      "id": "Europe/London",
      "fixed": false,
      "uncachedZone": {
        "id": "Europe/London",
        "fixed": false,
        "cachable": true
      }
    },
    "millis": 1668536796965,
    "afterNow": false,
    "equalNow": false,
    "weekyear": 2022,
    "beforeNow": true,
    "dayOfWeek": 2,
    "dayOfYear": 319,
    "hourOfDay": 18,
    "yearOfEra": 2022,
    "chronology": {
      "zone": {
        "id": "Europe/London",
        "fixed": false,
        "uncachedZone": {
          "id": "Europe/London",
          "fixed": false,
          "cachable": true
        }
      }
    },
    "dayOfMonth": 15,
    "millisOfDay": 66396965,
    "minuteOfDay": 1106,
    "monthOfYear": 11,
    "secondOfDay": 66396,
    "centuryOfEra": 20,
    "minuteOfHour": 26,
    "yearOfCentury": 22,
    "millisOfSecond": 965,
    "secondOfMinute": 36,
    "weekOfWeekyear": 46
  },
  "caseSensitive": "SENSITIVE"
}

期望将该字段序列化为"2022-11-15T19:02:30.912Z"这类ISO标准格式字符串。已尝试在DAO中配置ObjectMapper：

private final ObjectMapper objectMapper = new ObjectMapper()
        .disable(SerializationFeature.WRITE_DATES_AS_TIMESTAMPS)
        .setDateFormat(new StdDateFormat().withColonInTimeZone(true));

但配置未生效，相关DAO及AsyncResponse模型代码如下：

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.SerializationFeature;
import com.fasterxml.jackson.databind.util.StdDateFormat;
import com.healthmarketscience.sqlbuilder.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import javax.sql.DataSource;

@Component
public class AsyncResponseDao
{

    private static final String INSERT_ASYNC_RESPONSE_SQL = String.format(
        "INSERT INTO %s (%s,%s,%s,%s) VALUES (?,?,?,? :: jsonb)",
        GrammarDbSpec.asyncResponseTable.table.getName(),
        GrammarDbSpec.asyncResponseTable.tenantId.getName(),
        GrammarDbSpec.asyncResponseTable.id.getName(),
        GrammarDbSpec.asyncResponseTable.statusCode.getName(),
        GrammarDbSpec.asyncResponseTable.body.getName(),
        GrammarDbSpec.asyncResponseTable.tenantId.getName(),
        GrammarDbSpec.asyncResponseTable.id.getName());

    private final ObjectMapper objectMapper = new ObjectMapper()
        .disable(SerializationFeature.WRITE_DATES_AS_TIMESTAMPS)
        .setDateFormat(new StdDateFormat().withColonInTimeZone(true));

    private JdbcTemplate jdbcTemplate;

    @Transactional
    public void put(
        final String tenantId,
        final String id,
        final AsyncResponse asyncResponse)
    {

        // Convert AsyncResponse.body (java.lang.Object) to JSON before we insert it into the postgres JSONB column
        final String asyncResponseBodyJsonString = objectMapper.writeValueAsString(asyncResponse.getBody());
        
        jdbcTemplate.update(
            INSERT_ASYNC_RESPONSE_SQL,
            asyncResponse.getStatusCode(),
            asyncResponseBodyJsonString,
            tenantId,
            id);
    }
}

public class AsyncResponse   {
  @JsonProperty("statusCode")
  public Integer statusCode = null;

  @JsonProperty("body")
  public Object body = null;
}

原因分析 #

你配置的StdDateFormat仅支持JDK原生日期类型（如java.util.Date、java.sql.Timestamp），但Jackson默认没有内置org.joda.time.DateTime的序列化逻辑，所以你的配置对Joda DateTime完全无效，Jackson会将其当作普通Java对象，序列化所有公开属性，导致出现复杂的嵌套结构。

解决方案 #

1. 添加Jackson Joda模块依赖 #

首先引入Jackson专门处理Joda时间类型的模块，Maven依赖如下：

<dependency>
    <groupId>com.fasterxml.jackson.datatype</groupId>
    <artifactId>jackson-datatype-joda</artifactId>
    <version>与项目Jackson版本一致</version>
</dependency>

注意版本必须和项目中其他Jackson依赖保持统一，避免版本冲突。

2. 修改ObjectMapper配置 #

在创建ObjectMapper时注册Joda模块，并配置日期序列化规则：

private final ObjectMapper objectMapper = new ObjectMapper()
        // 注册Joda时间类型处理模块
        .registerModule(new JodaModule())
        // 禁用时间戳格式，强制输出字符串
        .disable(SerializationFeature.WRITE_DATES_AS_TIMESTAMPS)
        // 配置日期格式为带时区的ISO标准格式
        .setDateFormat(new StdDateFormat().withColonInTimeZone(true))
        // 关闭纳秒输出，避免格式冗余
        .configure(SerializationFeature.WRITE_DATE_TIMESTAMPS_AS_NANOSECONDS, false);

3. 自定义序列化格式（可选） #

如果需要更精准的格式控制，可以给JodaModule添加自定义序列化器：

private final ObjectMapper objectMapper = new ObjectMapper();

static {
    JodaModule jodaModule = new JodaModule();
    // 自定义DateTime序列化器，输出UTC时区的ISO 8601格式
    DateTimeFormatter formatter = new DateTimeFormatterBuilder()
            .append(ISODateTimeFormat.dateTime())
            .toFormatter()
            .withZoneUTC();
    jodaModule.addSerializer(DateTime.class, new DateTimeSerializer(formatter));
    
    objectMapper.registerModule(jodaModule)
            .disable(SerializationFeature.WRITE_DATES_AS_TIMESTAMPS);
}

验证效果 #

修改完成后，lastModified字段会被序列化为类似"2022-11-15T18:26:36.965Z"的标准ISO格式字符串，符合预期。

内容的提问来源于stack exchange，提问作者rmf