*
The moose likes JDBC and the fly likes A very common problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "A very common problem" Watch "A very common problem" New topic
Author

A very common problem

Benjamin Weaver
Ranch Hand

Joined: Apr 08, 2003
Posts: 161
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

Joined: Jul 11, 2001
Posts: 15299
    
    6

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.


GenRocket - Experts at Building Test Data
Ernest Friedman-Hill
author and iconoclast
Marshal

Joined: Jul 08, 2003
Posts: 24183
    
  34

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.


[Jess in Action][AskingGoodQuestions]
Benjamin Weaver
Ranch Hand

Joined: Apr 08, 2003
Posts: 161
Woops, thanks Ernest. It's likely not so hard at all. No problem, as you say. Thanks again.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: A very common problem
 
Similar Threads
Control the execution order in jsf
retrieving object hierarchy - many-to-one?
SQL injection?
query involving one to many relationship
Database errors and Hibernate