Details

    • Type: New Feature New Feature
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 3.0.5
    • Fix Version/s: General Backlog
    • Component/s: Data
    • Labels:
      None
    • Last commented by a User:
      false

      Description

      /**
       * An abstract template for row mapping operations that map multiple rows to a single object.
       * This is useful when joining a one-to-many relationship where there can be multiple rows returned per root (or aggregate).
       * For example, consider the relationship: "a Presentation has one-to-many Speakers".
       * When joining with the Speaker table to build a Presentation object, multiple rows will be returned for a Presentation if it has more than one Speaker.
       * This template is useful in that case.
       * This class has been submitted for contribution to Spring JDBC; see SPR-7698.
       * @author Keith Donald
       */
      public abstract class MultipleRowMapper<R, I> {
      
      	/**
      	 * Map a single root object R, where there may be multiple R rows for each child C.
      	 */
      	public R map(ResultSet rs) throws SQLException {
      		R root = mapRoot(mapId(rs), rs);
      		addChild(root, rs);
      		while (rs.next()) {
      			addChild(root, rs);			
      		}
      		return root;
      	}
      
      	/**
      	 * Map root objects R into Collection<L> where there may be multiple R rows for each child C.
      	 */
      	public <L extends Collection<R>> L mapInto(L collection, ResultSet rs) throws SQLException {
      		R root = null;
      		I previousId = null;
      		while (rs.next()) {
      			I id = mapId(rs);
      			if (!id.equals(previousId)) {
      				root = mapRoot(id, rs);
      				collection.add(root);
      			}
      			addChild(root, rs);
      			previousId = id;
      		}
      		return collection;
      	}
      
      	// subclassing hooks
      	
      	/**
      	 * Map the ID property for root entity R.
      	 */
      	protected abstract I mapId(ResultSet rs) throws SQLException;
      	
      	/**
      	 * Map root object R including its direct properties and excluding child association properties.
      	 */
      	protected abstract R mapRoot(I id, ResultSet rs) throws SQLException;
      
      	/**
      	 * Map the next child object and add it to root object R. 
      	 */
      	protected abstract void addChild(R root, ResultSet rs) throws SQLException;
      
      

      Example usage for mapping an Event object, where an Event has one-to-many Venues.

      new RowMapper<Event>() {
      	public Event mapRow(ResultSet rs, int row) throws SQLException {
      		return new MultipleRowMapper<Event, Long>() {
      			protected Long mapId(ResultSet rs) throws SQLException {
      				return rs.getLong("id");
      			}
      			protected Event mapRoot(Long id, ResultSet rs) throws SQLException {
      				return new Event(id, rs.getString("title"), DateTimeZone.forID(rs.getString("timeZone")), new DateTime(rs.getTimestamp("startTime"), DateTimeZone.UTC), new DateTime(rs.getTimestamp("endTime"), DateTimeZone.UTC),
      					rs.getString("slug"), rs.getString("description"), rs.getString("hashtag"), new ResourceReference<String>(rs.getString("groupSlug"), rs.getString("groupName")));
      			}
      			protected void addChild(Event event, ResultSet rs) throws SQLException {
      				event.addVenue(new Venue(rs.getLong("venueId"), rs.getString("venueName"), rs.getString("venuePostalAddress"), 
      					new Location(rs.getDouble("venueLatitude"), rs.getDouble("venueLongitude")), rs.getString("venueLocationHint")));
      			}
      		}.map(rs);
      	}
      };
      
      

      Example usage for mapping a Collection of EventSession objects, where an EventSession has one-to-many EventSessionLeaders:

      private ResultSetExtractor<List<EventSession>> eventSessionsExtractor = new ResultSetExtractor<List<EventSession>>() {
      	public List<EventSession> extractData(ResultSet rs) throws SQLException {
      		return new MultipleRowMapper<EventSession, Integer>() {
      			protected Integer mapId(ResultSet rs) throws SQLException {
      				return rs.getInt("id");
      			}
      			protected EventSession mapRoot(Integer id, ResultSet rs) throws SQLException {
      				return new EventSession(id, rs.getString("title"), new DateTime(rs.getTimestamp("startTime"), DateTimeZone.UTC), new DateTime(rs.getTimestamp("endTime"), DateTimeZone.UTC),
      						rs.getString("description"), rs.getString("hashtag"), rs.getFloat("rating"), new SubResourceReference<Long, Integer>(rs.getLong("venue"), rs.getInt("room"), rs.getString("roomName")), rs.getBoolean("favorite"));
      			}
      			protected void addChild(EventSession session, ResultSet rs) throws SQLException {
      				session.addLeader(new EventSessionLeader(rs.getString("name")));					
      			}
      		}.mapInto(new ArrayList<EventSession>(), rs);
      	}
      };
      

        Activity

        Hide
        Thomas Risberg added a comment -

        Even if this issue is getting old, this is a fairly common problem and it's still relevant. It would be good to have a general solution. Not sure I like the RowMapper implementation navigating the ResultSet since that's something that should be handled at a higher level.

        I have implemented a ResutSetExtractor that handles this One-to-Many mapping problem in the Spring Data JDBC Extensions project - https://github.com/SpringSource/spring-data-jdbc-ext/blob/master/spring-data-jdbc-core/src/main/java/org/springframework/data/jdbc/core/OneToManyResultSetExtractor.java

        Since I took code and ideas from this submission I added Keith as the author on that one.

        We could consider moving the OneToManyResultSetExtractor to Spring Framework if this is considered generally useful.

        -Thomas

        Show
        Thomas Risberg added a comment - Even if this issue is getting old, this is a fairly common problem and it's still relevant. It would be good to have a general solution. Not sure I like the RowMapper implementation navigating the ResultSet since that's something that should be handled at a higher level. I have implemented a ResutSetExtractor that handles this One-to-Many mapping problem in the Spring Data JDBC Extensions project - https://github.com/SpringSource/spring-data-jdbc-ext/blob/master/spring-data-jdbc-core/src/main/java/org/springframework/data/jdbc/core/OneToManyResultSetExtractor.java Since I took code and ideas from this submission I added Keith as the author on that one. We could consider moving the OneToManyResultSetExtractor to Spring Framework if this is considered generally useful. -Thomas

          People

          • Assignee:
            Unassigned
            Reporter:
            Keith Donald
            Last updater:
            Thomas Risberg
          • Votes:
            1 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Days since last comment:
              1 year, 43 weeks, 2 days ago