• Post Reply Bookmark Topic Watch Topic
  • New Topic

iterate through mysql database

 
surya preethaaa
Ranch Hand
Posts: 125
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi,,i am trying to retrieve data from database ..using jsp
this is my table



I want to print child1,if child1 is null,it should return to child 2 and print child2
if child 2 is null,should return to child1 column

this is my jsp code

i am getting this output


it will be helpfull if someone suggest me a right way to achieve this
 
Tim Moores
Bartender
Posts: 3202
54
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If that is not the output you want, what is?

JSPs are for generating views, not DB or IO code - move the code to a backing bean or servlet.
 
surya preethaaa
Ranch Hand
Posts: 125
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
actually i want the mysql table content in json format to pass it into the d3..
i just use JSP to display the result from the db..to check whether it is in correct json format or not
required json is below
 
Dave Tolls
Ranch Hand
Posts: 2208
20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That table structure looks completely wrong for modelling your relationship.

I would expect something like:

id, name, parent_id

So your data would be something like:


Then you would select the data and build objects out of that, and then map those objects to JSON using one of the existing frameworks, returning that from your servlet (skipping the JSP entirely).
 
surya preethaaa
Ranch Hand
Posts: 125
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks for your reply..yes you are right..i tried this model also but i have no idea,how to get it into a JSON format
 
Dave Tolls
Ranch Hand
Posts: 2208
20
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, one way is to produce a Java model that represents that (all it has is id, name and a List<TheModel>).
Populate that model from query (or queries).
Then use Gson or similar to turn that into json.
 
surya preethaaa
Ranch Hand
Posts: 125
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks for your suggestion ...let me try this ...
 
surya preethaaa
Ranch Hand
Posts: 125
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
actually i have 25,000 records in mysql..i used this sample table to get the json format..is it posible to use GSON HERE?
 
surya preethaaa
Ranch Hand
Posts: 125
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
before posting this question,i have tried this method

that gives me this output

some wat related to expected output except not having sub children..is it good to use like this?
 
Ron McLeod
Bartender
Posts: 1195
108
Android Angular Framework Eclipse IDE Java Linux MySQL Database Redhat TypeScript
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
As Dave recommended, create a model for the entity.  For example, to model a real person which may have children, it could loook like this:


Then assemble the data in to the representation you want, and serialize it to JSON format.  For example:
 
Ron McLeod
Bartender
Posts: 1195
108
Android Angular Framework Eclipse IDE Java Linux MySQL Database Redhat TypeScript
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Using the data that Dave suggested, this is what to expect for the output:

I prettied the JSON to make it easier to visual the structure.
 
surya preethaaa
Ranch Hand
Posts: 125
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
@Ron, this structure is exactly as my requirement
 
surya preethaaa
Ranch Hand
Posts: 125
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thank you,i will try
 
Dave Tolls
Ranch Hand
Posts: 2208
20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Extracting the data from the db is going to require some thinking.
If there's several "generations" then you're talking a number of passes, I think.

1. Find all entries with no parent...these are your root elements.
2. Find all entries which have the ones just found as a parent...add them to their parents.
3. Repeat 2.

I'd stick them in a Map<Integer, Person> as well as in the List held by a parent so you can find parents easily to add their children.

Note, this won't scale well at all, but it's a start point.  Should work OK for 25k rows, depending on the structure of the data.

I can't, offhand, think of a way of querying it in a single swipe.
 
surya preethaaa
Ranch Hand
Posts: 125
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
@DAVE thanks for your response...i will try
 
Paul Clapham
Sheriff
Posts: 21708
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:Extracting the data from the db is going to require some thinking.
If there's several "generations" then you're talking a number of passes, I think.


It's possible to do it all in one pass, I wrote that algorithm several years ago. The general idea is that you build up tree fragments as you go and convert small fragments into larger fragments as the links get read from the database. But I have to say, the code is Ugly with a capital Ugh.
 
surya preethaaa
Ranch Hand
Posts: 125
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
@Paul  thanks for your suggestion
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!