Using query parameter of type not supported in Spring Data JDBC?

Question

I tried to write a query method in my repository similar to this

@Modifying
@Query("UPDATE foo SET some_timestamp = :someTimestamp WHERE id = :id")
void updateSomeTimestamp(@Param("id") long id, @Param("someTimestamp") Instant someTimestamp)

When executing the code I got the following error:

org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of java.time.Instant. Use setObject() with an explicit Types value to specify the type to use.

Did I forget something or is usage of Instant as parameter simply not supported by Spring Data Jdbc? If so, is such a support planned in the future?

turbanoff · Accepted Answer · 2024-10-17 16:19:44Z

For those who came here looking for a solution to generate java.sql.Timestamp instances with better than millisecond precision (the Timestamp constructor that accepts nanos is deprecated):

Timestamp timestamp = Timestamp.from(Instant.now());

This assumes your operating system supports better than millisecond precision for the system clock. It's useful for databases that support better-than-millisecond TIMESTAMP precision, such as with PostgreSQL, which supports microsecond precision.

You can convert it back to an Instant or LocalDateTime:

System.out.println(timestamp.toInstant()); // UTC

or

System.out.println(timestamp.toLocalDateTime()); // local time

I will also reiterate information stoetti provided in a comment, that according to the documentation link that stoetti provided, https://jdbc.postgresql.org/documentation/query/#using-java-8-date-and-time-classes, the PostgreSQL JDBC driver does recognize the following java.time classes as timestamps:

java.time.LocalTime as TIME [ WITHOUT TIMEZONE ]

java.time.LocalDateTime as TIMESTAMP [ WITHOUT TIMEZONE ]

java.time.OffsetDateTime as TIMESTAMP WITH TIMEZONE

But not java.time.Instant, as stoetti pointed out.

Jens Schauder · Accepted Answer · 2019-12-19 06:25:08Z

1

Spring Data JDBC has no knowledge of the type expected by the SQL-statement. Therefore it passes parameters a long without any conversion and relies on the database to properly handle the parameter.

One might argue that it should convert parameters if a matching converter is registered, but this is currently not the case.

Therefore currently the solution is to convert the argument yourself and changing the argument type to java.util.Date or whatever your JDBC driver is accepting.

answered Dec 19, 2019 at 6:25

Jens Schauder

82.6k35 gold badges199 silver badges379 bronze badges

4 Comments

stoetti Over a year ago

I understand your point but from my opinion java.time.* is a set of classes that should be supported out of the box.

Jens Schauder Over a year ago

I kind of agree and would make that case against the JDBC driver. The question is: Should Spring Data JDBC try to convert parameters?

stoetti Over a year ago

According to the documentation (at least for postgresql-drive which we use) some classes from java.time are supported, Instant being not included. => jdbc.postgresql.org/documentation/head/8-date-time.html In my opinion converting parameters from java.time.* to some jdbc-compatible type would be a nice feature I guess more users of Spring Data JDBC would benefit from.

Jens Schauder Over a year ago

I created jira.spring.io/browse/DATAJDBC-464 for this.

Collectives™ on Stack Overflow

Using query parameter of type not supported in Spring Data JDBC?

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related