What's the best way to map the resultset to object

Question

Let's say there are 2 tables: PERSON and TELEPHONE.

A person can have more than one telephone. A telephone number can only be owned by one person.

When I join both table, the resultset returned is

P_ID     NAME   TEL_NO
1        ALVIN   911
1        ALVIN   912
1        ALVIN   913
2        ERIC    922
2        ERIC    923

But in my HTML, I want to show the page as:

ID: 1   Name : ALVIN        TEL_NO: 911, 912, 913
ID: 2   Name : ERIC         TEL_NO: 922, 923

What is the most efficient way to read the resultset and show the the data as shown above?

If I loop through the ResultSet and print it on the page. It would have multiple row with same name which are ALVIN and ERIC. But I want to print all the TEL_NO of same person on the same row.

I have a Java class that store the PERSON details.

public class Person {
  public int id;
  public String name;
  public List<Integer> telNos = new ArrayList<Integer>();
}

List<Person> persons = new ArrayList<Person>;
HashMap<Integer, Integer> map = new HashMap<Integer, Integer>();
while(resultSet.next()) {
  int pId = resultSet.getInt("P_ID");
  String name = resultSet.getString("NAME");
  int telNo = resultSet.getInt("TEL_NO");

  if(map.containsKey(pId)){
    Person person = persons.get(map.get(pId));
    person.telNos.add(telNo);
  } else {
    Person person = new Person();
    person.id = pId;
    person.name = name;
    person.telNos.add(telNo);

    map.put(pId, persons.size());
    persons.add(person);
  }
}

Finally, I pass the persons to the JSP for display.

My question is that: is there better way to loop and display the ResultSet without having same P_ID on different row of the html table.

Please clarify what you mean by "most efficient". Efficient in processing time? Efficient in clarity and code length? Efficient in memory usage? Efficient in data sent across the network? Also, what frameworks are you using? — jpmc26, Feb 08 '14 at 04:13
I am not using any framework, I just use java JDBC to query the ResultSet. I just want to know how to show the page as above using the ResultSet. — SooCheng Koh, Feb 08 '14 at 04:21
Your question says you have HTML. You're not using a web framework or templating engine to construct and deliver it? You also didn't answer my question about efficiency. — jpmc26, Feb 08 '14 at 04:25
What database are you using? (Some databases have capabilities that would make this simpler.) Meaning of efficiency? — jpmc26, Feb 08 '14 at 04:40
I am using MySql. I have updated the question. Most efficiency in clarity and code length — SooCheng Koh, Feb 08 '14 at 05:03
By the way, MySQL is not one such database that "makes this simpler" as I mentioned. In particular, I was thinking of PostgreSQL's [`ARRAY_AGG`](http://www.postgresql.org/docs/9.3/static/functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE) function and similar ones in other databases. That would have allowed you to get exactly what you want in a single query. — jpmc26, Feb 10 '14 at 07:24

score 0 · Answer 1 · answered Feb 08 '14 at 04:29

Best and Efficient way, you can use JQuery Data Tables plugin which will be used in Java Script and Html.

In .html, you have to define Table.
In .js, you can populate data into table dynamically.

Using Java script, it works fine every where that too you can handle data of web page dynamically.

score 0 · Accepted Answer · edited May 23 '17 at 11:56

To start with, I would just run two queries to get the PERSON and TELEPHONE data. Something like this:

Map<Integer, Person> people = new HashMap<Integer, Person>();
ResultSet resultSet = [some query on PERSON];

while(resultSet.next()) {
   Person p = [make a person]
   people.add(p.id, p);
}

resultSet = [some query on TELEPHONE];
while(resultSet.next()) {
   people.get(resultSet.getInt("P_ID")).telNos.add(resultSet.getInt("TEL_NO"));
}

This will give you a clean list of Person objects (without duplication) to work with. If performance is a huge concern (and I'm talking at least hundreds of requests per second, maybe more like thousands), you may need to stick with a single query. In that case, the idea of your code is more or less fine, but I would ditch the ArrayList<Person> and use Map<Integer, Person> like this:

Map<Integer, Person> people = new HashMap<Integer, Person>();
while(resultSet.next()) {
    int pId = resultSet.getInt("P_ID");
    if (!people.containsKey(pId)) {
       people.add(pId, [build a new person]);
    }
    people.get(pId).telNos.add(resultSet.getInt("TEL_NO"));
}

You can get your complete list of people by calling people.values().

Then in your template, you can loop over the telNos list to combine them into a comma separated list. Assuming you're already looping over your people and the current Person can be found in p, something like

<c:forEach items="#{p.telNos}" var="t" varStatus="loop"> ${t}${!loop.last ? ', ' : ''} </c:forEach>

(More or less taken from here)

What's the best way to map the resultset to object

2 Answers2