How to get ResulSet multiple values into Java objects

Question

I have 2 tables Person (Person_id, name) and Address(Address_id, Person_idAd, addressName) with relation of one-to-many.

With the following query I get

String query = "SELECT Person_id, addressName from Person, Address where Person_id = Person_idAd"
        
PreparedStatement pstmt = (PreparedStatement) con.prepareStatement(query);
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
        
}

Person_id	addressName
1001	Address-1
1001	Address-2
1002	Address-1

I want to map this entries into Java objects.

class Person {
    int id;
    ArrayList<String> addresses;
    // get and set...
}

Please let me know how it has to be done.
Thank you.

Answer 1

First of all, add ORDER BY Person_id to the [SQL] query.
Also, your [SQL] query should use JOIN.

select P.PERSON_ID, A.ADDRESSNAME
  from PERSON P
  join ADDRESS A
    on P.PERSON_ID = A.PERSON_IDAD
 order by P.PERSON_ID

Whenever the PERSON_ID changes, create a new Person object and set the id member.
While the PERSON_ID remains the same, collect all the addresses into a java.util.List.
You probably also want another java.util.List to hold all the people.

java.util.List<Person> people = new java.util.ArrayList<>();
int currentId = 0; // Assuming there is no such ID.
int id;
Person person = null;
java.util.List<String> addresses;
while (rs.next()) {
    id = rs.getInt(1);
    if (id != currentId) {
        if (person != null) {
            person.setAddresses(addresses);
            people.add(person);
        }
        person = new Person();
        person.setId(id);
        currentId = id;
        addresses = new java.util.ArrayList<>();
    }
    addresses.add(rs.getString(2));
}
// Add the last person.
if (person != null) {
    person.setAddresses(addresses);
    people.add(person);
}

Of course, as an alternative, and if relevant, you could use a Object Relational Mapping (ORM) tool like Hibernate.

Answer 2

You need to build object similar like this within while

Person person = null;
List<String> addrList = new ArrayList<String>();
while (rs.next()) {
int id = rs.getXXX() //you will personid 

if(person==null) {
   person = new Person();
   person.setId(id);
   addrList.add(rs.getXXX);
   person.setAddresses(addrList);
} else if(id!=person.getId()) {
  person = new Person();
   person.setId(id);
   addrList.add(rs.getXXX);
   person.setAddresses(addrList);
}
}