How to convert a ResultSet object to JSON output format

Question

I have a ResultSet after running the request. Please let me know how I can convert it to JSON output in JSP.

In the second stage, let's assume that we have a JSON output, as in this link> http://inknpost.com/eshopping/json.jsp

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

Please let me know how I can display "names" and "departments" on different lines on a page.

Answer 1

Create a reusable Javabean class that represents a single 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 reusable DAO class that maps the ResultSet to the collection of these Javabeans in 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 that uses the JSON serializer / deserializer that can convert between the arbirary Javabeans collection and the 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); } } } 

Call it http://localhost:8080/contextname/categories.json . No, no JSP. You should not use JSP for output formats other than HTML.

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

 $('#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)); }); }); }); 

Answer 2

To create json, you can use google-gson library .

Then, to process your json in jQuery, it depends on your needs, but if you want to show them in a table, you can do it like this:

HTML:

 <table id="mytable"> <tr> <th> Name </th> <th> Department </th> </tr> </table> 

JavaScript:

 $.getJSON("your_url", function(categories){ for(var i=0; i<categories.length; i++){ var name = categories[i].name; var department = categories[i].department; $("#mytable").append('<tr><td>'+ name +'</td><td>'+department+'</td></tr>'); } }); 

Hope this helps. Greetings