Win a copy of Testing JavaScript Applications this week in the HTML Pages with CSS and JavaScript forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

Converting Mysql data into JSON

 
Ranch Hand
Posts: 139
  • 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: 139
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


THIS IS MY TABLE
 
Bartender
Posts: 2265
20
Android Java ME Eclipse IDE Java
  • 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: 139
  • 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
Bartender
Posts: 2265
20
Android Java ME Eclipse IDE Java
  • 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
Bartender
Posts: 2265
20
Android Java ME Eclipse IDE Java
  • 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: 139
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


this query gives me this



(containing "name": "" )
 
Swastik Dey
Bartender
Posts: 2265
20
Android Java ME Eclipse IDE Java
  • 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
Bartender
Posts: 2265
20
Android Java ME Eclipse IDE Java
  • 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: 139
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i supposed to get this json using jsp

 
Swastik Dey
Bartender
Posts: 2265
20
Android Java ME Eclipse IDE Java
  • 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: 139
  • 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: 139
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
       
 
Swastik Dey
Bartender
Posts: 2265
20
Android Java ME Eclipse IDE Java
  • 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: 139
  • 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
Bartender
Posts: 2265
20
Android Java ME Eclipse IDE Java
  • 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: 139
  • 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: 139
  • 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
Bartender
Posts: 2265
20
Android Java ME Eclipse IDE Java
  • 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: 139
  • 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
Bartender
Posts: 2265
20
Android Java ME Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Apply a where clause in the query

 
surya preethaaa
Ranch Hand
Posts: 139
  • 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
Bartender
Posts: 2265
20
Android Java ME Eclipse IDE Java
  • 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: 139
  • 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
Bartender
Posts: 2265
20
Android Java ME Eclipse IDE Java
  • 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: 139
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks for your time..i try to use servlet
 
Swastik Dey
Bartender
Posts: 2265
20
Android Java ME Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You are welcome.  We are always here to help you.
 
Always! Wait. Never. Shut up. Look at this tiny ad.
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic