Package com.randomnoun.common.spring

Class StructuredListResultSetExtractor

java.lang.Object
com.randomnoun.common.spring.StructuredListResultSetExtractor
All Implemented Interfaces:
org.springframework.jdbc.core.ResultSetExtractor<Object>
public class StructuredListResultSetExtractor extends Object implements org.springframework.jdbc.core.ResultSetExtractor<Object>
This class returns structured object graphs composed of lists/maps. Think of it as a poor man's JDO, using Maps and Lists rather than concrete classes, which allows us to omit the POJO generation step.

This ResultReader is to be used in the JdbcTemplate class to retrieve structured representations of queries covering more than one table. In a simple case, say a cowGroup contains many cows, and we wish to return the cowGroup ID and name as well as the cow ID and name in the one query; typically, this would be done through the following hypothetical SQL: 

 SELECT cowGroupId, cowGroupName, cowId, cowName
 FROM cowGroups, cows
 WHERE cows.cowGroupId = cowGroups.cowGroupId
Normally, Spring would return this as a List containing a map containing four keys (cowGroupId, cowGroupName, cowId and cowName), containing the values of each column returned by the RDBMS; e.g. the code fragment 
             List result1 = jt.queryForList(sql);
             System.out.println(Text.structuredListToString("result1", result1));
(with the SQL above) would produce this output (a three-element list): 
              result1 = [
                0 = {
                  COWGROUPID => 1
                  COWGROUPNAME => 'ROLLINGFIELD7'
                  COWID => 1000
                  COWNAME => 'DAISY'
                }
                1 = {
                  COWGROUPID => 2
                  COWGROUPNAME => 'SLAUGHTERHOUSE5'
                  COWID => 1001
                  COWNAME => 'BUTTERCUP'
                }
                2 = {
                  COWGROUPID => 2
                  COWGROUPNAME => 'SLAUGHTERHOUSE5'
                  COWID => 1002
                  COWNAME => 'STEVE'
                }
              ]
note how both BUTTERCUP and STEVE belong to the SLAUGHTERHOUSE5 cowGroup, but DAISY is within it's own cowGroup. If we wanted to express this relationship by having a list of cows within each cowgroup, we would have to scan through this list manually, and filter each cow into it's parent cowGroup.

This object automates this process; e.g. the same structure above, when processed using the following code snippet: 

              String mapping = "cowGroupId, cowGroupName, " +
                "cowId AS cows.cowId, cowName AS cows.cowName";
              List result2 = jt.query(sql, new StructuredResultReader(mapping));
              System.out.println(Text.structuredListToString("result2", result2));
... would produce this output (a two-element list) ... 
              result2 = [
                0 = {
                  cowGroupName => 'ROLLINGFIELD7'
                  cowGroupId => 2
                  cows = [
                    0 = {
                      cowId => 1000
                      cowName => 'DAISY'
                    }
                  ]
                }
                1 = {
                  cowGroupName => 'SLAUGHTERHOUSE5'
                  cowGroupId => 1
                  cows = [
                    0 = {
                      cowId => 1001
                      cowName => 'BUTTERCUP'
                    }
                    1 = {
                      cowId => 1002
                      cowName => 'STEVE'
                    }
                  ]
                }
              ]
which may be more suitable for processing. This structure can also be used within JSTL, e.g. 
   <c:out value="${result2[0].cows[1].cowName}"/>
would generate the text "STEVE".

This class can generate an arbitrary number of levels within the returned structure by specifying more groupings in the mapping text. This class also performs camelCasing of returned columns (e.g. to return 'cowName' rather than 'COWNAME').

The guts of the interface lies in the mapping string passed to the StructuredResultReader constructor. The syntax of this mapping string is a list of column names, separated by commas; a column name may also have an "AS" definition, which defines how it is to be represented in the returned structure. e.g. assuming a department contains many people, this mapping will group each person within their department: 

 departmentName, personName AS people.name, personId AS people.id
which will return a List of Maps, containing two keys, 'departmentName' and 'people'. The people value will be a List of Maps, containing two keys, 'name' and 'id'.

If each person now has a number of addresses, we could perform a two-level grouping, using the following mapping: 

 department, personName AS people.name, personId AS people.id,
 addressType AS people.addresses.type

Each people value will now be a List of Maps containing three keys, 'name', 'id' and 'addresses'; the 'addresses' value will be a List of Maps with only one key, 'type'.

Hope that all makes sense.

Implementation note: The resultset returned by the SQL must be sorted by the top-level group, then by the second-level group, and so on, for this code to work as expected.

Author:
knoxg

  • Constructor Details

    • StructuredListResultSetExtractor

      public StructuredListResultSetExtractor(org.springframework.jdbc.core.JdbcTemplate jt, String mappings)
      Create a new RowMapperResultReader.
      Parameters:
      rowMapper - the RowMapper which creates an object for each row

    • StructuredListResultSetExtractor

      public StructuredListResultSetExtractor(org.springframework.jdbc.core.RowMapper<Map<String,Object>> rowMapper, String mappings, int rowsExpected)
      Create a new RowMapperResultReader.
      Parameters:
      rowMapper - the RowMapper which creates an object for each row
      rowsExpected - the number of expected rows (just used for optimized collection handling)

  • Method Details

    • extractData

      public Object extractData(ResultSet rs) throws SQLException, org.springframework.dao.DataAccessException
      Required to support ResultSetExtractor interface
      Specified by:
      extractData in interface org.springframework.jdbc.core.ResultSetExtractor<Object>
      Parameters:
      rs - resultSet to process
      Returns:
      a List of structured Maps, as described in the class javadoc
      Throws:
      SQLException
      org.springframework.dao.DataAccessException

    • processRow

      public void processRow(ResultSet rs) throws SQLException
      Used by the ResultReader interface to process a single row from the database.

      The row is read and matched against the 'levels' specified in the object constructor. As values change, tree branches are created in the returned structured List.

      Throws:
      SQLException
      See Also:
      • RowCallbackHandler.processRow(java.sql.ResultSet)