Spring Data JDBC存储LocalDateTime丢失时分秒精度问题求助

使用Spring Data JDBC操作仓库进行数据存储与查询，需要存储包含时、分、秒的日期时间字段。最初实体中该字段声明为LocalDateTime，担心框架会将其转换为java.sql.Date导致插入时丢失精度，于是换成Timestamp，但测试发现查询出的日期时间时分秒变为00:00，仅保留日期部分。参考同类问题发现他人使用LocalDateTime可保留毫秒精度，现寻求解决方法。

实体类代码 #

@Data
@NoArgsConstructor
@AllArgsConstructor
@Table("AUDIT_LOG")
public class AuditLogEntity {

    @Id
    private Long id;

    @Column("USER_ID")
    private String userId;

    @Column("ACTION_ORIGIN")
    private String actionOrigin;

    @Column("ACTION_NAME")
    private String actionName;

    @Column("ACTION_DATE")
    private Timestamp actionDate;

    @Column("REFERENCE")
    private String reference;

    @Column("ACTION_BODY")
    private String actionBody;

    public static AuditLogEntity create(AuditLogCreation auditLogCreation) {
        return new AuditLogEntity(null,
            auditLogCreation.getUserId(),
            auditLogCreation.getActionOrigin(),
            auditLogCreation.getActionName(),
            Timestamp.valueOf(auditLogCreation.getActionDate()),
            auditLogCreation.getReference(),
            auditLogCreation.getActionBody());

    }

    public static AuditLog toModel(AuditLogEntity entity) {
        return new AuditLog(entity.getUserId(),
            entity.getActionName(),
            entity.getActionOrigin(),
            entity.getActionDate().toLocalDateTime(),
            entity.getReference(),
            entity.getActionBody());
    }
}

仓库实现类代码 #

@Component
public class AuditLogRepositoryImpl implements AuditLogRepository {

    private final JdbcAuditLogRepository jdbcAuditLogRepository;

    public AuditLogRepositoryImpl(JdbcAuditLogRepository jdbcAuditLogRepository) {
        this.jdbcAuditLogRepository = jdbcAuditLogRepository;
    }

    @Override
    public AuditLog create(AuditLogCreation auditLogCreation) {
        AuditLogEntity auditLogEntity = AuditLogEntity.create(auditLogCreation);
        AuditLogEntity save = this.jdbcAuditLogRepository.save(auditLogEntity);
        return AuditLogEntity.toModel(save);
    }

    @Override
    public List<AuditLog> findAll() {
        List<AuditLogEntity> all = this.jdbcAuditLogRepository.findAll();
        return all
            .stream()
            .map(AuditLogEntity::toModel)
            .toList();
    }

}

ListCrudRepository定义 #

@Repository
public interface JdbcAuditLogRepository extends ListCrudRepository<AuditLogEntity, Long> {
}

测试代码 #

@SpringBootTest
@ActiveProfiles("integration")
class AuditLogRepositoryImplIntegrationTest {

    @Inject
    private AuditLogRepository auditLogRepository;

    @Test
    void test() {
        LocalDateTime expected = LocalDateTime.of(LocalDate.of(2020, 4, 30), LocalTime.of(3, 26, 50));
        AuditLogCreation auditLogCreation = AuditLogCreation.builder()
            .userId("UserId")
            .reference("Reference")
            .actionName("Action Name")
            .actionOrigin("Action origin")
            .actionDate(expected)
            .actionBody("Action Body")
            .build();
        this.auditLogRepository.create(auditLogCreation);
        List<AuditLog> all = this.auditLogRepository.findAll();
        AuditLog auditLog = all.get(0);
        assertThat(auditLog.getActionDate()).isEqualTo(expected);
    }

}

测试结果 #

org.opentest4j.AssertionFailedError: 
expected: 2020-04-30T03:26:50 (java.time.LocalDateTime)
 but was: 2020-04-30T00:00 (java.time.LocalDateTime)
when comparing values using 'ChronoLocalDateTime.timeLineOrder()'
Expected :2020-04-30T03:26:50 (java.time.LocalDateTime)
Actual   :2020-04-30T00:00 (java.time.LocalDateTime)

解决步骤 #

1. 检查数据库字段类型
   首要排查数据库中AUDIT_LOG表的ACTION_DATE字段类型，确保是支持时分秒的类型：

   • MySQL使用DATETIME或TIMESTAMP
   • PostgreSQL使用TIMESTAMP或TIMESTAMP WITH TIME ZONE
     若字段类型为DATE，会自动截断时分秒，需修改为对应时间类型。

2. 换回LocalDateTime类型
   Spring Data JDBC原生支持LocalDateTime，无需手动转换为Timestamp，修改实体类代码：

   @Column("ACTION_DATE")
   private LocalDateTime actionDate;
   
   // 创建实体时直接传入LocalDateTime
   public static AuditLogEntity create(AuditLogCreation auditLogCreation) {
       return new AuditLogEntity(null,
           auditLogCreation.getUserId(),
           auditLogCreation.getActionOrigin(),
           auditLogCreation.getActionName(),
           auditLogCreation.getActionDate(),
           auditLogCreation.getReference(),
           auditLogCreation.getActionBody());
   }
   
   // 转换为模型时直接返回LocalDateTime
   public static AuditLog toModel(AuditLogEntity entity) {
       return new AuditLog(entity.getUserId(),
           entity.getActionName(),
           entity.getActionOrigin(),
           entity.getActionDate(),
           entity.getReference(),
           entity.getActionBody());
   }
   

3. 验证依赖版本

   • 确保数据库驱动版本支持Java 8时间API：MySQL驱动用8.0+，PostgreSQL驱动用42.2+
   • 使用Spring Data JDBC 2.4及以上版本，对Java时间类型的兼容性更好

内容的提问来源于stack exchange，提问作者stessy