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

Livesay

Answer 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);
    }

Barrault

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

@HardikThaker Welcome :-) – Barrault

I extended this answer in: https://mcmap.net/q/585404/-general-java-method-that-queries-db-and-returns-results-in-json-format/… which shows the includes and maven dependencies. Feel free to include any of the info in this answer too. – Registered

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

Answer 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 = ?";

}

Sarcenet

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

@Preventive 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 – Sarcenet

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). – Preventive

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 – Sarcenet

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. – Preventive

Answer 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.

Gigantism

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. – Sarcenet

"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. – Gigantism

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. – Sarcenet

Answer 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;
}

Millihenry

Answer 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.

Petrina

Answer 6

It's pretty easy if you want to use Spring:

@RestController
public class MyController

  @Autowired
  private JdbcTemplate jdbcTemplate;

  @RequestMapping("/")
  List<Map<String,Object>> getAll() {
    return jdbcTemplate.queryForList("select * from my_table");
  }
}

In mvc-dispatcher-servlet.xml, you'd setup your JdbcTemplate like this:

<bean class="org.springframework.jdbc.core.JdbcTemplate" id="jdbcTemplate">
  <property name="dataSource">
    ...data source config...
  </property>
</bean>

Jackson should be in your classpath (ie a Maven dependency).

Trigg

Answer 7

Use jsonlib for Java. Iterate over the result set and add the properties you want as JSONObject objects from jsonlib.

Inevitable

Answer 8

Within my applications (MySQL/java servlet/javascript on browser) I use a string function with the fast stringbuilder methods and a generic rs.getObject(). I think it is the most elegant way to do the job:

public  String rStoJason(ResultSet rs) throws SQLException 
{
  if(rs.first() == false) {return "[]";} else {rs.beforeFirst();} // empty rs
  StringBuilder sb=new StringBuilder();
  Object item; String value;
  java.sql.ResultSetMetaData rsmd = rs.getMetaData();
  int numColumns = rsmd.getColumnCount();

  sb.append("[{");
  while (rs.next()) {

    for (int i = 1; i < numColumns + 1; i++) {
        String column_name = rsmd.getColumnName(i);
        item=rs.getObject(i);
        if (item !=null )
           {value = item.toString(); value=value.replace('"', '\'');}
        else 
           {value = "null";}
        sb.append("\"" + column_name+ "\":\"" + value +"\",");

    }                                   //end For = end record

    sb.setCharAt(sb.length()-1, '}');   //replace last comma with curly bracket
    sb.append(",{");
 }                                      // end While = end resultset

 sb.delete(sb.length()-3, sb.length()); //delete last two chars
 sb.append("}]");

 return sb.toString();
}

Sphericity

Answer 9

I found best solution here.

import org.json.JSONArray;
import org.json.JSONObject;
import java.sql.ResultSet;

/**
 * Convert a result set into a JSON Array
 * @param resultSet
 * @return a JSONArray
 * @throws Exception
 */
public static JSONArray convertToJSON(ResultSet resultSet)
        throws Exception {
    JSONArray jsonArray = new JSONArray();
    while (resultSet.next()) {
        int total_rows = resultSet.getMetaData().getColumnCount();
        for (int i = 0; i < total_rows; i++) {
            JSONObject obj = new JSONObject();
            obj.put(resultSet.getMetaData().getColumnLabel(i + 1)
                    .toLowerCase(), resultSet.getObject(i + 1));
            jsonArray.put(obj);
        }
    }
    return jsonArray;
}

Insomuch

Answer 10

If you utilizing the Spring' JDBCTemplate for executing stored functions which returns cursor as list of the tables entries and, in turns, you wish to map it as a list of the specified bean, then there is the most neat solution:

import com.fasterxml.jackson.databind.ObjectMapper;

...

final static ObjectMapper mapper = new ObjectMapper();

...

<T> List<T> populateExecuteRetrieve(SimpleJdbcCall call, Map inputParameters, Class<T> outputClass) {
    List<?> sqlResult;
    sqlResult = call.executeFunction(ArrayList.class, parameter);
    return sqlResult
            .stream()
            .map(entry -> mapper.convertValue(entry, outputBeanClass))
            .collect(Collectors.toList());
}

You are welcome!

Happy coding!

Fessler