How to make my Java JSONArray nested and add fields?

Question

Sorry, possible newbie question, I'm trying to learn Java while contributing to a project at work. Actually code is Groovy (& we're using Grails) but assume that's the same for this purpose.

I'm trying to convert a JDBC ResultSet to JSON (to send to the front end). Got the following code from a blog:

// Convert JDBC ResultSet to JSON string
public static JSONArray convertToJSON(ResultSet resultSet)
        throws Exception {
    JSONArray jsonArray = new JSONArray();
    ResultSetMetaData metaData = resultSet.getMetaData(); // Result set meta data
    int total_columns = metaData.getColumnCount(); // Number of columns in the row

    while (resultSet.next()) { // Take each row from the result set
        JSONObject obj = new JSONObject();
        for (int i = 0; i < total_columns; i++) {
            obj.put(metaData.getColumnLabel(i + 1)
                    .toLowerCase(), resultSet.getObject(i + 1));
        }
        jsonArray.put(obj);
    }
    return jsonArray.toString(); // Return as JSON string
}

This (I believe) will give me a JSON structure with the data in the root/top of the JSON. I want to move it into a sub-field (called e.g. 'data') and then have another couple of key/value pairs at root level. How would I modify the code to do this please? (I could pass the couple of extra values in as params)

Thanks.

swinters · Accepted Answer · 2019-02-06 14:19:57Z

1

You can do something like

JSONObject rootObject = new JsonObject();
rootObject.put("data", jsonArray);

This will let you treat your JSON like a key-value pair and should accomplish what you are asking.

Additionally, you may want to look at using an ObjectMapper to convert your object to JSON. This would allow you to create a JSONObject from a Java object with something like objectMapper.writeValueAsString(resultSet)

answered Feb 6, 2019 at 14:19

swinters

3473 gold badges6 silver badges16 bronze badges

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

Comments

Ilya · Accepted Answer · 2019-02-06 14:21:29Z

1

I think there is an error in the code snippet you provided, since your signature states JSONArray as a return type but you return a String, anyway here is what I would suggest:

public static JSONObject convertToJSON(ResultSet resultSet)
        throws Exception {
    JSONObject root = new JSONObject();
    JSONArray jsonArray = new JSONArray();
    root.put("data", jsonArray);
    ResultSetMetaData metaData = resultSet.getMetaData(); // Result set meta data
    int total_columns = metaData.getColumnCount(); // Number of columns in the row

    while (resultSet.next()) { // Take each row from the result set
        JSONObject obj = new JSONObject();
        for (int i = 0; i < total_columns; i++) {
            obj.put(metaData.getColumnLabel(i + 1)
                    .toLowerCase(), resultSet.getObject(i + 1));
        }
        jsonArray.put(obj);
    }
    return root;
}

answered Feb 6, 2019 at 14:21

Ilya

2,16715 silver badges19 bronze badges

2 Comments

Alex Kerr Over a year ago

Great thanks @Ilya, looks good. I didn't realise a JSONObject could return a JSONArray. Presume I can use root.toString() to get the complete JSON string to send to the front end? Thanks

Ilya Over a year ago

Yes, you can use toString to convert the object to json string.

tim_yates · Accepted Answer · 2019-02-06 15:15:44Z

1

As this is a Groovy question, you could make your code more Groovy ;-)

static String convertToJSON(ResultSet resultSet) throws Exception {
    def metaData = resultSet.metaData // Result set meta data
    def result = []
    while (resultSet.next()) { // Take each row from the result set
        result << (1..metaData.columnCount).collectEntries {
            [metaData.getColumnLabel(it).toLowerCase(), resultSet.getObject(it)]
        }
    }
    return new JsonBuilder(result).toString() // Return as JSON string
}

answered Feb 6, 2019 at 15:15

tim_yates

172k29 gold badges359 silver badges354 bronze badges

2 Comments

Alex Kerr Over a year ago

Thanks. What's the (it) reference though? And how would I have the resultset data in a sub section of the JSON and another couple of key/value pairs in the root? Thanks

tim_yates Over a year ago

it is the counter from 1 to the number of columns, and you could do new JsonBuilder([a: 1, b: 'tim', items: result]).toString()

Collectives™ on Stack Overflow

How to make my Java JSONArray nested and add fields?

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related