How to upgrade database with newer JPA timestamp fields?

Question

I have a Spring Boot application running using JPA and Hibernate to automagically manage my entities. When I created this application, I used an older version of JPA that didn't have support for Java 8 DateTime API. However, without a lot of knowledge about JPA, I used LocalDateTime in my entities and it worked! Not having to know about the underlying database structure was great!

Until now...

I am upgrading JPA to a version that does support LocalDateTime, and I am facing an error with the way JPA is using this field. It used to save this object as a VARBINARY (tinyblob) field in my MySQL database, but now it is smart and expects it to be a TIMESTAMP type. Which means that when I start my application using the configuration spring.jpa.hibernate.ddl-auto=validate I get the error:

...
Caused by: org.hibernate.tool.schema.spi.SchemaManagementException: 
    Schema-validation: wrong column type encountered in column [answer_time] in table [user_answer]; 
    found [tinyblob (Types#VARBINARY)], but expecting [datetime (Types#TIMESTAMP)]

So now I am kinda lost on how to convert these fields to their new timestamp types. I was thinking about using FlyWay to write a migration script, but I have no idea how JPA stored the object as blob. When print a VARBINARY field as string this is what it looks like:

¬í sr java.time.Ser]º"H² xpw ã !;:;Ö@x

This is how my entity looks like (which was unchanged during the upgrade):

@Entity
@Table(name = "user_answer")
public class UserAnswer {
    private Long id;
    private LocalDateTime answerTime;

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public LocalDateTime getAnswerTime() {
        return answerTime;
    }

    public void setAnswerTime(LocalDateTime answerTime) {
        this.answerTime = answerTime;
    }
}

How can I update my database so it converts the old VARBINARY fields that it used to store LocalDateTime data to TIMESTAMP fields?

Answer 1

What I would try (after backing up the DB!) :

• Keep the old JPA API + implementation (Hibernate) versions.
• Keep the old LocalDateTime field.
• Add another java.sql.Date field to your entity. Make sure to annotate it properly etc. so that Hibernate knows exactly how the column should be defined.
• For all entities:
  • Load each entity, read the LocalDateTime, convert and store it to the DateTime field, merge().
• Remove the DateTime field.
• Remove the column for the old LocalDateTime from the table.
• Change the type of the DateTime field to LocalDateTime.
• Upgrade the JPA API + implementation (Hibernate) versions.

---

• JPA impl (Hibernate?) should store the DateTime as TIMESTAMP.
• The JDBC driver should be able to pick up the TIMESTAMP into LocalDateTime.

Also, consider ZonedDateTime rather than LocalDateTime.