Announcement Announcement Module
Collapse
No announcement yet.
How to customize ResultSetExtractor. Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to customize ResultSetExtractor.

    Hi All,

    I'd like to customize ResultSetExtractor, here I'm giving details what I'm expecting...

    public class SelectFormProcedure extends StoredProcedure {

    public SelectFormProcedure(DataSource dataSource,String storedProcName,String input_value) {
    super();
    logger.info("SPRING before datasource SelectFormProcedure: "+storedProcName);
    setDataSource(dataSource);
    setSql(storedProcName);
    declareParameter(new SqlReturnResultSet(RESULT_SET, extractor));
    declareParameter(new SqlParameter(("input_value"), Types.VARCHAR));
    compile();
    }

    ResultSetExtractor extractor =
    new ResultSetExtractor() {
    public Object extractData(ResultSet rs)
    throws SQLException, DataAccessException
    {
    List<EmployeeBean> employeeDetails = new ArrayList<ReportBean>();
    recCount = 0;
    if(currentPage>0){
    startRecord = (currentPage-1)*pageRows+1;
    }
    while (rs.next()) {
    recCount += 1;
    if(recCount >= startRecord && recCount < startRecord + pageRows) {

    EmployeeBean employeeBean = new EmployeeBean();

    (here I'm taking resultset getMetaData and populating HashMap with key is column name and value is column value from resultset(rs). After populating one row then I'm using BeanUtil to populate employeeBean object and adding this object to employeeDetails list)

    }
    }

    return employeeDetails;
    }
    };
    }

    ---------------------

    This is working great but now I want to populate department details. I need to do same thing for this...only thing is chaing List to <DeptBean> and populating DeptBean values. Rather than putting this code one more time and changing bean references I'd like to customize ResultSetExtractor with parameter name with bean reference so that we can use one method to populate list of values(passing EmployeeBean or DeptBean).

    I don't know how to customize this, I can't use RowMapper in this case because I have some pagination logic inside this extractor.

    Please let me know, whether this is possible in spring?

    Thanks,
    Sharath.

  • #2
    When posting code please use [ code][/code ] tags, that way it remains readable.

    I don't know how to customize this, I can't use RowMapper in this case because I have some pagination logic inside this extractor.
    Not sure if I would consider that good design. Now the query always returns all the rows. Imagine if you have about 10000 rows and want to display the last 20, now the ResultSetExtractor starts to iterate until it finds the corresponding index.

    I would put paging in the query and then use a RowMapper to map all the rows performance wise and memory wise better then your current solution.

    However after having this off my mind .

    You could compine a ResultSetExtractor and a RowMapper ofcourse (there already is a BeanPropertyRowMapper implementation). Simply inject that into your ResultSetExtractor which contains the paging logic and delegate the constucting of an object/filling it to the RowMapper. (Which is basically also what spring does internally ).

    Code:
    public class PagingRowMapperResultSetExtractor implements ResultSetExtractor {
    
    	private RowMapper rowMapper;
    	private int currentPage;
    	private int pageRows = 20;
    	
    	public PagingRowMapperResultSetExtractor(RowMapper rowMapper, int currentPage) {
    		super();
    		this.rowMapper=rowMapper;
    	}
    
    	public Object extractData(ResultSet rs) throws SQLException {
    		int recCount = 0;
    		int startRecord = 0;
    		if (currentPage > 0) {
    			startRecord = (currentPage - 1) * pageRows + 1;
    		}
    		
    		List results = new ArrayList();
    		while (rs.next() ) {
    			recCount++;
    			if (recCount >= startRecord && recCount < startRecord + pageRows) {
    				Object record = rowMapper.mapRow(rs, recCount);
    				results.add(record);
    			} 
    		}
    		return results;
    	}
    
    
    }
    Usage
    Code:
    ResultSetExtractor rse = new PagingRowMapperResultSetExtractor(new BeanPropertyRowMapper(EmployeeBean.class), 1);
    jdbcTemplate.query("some query", rse);
    Now if you need a different class simply construct the BeanPropertyRowMapper differently.

    Comment


    • #3
      How to use PagingRowMapperResultSetExtractor while calling procedure.

      Thanks Marten,


      How to use PagingRowMapperResultSetExtractor while calling procedure, can I use this as below while declaring result set

      Code:
         declareParameter(new SqlReturnResultSet(RESULT_SET, new PagingRowMapperResultSetExtractor(new BeanPropertyRowMapper(EmployeeBean.class), 1);
      ));
      When I call procedure with below lines

      Code:
          Map results = procedure.execute();
          List<EmployeeBean> employeeDetails = (List<EmployeeBean>)results.get(RESULT_SET);
      this will return employeeDetails object consisting of all 'EmployeeBean', Please check this and correct me.

      Thanks,
      Sharath.

      Comment


      • #4
        Use it the same way as you use the resultsetextractor now (you seem to be using an anonymous inner class instead of that use the one provided here).

        Comment


        • #5
          Can I add one more Bean class along with EmployeeBean.

          say when I'm executing procedure it is getting values of 2 beans say EmployeeBean.class and DepartmentBean.class

          In RESULT_SET1 it is having employee data and in RESULT_SET2 it is having Department data, whether we can modify this code to populate 2 beans...

          Thanks in advance.

          Regards,
          Sharath.

          Comment

          Working...
          X