Improve Timestamp support with postgres [SPR-16719]

[spring-projects-issues]

Arnaud Mergey opened SPR-16719 and commented

I was facing this "seem to be" famous issue by setting a timestamp binding against postgres.

Following test fails with ERROR: could not determine data type of parameter $1

@Test
public void testGetCurrentDate() {

    NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(datasources.get(dbType));

    PlatformTransactionManager manager = new DataSourceTransactionManager(datasources.get(dbType));

    final Timestamp ts = new Timestamp(System
            .currentTimeMillis());

    new TransactionTemplate(manager).execute(new TransactionCallbackWithoutResult() {

        @Override
        protected void doInTransactionWithoutResult(TransactionStatus status) {
            template.update("create table test_timestamp (col timestamp)", Collections.emptyMap());
            template.update("insert into test_timestamp (col) values (:TS) ", Collections.singletonMap("TS", ts));
        }
    });

    Assert.assertEquals((Integer) 1, new TransactionTemplate(manager).execute(new TransactionCallback<Integer>() {

        @Override
        public Integer doInTransaction(TransactionStatus status) {
            MapSqlParameterSource paramsSource = new MapSqlParameterSource();
            paramsSource.addValue("TS", ts, Types.TIMESTAMP, "timestamp");

            return template.queryForObject("select 1 from test_timestamp where :TS is not null and col=:TS ", paramsSource,
                    Integer.class);

        }
    }));

}

I found a discution with postgres that explain why: http://www.postgresql-archive.org/quot-could-not-determine-data-type-of-parameter-quot-with-timestamp-td5995489.html

They give a workaround for that (Setting a PGTimestamp instance instead of Timestamp). I was wondering if it is something that could be implemented inside Spring JDBC to increase portability accross multiple databases. It seems there are already some specific code in StatementCreatorUtil related to similar things.

If you think that it is something that makes sense, I can make a pull request

---

Affects: 5.0.5

Referenced from: pull request #1790

[spring-projects-issues]

Juergen Hoeller commented

Since PGTimestamp is a Postgres-specific class that we'd have to instantiate etc, not just a Postgres-specific convertion for how to use the standard JDBC API, I'm not sure we can easily roll this into StatementCreatorUtils itself.

A pull request highlighting the actual impact of such a change would be useful indeed...

[snicoll]

@jhoeller we do have a PR (#1790) but I am not sure it highlights the impact though.

[jhoeller]

Locally creating PGTimestamp instances out of given Timestamp or Date values seems like a quite specific workaround. The Postgres JDBC driver could easily do so itself behind the regular JDBC API methods, and that's exactly what I suggest as a proper way out of this. I'm therefore closing this issue on Spring's end.