Win a copy of Learn Spring Security (video course) this week in the Spring forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

A very common problem

 
Benjamin Weaver
Ranch Hand
Posts: 161
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I face what must be a very common problem, yet don't know the best way to solve it. As an example, let's assume a very simple database listing last names of authors and home addresses for each author. I want to pull this information out of the database and display it on a web page. For many authors, this will be simple: 1 last name is pulled with 1 address. Some sql like



will yield l row of data for each of the authors having 1 address, which can easily be loaded into a Bean and ultimately be displayed via JSP, etc.

But some authors will have more than 1 address, say, for example 2 addresses. In these cases, the example SQL will return as many rows as there are addresses: 2. Use of DISTINCT will not eliminate this problem as long as author.lastName and address.line1, etc. are retrieved in the same query. In this example, 2 rows will be retrieved. But I want to display the author's last name only once, while each of the several addresses is also displayed, perhaps in an indented column.

One easy way of handling this is to create a "flattened table" every night for easy querying and displaying, with fields like "address1", "address2", etc. But such a table, is, ultimately, not flexible.

What's the best way of solving the display of 1-to-many data problem? Dividing the query into 2 queries? a query for author.lastName and a separate query for the address information? Inefficient and quite slow if lots of tables/objects are involved, but I don't see any other way.

Or can java.sql.RowSet somehow help with this?

What do you think?
 
Gregg Bolinger
GenRocket Founder
Ranch Hand
Posts: 15302
6
Chrome IntelliJ IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This doesn't seem to be about any ORM type of solutions (Hibernate, iBatis, etc) and looks more like a pure SQL type of question. I'll move this to the JDBC forum where it is more appropriate.
 
Ernest Friedman-Hill
author and iconoclast
Marshal
Pie
Posts: 24208
35
Chrome Eclipse IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm not seeing a problem, actually. Add an "ORDER BY author.id" so that all the records for a given author are together. Then use a state variable to store the id of the author after you display each one. Then each time you display a row, check if the id is the same as it was the previous time around the loop; if so, don't display the author's name, just the address.
 
Benjamin Weaver
Ranch Hand
Posts: 161
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Woops, thanks Ernest. It's likely not so hard at all. No problem, as you say. Thanks again.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic