How to insert/update a row in MySQL for JSON column using Java SprinBoot CrudRepository

Question

I am trying to insert a row in MySQL JSON column using Java SpringBoot CrudRepository. I already skimmed through multiple posts but couldn't get answer for this.

I defined a custom query as:

@Modifying
@Transactional
@Query(value = "insert into cs_test_json(config) values (':data')", nativeQuery = true)
	public void setData(@Param("data") String string);

and MySQL table(cs_test_json) is :

id int
config JSON

In my Controller I wrote

JSONObject data = new JSONObject();
		data.put("name", "kc");
		data.put("age", 28);
		data.put("city", "Abad");

		repository.setData(data.toString());

When I run this application I always get (on calling setData method) com.mysql.cj.jdbc.exceptions.MysqlDataTruncation: Data truncation: Invalid JSON text: "Invalid value." at position 0 in value for column 'cs_test_json.config'

Please suggest me what I am missing. I am using MySQL 8

Thank you very much.

Karthik R · Accepted Answer · 2019-06-19 12:39:37Z

1

I spotted the ':data'. Can you give a try with :data.? It's a string that you pass and it doesn't need a quotation to further qualify. Spring data takes care of it.

I'm replying from a mobile. Kindly excuse typos and formatting.

answered Jun 19, 2019 at 12:39

Karthik R

5,8162 gold badges23 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to insert/update a row in MySQL for JSON column using Java SprinBoot CrudRepository

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