Treating JSON array as a String to insert into MYSQL

Question

say I've got a JSON like this: {"name": "tom", "id":1, "clothes":[{"shirt":"yellow"},{"shoes":black},.......]} I'm trying to insert it, as is, into a column in a mysql DB using Java.

void insertVal(JsonObject json){
    Connection conn = null;
    try {
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/Orders?user=root", "root", "1234");
        Statement st = conn.createStatement();
        StringBuilder values = new StringBuilder("(");
        for (String key : json.keySet()){
            if (key.equals("clothes")){
            //do something to deal with this array

                break;
            }
            values.append(json.get(key)).append(",");

        }
        values.append(")");
        String insert = "INSERT INTO ORDERS VALUES " + values;
        st.executeUpdate(insert);
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

}

This code works fine up to clothes key. So far, I've tried using JSON OBJECT and JSON MERGE but the format doesn't quite match, and I'd rather not parse right now. So, is it possible to add clothes or do I have to parse it somehow? if so, what format would work best? Thanks a lot

You may need to clarify your issue. I don't know what you are asking. What string do you want to insert for cloths? — Chris Maggiulli
– Chris Maggiulli, Commented Apr 21, 2021 at 19:42
Sorry for not being clear. Honestly, I don't quite mind - what I thought would be easiest is all content between the two square brackets. — Omri. B
– Omri. B, Commented Apr 21, 2021 at 19:43
Its a string, place it in the query like you would any string, although preparing and bind the parameters would probably help you — RiggsFolly
– RiggsFolly, Commented Apr 21, 2021 at 19:46
That doesn't work - Mysql claims syntax issue, and reading through the web, there is an issue with closed brackets and arrays — Omri. B
– Omri. B, Commented Apr 21, 2021 at 19:46
please post the error message, there should be the insert query — nbk
– nbk, Commented Apr 21, 2021 at 19:59

Omri. B · Accepted Answer · 2021-04-22 00:08:58Z

1

To all those concerned: The problem was the " string. mysql, and probably SQL in general, don't handle these well. There are two options. The first, and less recommended: String parsed = json.get(key).toString().replace("\"", ""); Then, insert normally. The second, much more recommended - use PreparedStatement. This way, you can write an insert/update query without minding the escape characters. Example, after i've inserted 'null' values for the clothes column:

            PreparedStatement ps = conn.prepareStatement("UPDATE ORDERS set CLOTHES = ?  WHERE ID = ?");
            ps.setString(1, clothes); // clothes - get(key).toString()
            ps.setInt(2, count); // count - counts which iteration we are
            ps.executeUpdate();

I hope anyone in the future finds this useful.

answered Apr 22, 2021 at 0:08

Omri. B

4614 silver badges21 bronze badges

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

Collectives™ on Stack Overflow

Treating JSON array as a String to insert into MYSQL

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