java - Spring R2DBC DatabaseClient 丢失 TZ 信息

Question

我有一个带有一些即时类型字段的数据库实体。org.springframework.data.r2dbc.core.DatabaseClient（现在已弃用），有一个.as(..)自动映射到java实体的方法，它也尊重时区。不知道内部是怎么发生的。

但是使用没有自动映射器的 org.springframework.r2dbc.core.DatabaseClient ，我必须使用.map(...)提供行的 a 并且我像映射它们一样

row.get("blah", Instant.class) 但它只是在我当地的 TZ 中给出时间，而不是 UTC。

有人知道根本原因吗？谢谢。

标签： javaspringspring-data-r2dbcr2dbc

Answer 1

简答

我假设您正在使用 PostgresSQL 数据库和适当的驱动程序。另外，我假设您正在TIMESTAMP为您的文件使用 DB 类型Instant。要从中获取正确的时区，org.springframework.r2dbc.core.DatabaseClient您可以将TIMESTAMP WITH TIME ZONE其用作数据类型。

长答案

我创建了一个测试存储库来测试R2dbcRepositoryR2DBC的行为DatabaseClient以检索timestamp和timestampz进入Instant. 为此，我创建了下表： CREATE TABLE test_table (id SERIAL PRIMARY KEY, timestamp_without_tz TIMESTAMP, timestamp_with_tz TIMESTAMP WITH TIME ZONE);

并实现了以下服务：

package com.patotski.r2dbctimestamps.service;


import com.patotski.r2dbctimestamps.domain.TestEntity;
import com.patotski.r2dbctimestamps.repo.TestEntityRepo;
import lombok.RequiredArgsConstructor;
import org.springframework.r2dbc.core.DatabaseClient;
import org.springframework.stereotype.Service;
import reactor.core.publisher.Mono;

import java.time.Instant;

@Service
@RequiredArgsConstructor
public class TestEntityService {

    private final TestEntityRepo testEntityRepo; // uses standard R2dbcRepository implementation
    private final DatabaseClient databaseClient;

    public Mono<TestEntity> saveUsingRepo(TestEntity entity) {
        return testEntityRepo.save(entity);
    }

    public Mono<TestEntity> getByIdFromRepo(int id) {
        return testEntityRepo.findById(id);
    }

    public Mono<TestEntity> saveUsingDbClient(TestEntity entity) {
        return databaseClient.sql("INSERT INTO test_table (timestamp_without_tz, timestamp_with_tz) VALUES(:timestamp_without_tz, :timestamp_with_tz)")
                .bind("timestamp_without_tz", entity.getTimestamp_without_tz())
                .bind("timestamp_with_tz", entity.getTimestamp_with_tz())
                .map(row -> TestEntity.builder()
                        .id(row.get("id", Integer.class))
                        .timestamp_with_tz(row.get("timestamp_with_tz", Instant.class))
                        .timestamp_without_tz(row.get("timestamp_without_tz", Instant.class))
                        .build()).first();
    }

    public Mono<TestEntity> getByIdFromDbClient(int id) {
        return databaseClient.sql("SELECT * from test_table where id = :id")
                .bind("id", id)
                .map(row -> TestEntity.builder()
                        .id(row.get("id", Integer.class))
                        .timestamp_with_tz(row.get("timestamp_with_tz", Instant.class))
                        .timestamp_without_tz(row.get("timestamp_without_tz", Instant.class))
                        .build()).first();
    }

}

并创建了仅存储实体并检索断言时间戳的测试：

@SpringBootTest
class TestEntityServiceTest {

    @Autowired
    TestEntityService testEntityService;

    @Test
    @DisplayName("Should store and retrieve Entity with both timestamp fields in correct timezones using R2DBC repo.")
    void shouldStoreCorrectTimestampsAndRetriveWithRepo() {
        Instant now = Instant.now();
        TestEntity entity = TestEntity.builder()
                .timestamp_with_tz(now)
                .timestamp_without_tz(now)
                .build();

        TestEntity saved = testEntityService.saveUsingRepo(entity).block();

        Assertions.assertThat(testEntityService.getByIdFromRepo(saved.getId()).block()).isNotNull()
                .extracting(TestEntity::getId,
                        TestEntity::getTimestamp_without_tz,
                        TestEntity::getTimestamp_with_tz)
                .containsExactly(saved.getId(), now, now);
    }

    @Test
    @DisplayName("Should store and retrieve Entity with both timestamp fields in correct timezones using R2DBC DatabaseClient.")
    void shouldStoreCorrectTimestampsAndRetriveWithDbClient() {
        Instant now = Instant.now();
        TestEntity entity = TestEntity.builder()
                .timestamp_with_tz(now)
                .timestamp_without_tz(now)
                .build();

        testEntityService.saveUsingDbClient(entity).block();

        Assertions.assertThat(testEntityService.getByIdFromDbClient(1).block()).isNotNull()
                .extracting(TestEntity::getId,
                        TestEntity::getTimestamp_without_tz,
                        TestEntity::getTimestamp_with_tz)
                .containsExactly(1, now, now);
    }
}

结果表明，这两种方法并不一致：

• R2dbcRepository测试总是在所有 TZ 中通过
• DatabaseClient仅当测试在 UTC TZ 中运行时测试通过，否则失败。原因是TIMESTAMP在 DB 中定义的字段获得了 TZ 偏移量。
• TIMESTAMPZ字段在这两种情况下始终正常工作

回购重现：https ://github.com/xp-vit/r2dbc-timestamps

为 Spring 团队创建和发布至少讨论：https ://github.com/spring-projects/spring-data-r2dbc/issues/608