Closed cjacky475 closed 5 months ago
If you would like us to spend some time helping you to diagnose the problem, please spend some time describing it and, ideally, providing a minimal yet complete sample that reproduces the problem. You can share it with us by pushing it to a separate repository on GitHub or by zipping it up and attaching it to this issue.
Sure, I will provide more details:
in my PostgreSQL database I store posts. One of the columns is created_at
which is of type timestamptz
. For this in Spring Boot I have entity class with projection class:
interface PostProjection {
// Other functions
@JsonProperty("createdAt")
@JsonFormat(pattern = "yyyy-MM-dd'T'HH:mm:ss.SSSSSSX")
fun getCreated_at(): OffsetDateTime
}
@Entity
@Table(name = "posts")
data class Post(
// Other variables
@NotBlank
@JsonFormat(pattern = "yyyy-MM-dd'T'HH:mm:ss.SSSSSSX")
val createdAt: OffsetDateTime = OffsetDateTime.now()
)
To get the data from the database I use repository interface:
@Repository
interface PostRepository : JpaRepository<Post, Long> {
@Query(
value = "SELECT p.* " +
"FROM posts AS p " +
"LEFT JOIN users AS u ON p.user_id = u.id " +
"GROUP BY p.id ",
countQuery = "SELECT COUNT(*) " +
"FROM posts AS p " +
"LEFT JOIN users AS u ON p.user_id = u.id " +
"GROUP BY p.id ",
nativeQuery = true
)
fun <T> findPosts(
cursorBasedPageable: CursorBasedPageable<T>,
pageable: Pageable
): Page<Post>
@Query(
value = "SELECT p.*, SUM(u.reporting_score) " +
"FROM posts AS p " +
"LEFT JOIN users AS u ON p.user_id = u.id " +
"GROUP BY p.id ",
countQuery = "SELECT COUNT(*) " +
"FROM posts AS p " +
"LEFT JOIN users AS u ON p.user_id = u.id " +
"GROUP BY p.id ",
nativeQuery = true
)
fun <T> findPostsWithReportingScore(
cursorBasedPageable: CursorBasedPageable<T>,
pageable: Pageable
): Page<PostProjection>
}
When I call findPosts()
function I do receive all information, including created_at
field which in database is stored as shown below (one of the examples):
The Spring JPA generates the following JSON response (one of the examples):
"createdAt": "2024-03-26T16:10:07.688571Z",
, which is correct.
Now when I call findPostsWithReportingScore()
function I receive a warning: Resolved [org.springframework.http.converter.HttpMessageNotWritableException: Could not write JSON: Cannot project java.time.Instant to java.time.OffsetDateTime; Target type is not an interface and no matching Converter found]
.
Returning Instant
is a consequence of Hibernate's TimestampUtcAsJdbcTimestampJdbcType
class that converts Timestamp
values into Instant
.
Spring Data uses tuple-queries to provide data for interface projections hence we have to rely on the types that Hibernate provides to us.
With the source value being Instant
we do not have sufficient information to convert the value into OffsetDateTime
.
Hello,
in PostgreSQL DB I have a
createdAt
variable of typetimestampz
. My@Entity
class has this variable:Now when I call repository without projection it works fine and returns correct OffsetDateTime value. However, returning a projection:
throws:
Resolved [org.springframework.http.converter.HttpMessageNotWritableException: Could not write JSON: Cannot project java.time.Instant to java.time.OffsetDateTime; Target type is not an interface and no matching Converter found]
Why does JPA projection interprets
timestampz
value asInstant
? Is there something I am missing?