Retrieving Data from Database and show in the json format

Question

I am trying to retrieve the data from database and display it out in jSON format. but it is showing the blank page.

Below is my java code in JsonServlet.java file:

    import java.io.IOException;
    import java.io.PrintWriter;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;       
    import javax.servlet.ServletException;
    import javax.servlet.annotation.WebServlet;
    import javax.servlet.http.HttpServlet;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;        
    import org.json.simple.JSONObject;

    /**
     * Servlet implementation class JsonServlet
     */
    @WebServlet("/JsonServlet")
    public class JsonServlet extends HttpServlet {
        private static final long serialVersionUID = 1L;

        /**
         * @see HttpServlet#HttpServlet()
         */
        public JsonServlet() {
            super();
            // TODO Auto-generated constructor stub
        }

        protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {             
        }  

        protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {  

            response.setContentType("text/html;charset=UTF-8");
            PrintWriter out = response.getWriter();

    try {
        response.setContentType("text/html;charset=UTF-8");

        Class.forName("com.mysql.jdbc.Driver");  
        Connection con=DriverManager.getConnection(  
        "jdbc:mysql://localhost:3306/register","root","toor"); 


        PreparedStatement pstmt = con.prepareStatement(  
                "select * from headwy " );

                    ResultSet rs = pstmt.executeQuery(); 

        while(rs.next())
        {       
           out.println("hiii");               
            JSONObject json = new JSONObject();  
            json.put("phone", rs.getString(1));  
            json.put("uname", rs.getString(2));
            json.put("pass", rs.getString(3));  
            out.print(json);        
        }       
    }
      catch (Exception e2) {System.out.println(e2);
       }           
        }  
    }

I am using MySQl as a database. Below is the data result from Database table with select query:

select * from head;
+----+-------+--------+-------------+
| id | name  | salary | designation |
+----+-------+--------+-------------+
|  1 | srinu |  50000 | test        |
|  2 | srinu | 500000 | devel       |
|  3 | pawan | 100000 | net         |
|  4 | ravi  |   2000 | .net        |
|  5 | ramya |  22222 | tester      |
|  6 | srinu |      0 | sql         |
|  7 | srinu | 200000 | test        |
|  8 | srinu |   1000 | java        |
|  9 | srinu |  11111 | html        |
| 10 | srinu |  11111 | html        |
| 11 | vv    |  29999 | hg          |
| 12 | s     |    147 | aa          |
| 13 | srinu |   5000 | test        |
+----+-------+--------+-------------+

I need help to display the data in correct JSON format. Any suggestions are always welcome.

Answer 1

You can do something like this:

   protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { 

            ArrayList<HeadObject> heads = new ArrayList<HeadObject>();

            response.setContentType("text/html;charset=UTF-8");
            PrintWriter out = response.getWriter();

    try {
        response.setContentType("text/html;charset=UTF-8");

        Class.forName("com.mysql.jdbc.Driver");  
        Connection con=DriverManager.getConnection(  
        "jdbc:mysql://localhost:3306/register","root","toor"); 


        PreparedStatement pstmt = con.prepareStatement("select * from headwy;");
        ResultSet rs = pstmt.executeQuery(); 

        while(rs.next()){    
          HeadObject head = new HeadObject();

            head.setId(rs.getInt(1));
            head.setName(rs.getString(2));
            head.setSalary(rs.getString(3));
            head.setDesignation(rs.getString(4));

            heads.add(head);
        }       
    }
      catch (Exception e2) {System.out.println(e2);} 

    String json = new Gson().toJson(heads);   
    out.print(json);
        }  

HeadObject Class

public class HeadObject {

    int id;
    String name;
    String salary;
    String designation;

    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getSalary() {
        return salary;
    }
    public void setSalary(String salary) {
        this.salary = salary;
    }
    public String getDesignation() {
        return designation;
    }
    public void setDesignation(String designation) {
        this.designation = designation;
    }

}

Answer 2

use libraries like gson https://github.com/google/gson to make it easier for json conversion.