make hibernate entity work with postgres and sqlite for json field

Question

There is a requirement to run the same version of the application on a remote device ( backed with sqlite ) as well as on the cloud ( backed with postgres ). The account entity looks like:

        @Entity
        @Table(name="account")
        public class Account extends BaseEntity{

        @Id
        @Column(name="id")
        private String id;

        @Column(name="valid_from")
        private LocalDateTime validFrom;

        @Column(name="expires_on")
        private LocalDateTime expiresOn;

        @Column(name="events")
        @Convert(converter = EventListConverter.class)
        @NotNull
        @NotEmpty
        private List<Event> events;

The EventListConverter is converting the events to JSON string and back to List<Event> for SQLite. However, the events column is of type jsonb on Postgres - how can we make it work for both ?

Constraints: POSTGRES schema cant be changed ( events in SQLite could be changed to binary though )

Answer 1

I suppose the problem is the generated DDL because you use hbm2ddl update?

I would recommend that you do schema updates through a tool like Liquibase or Flyway as these tools can define DDL per DBMS. If you really must use hbm2ddl, you could subclass the Dialect implementation and provide a custom TableExporter. You need to copy a bit of the StandardTableExporter but it's doable, you can remap the column types to whatever you think is more appropriate.