title: Data extraction from the database using ResultSetExtractor or RowMapper
body: <p>I have a probelem with the data extraction from the database. I have following tables in my database</p>
  
  <p>TABLE USER, attributes ID and USERNAME, 
  e.g. </p>
  
  <pre><code>[ ID | USERNAME ]
  [ 1  | John     ]
  [ 2  | Jane     ]
  </code></pre>
  
  <p>TABLE FRUIT, attributes FRUITID and NAME,
  e.g.</p>
  
  <pre><code>[ FRUITID | NAME     ]
  [ 1       | Apple    ]
  [ 2       | Pear     ]
  </code></pre>
  
  <p>TABLE CONSUMATION, attributes UID references USER.ID, FID references FRUIT.FRUITID and QUANTITY, PRIMARY KEY (uid, fid),
  e.g.</p>
  
  <pre><code>[ UID | FID | QUANTITY ]
  [ 1   |  1  | 1        ]
  [ 1   |  2  | 2        ]
  [ 2   |  1  | 1        ]
  </code></pre>
  
  <p>Model User which I am trying to extract from the database</p>
  
  <pre><code>public class User {
  
  private int userId;
  private String userName;
  private Map&lt;Fruit, Integer&gt; fruits;
  
  public User() {
      fruits = new HashMap&lt;Fruit, Integer&gt;();
  }
  
  public User(int userId) {
      this.userId = userId;
      fruits = new HashMap&lt;Fruit, Integer&gt;();
  }
  
  public User(int userId, String userName) {
      this.userId = userId;
      this.userName = userName;
      fruits = new HashMap&lt;Fruit, Integer&gt;();
  }
  
  public int getUserId() {
      return userId;
  }
  
  public void setUserId(int userId) {
      this.userId = userId;
  }
  
  public Map&lt;Fruit, Integer&gt; getFruits() {
      return fruits;
  }
  
  public void setFruits(Map&lt;Fruit, Integer&gt; fruits) {
      this.fruits = fruits;
  }
  
  public String getUserName() {
      return userName;
  }
  
  public void setUserName(String userName) {
      this.userName = userName;
  }
  }
  </code></pre>
  
  <p>I use this query to select the data from the database</p>
  
  <pre><code>SELECT * FROM USERS U LEFT JOIN CONSUMATION C ON (U.ID = C.UID)
  </code></pre>
  
  <p>and after I execute it i get these values, like on the image below
  <a href="http://s30.postimg.org/4bbu3q9oh/image.png" rel="nofollow">http://s30.postimg.org/4bbu3q9oh/image.png</a></p>
  
  <p>For each user there should be created one object and it's consumation values should be saved in his map. e.g.</p>
  
  <pre><code>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: 
  </code></pre>
  
  <p>I am using Spring JdbcTemplate and this is what I have tried so far. Unfortunately, it didn't work...</p>
  
  <pre><code>@Component
  public class UserDaoImpl {
  
  private DataSource dataSource;
  
  private JdbcTemplate jdbcTemplate;
  
  public User getUser(int userId) {
      String sql = "SELECT * FROM USERS WHERE id = ?";
      return jdbcTemplate.queryForObject(sql, new Object[] {userId}, new UserMapper());
  }
  
  public List&lt;User&gt; getAllUsers() {
      String sql = "SELECT * FROM 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&lt;User&gt; {
  
      @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&lt;User&gt; users = new ArrayList&lt;User&gt;();
          while(rs.next()) {
              User user = new User();
  
              HashMap&lt;Fruit, Integer&gt; fruits = new HashMap&lt;Fruit, Integer&gt;();
              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;
      }
  }
  
  }
  </code></pre>
  
  <p>so how would it be possible to achieve this? I'll appreciate any help
  Thank you!</p>