how to store PostgreSQL jsonb using SpringBoot + JPA?

Question

I'm working on a migration software that will consume unknown data from REST services.

I already think about use MongoDB but I decide to not use it and use PostgreSQL.

After read this I'm trying to implement it in my SpringBoot app using Spring JPA but I don't know to map jsonb in my entity.

Tried this but understood nothing!

Here is where I am:

@Repository
@Transactional
public interface DnitRepository extends JpaRepository<Dnit, Long> {

    @Query(value = "insert into dnit(id,data) VALUES (:id,:data)", nativeQuery = true)
    void insertdata( @Param("id")Integer id,@Param("data") String data );

}

and ...

@RestController
public class TestController {

    @Autowired
    DnitRepository dnitRepository;  

    @RequestMapping(value = "/dnit", method = RequestMethod.GET)
    public String testBig() {
        dnitRepository.insertdata(2, someJsonDataAsString );
    }

}

and the table:

CREATE TABLE public.dnit
(
    id integer NOT NULL,
    data jsonb,
    CONSTRAINT dnit_pkey PRIMARY KEY (id)
)

How can I do this?

Note: I don't want/need an Entity to work on. My JSON will always be String but I need jsonb to query the DB

Carafe

Then why use JPA at all, you aren't using it now (as you are writing native queries already). – Petronella

You're right. But I want to take advantage of the very simple configuration-less of Spring in this case... Creating all connection stuff is boring. Is there some way to get the Session or something to run the query without the @Repository? – Carafe

What has configuration JDBC to do with Spring Data JPA? Where dit you get that idea? Spring Data JPA has nothing to do with configuring a datasource and JdbcTemplate. Both of them are automatically configured even without using Spring Data JPA? – Petronella

Spring Data JPA using SpringBoot just asking me for an Interface and an URL connection in application.properties. If I don't use it I need to configurate all in a old fashion way (Session, DAO, Connection, EntityManager, etc... ) and I don't want to. – Carafe

Again where did you get that impression.. No you don't... Spring Boot wills till configure that, Spring Data JPA isn't a requirement for that!.... And you don't need JPA... You aren't even using it, then why even bother with it. – Petronella

Anyway.... Solved using a database insert function to wrap the jsonb type exposing the function parameter as a text and then inserting as jsonb. Just using the @Query as select insert_data( my_string_json ). Done. – Carafe

@M. Deinum I think is not enough just tell me I don't need to use. Plase tell us how... – Carafe

So instead of making things easier you choose to make it more complex, by "using" a framework but actually bypassing it, all due to a misunderstanding of how the configuration works? – Petronella

Give us some code please? – Carafe

As I stated by simply NOT using it and use a plain JdbcTemplate that is all you need to execute the same SQL statement. – Petronella

Instead of spring-boot-starter-data-jpa add spring-boot-starter-jdbc leave the datasource stuff. Instead of calling the method on your interface (ditch the interface) do jdbcTemplate.executeUpdate("insert into dnit(id, data) VALUES (?,to_json(?))", id, data); – Petronella

Ah! Now I got it! Can you post as an answer? ( paste and edit my controller with your solution will be great ) – Carafe

Still someone looking for solution this might work https://mcmap.net/q/357101/-error-column-is-of-type-json-but-expression-is-of-type-character-varying-in-hibernate – Lawlor

Answer 1

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.

Petronella

Worked like a charm. Many thanks. The Service will be a must have indeed. Wait... "convoluted way" ?? – Carafe

Well convoluted as in trying to shoehorn it into JPA without actually using JPA and using a layer that you don't need. – Petronella

Answer 2

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();

Thuthucydides

That's the problem. As I said at first: I'm working on a migration software that will consume unknown data from REST services so I can't create any entity. If I have entities then there's no need for JSON. The M. Deinum solution is far the best. – Carafe

@MagnoC my answer isn't a solution. It's just help with 'hibernate-types' ;) – Thuthucydides

what would we do if there is no vlad in this world? – Spier

@Thuthucydides What if I just want to map that with JsonObject, not any custom class? – Evanthe

For Hibernate 5.6 follow this: vladmihalcea.com/… – Tallu

this solution is not suitable for restricted artifactory's: "vlad" – Illsorted

Answer 3

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

Chromaticity

Great answer! Saved me tons of time. One thing that didn't work for me as described here was setting the stringtype to unspecified; only way I could get it done is providing explicitly in the connection url, e.g. jdbc:postgresql://localhost:5432/mydb?stringtype=unspecified – Heyde

Thanks Arthur, Adding this parameter to my connection string did the trick – Gratt

What to do if the JSON type object class (MyJsonObject) is a list of MyJsonObject class? – Servant

Answer 4

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.

Allout

Can Any one say me how to retrieve a data matching to a object present in the Jsonb Column – Charmeuse

This is a different question, you may find a similar question or add your own in order to get an answer. You probably need to configure properly the where section of your query. – Allout

Using Hibernate I receive the error column "info" is of type jsonb but expression is of type bytea. Has anyone managed to use this solution with Hibernate? – Subhuman

Answer 5

With Spring Boot 3 and Hibernate 6, you can use: @JdbcTypeCode(SqlTypes.JSON) and @Column(name = "data", columnDefinition = "jsonb"). "data" is the column name in the DB.

@Entity
@Table(name = "projects")
@Data
public class ProjectEntity {

    @Id
    private String id;
    @JdbcTypeCode(SqlTypes.JSON)
    @Column(name = "data", columnDefinition = "jsonb")
    private Project project;
}

more details here, see the comments section -> https://www.baeldung.com/spring-boot-jpa-storing-postgresql-jsonb

Sherrylsherurd

works without columnDefinition, unless perhaps you want auto ddl(which you shouldn't) – Candis

Answer 6

If you're using R2DBC you can use dependency io.r2dbc:r2dbc-postgresql, and use type io.r2dbc.postgresql.codec.Json in your member attributes of an entity class, e.g.:

public class Rule {
    @Id
    private String client_id;
    private String username;
    private String password;
    private Json publish_acl;
    private Json subscribe_acl;
}

Whatever