How to persist a large JSON String in SQL Server using Java

Question

I have a very large JSON String that i need to store in the data, by very large means more than 48000 characters, and i am using SQL server to store this data in field of text type, then i tried changing it to nvarchar(max) but still seems no difference.

The problem that is occurring my string gets truncated after 40000 characters when i insert in the DB. i am using hibernate to store the data in that particular column and mapping it as java.lang.String

following is my code that converts from object to jsonobject

public static JSONObject toJSONListWithKey(List<?> object, String key) {
        JSONObject jsonObject = new JSONObject();
        JSONArray jsonArray = new JSONArray();
        Gson gson = new Gson();
        try {
            for (Object object2 : object) {
                jsonArray.put(new JSONObject(gson.toJson(object2)));
        }

            if (null != key || !"".equals(key)) {
                jsonObject.put(key, jsonArray);
            } else {
                jsonObject = new JSONObject(jsonArray.toString());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return jsonObject;
}

and the following is the way i store it in the db

JSONObject jsonObject = JSONUtils.toJSONListWithKey(reports,"reports");

ins.setReportJson(jsonObject.toString());

instanceDAO.update(ins);

Can anyone please suggest/guide me where might things be going wrong and what should i adopt if this approach is not correct?

Answer 1

Actually, you can easily map a JSON column type to a JPA entity using the Hibernate Types open-source project.

Now, assuming you have the following database table:

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

You can map the JSON properties columns in two ways:

• as a Java String
• as a POJO

Mapping the JSON column as a String entity attribute

The most flexible way of mapping the JSON column is to use a String entity attribute that's handled by the JsonType.

@Entity(name = "Book")
@Table(name = "book")
@TypeDef(
    name = "json",
    typeClass = JsonType.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);
    }
}

Mapping the JSON column as a POJO entity attribute

You are not limited to using a String entity attribute. You can use a POJO as well, considering that the POJO properties match the JSON attributes:

This time, the properties entity attribute will be mapped like this:

@Type(type = "json")
private BookProperties properties;

Using a POJO instead of a String-based JSON attribute allows us to simplify the read and write operations on the application side.

Notice how nicely we can build a Book entity instance thanks to the Fluent-style API employed by both the entity and the POJO class:

entityManager.persist(
    new Book()
        .setId(1L)
        .setIsbn("978-9730228236")
        .setProperties(
            new BookProperties()
                .setTitle("High-Performance Java Persistence")
                .setAuthor("Vlad Mihalcea")
                .setPublisher("Amazon")
                .setPrice(44.99D)
        )
);

When persisting the entity above, Hibernate generates the proper SQL INSERT statement:

INSERT INTO book (
    isbn,
    properties,
    id
)
VALUES (
    '978-9730228236',
    '{  
        "title": "High-Performance Java Persistence",  
        "author": "Vlad Mihalcea",  
        "publisher": "Amazon",  
        "price": 44.99
    }',
    1
)

Changing the properties entity attribute is also much simpler when using a POJO:

Book book = entityManager
    .unwrap(Session.class)
    .bySimpleNaturalId(Book.class)
    .load("978-9730228236");
 
book.getProperties().setUrl(
    "https://amzn.com/973022823X"
);

By updating the JSON entity attribute, Hibernate generates the proper SQL UPDATE statement:

UPDATE
    book
SET
    properties = 
    '{  
        "title": "High-Performance Java Persistence",  
        "author": "Vlad Mihalcea",  
        "publisher": "Amazon",  
        "price": 44.99,  
        "url": "https://amzn.com/973022823X"
    }'
WHERE
    id = 1

Answer 2

You may need to modify the ins mapping, so the reportJson column admits a larger string, maybe even change the type to TEXT. If you're using XML files for the mapping configuration, you can specify the column type like this:

<property name="reportJson" >
  <column name="reportJson" sql-type="TEXT"/>
</property>

You might find this other question helpful:

how to set length of an column in hibernate with maximum length