Spring JDBC RowMapper usage for eager fetches

Question

The question is about the best practice usage for RowMapper in master/detail scenarios where we want to eagerly fetch details using spring jdbc.

Assume that we have both Invoice and InvoiceLine classes.

public class Invoice{
    private BigDecimal invId;
    private Date invDate;
    private List<InvoiceLine> lines;
}
public class InvoiceLine{
    private int order;
    private BigDecimal price;
    private BigDecimal quantity;
}

When using Spring Jdbc with a row mapper we usually have a

public class InvoiceMapper implements RowMapper<Invoice>{
    public Invoice mapRow(ResultSet rs, int rowNum) throws SQLException {
         Invoice invoice = new Invoice();
         invoice.setInvId(rs.getBigDecimal("INVID"));
         invoice.setInvDate(rs.getDate("INVDATE"));
         return invoice;
    }
}

Now the problem is I want to eagerly fetch InvoiceLine's related with this invoice instance. Would it be OK if I query database in the rowmapper class? Or anyone prefers another way? I use the pattern below but not happy with that.

public class InvoiceMapper implements RowMapper<Invoice>{
    private JdbcTemplate jdbcTemplate;
    private static final String SQLINVLINE=
            "SELECT * FROM INVOICELINES WHERE INVID = ?";

    public Invoice mapRow(ResultSet rs, int rowNum) throws SQLException {
         Invoice invoice = new Invoice();
         invoice.setInvId(rs.getBigDecimal("INVID"));
         invoice.setInvDate(rs.getDate("INVDATE"));
         invoice.setLines(jdbcTemplate.query(SQLINVLINE, 
                          new Object[]{invoice.getInvId},new InvLineMapper());

         return invoice;
    }
}

I sense that something is wrong with this approach but could not get a better way. I would be more than glad if someone can show me why is this a bad design and if so what would be the correct usage.

Answer 1

Easiest Method

You can simply use this library - SimpleFlatMapper has already solved that problem. All you need to do is create a ResultSetExtractor using the JdbcTemplateMapperFactory.

import org.simpleflatmapper.jdbc.spring.JdbcTemplateMapperFactory;

private final ResultSetExtractor<List<Invoice>> resultSetExtractor = 
    JdbcTemplateMapperFactory
        .newInstance()
        .addKeys("id") // the column name you expect the invoice id to be on
        .newResultSetExtractor(Invoice.class);

String query = "SELECT * FROM INVOICE inv JOIN INVOICE_LINE line on inv.id = line.invoice_id"

List<Invoice> results = jdbcTemplate.query(query, resultSetExtractor);

Add this dependancey to the pom.xml

<dependency>
    <groupId>org.simpleflatmapper</groupId>
    <artifactId>sfm-springjdbc</artifactId>
    <version>8.2.1</version>
</dependency>

Here is an article to refer - https://arnaudroger.github.io/blog/2017/06/13/jdbc-template-one-to-many.html

Here are examples for different usages - https://github.com/arnaudroger/SimpleFlatMapper/blob/master/sfm-springjdbc/src/test/java/org/simpleflatmapper/jdbc/spring/test/JdbcTemplateMapperFactoryTest.java

Answer 2

The accepted solution based on the ResultSetExtractor can be made more modular and reusable: in my application I created a CollectingRowMapper interface and an abstract implementation. See code below, it contains Javadoc comments.

CollectingRowMapper interface:

import org.springframework.jdbc.core.RowMapper;

/**
 * A RowMapper that collects data from more than one row to generate one result object.
 * This means that, unlike normal RowMapper, a CollectingRowMapper will call
 * <code>next()</code> on the given ResultSet until it finds a row that is not related
 * to previous ones.  Rows <b>must be sorted</b> so that related rows are adjacent.
 * Tipically the T object will contain some single-value property (an id common
 * to all collected rows) and a Collection property.
 * <p/>
 * NOTE. Implementations will be stateful (to save the result of the last call
 * to <code>ResultSet.next()</code>), so <b>they cannot have singleton scope</b>.
 * 
 * @see AbstractCollectingRowMapper
 * 
 * @author Pino Navato
 **/
public interface CollectingRowMapper<T> extends RowMapper<T> {
    /**
     * Returns the same result of the last call to <code>ResultSet.next()</code> made by <code>RowMapper.mapRow(ResultSet, int)</code>.
     * If <code>next()</code> has not been called yet, the result is meaningless.
     **/
    public boolean hasNext();
}

Abstract Implementation class:

import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Basic implementation of {@link CollectingRowMapper}.
 * 
 * @author Pino Navato
 **/
public abstract class AbstractCollectingRowMapper<T> implements CollectingRowMapper<T> {

    private boolean lastNextResult;

    @Override
    public T mapRow(ResultSet rs, int rowNum) throws SQLException {
        T result = mapRow(rs, null, rowNum);
        while (nextRow(rs) && isRelated(rs, result)) {
            result = mapRow(rs, result, ++rowNum);
        }           
        return result;
    }

    /**
     * Collects the current row into the given partial result.
     * On the first call partialResult will be null, so this method must create
     * an instance of T and map the row on it, on subsequent calls this method updates
     * the previous partial result with data from the new row.
     * 
     * @return The newly created (on the first call) or modified (on subsequent calls) partialResult.
     **/
    protected abstract T mapRow(ResultSet rs, T partialResult, int rowNum) throws SQLException;

    /**
     * Analyzes the current row to decide if it is related to previous ones.
     * Tipically it will compare some id on the current row with the one stored in the partialResult.
     **/
    protected abstract boolean isRelated(ResultSet rs, T partialResult) throws SQLException;

    @Override
    public boolean hasNext() {
        return lastNextResult;
    }

    protected boolean nextRow(ResultSet rs) throws SQLException {
        lastNextResult = rs.next();
        return lastNextResult;
    }
}

ResultSetExtractor implementation:

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.util.Assert;


/**
 * A ResultSetExtractor that uses a CollectingRowMapper.
 * This class has been derived from the source code of Spring's RowMapperResultSetExtractor.
 * 
 * @author Pino Navato
 **/
public class CollectingRowMapperResultSetExtractor<T> implements ResultSetExtractor<List<T>> {
    private final CollectingRowMapper<T> rowMapper;
    private final int rowsExpected;

    /**
     * Create a new CollectingRowMapperResultSetExtractor.
     * @param rowMapper the RowMapper which creates an object for each row
     **/
    public CollectingRowMapperResultSetExtractor(CollectingRowMapper<T> rowMapper) {
        this(rowMapper, 0);
    }

    /**
     * Create a new CollectingRowMapperResultSetExtractor.
     * @param rowMapper the RowMapper which creates an object for each row
     * @param rowsExpected the number of expected rows (just used for optimized collection handling)
     **/
    public CollectingRowMapperResultSetExtractor(CollectingRowMapper<T> rowMapper, int rowsExpected) {
        Assert.notNull(rowMapper, "RowMapper is required");
        this.rowMapper = rowMapper;
        this.rowsExpected = rowsExpected;
    }


    @Override
    public List<T> extractData(ResultSet rs) throws SQLException {
        List<T> results = (rowsExpected > 0 ? new ArrayList<>(rowsExpected) : new ArrayList<>());
        int rowNum = 0;
        if (rs.next()) {
            do {
                results.add(rowMapper.mapRow(rs, rowNum++));
            } while (rowMapper.hasNext());
        }
        return results;
    }

}

All the code above can be reused as a library. You have only to subclass AbstractCollectingRowMapper and implement the two abstract methods.

Usage example:

Given a query like:

SELECT * FROM INVOICE inv 
         JOIN INVOICELINES lines
      on inv.INVID = lines.INVOICE_ID
order by inv.INVID

You can write just one mapper for the two joined tables:

public class InvoiceRowMapper extends AbstractCollectingRowMapper<Invoice> {

    @Override
    protected Invoice mapRow(ResultSet rs, Invoice partialResult, int rowNum) throws SQLException {
        if (partialResult == null) {
            partialResult = new Invoice();
            partialResult.setInvId(rs.getBigDecimal("INVID"));
            partialResult.setInvDate(rs.getDate("INVDATE"));
            partialResult.setLines(new ArrayList<>());
        }

        InvoiceLine line = new InvoiceLine();
        line.setOrder(rs.getInt("ORDER"));
        line.setPrice(rs.getBigDecimal("PRICE"));
        line.setQuantity(rs.getBigDecimal("QUANTITY"));
        partialResult.getLines().add(line);

        return partialResult;
    }


    /** Returns true if the current record has the same invoice ID of the previous ones. **/
    @Override
    protected boolean isRelated(ResultSet rs, Invoice partialResult) throws SQLException {
        return partialResult.getInvId().equals(rs.getBigDecimal("INVID"));
    }

}

Final note: I use CollectingRowMapper and AbstractCollectingRowMapper mainly with Spring Batch, in a custom subclass of JdbcCursorItemReader: I described this solution in another answer. With Spring Batch you can process each group of related rows before you get the next one, so you can avoid loading the whole query result that could be huge.

Answer 3

The ResultSetExtractor is a better option for doing this. Execute one query that joins both the tables and then iterate through the result set. You will need to have some logic to aggregate multiple rows belonging to the same invoice - either by ordering by invoice id and checking when the id changes or using a map like shown in the example below.

jdbcTemplate.query("SELECT * FROM INVOICE inv JOIN INVOICE_LINE line " +
   + " on inv.id = line.invoice_id", new ResultSetExtractor<List<Invoice>>() {

    public List<Invoice> extractData(ResultSet rs) {
        Map<Integer,Invoice> invoices = new HashMap<Integer,Invoice>();
        while(rs.hasNext()) {
            rs.next();
            Integer invoiceId = rs.getInt("inv.id");
            Invoice invoice = invoces.get(invoiceId);
            if (invoice == null) {
               invoice = invoiceRowMapper.mapRow(rs);
               invoices.put(invoiceId,invoice);
            }
            InvoiceItem item = invLineMapper.mapRow(rs);
            invoice.addItem(item);  
        }
        return invoices.values();
    }


});

Answer 4

What you have recreated here the 1 + n problem.

To solve it you need to use change your outer query to a join and then hand craft a loop to parse the flat join result set into your Invoice 1 -> * InvLine

List<Invoice> results = new ArrayList<>();
jdbcTemplate.query("SELECT * FROM INVOICE inv JOIN INVOICE_LINE line on inv.id = line.invoice_id", null, 
    new RowCallbackHandler() {
    private Invoice current = null;
    private InvoiceMapper invoiceMapper ;
    private InvLineMapper lineMapper ;

    public void processRow(ResultSet rs) {
        if ( current == null || rs.getInt("inv.id") != current.getId() ){
            current = invoiceMapper.mapRow(rs, 0); // assumes rownum not important
            results.add(current);
        }
        current.addInvoiceLine( lineMapper.mapRow(rs, 0) );
    }
}

I obviously haven't compiled this ... hopefully you get the idea. There is another option, use hibernate or any JPA implementation for that matter, they do this sort of thing out of the box and will save you a bunch of time.

Correction: Should really use the ResultSetExtractor as @gkamal has used in his answer, but the over all logic still stands.