Treating JSON array as a String to insert into MYSQL

user_10667002 0
Viewed 455

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

mysql json java
1 Answers

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.

user_106670020
Related