trying to store complex data in PostgreSQL as JSONB.
Storage works fine except the format of attributes of type java.time.ZonedDateTime.
1 - Given is the entity Route:
@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
…
class
…
@Column(name = "stops", columnDefinition = "jsonb")
@Type(type = "jsonb")
@Valid
private List<Stop> stops;
…
which has an @Embeddable object Stop that holds attribute:
@Column(name = "date_from")
private ZonedDateTime dateFrom;
2 - When the POST REST-endpoint receives:
{…
"dateFrom": "2018-07-28T17:47:50.331+02:00",
…}
Mapping of entire JSON to entity works!
BUT
3 - Then the postgreSQL database stores as date:
{…
"loadingDateFrom": {
"hour": 17,
"nano": 331000000,
"year": 2018,
"zone": {
"id": "+02:00",
"rules": {
"fixedOffset": true,
"transitions": [],
"transitionRules": []
},
"totalSeconds": 7200
},
"month": "JULY",
"minute": 47,
"offset": {
"id": "+02:00",
"rules": {
"fixedOffset": true,
"transitions": [],
"transitionRules": []
},
"totalSeconds": 7200
},
"second": 50,
"dayOfWeek": "SATURDAY",
"dayOfYear": 209,
"chronology": {
"id": "ISO",
"calendarType": "iso8601"
},
"dayOfMonth": 28,
"monthValue": 7
},
…}
Expected would be the same as the endpoint receives (date string):
"2018-07-28T17:47:50.331+02:00"
(other example: if stored as postgreSQL timestamp, like a different attribute in entity Stop, serialisation as date string works fine)
Calling the GET REST-endpoint delivers the complex date-structure, which is bad since date handling in frontend is complicated.
Question:
How can it be achieved that postgreSQL stores the date as date string, not a s complex object?
After a lot of debugging i think it has something to do with the Jackson serialiser. I see the DefaultSerializer is getting a ZonedDateTime object. After serialisation the complex data structure is stored.
Is there a way of configuration to format the date instead writing own serialiser/deserialiser?
I tried:
- different @JsonFormat(…) at date attribute but got always "JSON parse error" for deserialisation. Anyway I don't want do specify a format. This should be out-of-the-box.
Config:
jhipster/spring-boot project
- compile "com.fasterxml.jackson.datatype:jackson-datatype-jsr310"
- compile "com.fasterxml.jackson.core:jackson-databind"
- spring: jackson: serialization.write_dates_as_timestamps: false
Use CustomSerializer:
public class ZonedDateTimeSerialzier extends JsonSerializer<ZonedDateTime>{
@Override
public void serialize(ZonedDateTime value, JsonGenerator gen, SerializerProvider serializers) throws IOException {
String parseDate = null;// parse here zoned date time
gen.writeString(parseDate);
}
}
then add this:
@Column(name = "date_from")
@JsonSerialize(using = ZonedDateTimeSerialzier.class)
private ZonedDateTime dateFrom;
If you want to do it globaly then write this bean in configuration class:
@Bean
public Jackson2ObjectMapperBuilder configureObjectMapper() {
Jackson2ObjectMapperBuilder builder = new Jackson2ObjectMapperBuilder();
SimpleModule zonedDateTimeSerializer = new SimpleModule();
zonedDateTimeSerializer.addSerializer(ZonedDateTime.class,new ZonedDateTimeSerialzier());
builder.modules(zonedDateTimeSerializer);
return builder;
}
Or if you want timestamp add it in application.properties:
spring.jackson.serialization.write-dates-as-timestamps=true
or application.yml:
spring:
jackson:
serialization:
write-dates-as-timestamps: true