CODEWITHSUNDEEP
javaspringtype-conversionspring-dataspring-data-jdbc
stoetti
stoetti

Reputation: 103

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

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?

Upvotes: 4

Views: 6211

Answers (2)

acker9
acker9

Reputation: 503

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.

Upvotes: 4

Jens Schauder
Jens Schauder

Reputation: 81988

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.

Upvotes: 1

Related Questions