How to use a prepared statement for JSON data types for mysql in java?

Question

I have a JSON column that I want to create a prepared statement for, but I'm not sure how to go about it since the parameters for the statement are inside a literal string.

For example:

PreparedStatement stmt = myConn.prepareStatement("SELECT JSON_SET(new_friends, '$.?', '[?]') from friend_list where id = ?");

for (int i = 0; i < new_friends.size(); i++) {
        String friend = new_friends.get(i);

        // not sure what to put here
        stmt.set....
        stmt.set....
        stmt.set(3, i);
}

Anyone have any suggestions?

I think you should pass a string as an argument to JSON_SET function - after all it's not different from any other function: JSON_SET(new_friends, ?, ?). In this case for the first placeholder you will pass the value of '$.whatever' and for the second one - '[1,2,3,4,5]'. Note that those should be strings. At least give it a try. — Igor Nikolaev
– Igor Nikolaev, Commented Jan 3, 2017 at 7:11
After having a look at MySQL documentation, there is a collection of JSON-related functions available and maybe in you case of adding a new value to the array you could use JSON_ARRAY_APPEND. dev.mysql.com/doc/refman/5.7/en/… — Igor Nikolaev
– Igor Nikolaev, Commented Jan 3, 2017 at 7:35

Michael - sqlbot · Accepted Answer · 2017-01-03 10:45:56Z

4

? placeholders are not used like string interpolation. They replace entire parameters. When the function argument is a string, the entire argument must be the literal string or built with SQL string functions taking parameters as further arguments.

Assuming your syntax is correct for what you want to achieve, consider these:

JSON_SET(new_friends, '$.?', '[?]') -- not valid
JSON_SET(new_friends, ?, ?) -- valid
JSON_SET(new_friends, CONCAT('$.',?), CONCAT('[',?,']')) -- valid

Parameter 1 would be set to the string $.foo for a query in the style of line 2 above, or foo in the style of line 3.

The reason your version isn't valid is exactly the reason you don't/can't use something like WHERE username = '?' but instead would use WHERE username = ? (no quotes around ?).

answered Jan 3, 2017 at 10:45

Michael - sqlbot

180k28 gold badges388 silver badges458 bronze badges

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

1 Comment

Edmond Over a year ago

thanks! I'm going to try this out right now. would this work for numerical values as well?

Atul · Accepted Answer · 2021-04-14 02:21:03Z

If you want to add java list objects into a json array like mentioned below:

{"abc":["var1":"val1","var2":"val2","var3":"val3"]}

If there is already a json value in the column then you can use JSON_SET to update value.

            String query;
            query = "UPADTE tbl1 SET col1 = JSON_SET(col1, '$.abc', CAST(? AS JSON) ) WHERE cdtn = ?"; 
            // if col1 is null then, JSON_SET would be: JSON_SET('{}', '$.abc', CAST(? AS JSON) )
            try (Connection conn = DBUtil.getConnection();
                 PreparedStatement ps = conn.prepareStatement(query)) {
                ps.setString(1, jsonArray.toString());
                ps.setString(2, cd);
                ps.executeUpdate();
            }
            catch (SQLException e) {
                log.error("Error : ", e);
            }

Collectives™ on Stack Overflow

How to use a prepared statement for JSON data types for mysql in java?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related