How do I Insert JSON-formatted string to a H2 database using Hibernate?

Question

I have a PostgresSQL production database, but I'm trying to run some of my automated tests against H2 in-memory. I'm trying to persist JSON formatted data to a table but while I'm able to write the data with no complaints, I get conversion exceptions when I read them back. I have no problem doing this in the production Postgres database.

The object I'm persisting is structured similar to the following:

@Entity
public class Record {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    @Column(columnDefinition = "jsonb")
    @Convert(converter = PersonalInfoConverter.class)
    private PersonalInfo personalInfo;

    public Record() {}

    public Record(PersonalInfo personalInfo) {
        this.personalInfo = personalInfo;
    }

}

The PersonalInfoConverter just uses a Jackson ObjectMapper to de/serialise the object from/to a String (pretty standard stuff with writeValueAsString, and readValue). To get jsonb to work with H2, I used this trick, which basically sets jsonb as an alias for H2's JSON.

I kept running into conversion errors when reading records from the database, until I stumbled upon this question, which linked to a further discussion on Github about inserting JSON formatted strings into H2 tables. It sounds like to be able to get this to work properly, I need to specifically annotate the string inserted into the H2 database. I assumed that, if this were the case, then Hibernate should have handled this properly itself, but it didn't seem like it works out of the box. How do I configure my code to get this to work?

In the meantime, I'm working around this issue by using jsonb as an alias to H2's text type instead:

CREATE TYPE "JSONB" as text;

I've created a project to demonstrate the issue.

@AlbertoSinigaglia the root cause of the exception is with the `ObjectMapper` complaining that there isn't a JSON creator/constructor that takes a string. I'm going to upload a demo project to Github in a moment to demonstrate. — Psycho Punch, Sep 28 '21 at 22:08

score 0 · Answer 1 · answered Oct 14 '21 at 06:05

0

Hibernate does not know about the "JSON" SQL data type and how it needs to be handled. Just use text like you do now, that's totally fine. AFAIU the JSON data type in H2 is just like a domain type with validation i.e. you could replace it with TEXT CHECK is_json(..), so there is not much value in using that particular data type. You could tell hibernate to use @ColumnTransformer to append this FORMAT JSON, but then you'd have issues with PostgreSQL again. Overall, this cross database testing with proprietary features that Hibernate does not abstract over is simply a mess. I would suggest you simply drop H2 and use PostgreSQL with fsync=off for testing which is quite fast already.

answered Oct 14 '21 at 06:05

Christian Beikov

15,141
2
32
58

Thanks @Christian. I would really have preferred using Postgres for testing, but I couldn't find an easy way to run it in-memory. Testcontainers are also out of the question due to some limitations in our build pipeline (and I don't have much control over it). This whole problem could have been easily solved by being able to run containers, but I have to work with what I've got. – Psycho Punch Oct 14 '21 at 10:25
I tried a lot and the best config I was able to come with till now is running on tmpfs, configuring io cost to be very low and turn off fsync. – Christian Beikov Oct 15 '21 at 06:51
Local development is on Windows. Not sure if that's possible. – Psycho Punch Oct 15 '21 at 08:56
I'm running PostgreSQL on docker on a WSL VM and that works great for me. Not sure about the performance when deploying it directly on Windows, but maybe it is good enough? – Christian Beikov Oct 15 '21 at 10:59
I sadly don't have access to WSL either. I do have access to Docker (docker-machine) but it's a different story for the build pipeline. – Psycho Punch Oct 15 '21 at 12:51

How do I Insert JSON-formatted string to a H2 database using Hibernate?

1 Answers1