How to use json datatype in Hibernate using SqlServer

Hibernate ORM
1

Hi,

I am new to hibernate and so far everything seems working fine. I have a json which has 4 values. Out of 4, 3 are normal string values. But in the 4th value there is a complete json data which I need to store as it is in the SqlServer.

@JsonProperty(“DETAIL”)
@Column(name = “DETAIL”)
private String jsonCheck;

Also I am using spring boot for logic purposes.

2

By default, Hibernate does not support JSON. However, you can use this hibernate-types open source project which provides additional types for JSON or ARRAYs.

But this is only useful if you want to use a JSON column type. if you use a VARCHAR or a TEXT column type, then you can just map the JSON as a String, as you already did:

@Column(name = “DETAIL”)
private String jsonCheck;
3

Hi Vlad,

I have checked your posts and blogs in stackoverflow. You have mentioned in your blog that open project is supported for postgreSql & MySql. I wanted to ask you whether the open source project is compatible to SqlServer also.

5

I have tested it and it works like a charm.

Assuming this is your database table:

CREATE TABLE book (
    id BIGINT NOT NULL PRIMARY KEY,
    isbn VARCHAR(15),
    properties NVARCHAR(4000) CHECK(
        ISJSON(properties) = 1
    )
)

you can map it the JPA entity like this:

@Entity(name = "Book")
@Table(name = "book")
@TypeDef(
    name = "json",
    typeClass = JsonStringType.class
)
public class Book {
 
    @Id
    private Long id;
 
    @NaturalId
    @Column(length = 15)
    private String isbn;
 
    @Type(type = "json")
    private String properties;
 
    public Long getId() {
        return id;
    }
 
    public Book setId(Long id) {
        this.id = id;
        return this;
    }
 
    public String getIsbn() {
        return isbn;
    }
 
    public Book setIsbn(String isbn) {
        this.isbn = isbn;
        return this;
    }
 
    public String getProperties() {
        return properties;
    }
 
    public Book setProperties(String properties) {
        this.properties = properties;
        return this;
    }
 
    public JsonNode getJsonNodeProperties() {
        return JacksonUtil.toJsonNode(properties);
    }
}

Check out this article for more details.