Spring Jdbctemplate mapping a one to many example is this the most efficient or simple way

[Tony Evans]

Is this the most efficient or simple way to code a ResultSetExtractor

public class PortfolioResultSetExtractor implements ResultSetExtractor{

	public Object extractData(ResultSet rs) throws SQLException,
			DataAccessException {
		Portfolio portfolio = new Portfolio();
		portfolio.setPortfolioId(rs.getInt(1));
		portfolio.setPortfolioName(rs.getString(2));
		List<Fund> list = portfolio.getFunds();
		if(list == null){
			list = new ArrayList<Fund>();
		}
		Fund fund = new Fund();
		fund.setFundId(rs.getInt(3));
		fund.setName(rs.getString(4));
		fund.setPrice(rs.getBigDecimal(5));
		fund.setQuanity(rs.getInt(6));
		list.add(fund);
		portfolio.setFunds(list);
		return portfolio;
	}

}

the mapper

public class PortfolioRowMapper implements RowMapper{

	public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
		PortfolioResultSetExtractor extractor = new PortfolioResultSetExtractor();
		return extractor.extractData(rs);
	}

}

the DAO

public Portfolio getPortfolio(Integer portfolioId) {
		jdbcTemplate = new JdbcTemplate(this.dataSource);
		String sql = "select p.portfolio_id, p.portfolio_name, f.fund_id, f.fund_name, f.stock_price, f.quantity from portfolio p INNER join fund f ON p.portfolio_id = f.portfolio_id and p.portfolio_id ="+portfolioId;
		RowMapper mapper = (RowMapper) new PortfolioRowMapper();
		List traderList = jdbcTemplate.query(sql, mapper);
		for(Object obj : traderList){
			return (Portfolio)obj;
		}
		return null;
	}

Thanks for any reviews an helpgul hints, this is not about Jdbctemplate vs hibernate, I just want to look at the best way to utilise Jdbctemplate

[Bill Gorder]

You use one or the other. A RowMapper is the preferred way for mapping one result object per row, ResultSetExtractor is for one result object for the entire ResultSet. There is no performance difference but the RowMapper does more for you and you don't have to iterate yourself.

Also make sure you type these so you don't need the casts.

To answer your question a ResultSetExtractor would usually look something like follows: (I would definitely look at initializing your Fund list in your Portfolio object that way it will never be null when you create a new Porfolio and you can just get it and add to it)

    public class PortfolioResultSetExtractor  implements ResultSetExtractor<List<Portfolio>> {

        @Override
        public Object extractData(ResultSet rs) throws SQLException {
           List<Portfolio> portfolioList = new ArrayList<Portfolio>();      
           while(rs.next){
               Portfolio portfolio = new Portfolio();  
               portfolio.setPortfolioId(rs.getInt(1));  
               //rest of you processing here
           }
           return portfolioList;
       }
     }

For this case a row mapper can do what you need an you can do away with the result set extractor altogether:

    public class PortfolioRowMapper  implements RowMapper<Portfolio> {

        @Override
        public Portfolio  mapRow(ResultSet rs, int rowNum) throws SQLException
           Portfolio portfolio = new Portfolio();
           //set stuff on your object here
           return portfolio;
       }
     }

If there is more than one row then will get a list back in both cases.

You would use it as follows:

PortfolioRowMapper mapper = new PortfolioRowMapper();
List<Portfolio> list = jdbcTemplate.query(sql, mapper, args)

[Tony Evans]

Thanks Bill Gorder

Thats food for thought will code your way try it out, I like this I would definitely look at initializing your Fund list in your Portfolio object that way it will never be null when you create a new Porfolio it will remove that if block.

Thanks Tony