CODEWITHSUNDEEP
javapostgresqlhibernatejpa
NikMashei
NikMashei

Reputation: 411

Map string properties to JSONB

I've been trying map my string properties to Postgresql's JSONB using JPA. I did read perfect article by Vlad Mihalcea many times and also seen relative questions and problems with similar stuff. BUT I still have this exception org.postgresql.util.PSQLException: ERROR: column "json_property" is of type jsonb but expression is of type character varying every time when I'm trying insert something into my table.

And what even worse is - all these advices in similar questions were useful until I changed my entity class and made him inherits super class. And now situation is like this:

  1. If @TypeDef and @Type on my child class and it works great
  2. But I want use abstraction layer and set annotations, which I noticed above, to my base entity class and after that exception says me 'Hello! It's me again'

My hierarchy is pretty simple and here it is:

Base entity

@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
@MappedSuperclass
public abstract class AbstractServiceEntity implements Serializable {

private Integer id;

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id")
public Integer getId() {
    return id;
}

public void setId(Integer id) {
    this.id = id;
}

Child entity

@Entity
@Table(schema = "ref", name = "test_json_3")
@NoArgsConstructor
@AllArgsConstructor
@Getter
@Setter
public class TestJson extends AbstractServiceEntity {

@Type(type = "jsonb")
@Column(columnDefinition = "jsonb")
private String jsonProperty;

My table

create table ref.test_json_3
(
id serial primary key,
json_property jsonb 
)

UPD I've succesfully inserted record with JPA native query, but I had to unwrap my query into hibernate query. Not sure that it's the most convinient way to manage inserting data into DB. The my question is actual, I still need your help) Example with native query below.

Code snippent with result

@Repository
public class JpaTestRepository {

@PersistenceContext
private EntityManager entityManager;

@Transactional
public void insert(TestJson testJson) {
    entityManager.createNativeQuery("INSERT INTO test_json_3 (json_property) VALUES (?)")
            .unwrap(Query.class)
            .setParameter(1, testJson.getJsonProperty(), JsonBinaryType.INSTANCE)
            .executeUpdate();
}

Upvotes: 4

Views: 7500

Answers (3)

taichouvik
taichouvik

Reputation: 331

Alternate solution for mapping String to Jsonb type. Just add the following annotation on your string.

@ColumnTransformer(write = "?::jsonb")
private String jsonProperty;

Upvotes: 4

RoutesMaps.com
RoutesMaps.com

Reputation: 1690

You can try to add @TypeDefs under class TestJson:

@TypeDefs({
        @TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
})
public class TestJson extends AbstractServiceEntity {

Upvotes: 0

NikMashei
NikMashei

Reputation: 411

Finally I found solution for my problem. Answer is - just use your @Column(columnDefinition = "jsonb") and @Type(type = "jsonb" via getters but not class properties.

entity definition

@Entity
@Table(schema = "ref", name = "test_json_3")
@NoArgsConstructor
@AllArgsConstructor
@Setter
public class TestJson extends AbstractServiceEntity {

private String jsonProperty;

@Type(type = "jsonb")
@Column(columnDefinition = "jsonb")
public String getJsonProperty() {
    return jsonProperty;
}

Upvotes: 2

Related Questions