CODEWITHSUNDEEP
javaarraysjsonjdbcmapping
Gil404
Gil404

Reputation: 721

In Java - How to map resultSet into a complex object?

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!!

Upvotes: 1

Views: 2801

Answers (4)

Jeff Miller
Jeff Miller

Reputation: 1434

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

Upvotes: 0

CBredlow
CBredlow

Reputation: 2840

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);
    }

Upvotes: 1

brso05
brso05

Reputation: 13232

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
}

Upvotes: 0

StackBox
StackBox

Reputation: 326

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

Upvotes: 0

Related Questions