Skip to content

conversion to class java.time.Instant from timestamp not supported #5

@zartc

Description

@zartc

Having a DTO with a java.util.Instant property:

public record MyDTO(Integer id, java.util.Instant createdTime, String createdBy) { }

mapped to a database table with a TIMESTAMP WITHOUT TIME ZONE column:

create table "my_table" (
    "id"                   int4 primary key generated always as identity,
    "created_time"         TIMESTAMP WITHOUT TIME ZONE     not null default current_timestamp,
    "created_by"           text          not null
);

then when performing:

var result = jdbcTemplate.query("""
        select * from "my_table"
    """, new DataClassRowMapper<MyDTO>(MyDTO.class));

it throws the following exception:

ERROR log4jdbc.log4j2      : 1. ResultSet.getObject(2,class java.time.Instant)
org.postgresql.util.PSQLException: conversion to class java.time.Instant from timestamp not supported
	at org.postgresql.jdbc.PgResultSet.getObject(PgResultSet.java:3754) ~[postgresql-42.3.3.jar:42.3.3]
	at net.disy.oss.log4jdbc.sql.jdbcapi.ResultSetSpy.getObject(ResultSetSpy.java:2393) ~[log4jdbc-2.2.jar:?]
	at com.zaxxer.hikari.pool.HikariProxyResultSet.getObject(HikariProxyResultSet.java) ~[HikariCP-4.0.3.jar:?]
	at org.springframework.jdbc.support.JdbcUtils.getResultSetValue(JdbcUtils.java:229) ~[spring-jdbc-5.3.16.jar:5.3.16]
	at org.springframework.jdbc.core.BeanPropertyRowMapper.getColumnValue(BeanPropertyRowMapper.java:421) ~[spring-jdbc-5.3.16.jar:5.3.16]
	at org.springframework.jdbc.core.DataClassRowMapper.constructMappedInstance(DataClassRowMapper.java:103) ~[spring-jdbc-5.3.16.jar:5.3.16]
	at org.springframework.jdbc.core.BeanPropertyRowMapper.mapRow(BeanPropertyRowMapper.java:300) ~[spring-jdbc-5.3.16.jar:5.3.16]
	at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:94) ~[spring-jdbc-5.3.16.jar:5.3.16]
	at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:61) ~[spring-jdbc-5.3.16.jar:5.3.16]
	at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:453) ~[spring-jdbc-5.3.16.jar:5.3.16]
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:381) ~[spring-jdbc-5.3.16.jar:5.3.16]
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:465) ~[spring-jdbc-5.3.16.jar:5.3.16]
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:475) ~[spring-jdbc-5.3.16.jar:5.3.16]

Changing the database column to TIMESTAMP WITH TIME ZONE gives a similar exception:

ERROR log4jdbc.log4j2      : 1. ResultSet.getObject(2,class java.time.Instant)
org.postgresql.util.PSQLException: conversion to class java.time.Instant from timestamptz not supported
	at org.postgresql.jdbc.PgResultSet.getObject(PgResultSet.java:3754) ~[postgresql-42.3.3.jar:42.3.3]
	at net.disy.oss.log4jdbc.sql.jdbcapi.ResultSetSpy.getObject(ResultSetSpy.java:2393) ~[log4jdbc-2.2.jar:?]
	at com.zaxxer.hikari.pool.HikariProxyResultSet.getObject(HikariProxyResultSet.java) ~[HikariCP-4.0.3.jar:?]
	at org.springframework.jdbc.support.JdbcUtils.getResultSetValue(JdbcUtils.java:229) ~[spring-jdbc-5.3.16.jar:5.3.16]
	at org.springframework.jdbc.core.BeanPropertyRowMapper.getColumnValue(BeanPropertyRowMapper.java:421) ~[spring-jdbc-5.3.16.jar:5.3.16]
	at org.springframework.jdbc.core.DataClassRowMapper.constructMappedInstance(DataClassRowMapper.java:103) ~[spring-jdbc-5.3.16.jar:5.3.16]
	at org.springframework.jdbc.core.BeanPropertyRowMapper.mapRow(BeanPropertyRowMapper.java:300) ~[spring-jdbc-5.3.16.jar:5.3.16]
	at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:94) ~[spring-jdbc-5.3.16.jar:5.3.16]
	at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:61) ~[spring-jdbc-5.3.16.jar:5.3.16]
	at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:453) ~[spring-jdbc-5.3.16.jar:5.3.16]
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:381) ~[spring-jdbc-5.3.16.jar:5.3.16]
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:465) ~[spring-jdbc-5.3.16.jar:5.3.16]
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:475) ~[spring-jdbc-5.3.16.jar:5.3.16]

Then changing the DTO property to java.time.LocalDateTime gives the exception:
Note the bizarre <<... to requested type timestamp>> which probably refer to the old java.sql.timestamp

ERROR log4jdbc.log4j2    : 1. ResultSet.getObject(2,class java.time.LocalDateTime)
org.postgresql.util.PSQLException: Cannot convert the column of type TIMESTAMPTZ to requested type timestamp.
	at org.postgresql.jdbc.PgResultSet.getLocalDateTime(PgResultSet.java:725) ~[postgresql-42.3.3.jar:42.3.3]
	at org.postgresql.jdbc.PgResultSet.getObject(PgResultSet.java:3731) ~[postgresql-42.3.3.jar:42.3.3]
	at net.disy.oss.log4jdbc.sql.jdbcapi.ResultSetSpy.getObject(ResultSetSpy.java:2393) ~[log4jdbc-2.2.jar:?]
	at com.zaxxer.hikari.pool.HikariProxyResultSet.getObject(HikariProxyResultSet.java) ~[HikariCP-4.0.3.jar:?]
	at org.springframework.jdbc.support.JdbcUtils.getResultSetValue(JdbcUtils.java:229) ~[spring-jdbc-5.3.16.jar:5.3.16]
	at org.springframework.jdbc.core.BeanPropertyRowMapper.getColumnValue(BeanPropertyRowMapper.java:421) ~[spring-jdbc-5.3.16.jar:5.3.16]
	at org.springframework.jdbc.core.DataClassRowMapper.constructMappedInstance(DataClassRowMapper.java:103) ~[spring-jdbc-5.3.16.jar:5.3.16]
	at org.springframework.jdbc.core.BeanPropertyRowMapper.mapRow(BeanPropertyRowMapper.java:300) ~[spring-jdbc-5.3.16.jar:5.3.16]
	at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:94) ~[spring-jdbc-5.3.16.jar:5.3.16]
	at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:61) ~[spring-jdbc-5.3.16.jar:5.3.16]
	at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:453) ~[spring-jdbc-5.3.16.jar:5.3.16]
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:381) ~[spring-jdbc-5.3.16.jar:5.3.16]
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:465) ~[spring-jdbc-5.3.16.jar:5.3.16]
	at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:475) ~[spring-jdbc-5.3.16.jar:5.3.16]

Only the combination of the DTO property being java.time.LocalDateTime and the database column being TIMESTAMP WITHOUT TIME ZONE complete without throwing an exception.

What is interesting is that when removing log4jdbc: from the spring.datasource.url property, thus returning to the naked postgresql driver, then all combinations work without throwing any exception.

This leads me to thing that there is a problem in net.disy.oss.log4jdbc.sql.jdbcapi.DriverSpy.
Does the net.disy.oss.log4jdbc.sql.jdbcapi.DriverSpy recognize/use the new java.time.* types available since JDBC-4.2 ?

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type
    No fields configured for issues without a type.

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions