java.io.NotSerializableException: org.json.JSONObject

Question

JDBCTemplate and MYSQL JSON

I am writing a rest service where i need to add few details at mysql table.

My mysql table as a column area_json as type json.

I got a pojo object from post call in rest service and i tried to insert it using jdbctemplate like

jdbcTemplate.update("insert into site(area_id,area_json) values(?,?)", area.getareaID(), area.getareaJson());

Once I do a post call using post man i get the below error

Invalid argument value: java.io.NotSerializableException;
nested exception is java.sql.SQLException: Invalid argument value: java.io.NotSerializableException"

Please help

What makes you think passing a JSONObject as parameter, using JdbcTemplate, is supported and stores the underlying JSON document in a MySQL json column? Is this documented somewhere? If not, I would just rely on the MySQL documentation: dev.mysql.com/doc/refman/5.7/en/json.html. And pass a JSON document as a String (not tested, but seems much more likely to succeed, according to the documentation). — JB Nizet
– JB Nizet, Commented Dec 19, 2017 at 20:07

John Bollinger · Accepted Answer · 2017-12-20 13:42:03Z

In the first place, be sure to use a version of Connector/J no older than v5.1.37, where support for the JSON datatype was added, and preferrably no older than v5.1.40, which fixed some JSON-related bugs. But if that's an issue for you, then it is at best secondary.

Before that even matters, JdbcTemplate needs to understand your JSONObject argument. The particular method you are using documents that the variable arguments, including your JSONObject are

arguments to bind to the query (leaving it to the PreparedStatement to guess the corresponding SQL type); may also contain SqlParameterValue objects which indicate not only the argument value but also the SQL type and optionally the scale

When it says the PreparedStatement will guess, it can only mean the two-arg version of PreparedStatement.setObject() which documents:

The JDBC specification specifies a standard mapping from Java Object types to SQL types. The given argument will be converted to the corresponding SQL type before being sent to the database.

Note that this method may be used to pass datatabase- specific abstract data types, by using a driver-specific Java type. If the object is of a class implementing the interface SQLData, the JDBC driver should call the method SQLData.writeSQL to write it to the SQL data stream. If, on the other hand, the object is of a class implementing Ref, Blob, Clob, NClob, Struct, java.net.URL, RowId, SQLXML or Array, the driver should pass it to the database as a value of the corresponding SQL type.

(Emphasis added.)

JDBC does not have built-in support for a JSON datatype. The docs do allow for Connector/J to provide a driver-specific type corresponding to the JSON SQL datatype, but just because MySQL and Connector/J support JSON does not mean you can pull a random JSON-representing object off the shelf and present it to them, nor indeed that they provide or accommodate a JSON-specific Java datatype at all. Inasmuch as MySQL seems not to publish or reference any documentation of JDBC extension APIs in its Connector/J developer guide, I'm inclined to doubt that it recognizes any such type.

It seems, then, that you should rely on the same technique that you would do when programming directly in MySQL's SQL dialect:

In MySQL, JSON values are written as strings. MySQL parses any string used in a context that requires a JSON value, and produces an error if it is not valid as JSON. These contexts include inserting a value into a column that has the JSON data type and passing an argument to a function that expects a JSON value [...].

(MySQL 5.7 Reference Manual, section 11.6)

That is, convert your JSONObject to a JSON-format String, and present it to your template that way. On retrieval, expect to have to do the reverse.

Collectives™ on Stack Overflow

java.io.NotSerializableException: org.json.JSONObject

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related