how to store PostgreSQL jsonb using SpringBoot + JPA?

Tried this but understood nothing!

To fully work with jsonb in Spring Data JPA (Hibernate) project with Vlad Mihalcea's hibernate-types lib you should just do the following:

1) Add this lib to your project:

<dependency>
    <groupId>com.vladmihalcea</groupId>
    <artifactId>hibernate-types-52</artifactId>
    <version>2.2.2</version>
</dependency>

2) Then use its types in your entities, for example:

@Data
@NoArgsConstructor
@Entity
@Table(name = "parents")
@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
public class Parent implements Serializable {

    @Id
    @GeneratedValue(strategy = SEQUENCE)
    private Integer id;

    @Column(length = 32, nullable = false)
    private String name;

    @Type(type = "jsonb")
    @Column(columnDefinition = "jsonb")
    private List<Child> children;

    @Type(type = "jsonb")
    @Column(columnDefinition = "jsonb")
    private Bio bio;

    public Parent(String name, List children, Bio bio) {
        this.name = name;
        this.children = children;
        this.bio = bio;
    }
}

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Child implements Serializable {
    private String name;
}

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Bio implements Serializable {
    private String text;
}

Then you will be able to use, for example, a simple JpaRepository to work with your objects:

public interface ParentRepo extends JpaRepository<Parent, Integer> {
}

parentRepo.save(new Parent(
                     "parent1", 
                     asList(new Child("child1"), new Child("child2")), 
                     new Bio("bio1")
                )
);

Parent result = parentRepo.findById(1);
List<Child> children = result.getChildren();
Bio bio = result.getBio();

For this case, I use the above tailored converter class, you are free to add it in your library. It is working with the EclipseLink JPA Provider.

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;
import org.apache.log4j.Logger;
import org.postgresql.util.PGobject;

import javax.persistence.AttributeConverter;
import javax.persistence.Converter;
import java.io.IOException;
import java.sql.SQLException;
import java.util.Map;

@Converter
public final class PgJsonbToMapConverter implements AttributeConverter<Map<String, ? extends Object>, PGobject> {

    private static final Logger LOGGER = Logger.getLogger(PgJsonbToMapConverter.class);
    private static final ObjectMapper MAPPER = new ObjectMapper();

    @Override
    public PGobject convertToDatabaseColumn(Map<String, ? extends Object> map) {
        PGobject po = new PGobject();
        po.setType("jsonb");

        try {
            po.setValue(map == null ? null : MAPPER.writeValueAsString(map));
        } catch (SQLException | JsonProcessingException ex) {
            LOGGER.error("Cannot convert JsonObject to PGobject.");
            throw new IllegalStateException(ex);
        }
        return po;
    }

    @Override
    public Map<String, ? extends Object> convertToEntityAttribute(PGobject dbData) {
        if (dbData == null || dbData.getValue() == null) {
            return null;
        }
        try {
            return MAPPER.readValue(dbData.getValue(), new TypeReference<Map<String, Object>>() {
            });
        } catch (IOException ex) {
            LOGGER.error("Cannot convert JsonObject to PGobject.");
            return null;
        }
    }

}

Usage example, for an entity named Customer.

@Entity
@Table(schema = "web", name = "customer")
public class Customer implements Serializable {

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

    @Convert(converter = PgJsonbToMapConverter.class)
    private Map<String, String> info;

    public Customer() {
        this.id = null;
        this.info = null;
    }

    // Getters and setter omitted.

There are already several answers and I am pretty sure they work for several cases. I don't wanted to use any more dependencies I don't know, so I look for another solution. The important parts are the AttributeConverter it maps the jsonb from the db to your object and the other way around. So you have to annotate the property of the jsonb column in your entity with @Convert and link your AttributeConverter and add @Column(columnDefinition = "jsonb") as well, so JPA knows what type this is in the DB. This should already make it possible to start the spring boot application. But you will have issues, whenever you try to save() with the JpaRepository. I received the message:

PSQLException: ERROR: column "myColumn" is of type jsonb but expression is of type character varying.

Hint: You will need to rewrite or cast the expression.

This happens because postgres takes the types a little to serious. You can fix this by a change in your conifg:

datasource.hikari.data-source-properties: stringtype=unspecified

datasource.tomcat.connection-properties: stringtype=unspecified

Afterwards it worked for me like a charm, and here is a minimal example. I use JpaRepositories:

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;

@Repository
public interface MyEntityRepository extends JpaRepository<MyEntity, Integer> {
}

The Entity:

import javax.persistence.Column;
import javax.persistence.Convert;

public class MyEntity {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  protected Integer id;

  @Convert(converter = MyConverter.class)
  @Column(columnDefinition = "jsonb")
  private MyJsonObject jsonContent;

}

The model for the json:

public class MyJsonObject {

  protected String name;

  protected int age;

}

The converter, I use Gson here, but you can map it however you like:

import javax.persistence.AttributeConverter;
import javax.persistence.Converter;

@Converter(autoApply = true)
public class MyConverter implements AttributeConverter<MyJsonObject, String> {

  private final static Gson GSON = new Gson();

  @Override
  public String convertToDatabaseColumn(MyJsonObject mjo) {
    return GSON.toJson(mjo);
  }

  @Override
  public MyJsonObject convertToEntityAttribute(String dbData) {
    return GSON.fromJson(dbData, MyJsonObject.class);
  }
}

SQL:

create table my_entity
(
    id serial primary key,
    json_content jsonb

);

And my application.yml (application.properties)

  datasource:
    hikari:
      data-source-properties: stringtype=unspecified
    tomcat:
      connection-properties: stringtype=unspecified

You are making things overly complex by adding Spring Data JPA just to execute a simple insert statement. You aren't using any of the JPA features. Instead do the following

1. Replace spring-boot-starter-data-jpa with spring-boot-starter-jdbc
2. Remove your DnitRepository interface
3. Inject JdbcTemplate where you where injecting DnitRepository
4. Replace dnitRepository.insertdata(2, someJsonDataAsString ); with jdbcTemplate.executeUpdate("insert into dnit(id, data) VALUES (?,to_json(?))", id, data);

You were already using plain SQL (in a very convoluted way), if you need plain SQL (and don't have need for JPA) then just use SQL.

Ofcourse instead of directly injecting the JdbcTemplate into your controller you probably want to hide that logic/complexity in a repository or service.