How to put JSONB throught Postgres database with JPA

Question

I am trying to build an REST application using Jersey, Gson, JPA and I would like to put a JSON object to JSON or JSONB database field into Postgres.

I tried to do something like this : How to use Postgres JSONB datatype with JPA? but my table see a 'VARCHAR' type instead of 'JSONB'.

My Entity :

@Entity
@Table(name = "votes")

public class Votes {

@Id
@GeneratedValue
private int id;
private int idSocialChoice;

@Convert(converter=JsonConverter.class)
private JsonObject vote;

public Votes() {
}

public JsonObject getVote() {
    return vote;
}

public void setVote(JsonObject vote) {
    this.vote = vote;
}

public int getIdSocialChoice() {
    return idSocialChoice;
}

public void setIdSocialChoice(int idSocialChoice) {
    this.idSocialChoice = idSocialChoice;
}

How i insert my entity :

Votes votes0 = new Votes();
votes0.setIdSocialChoice(3);

JsonObject object = Json.createObjectBuilder().build();
votes0.setVote(object);

List<Votes> listvotes = new ArrayList<Votes>();
listvotes.add(votes0);

ModelEntities.insertVotes(listvotes);

Method insertVotes :

public static void insertVotes(List<Votes> animalList) {

    CrudServiceBean crudServiceBean = new CrudServiceBean(CrudServiceBean.PU_DB);
    crudServiceBean.newTransaction();

    for(Votes votes : animalList)
        crudServiceBean.create(votes);

    crudServiceBean.commit();

    crudServiceBean.closeTransaction();

}

Method create of crudServiceBean.create :

public  <T> T create(T t) {
    this.em.persist(t);
    this.em.flush();
    this.em.refresh(t);
    return t;
}

My JPA provider create my the schema like this (createDDL_ddlGeneration.sql) :

CREATE TABLE votes (ID INTEGER NOT NULL, IDSOCIALCHOICE INTEGER, VOTE VARCHAR(255), PRIMARY KEY (ID))

I would also avoid to use Hibernate.

Thank you.

"it does not work" (TM). I'm sure something must happen, maybe an exception, maybe an SQL in the log, but saying "it does not work" is little use for people here diagnosing your problem — Neil Stockton
@NeilStockton I've add the class JsonConverter to my project but i don't know which annotation i need to put in my Entity attribut value. So for now i have "'Basic' attribut type should not be a map" over the attribut. — Pierre Rol
You follow a JPA AttributeConverter guide such as datanucleus.org/products/accessplatform_5_1/jpa/… to configure it. And if you get some exception then you state in your question the exception+stack trace and which JPA provider — Neil Stockton
@NeilStockton Ok i've added the annotation needed. Know everything work fine except that my database detect a 'BYTEA' instead of 'jsonb' — Pierre Rol
which means what? You created your schema? or your JPA provider created it? in which case POST the schema that was created, and by what process. And then the SQL used — Neil Stockton

Pierre Rol Pierre Rol · Accepted Answer · 2017-05-15T12:32:54

I have finally found the problem. I have forgetten this line on my attribute :

@Column (nullable = true, columnDefinition = "jsonb")

How to put JSONB throught Postgres database with JPA

1 Answers