2

I wrote for my column

class MyEntity {

    @Column(columnDefinition="DATETIME(6) DEFAULT CURRENT_TIMESTAMP")
    private LocalDateTime createdDate = LocalDateTime.now(ZoneOffset.UTC);

and it works in MySQL but fails in H2 which I use for automated testing.

Is it possible to have default datetime serverside with JPA which is portable?

Dims
  • 47,675
  • 117
  • 331
  • 600
  • I guess that you are letting JPA handle the schema creation/updates. Do you really need the column default value at the SQL level - i.e. will something outside of JPA insert data in that table? JPA can create the date on a `@PrePersist` hook too. – Nikos Paraskevopoulos Apr 13 '21 at 11:27
  • @NikosParaskevopoulos yes I need non-JPA code to write to the table – Dims Apr 13 '21 at 12:22

1 Answers1

1

Avoiding the problem by avoiding the "test database" H2

I would generally recommend to test your code using testcontainers rather than with H2 to avoid any issues purely caused by translation,

Using a proxy to translate your SQL

If for some reason you need multi RDBMS support (possibly not just with H2), you could hook jOOQ's ParsingConnection or DataSource in between when you generate your schema or run your JPA native SQL queries to make sure (most) MySQL specific stuff also works on H2.

I've written a blog post on the jOOQ blog to explain how this could work.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • I am using it, and it is using H2 :) – Dims Apr 15 '21 at 06:37
  • Oh you're using jOOQ's `JPADatabase`? Hah, but you didn't mention that in your question! If that doesn't work, it might be a bug, though I can't reproduce a problem... – Lukas Eder Apr 15 '21 at 08:27
  • No, I am using `Quarkus` and `Quarkus` is using `testcontainers` and it is using `H2`... in the house that Jack built :D – Dims Apr 15 '21 at 09:18