CODEWITHSUNDEEP
jsonspring-bootspring-data-jpa
404or505
404or505

Reputation: 165

Save Raw JSON as string in the database

How can I save Raw Json as String in the MsSql db with the POST request - using Jackson ObjectMapper to convert the string to Json but not able to change raw json into string?

{
     "id": 1,
     "someName":"someName",
     "json": {
         "title": "example glossary",
         "GlossDiv": {
             "title": "S",
             "GlossTerm": "Standard Generalized Markup Language"
         }
},
     "anotherjson":{
          "name":"someone",
          "age": 121
},
     "somedate":"03-11-2019.00:00:00" 
}

How can I save this save json as integer, varchar, string, string, date column in the db? 1,someName, "{"title": "example glossary","GlossDiv": {"title": "S","GlossTerm": "Standard Generalized Markup Language"}","{"name":"someone","age": 121}", 03-11-2019.00:00:00.

** Update ** For simplicity here is the simple json 

{
"id":1,
"jsonObjectHolder":{
        "name": "Name",
        "age" : 404
        }}

Controller:

@PostMapping("/postJson")
public void postJson(@RequestBody TryJson tryJson) {
    tryJsonService.postJson(tryJson);
}

Service:

public void postJson(TryJson tryJson) {
    tryJsonRepository.save(tryJson);
}

Repo:

public interface TryJsonRepository extends CrudRepository<TryJson, Integer> {

}

Model:

@Entity

@Table(name = "TryJson") public class TryJson {

@Id
@Column(name = "id")
private Integer id;

@JsonIgnore
@Column(name = "json_column")
private String jsonColumn;

@Transient
private JsonNode jsonObjectHolder;

public TryJson() {
}

public TryJson(Integer id, String jsonColumn) {
    this.id = id;
    this.jsonColumn = jsonColumn;
}

public Integer getId() {
    return id;
}

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

public JsonNode getJsonObjectHolder() {
    return jsonObjectHolder;
}

public void setJsonObjectHolder(JsonNode jsonObjectHolder) {
    this.jsonObjectHolder = jsonObjectHolder;
}

public String getJsonColumn() {
    return this.jsonObjectHolder.toString();
}

public void setJsonColumn(String jsonColumn) throws IOException {
    ObjectMapper mapper = new ObjectMapper();
    this.jsonObjectHolder = mapper.readTree(jsonColumn);
}

@Override
public String toString() {
    return String.format("TryJson [Id=%s, JsonColumn=%s, jsonObjectHolder=%s]", id, jsonColumn, jsonObjectHolder);
}

}

http://localhost:8080/api/postJson

ID JSON_COLUMN
1 null

Not sure what I am missing here. I do get jsonObjectHolder populated during the debugging but then still I get NULL TryJson [Id=1, JsonColumn=null, jsonObjectHolder={"name":"Name","age":404}]

Update 2

I am getting null pointer exception.

 Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.orm.jpa.JpaSystemException: Exception occurred inside getter of com.example.tryjson.tryjson.model.TryJson.jsonColumn; nested exception is org.hibernate.PropertyAccessException: Exception occurred inside getter of com.example.tryjson.tryjson.model.TryJson.jsonColumn] with root cause 
java.lang.NullPointerException: null
at com.example.tryjson.tryjson.model.TryJson.getJsonColumn(TryJson.java:52) ~[classes/:na]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_192]

Here is my new model

@Entity
@Table(name = "TryJson")
public class TryJson {
private Integer id;

@Transient
private JsonNode jsonObjectHolder;

public TryJson() {
}

public TryJson(Integer id, JsonNode jsonObjectHolder) {
    this.id = id;
    this.jsonObjectHolder = jsonObjectHolder;
}

@Id
@Column(name = "id")
public Integer getId() {
    return id;
}

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

@Transient
public JsonNode getJsonObjectHolder() {
    return jsonObjectHolder;
}

public void setJsonObjectHolder(JsonNode jsonObjectHolder) {
    this.jsonObjectHolder = jsonObjectHolder;
}

@Column(name = "json_column")
public String getJsonColumn() {
    return this.jsonObjectHolder.toString();
}

public void setJsonColumn(String jsonColumn) throws IOException {
    ObjectMapper mapper = new ObjectMapper();
    this.jsonObjectHolder = mapper.readTree(jsonColumn);
}
}

Upvotes: 1

Views: 17240

Answers (2)

reverse
reverse

Reputation: 350

ObjectMapper mapper = new ObjectMapper();
TypeReference<List<User>> typeReference = new TypeReference<List<Your_Entity>>() {};
InputStream inputStream = TypeReference.class.getResourceAsStream("/bootstrap.json");
try {
    List<Your_Entity> users = mapper.readValue(inputStream, typeReference);
    log.info("Saving users...");
    userService.saveAllUsers(users);
    log.info(users.size() + " Users Saved...");
} catch (IOException e) {
    log.error("Unable to save users: " + e.getMessage());
}

Upvotes: 0

Cristian Colorado
Cristian Colorado

Reputation: 2040

You could define a JsonNode json property to hold the part you want to persist as text, then mark it as @Transient so JPA does not try to store it on database. However, jackson should be able to translate it back and forward to Json.

Then you can code getter/setter for JPA, so you translate from JsonNode to String back and forward. You define a getter getJsonString that translate JsonNode json to String. That one can be mapped to a table column, like 'json_string', then you define a setter where you receive the String from JPA and parse it to JsonNode that will be avaialable for jackson.

Do not forget to add @JsonIgnore to getJsonString so Jackson does not try to translate to json as jsonString.

@Entity
@Table(name = "request")
public class Request {

  private Long id;

  private String someName;

  @Transient
  private JsonNode json;

  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  public Long getId() {
    return id;
  }

  @Column(name ="someName")
  public String getSomeName() {
    return name;
  }

  public void setSomeName(String name) {
    this.name = name;
  }

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

  // Getter and setter for name

  @Transient  // This is for Jackson
  public JsonNode getJson() {
    return json;
  }

  public void setJson(JsonNode json) {
    this.json = json;
  }


  @Column(name ="jsonString")
  public String getJsonString() { // This is for JPA
    return this.json.toString();
  }

  public void setJsonString(String jsonString) {  // This is for JPA
    // parse from String to JsonNode object
    ObjectMapper mapper = new ObjectMapper();
    try {
      this.json = mapper.readTree(jsonString);
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
}

UPDATE:

If you mark jsonColumn with @Column spring will use reflection to pull out the data with default initialization null, getJsonColumn translation will never be executed: 

@JsonIgnore
@Column(name = "json_column")
private String jsonColumn;

You do not need a jsonColumn, just make sure you mark your setters with @Column, so spring uses gettets/setters to persist to database, when persisting, jpa will execute getJsonColumn, when reading, jpa will execute setJsonColumn and jsonNode will be translated back and forward to string:

@Entity
@Table(name = "TryJson") public class TryJson {

  private Integer id;

  @Transient
  private JsonNode jsonObjectHolder;

  public TryJson() {
  }

  public TryJson(Integer id, String jsonColumn) {
      this.id = id;
      this.jsonObjectHolder = // use mapper to create the jsonObject;
  }

  @Id
  @Column(name = "id")
  public Integer getId() {
      return id;
  }

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

  public JsonNode getJsonObjectHolder() {
      return jsonObjectHolder;
  }

  public void setJsonObjectHolder(JsonNode jsonObjectHolder) {
      this.jsonObjectHolder = jsonObjectHolder;
  }

  @Column(name = "json_column")
  public String getJsonColumn() {
      return this.jsonObjectHolder.toString();
  }

  public void setJsonColumn(String jsonColumn) throws IOException {
      ObjectMapper mapper = new ObjectMapper();
      this.jsonObjectHolder = mapper.readTree(jsonColumn);
  }

  @Override
  public String toString() {
      return String.format("TryJson [Id=%s, JsonColumn=%s, jsonObjectHolder=%s]", id, jsonColumn, jsonObjectHolder);
  }
}

Upvotes: 3

Related Questions