spring data jdbc store and retrieve LocalDateTime as java.sql.Timestamp instead of java.sql.Date

Question

I use spring-data-jdbc to store and retrieve data from the underlying repository.

I have a date-time field that has to be stored with hour, minutes and seconds.

So in my entity the field is declared as LocalDateTime.

My understanding is that the framework converts my LocalDateTime to a java.sql.Date, so the precision is lost during the insertion in the database.

I use ListCrudRepository<AuditLogEntity,Long> to store and retrieve the records.

After multiple searches on the web, I didn't find any article, tutorial related to my problem.

What I tried is to replace my LocalDateTime by Timestamp.

Here is my entity:

@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());
    }

Here is my repository:

@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();
    }

}

The ListCrudRepository:

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

My test:

@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);
    }

}

And the result:

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)

If I check the following topic: Spring-data-jdbc microsecond precision lost during insert of LocalDateTime type variable It seems he succeeds in storing the date with a precision of the milliseconds whilst on my side only the date without hh:MM:ss is stored.

So I'm a bit lost. Am I missing something?

Thanks for your help.

*So in my entity the field is declared as LocalDateTime.* Wrong. A `LocalDateTime` has both date and time of day so doesn’t work for the time part alone. *My understanding is that the framework converts my LocalDateTime to a java.sql.Date,* That would be wrong. Apart from the fact that they should not use `java.sql.Date` at all, that class was for a date without time of day, so unsuitable for a `LocalDateTime`. — Ole V.V., Mar 17 '23 at 15:27
Maybe there is a misundestanding in my question. I want to store the date and time, thats's the reason why I use LocalDateTime. And my date is like 30-04-2020 03:26:50, so dd-MM-yyyy hh:mm:ss. With JPA it's simple as using the @Temporal annotation. But with spring-data-jdbc I didn't find any type of annotation to tell him to store my date as a timestamp. He keeps storing it by truncating hh:mm:ss which is the default behavior because behind they implicitely convert my LocalDateTime to a java.sql.Date. — stessy, Mar 17 '23 at 16:17
Which brand of database engine are you using under Spring JDBC? — Ole V.V., Mar 17 '23 at 16:21
Yep sorry it was unclear. I use H2 for my integration tests and Oracle as my primary database. — stessy, Mar 17 '23 at 16:38
Edit your Question to state clearly (a) exactly what database engine you use, and (b) *precisely* what is the data type of the column. — Basil Bourque, Mar 17 '23 at 18:27
And delete irrelevant code and prose. Your question appears to be much too long for what I'm guessing is a rather simple issue. — Basil Bourque, Mar 17 '23 at 18:27
Well, finally the problem is between the seat and the keyboard. I use Liquibase to generate my tables and the datatype differs between H2 and Oracle for the Date. With Oracle you can use Date whilst with H2 you have to use Timestamp. — stessy, Mar 21 '23 at 08:25

score 1 · Answer 1 · answered Mar 22 '23 at 14:33

I finally understood what's going on.

In fact I use Liquibase to generate my tables.

And after a small research I found that by using the date type the conversion to the database datatype differs between H2 and Oracle.

With H2 the date type means that only the date is stored. On the other hand with Oracle the date and time are stored.

So the solution is to use two properties in your databaseChangeLog:

For H2: <property name="dateType" value="timestamp" dbms="h2"/>

For Oracle: <property name="dateType" value="date" dbms="oracle"/>

And then use the property in your column definition:

<column name="ACTION_DATE" type="${dateType}"/>

Here is a link where all datatypes are listed: https://stackoverflow.com/a/28626825/1878414

spring data jdbc store and retrieve LocalDateTime as java.sql.Timestamp instead of java.sql.Date

1 Answers1