hsqldb - Data extraction from the database using ResultSetExtractor or RowMapper -

i have probelem data extraction database. have following tables in database

table user, attributes id , username, e.g.

[ id | username ] [ 1  | john     ] [ 2  | jane     ] 

table fruit, attributes fruitid , name, e.g.

[ fruitid | name     ] [ 1       | apple    ] [ 2       | pear     ] 

table consumation, attributes uid references user.id, fid references fruit.fruitid , quantity, primary key (uid, fid), e.g.

[ uid | fid | quantity ] [ 1   |  1  | 1        ] [ 1   |  2  | 2        ] [ 2   |  1  | 1        ] 

model user trying extract database

public class user {  private int userid; private string username; private map<fruit, integer> fruits;  public user() {     fruits = new hashmap<fruit, integer>(); }  public user(int userid) {     this.userid = userid;     fruits = new hashmap<fruit, integer>(); }  public user(int userid, string username) {     this.userid = userid;     this.username = username;     fruits = new hashmap<fruit, integer>(); }  public int getuserid() {     return userid; }  public void setuserid(int userid) {     this.userid = userid; }  public map<fruit, integer> getfruits() {     return fruits; }  public void setfruits(map<fruit, integer> fruits) {     this.fruits = fruits; }  public string getusername() {     return username; }  public void setusername(string username) {     this.username = username; } } 

i use query select data database

select * users u left join consumation c on (u.id = c.uid) 

and after execute these values, on image below http://s30.postimg.org/4bbu3q9oh/image.png

for each user there should created 1 object , it's consumation values should saved in map. e.g.

user: id:1 username: "amer"    fruits: (fruitid: 1 q:1); (fruitid 2 q:2) user: id:2 username: "nina"    fruits: (fruitid: 3 q:2) user: id:3 username: "viktor"  fruits:  

i using spring jdbctemplate , have tried far. unfortunately, didn't work...

@component public class userdaoimpl {  private datasource datasource;  private jdbctemplate jdbctemplate;  public user getuser(int userid) {     string sql = "select * users id = ?";     return jdbctemplate.queryforobject(sql, new object[] {userid}, new usermapper()); }  public list<user> getallusers() {     string sql = "select * users u left join consumation c on (u.id = c.uid)";     return jdbctemplate.query(sql, new myuserextractor()); }  public datasource getdatasource() {     return datasource; }  @autowired public void setdatasource(datasource datasource) {     jdbctemplate = new jdbctemplate(datasource); }  public jdbctemplate getjdbctemplate() {     return jdbctemplate; }  public void setjdbctemplate(jdbctemplate jdbctemplate) {     this.jdbctemplate = jdbctemplate; }  private static final class usermapper implements rowmapper<user> {      @override     public user maprow(resultset resultset, int rownum) throws sqlexception {         user user = new user();         user.setuserid(resultset.getint("id"));         user.setusername(resultset.getstring("username"));         return user;     }  }  private static final class myuserextractor implements resultsetextractor {      @override     public object extractdata(resultset rs) throws sqlexception,     dataaccessexception {         arraylist<user> users = new arraylist<user>();         while(rs.next()) {             user user = new user();              hashmap<fruit, integer> fruits = new hashmap<fruit, integer>();             user.setuserid(rs.getint("id"));             user.setusername(rs.getstring("username"));             fruits.put(new fruit(rs.getint("fid")), rs.getint("quantity"));             user.setfruits(fruits);             users.add(user);         }                                return users;     } }  } 

so how possible achieve this? i'll appreciate thank you!

it depends on not work specifically. in general, flow , usage of resultsetextractor correct. can see not aggregate users - continue creating new user each row in result set. this, use rowmapper. if use resultsetextractor create single row per user, need check if list contains user , update fruits map new fruit, rather creating new user.