MySQL Spatial Data – Insert GEOMFROMTEXT (lat lng)

You need to change java code to something like (for simplicity sake error handling and closing statements omitted)

 try {
    conn = ..... 
    conn.setAutoCommit(false);
    Statement s1 = conn.createStatement();
    s1.executeUpdate(/*first insert */);
    s1.executeUpdate(/*second insert */);
     // etc
    conn.commit();
  }

Side notes. 1) it's much better to use PreparedStatement if you executing the same query multiple times with different parameters. 2) make sure your catch block has conn.rollback() - you don't want to leave transaction open

Let's create the table:

mysql> create table jsontest (id serial, value json);
Query OK, 0 rows affected (0.02 sec)

mysql> show create table jsontest\G
*************************** 1. row ***************************
Table: jsontest
Create Table: CREATE TABLE `jsontest` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `value` json DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

And insert some JSON data/objects:

mysql> INSERT INTO jsontest(value) VALUES (JSON_OBJECT('test', 'string with "escaped quotes" does not work'));
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO jsontest(value) VALUES ('{"test": "string with \\"escaped quotes\\" does not work"}');
Query OK, 1 row affected (0.01 sec)

mysql> select id, value->>"$.test" from jsontest;
+----+--------------------------------------------+
| id | value->>"$.test"                           |
+----+--------------------------------------------+
|  2 | string with "escaped quotes" does not work |
|  9 | string with "escaped quotes" does not work |
+----+--------------------------------------------+
2 rows in set (0.00 sec)

As you can see here you have to use the double backslash escape sequence because you don't want the SQL parser to perform the standard escape sequence processing but instead you want to pass the literal string containing the escape sequence down to the storage engine for the JSON data type processing.

I'll talk to the MySQL docs team about adding some examples and explanation for that here:

12.6 The JSON Data Type - Creating JSON Values