How to convert a Java resultset into JSON?

Question

I have a resultset as a result of a MySQL query using the JDBC connector. So my job is to convert the resultset into a JSON format. So that I can send it to the clientside as a AJAX response. Can some one explain how to do the conversion to JSON format as I am new to both Java and as well as the concept of JSON

Solution 1

Many people have answered the question correctly. But, I think i can add more value to the post with the following small snippet of code. It uses the Apache-DBUtils and the Gson library.

public static String resultSetToJson(Connection connection, String query) {
        List<Map<String, Object>> listOfMaps = null;
        try {
            QueryRunner queryRunner = new QueryRunner();
            listOfMaps = queryRunner.query(connection, query, new MapListHandler());
        } catch (SQLException se) {
            throw new RuntimeException("Couldn't query the database.", se);
        } finally {
            DbUtils.closeQuietly(connection);
        }
        return new Gson().toJson(listOfMaps);
    }

Solution 2

If you are using JSON I recommend the Jackson JSON library.

http://wiki.fasterxml.com/JacksonHome

The jar files can be found here:

http://wiki.fasterxml.com/JacksonDownload

Here is the generic code I use to convert any result set into a Map<> or List< Map<> > Converting this to JSON using JacksonJSON is pretty straight forward (See Below).

package com.naj.tmoi.entity;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class EntityFactory {

    public EntityFactory(Connection connection, String queryString) {
        this.queryString = queryString;
        this.connection = connection;
    }

    public Map<String, Object> findSingle(Object[] params) throws SQLException {
        List<Map<String, Object>> objects = this.findMultiple(params);

        if (objects.size() != 1) {
            throw new SQLException("Query did not produce one object it produced: " + objects.size() + " objects.");
        }

        Map<String, Object> object = objects.get(0);  //extract only the first item;

        return object;
    }

    public List<Map<String, Object>> findMultiple(Object[] params) throws SQLException {
        ResultSet rs = null;
        PreparedStatement ps = null;
        try {
            ps = this.connection.prepareStatement(this.queryString);
            for (int i = 0; i < params.length; ++i) {
                ps.setObject(1, params[i]);
            }

            rs = ps.executeQuery();
            return getEntitiesFromResultSet(rs);
        } catch (SQLException e) {
            throw (e);
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
        }
    }

    protected List<Map<String, Object>> getEntitiesFromResultSet(ResultSet resultSet) throws SQLException {
        ArrayList<Map<String, Object>> entities = new ArrayList<>();
        while (resultSet.next()) {
            entities.add(getEntityFromResultSet(resultSet));
        }
        return entities;
    }

    protected Map<String, Object> getEntityFromResultSet(ResultSet resultSet) throws SQLException {
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        Map<String, Object> resultsMap = new HashMap<>();
        for (int i = 1; i <= columnCount; ++i) {
            String columnName = metaData.getColumnName(i).toLowerCase();
            Object object = resultSet.getObject(i);
            resultsMap.put(columnName, object);
        }
        return resultsMap;
    }
    private final String queryString;
    protected Connection connection;
}

In the servlet I convert the List into JSON using the com.fasterxml.jackson.databind.ObjectMapper which converts Java Generics into a JSON String.

    Connection connection = null;
    try {
        connection = DataSourceSingleton.getConnection();
        EntityFactory nutrientEntityFactory = new EntityFactory(connection, NUTRIENT_QUERY_STRING);
        List<Map<String, Object>> nutrients = nutrientEntityFactory.findMultiple(new Object[]{});

        ObjectMapper mapper = new ObjectMapper();

        String json = mapper.writeValueAsString(nutrients);


        response.setContentType("application/json;charset=UTF-8");
        response.getWriter().write(json);
    } catch (SQLException e) {
        throw new ServletException(e);
    } finally {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                throw new ServletException(e);
            }
        }
    }

You can pass in Parameters to the PreparedStatement like this:

String name = request.getHeader("name");
EntityFactory entityFactory = new EntityFactory(DataSourceSingleton.getConnection(), QUERY_STRING);
Map<String, Object> object = entityFactory.findSingle(new String[]{name});


private static final String QUERY_STRING = "SELECT NAME, PASSWORD, TOKEN, TOKEN_EXPIRATION FROM USER WHERE NAME = ?";

}

Solution 3

I have used Google GSON library, its one tiny gson-2.2.4.jar 190KB library in a mywebapp/WEB-INF/lib folder. http://code.google.com/p/google-gson/

import com.google.gson.stream.JsonWriter;
---
httpres.setContentType("application/json; charset=UTF-8");
httpres.setCharacterEncoding("UTF-8");
JsonWriter writer = new JsonWriter(new OutputStreamWriter(httpres.getOutputStream(), "UTF-8"));
while(rs.next()) {
   writer.beginObject();
   // loop rs.getResultSetMetadata columns
   for(int idx=1; idx<=rsmd.getColumnCount(); idx++) {
     writer.name(rsmd.getColumnLabel(idx)); // write key:value pairs
     writer.value(rs.getString(idx));
   }
   writer.endObject();
}
writer.close();
httpres.getOutputStream().flush();

If you want typed JSON key:value pairs there is a writer.value(String,long,integer,etc..) setters. Do switch-case within foreach rsmd loop and use appropriate setter for numbered sql types. Default could use writer.value(rs.getString(idx)) setter.

Using JsonWriter allows writing large json replys CPU+RAM effectively. You dont need to loop sqlresultset first and create massive List in RAM. Then loop list again while writing json document. This example flows as it goes, http reply is being chunked while remaining data is still written to servlet output.

Its relatively easy to create higher-level wrapper utils around GSON+Sql resultset. jsp page could use SqlIterator(sqlquery) methods (.next(), getColumnCount(), getType(idx), .getString(idx), .getLong(idx) ...) while writing http reply. It loops the original sql without intermediate List. This does not matter for smaller apps but heavy-use apps must consider cpu+ram usage patterns more closely. Or even better do SqlToJson(httpresponse, sqlrs) helper then jsp or servlet code noice is minimal.

Solution 4

You can use any JSON library.

The following is an implementation of this, return a list, with each element a JSON Object:

/*
 * Convert ResultSet to a common JSON Object array
 * Result is like: [{"ID":"1","NAME":"Tom","AGE":"24"}, {"ID":"2","NAME":"Bob","AGE":"26"}, ...]
 */
public static List<JSONObject> getFormattedResult(ResultSet rs) {
    List<JSONObject> resList = new ArrayList<JSONObject>();
    try {
        // get column names
        ResultSetMetaData rsMeta = rs.getMetaData();
        int columnCnt = rsMeta.getColumnCount();
        List<String> columnNames = new ArrayList<String>();
        for(int i=1;i<=columnCnt;i++) {
            columnNames.add(rsMeta.getColumnName(i).toUpperCase());
        }

        while(rs.next()) { // convert each object to an human readable JSON object
            JSONObject obj = new JSONObject();
            for(int i=1;i<=columnCnt;i++) {
                String key = columnNames.get(i - 1);
                String value = rs.getString(i);
                obj.put(key, value);
            }
            resList.add(obj);
        }
    } catch(Exception e) {
        e.printStackTrace();
    } finally {
        try {
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    return resList;
}

Solution 5

• Convert resultset into List<Map<String, Object>> (each map contains a row with column names as keys and column content as value, List is a list of such rows)
• Use Gson or Jackson library to covert this object into JSON.

View more solutions

Comments

[Nathaniel Johnson]

You raise a good point about large result sets. Both libraries implement Streaming. I prefer to take a result set and convert it to a collection because it is easier to debug and, generally speaking, no result set being parsed into JSON should be very large, I also often have more complex objects that require additional data from multiple queries or other sources. One final point is that this method allows you to change a table and the only piece of code that needs to change is the query string.

[Whome]

"generally speaking, no result set being parsed into JSON should be very large". That is true, massive data structure between client<->server should think about pagination of some sort. But then someone raises a question about concurrent clients, having all the time +3000 active clients each reading 100 rows per JSON creates a decent jvm noice.

[Nathaniel Johnson]

I thought about this some more... Your are correct. The memory problem is most definitely an issue. I was thinking about my own design and perhaps using a proxy with each query element having a sendToStream method.

[cYn]

This piece of code saved me about 2 days of work. Thanks so much man.

[Nathaniel Johnson]

@cYn I haven't used this new lib (JSR-353) yet but I would recommend looking at this implementation as it is now a standard. The switch from Jackson would be simple and you are then on the JSR standard. jsonp.java.net

[cYn]

Thanks for that bit of info. But using resultSets as JSON is very minimalistic, isn't it? Since the objects will never be deep, it probably wouldn't matter much? ResultSet objects, to me, seems to be just a key (column) and a value (row).

[Nathaniel Johnson]

Yes, the only benefit of using JSR-353 is that it will be part of the standard JRE rather than needing an external dependency. You might find the Apache DBUtils class MapListHandler very useful. It does the same thing as this code (minus the json part) and has the advantage of coming with a bunch of other useful things. commons.apache.org/proper/commons-dbutils/apidocs/index.html

[cYn]

Hi Nate, I recently ran into a problem with this implementation. Not sure if you caught this but using Map returns an unsorted ordering of the columns. This behavior is unwanted if you already set the order of the columns that you wanted in the SQL call. To fix this I just changed all Map<String, Object> to LinkedHashMap<String, Object>. Thank you again for this code as it has been extremely useful for me.

[Hardik Thaker]

Worked Like A Charm :) Thank you buddy ! Apache Rocks

[phoenix]

@HardikThaker Welcome :-)

[James Oravec]

I extended this answer in: stackoverflow.com/questions/35134337/java-query-into-json/… which shows the includes and maven dependencies. Feel free to include any of the info in this answer too.

[Sharhabeel Hamdan]

Nicely working example, a great couple of tools (Apache-DBUtils and the Gson).