• Post Reply Bookmark Topic Watch Topic
  • New Topic

Converting Mysql data into JSON

 
surya preethaaa
Ranch Hand
Posts: 136
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i am trying to get data from the mysql table in JSON  format..

my jsp code




i cant able to get correct json format and also the null values includes as child in json..any suggestions would be helpfull..


here is my output




can anyone suggest me a way to get the json as below

 
surya preethaaa
Ranch Hand
Posts: 136
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


THIS IS MY TABLE
 
Swastik Dey
Rancher
Posts: 1686
7
Android Eclipse IDE Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Your approach is not very correct.  You should ideally ave a servlet this purpose.  Once you retrieve the data from query use a java class to set the data using setter methods.  Now use some Json builder class to retrieve the data from the class and build the json string.  As far as null is concerned, you can use ifnull function to replace null value with something else.

Your query might look something like this



The above query will replace the null values with an empty string.

 
surya preethaaa
Ranch Hand
Posts: 136
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks for your response,but i completed almost half of the work in JSP,the only problem in



org.apache.jasper.JasperException: An exception occurred processing JSP page /sample jsp.jsp at line 42

39:          <% while (resultset.next()) {
40:        %>
41:        
42:   { "name": "<%= resultset.getString("parent") %>" ,   "children": [{ "name": "<%= resultset.getString("child1") %>"},
43:  
44:     "children": [{ "name": "<%= resultset.getString("child2") %>"},
45:    { "name": "<%= resultset.getString("child3") %>"}   ,


Stacktrace:
org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:584)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:466)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:385)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:329)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
root cause

javax.servlet.ServletException: java.sql.SQLException: Column 'child1' not found.
org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:909)
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:838)
org.apache.jsp.sample_0020jsp_jsp._jspService(sample_0020jsp_jsp.java:202)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:443)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:385)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:329)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
root cause

java.sql.SQLException: Column 'child1' not found.
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
com.mysql.jdbc.ResultSet.findColumn(ResultSet.java:987)
com.mysql.jdbc.ResultSet.getString(ResultSet.java:5584)
org.apache.jsp.sample_0020jsp_jsp._jspService(sample_0020jsp_jsp.java:166)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:443)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:385)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:329)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)


but i have child1
 
Swastik Dey
Rancher
Posts: 1686
7
Android Eclipse IDE Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Did you use the ifnull query, and what happens if you use column index instead of column name?
 
Swastik Dey
Rancher
Posts: 1686
7
Android Eclipse IDE Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
One small mistake from my end in the query.   As you are using the column name while retrieving the values, the query needs a small modification


 
surya preethaaa
Ranch Hand
Posts: 136
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


this query gives me this



(containing "name": "" )
 
Swastik Dey
Rancher
Posts: 1686
7
Android Eclipse IDE Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's how it's supposed to work as per the query.  Null values are replaced with empty string.  What is your exact requirement?
 
Swastik Dey
Rancher
Posts: 1686
7
Android Eclipse IDE Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you run the query from mysql engine what output do you get?  How is getting "" in name?
 
surya preethaaa
Ranch Hand
Posts: 136
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i supposed to get this json using jsp

 
Swastik Dey
Rancher
Posts: 1686
7
Android Eclipse IDE Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You mean each parent and it's all children?
 
surya preethaaa
Ranch Hand
Posts: 136
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Swastik Dey wrote:If you run the query from mysql engine what output do you get?  How is getting "" in name?

[code]
parent          child1    child2      child3      child4      child5     child6
paren      c1     c11
                     c1     c12
             c1     c12      c121
             c2     c21
            c2            c22
            c2           c23
[\code]

this query deletes the child5 and child6 column
 
surya preethaaa
Ranch Hand
Posts: 136
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
       
 
Swastik Dey
Rancher
Posts: 1686
7
Android Eclipse IDE Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It doesn't delete the column.  It's replacing the null value with an empty string.  A select query can never delete a column.   instead of ifnull(child1,'') child try ifnull(child1,'na') child it will show na wherever the value is null, and if you see in many places the parent column is also empty in table that's why it's showing name as empty in the json string too.
 
surya preethaaa
Ranch Hand
Posts: 136
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
so ,is there any ways to avoid the printing of empty rows in json...any ways  to skip the empty rows   during this step

 
Swastik Dey
Rancher
Posts: 1686
7
Android Eclipse IDE Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
By empty row do you mean when the parent is empty?
 
surya preethaaa
Ranch Hand
Posts: 136
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Swastik Dey wrote:You mean each parent and it's all children?


i mean ,i required in this way
parent  
        children
                  sub children
                                 sub sub children

 
surya preethaaa
Ranch Hand
Posts: 136
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Is this line is correct for my requirement JSON format as i posted?

 
Swastik Dey
Rancher
Posts: 1686
7
Android Eclipse IDE Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's a different part.  But what exactly do you mean by empty row?  When the parent is empty or null?
 
surya preethaaa
Ranch Hand
Posts: 136
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Swastik Dey wrote:That's a different part.  But what exactly do you mean by empty row?  When the parent is empty or null?


when the parent is empty,i should not considered in the json
 
Swastik Dey
Rancher
Posts: 1686
7
Android Eclipse IDE Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Apply a where clause in the query

 
surya preethaaa
Ranch Hand
Posts: 136
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sorry.i tried this in mysql that shows the first rows

parent            child1            child2          child3           child4
paren         c1           c11
 
Swastik Dey
Rancher
Posts: 1686
7
Android Eclipse IDE Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In your table all other rows contain parent as null or empty, that's why it's returning only one row.
 
surya preethaaa
Ranch Hand
Posts: 136
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you please suggest me a efficient way to  mysql table to json format using jsp
 
Swastik Dey
Rancher
Posts: 1686
7
Android Eclipse IDE Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I already suggested you.   Putting a java code inside jsp is very old practice and is not going to help in future at all.  Moreover debugging is difficult as well.  A better approach is to put all data access code, json building code in a servlet.   Jsp will just display the output obtained from servlet.
 
surya preethaaa
Ranch Hand
Posts: 136
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks for your time..i try to use servlet
 
Swastik Dey
Rancher
Posts: 1686
7
Android Eclipse IDE Java Java ME
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You are welcome.  We are always here to help you.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!