In Java - How to map resultSet into a complex obje

Question

How can I map a resultset from a few tables into a complex object? let me elaborate:

Lets say I have these 2 classes:

public class User {
    private int user_id;
    private String fname;
    private String lname;
    //getters setters...
}

public class Country{
    private String country;
    private ArrayList<User> users;
    }

The sql query:

SELECT * FROM users U, Country C WHERE c.user_id = U.id

gives me the following output -

id | fname | lname| country 
1  | Jack  | Levi |  USA
1  | Jack  | Levi |  UK
2  | Mike  | Brown|  Germany

How can I map these results into the Country class? I need make from it a Json string to get something like this:

{
id : 1,
fname: "jack",
lname" "levi",
countries : {"USA", "UK"}
}

Thank you very much!!

Answer 1

Maybe try something like this:

ArrayList<User> users = new ArrayList<User>();
while(resultSet.next)
{
     users.add(new User(resultSet.getString("id"), resultSet.getString("fname"), resultSet.getString("lname"));
     //you can also add your countries to an array list
}

Answer 2

Create a HashMap of where user id is the key, and user is the value. Then when you process the result set, do map.get(resultset.get(userid)), if it's null, create a new user, but if it isn't then do the logic to append a user to the country.

HashMap<Integer, User> users;
HashMap<String, Country> countries;
//inside the result set processing
User u = users.get(resultSet.get("id"));
    if(null != u) {
      //populate user here
    } else {
       Country c = countries.get(resultSet.get(country);
       c.users.add(u);
    }

Answer 3

When using jdbcTemplate of springframework, to implement the RowMapper interface is a good way.

Answer 4

ORM libraries will do want you describe. Although most will likely perform 2 queries instead of a join. Usually you need to provide some kind of annotation on the list field so that it knows how to perform the query:

public class Country{
  private String country;

  @OneToMany(...)
  private ArrayList<User> users;
}

When you select a Country object the ORM selects all related User objects and adds them into the users ArrayList.

The library that I use is sormula (I am the author). See the one to many example. No annotations are required if foreign key in User class is same name as primary key in Country class.

This page has good information about JPA OneToMany annotation: en.wikibooks.org/wiki/Java_Persistence/OneToMany