Announcement Announcement Module
Collapse
No announcement yet.
ResultSetExtractor for One-To-Many and Join Column query Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • ResultSetExtractor for One-To-Many and Join Column query

    Hi,

    I have a difficult time understanding how to implement the ResultSetExtractor to map my SQL Join query to my objects. I tried searching google and the spring documentation but with little success. I hope somebody can help me understand how to do this. I currently use the RowMapper for single entities which works quit well.

    I basically have two classes with a one-to-many relationship in MySQL.

    Code:
    public class Artist {
    
        private int artist_id;
        private String artist_name;
        private String artist_bio;
        private byte[] artist_avatar;
        private List<Album> albumList;
      
        // Getters & Setters
    }
    Code:
    public class Album {
    
        private int album_id;
        private int artist_id;
        private String album_name;
        private String album_description;
        private byte[] album_avatar;
    
       // Getters & Setters
    }
    The following are the results from my query in SQL:
    mysql> SELECT artist.artist_id, artist_name, album_id, album_name FROM artist LEFT JOIN album ON album.artist_id = artist.artist_id;
    +-----------+-------------+----------+------------------------+
    | artist_id | artist_name | album_id | album_name |
    +-----------+-------------+----------+------------------------+
    | 1 | Usher | 1 | Usher Album |
    | 1 | Usher | 2 | Usher Album2 |
    | 2 | 50 cent | 3 | Get Rich or Die Trying |
    +-----------+-------------+----------+------------------------+
    3 rows in set (0.00 sec)

    Now how do I go about creating the java logic for iterating through the rows and mapping this to my entities?
    Any help, tips, suggestions are appreciated.

  • #2
    Anyone? Any Tip or Advice?

    Comment


    • #3
      I found the solution to my problem:


      Code:
      public List&lt;Artist&gt; getAllArtistAlbum() {
              String sql = "SELECT artist.artist_id, artist_name, artist_bio, artist_avatar, album_id, album_name, album_description, album_avatar FROM artist LEFT JOIN album ON album.artist_id = artist.artist_id";
              return jdbcTemplate.query(sql, new ArtistWithAlbumExtractor());
          }

      Code:
      private static final class ArtistWithAlbumExtractor implements ResultSetExtractor&lt;List&lt;Artist&gt;&gt; {
      
              @Override
              public List&lt;Artist&gt; extractData(ResultSet rs) throws SQLException, DataAccessException {
                  Map&lt;Integer, Artist&gt; map = new HashMap&lt;Integer, Artist&gt;();
                  // Artist artist = null;
      
                  while (rs.next()) {
                     int artist_id = rs.getInt("artist_id");
                     Artist artist = map.get(artist_id);
                     if (artist == null) {
                          artist = new Artist();
                          artist.setArtist_id(artist_id);
                          artist.setArtist_name(rs.getString("artist_name"));
                          artist.setArtist_bio(rs.getString("artist_bio"));
                          artist.setArtist_avatar(rs.getString("artist_avatar").getBytes());
                          map.put(artist_id, artist);
                      }
      
                     int album_id = rs.getInt("album_id");
                     if ( album_id &gt; 0) {
                          Album album = new Album();
                          album.setAlbum_id(album_id);
                          album.setArtist_id(artist_id);
                          album.setAlbum_name(rs.getString("album_name"));
                          album.setAlbum_description(rs.getString("album_description"));
                          album.setAlbum_avatar(rs.getString("album_avatar").getBytes());
                          List&lt;Album&gt; albumList = new ArrayList&lt;&gt;();
                          albumList.add(album);
                          artist.setAlbumList(albumList);
                      }
                  }
                  return new ArrayList&lt;Artist&gt;(map.values());
              }
          }

      But now when I try to iterate over the album list from my artist entity :

      Code:
      public class Artist {
      
          private int artist_id;
          private String artist_name;
          private String artist_bio;
          private byte[] artist_avatar;
          private List&lt;Album&gt; albumList;
      
         // Getters &amp; Setters
      overview.jsp
      Code:
      <c:forEach items="${artists.albumList}" var="album">
       <h1>${album.album_name}</h1>
          </c:forEach>
      I get the following error:
      Code:
       java.lang.NumberFormatException: For input string: "albumList" java.lang.NumberFormatException.forInputString(NumberFormatException.java:65) java.lang.Integer.parseInt(Integer.java:492) java.lang.Integer.parseInt(Integer.java:527) javax.el.ListELResolver.coerce(ListELResolver.java:157)
      Last edited by vinchenzo; Apr 2nd, 2014, 09:38 AM.

      Comment

      Working...
      X