How to convert a ResultSet object to a JSON format output

Question

I have got a ResultSet after firing a query. Kindly let me know how i can convert it to a JSON output in JSP.

In the second stage, lets assume that we have got a JSON output like in this link > http://inknpost.com/eshopping/json.jsp

The above file is being accessed by a $.getJSON(); in another file.

Kindly let me know how can i display the "names" and the "departments" in different rows in a page.

Answer 1

Create a reuseable Javabean class which represents one row, a single entity.

public class Category {
    private Long id;
    private String name;
    private String department;

    // Add/generate getters/setters/c'tors/equals/hashcode and other boilerplate.
}

Create a reuseable DAO class which maps the ResultSet to a collection of those Javabeans the usual JDBC way.

public class CategoryDAO {
    private static final String SQL_LIST = "SELECT id, name, department FROM category";
    // ...

    public List<Category> list() throws SQLException {
        List<Category> categories = new ArrayList<Category>();

        try (
            Connection connection = database.getConnection();
            PreparedStatement statement = connection.prepareStatement(SQL_LIST);
            ResultSet resultSet = statement.executeQuery();
        ) {
            while (resultSet.next()) {
                Category category = new Category();
                category.setId(resultSet.getLong("id"));
                category.setName(resultSet.getString("name"));
                category.setDepartment(resultSet.getString("department"));
                categories.add(category);
            }
        }

        return categories;
    }

    // ...
}

Create a servlet class which uses a JSON serializer/deserializer which is able to convert between an arbirary collection of Javabeans and a JSON String, such as Google Gson.

@WebServlet("/categories.json")
public class CategoriesJsonServlet extends HttpServlet {

    @Override
    public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        try {
            List<Category> categories = categoryDAO.list();
            String categoriesJson = new Gson().toJson(categories);
            response.setContentType("application/json");
            response.setCharacterEncoding("UTF-8");
            response.getWriter().write(categoriesJson);
        } catch (SQLException e) {
            throw new ServletException("DB error", e);
        }
    }

}

Invoke it by http://localhost:8080/contextname/categories.json. No, there is no JSP involved. You should not be using JSP for output formats other than HTML.

Finally, in jQuery, just access it the usual $.getJSON() way.

$('#somebutton').click(function() {
    $.getJSON('categories.json', function(categoriesJson) {
        var $table = $('<table>').appendTo($('#somediv'));
        $.each(categoriesJson, function(index, category) {
            $('<tr>').appendTo($table)
                .append($('<td>').text(category.id))
                .append($('<td>').text(category.name))
                .append($('<td>').text(category.department));
        });
    });
});