How to make convert ResultSet Query to JSON using gson in eclipse?

Question

The code that I have written is:-

import com.google.gson.Gson;
import com.google.gson.JsonObject;

 public class JsonFileCreation{
    public static JsonArray convertToJSON(ResultSet resultSet)
            throws Exception {
        JsonArray jsonArray = new JsonArray();
        while (resultSet.next()) {
            int total_columns = resultSet.getMetaData().getColumnCount();
            JsonObject obj = new JsonObject();
            for (int i = 0; i < total_columns; i++) {
                obj.put(resultSet.getMetaData().getColumnLabel(i + 1).toLowerCase(), resultSet.getObject(i + 1));
            }
          jsonArray.put(obj);
        }
        return jsonArray;
    }
public static void main(String args[]) {
        Gson gson = new Gson(); 
        JsonArray jsonArr = new JsonArray();
        ....
        }

Which is showing error in the line. It is showing put(String,Object) is not defined for the type Json Object.

jsonArray.put(obj);

My ResultSet Query is-

sql = "SELECT * FROM EMPLOYEE";
ResultSet rs = stmt.executeQuery(sql); 

The table looks like this:

Click here to view the table

I am a beginner. Please help me with how to write the code correctly and get the json output in the browser.

Answer 1

The error you are getting is:

put(String,Object) is not defined for the type JsonObject.

And if you have a look at the Gson Javadoc for JsonObject, that message is correct. The JsonObject class has no put method.

Instead, there are some add methods, and it is likely that you will want to use those.

However, there is no add method that takes any type of object and puts it into JSON. You will have to handle the various different types of value yourself. You may get null values, strings, numbers, dates and possibly others too.

I would suggest creating a new method, something like the following, to handle adding a single value to your JSON object obj. It would check the given value among a few different types that it knows of, and use the relevant JsonObject add or addProperty methods to add the values:

    private static void addValueToJSON(JsonObject obj, String propertyName, Object value) throws Exception {
        if (value == null) {
            obj.add(propertyName, JsonNull.INSTANCE);
        } else if (value instanceof Number) {
            obj.addProperty(propertyName, (Number)value);
        } else if (value instanceof String) {
            obj.addProperty(propertyName, (String)value);
        } else if (value instanceof java.sql.Date) {
            // Not clear how you want dates to be represented in JSON.
            // Perhaps use SimpleDateFormat to convert them to a string?
            // I'll leave it up to you to finish this off.
        } else {
           // Some other type of value.  You can of course add handling
           // for extra types of values that you get, but it's worth
           // keeping this line at the bottom to ensure that if you do
           // get a value you are not expecting, you find out about it.
           throw new Exception("Unrecognised type of value: " + value.getClass().getName());
        }
    }

Once you've done this, you would call your new method by replacing the line

                obj.put(resultSet.getMetaData().getColumnLabel(i + 1).toLowerCase(), resultSet.getObject(i + 1));

with

                addValueToJSON(obj, resultSet.getMetaData().getColumnLabel(i + 1).toLowerCase(), resultSet.getObject(i + 1));

Finally, you wrote that your error occurred on the line

jsonArray.put(obj);

I don't believe this is correct, as on this line you are not attempting to call a method on a JsonObject. However, the JsonArray class also has no put method, so there is an error on this line too. The error in this case is much easier to fix: like the JsonObject class, the JsonArray class also has add methods, but you can use the one that takes a JsonElement because you are adding a JsonObject to the array and JsonObject extends JsonElement. The fix this time is just a case of replacing

jsonArray.put(obj);

with

jsonArray.add(obj);