How to insert a jsonb data type to the database

Support
#1

Hello!
I have a field type JSONB in my Postgres database table.
How could I paste data into this field. When I trying to paste, I get an Error

Internal Exception: org.postgresql.util.PSQLException: ОШИБКА: столбец “personnel” имеет тип jsonb, а выражение - character varying
Подсказка: Перепишите выражение или преобразуйте его тип.
Позиция: 32
Error Code: 0
Call: UPDATE Company SET PERSONNEL = ?, UPDATE_TS = ?, VERSION = ? WHERE ((ID = ?) AND (VERSION = ?))
bind => [{“Key”:“valudse”,“type”:“jsonb”}, 2020-11-26 15:11:02.65, 3, 1e01eea4-5c0c-2873-f6e6-a5c72bb34624, 2]

1 Like
#3

Hi,

In order to use JSONB in CUBA, you’ll need to create custom data converters and specify them in persistence.xml. Also, you’ll need to change an entity’s column definition to store JSONB.

I have created a small example illustrating JSONB usage in PostgreSQL. It stores a person in a DB table and their address as JSON.

Please note that you won’t be able to search entities that are stored in JSON format, but you should be able to edit data.

Key moments:

  1. com.company.jsonb.entity.JsonConverter class that converts JSON to Address entity.
  2. Person's attribute definition:
    @Convert(converter = JsonConverter.class)
    @Column(name = "ADDRESS", columnDefinition = "jsonb")
    private Address address;
  1. View definition in both browser and editor screens. Please note fetch type:
            <view extends="_local">
                <property name="address" fetch="UNDEFINED" view="_minimal"/>
            </view>
  1. Persistence unit definition:
    <persistence-unit name="jsonb-test" transaction-type="RESOURCE_LOCAL">

        <class>com.company.jsonb.entity.JsonConverter</class>
        <class>com.company.jsonb.entity.Person</class>

    </persistence-unit>
  1. PostgreSQL’s driver should be specified as a dependency in global module:
    dependencies {
        if (!JavaVersion.current().isJava8()) {
            runtime('javax.xml.bind:jaxb-api:2.3.1')
            runtime('org.glassfish.jaxb:jaxb-runtime:2.3.1')
        }
        compile(postgres);
    }

I need to mention that this is a quick and dirty unofficial example, CUBA does not support DB-specific types like JSONB directly out-of-the-box, so you’ll need to perform some actions manually.

1 Like
#5

Hi, Andrey! It’s a very nice solution, and I will take it to the work! Thank’s a lot.

Now I have one more question relating this topic.

What if I don’t need to serialize Object. I need to convert a only a String to jsonb and back.
And every time this string could be different (but, of course in a json format)

According your case Person.addres need to be a String type.

Best regards

#6

Yes, you’re right. Just use String instead of Person to serialize it properly.

#7

And how will look the JsonConverter class??

@Converter
public class JsonConverter implements AttributeConverter<String, Object>

It will convert every String field from every entity.

#8

From the @Converter javadoc:

#9

Sorry, I left out the annotation @Convert over the field description.

Thank’s a lot!

1 Like
#10

Andrey, thanks for the “quick and dirty unofficial example” :grinning: It’s very helpfull.
I have a question. Is it possible to do with the list of transient entities stored in json?

    @Convert(converter = JsonToPeriodicValueConverter.class)
    @Column(name = "PERIODIC_VALUES_DATA", columnDefinition = "nvarchar(max)")
    private List<PeriodicValue> periodicValues;

    public void setPeriodicValues(List<PeriodicValue> periodicValues) {
        this.periodicValues = periodicValues;
    }

    public List<PeriodicValue> getPeriodicValues() {
        return periodicValues;
    }
I have an error on startup

Caused by: org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is javax.persistence.PersistenceException: java.lang.ClassCastException: class org.eclipse.persistence.mappings.DirectToFieldMapping cannot be cast to class org.eclipse.persistence.mappings.OneToOneMapping (org.eclipse.persistence.mappings.DirectToFieldMapping and org.eclipse.persistence.mappings.OneToOneMapping are in unnamed module of loader java.net.URLClassLoader @5fdcaa40)

Upd: tried to use @ElementCollection annotation with @Convert.

Have another error

org.springframework.transaction.CannotCreateTransactionException: Could not open JPA EntityManager for transaction; nested exception is javax.persistence.PersistenceException: java.lang.ClassCastException: class org.eclipse.persistence.mappings.DirectCollectionMapping cannot be cast to class org.eclipse.persistence.mappings.OneToOneMapping (org.eclipse.persistence.mappings.DirectCollectionMapping and org.eclipse.persistence.mappings.OneToOneMapping are in unnamed module of loader java.net.URLClassLoader @5fdcaa40)

#11

Unfortunately, it is not an easy task as of today.

I have updated the example, now you can add JSON entities to the Company entity, but you cannot delete them due to a SQL generation statement that does not support JSONB. All addresses are stored in a separate table JSONB_COMPANY_ADDRESSES, but not in the database field.

I could not achieve the desired behavior using only declarative coding. I’m pretty sure it is possible with EclipseLink, but it requires way more effort than I’m ready to spend now. If you need it really bad, you can look for inspiration in this library. Hope this helps.

1 Like