how to store PostgreSQL jsonb using SpringBoot + JPA?

Magno C picture Magno C · Jul 11, 2018 · Viewed 25.6k times · Source

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

Answer

Cepr0 picture Cepr0 · Jul 12, 2018

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