Solución:
ResultSetExtractor es una mejor opción para hacer esto. Ejecute una consulta que combine ambas tablas y luego repita el conjunto de resultados. Deberá tener algo de lógica para agregar varias filas que pertenecen a la misma factura, ya sea ordenando por identificación de factura y verificando cuando cambia la identificación o usando un mapa como se muestra en el ejemplo a continuación.
jdbcTemplate.query("SELECT * FROM INVOICE inv JOIN INVOICE_LINE line " +
+ " on inv.id = line.invoice_id", new ResultSetExtractor>()
public List extractData(ResultSet rs)
Map invoices = new HashMap();
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();
);
La solución aceptada basada en el ResultSetExtractor puede hacerse más modular y reutilizable: en mi aplicación creé un CollectingRowMapper interfaz y una implementación abstracta. Consulte el código a continuación, contiene comentarios de Javadoc.
Interfaz CollectingRowMapper:
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
* next() on the given ResultSet until it finds a row that is not related
* to previous ones. Rows must be sorted 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.
*
* NOTE. Implementations will be stateful (to save the result of the last call
* to ResultSet.next()), so they cannot have singleton scope.
*
* @see AbstractCollectingRowMapper
*
* @author Pino Navato
**/
public interface CollectingRowMapper extends RowMapper
/**
* Returns the same result of the last call to ResultSet.next() made by RowMapper.mapRow(ResultSet, int).
* If next() has not been called yet, the result is meaningless.
**/
public boolean hasNext();
Clase de implementación abstracta:
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* Basic implementation of @link CollectingRowMapper.
*
* @author Pino Navato
**/
public abstract class AbstractCollectingRowMapper implements CollectingRowMapper
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;
Implementación de ResultSetExtractor:
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 implements ResultSetExtractor>
private final CollectingRowMapper rowMapper;
private final int rowsExpected;
/**
* Create a new CollectingRowMapperResultSetExtractor.
* @param rowMapper the RowMapper which creates an object for each row
**/
public CollectingRowMapperResultSetExtractor(CollectingRowMapper 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 rowMapper, int rowsExpected)
Assert.notNull(rowMapper, "RowMapper is required");
this.rowMapper = rowMapper;
this.rowsExpected = rowsExpected;
@Override
public List extractData(ResultSet rs) throws SQLException
List 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;
Todo el código anterior se puede reutilizar como biblioteca. Solo tienes que subclasificar AbstractCollectingRowMapper e implementar los dos métodos abstractos.
Ejemplo de uso:
Dada una consulta como:
SELECT * FROM INVOICE inv
JOIN INVOICELINES lines
on inv.INVID = lines.INVOICE_ID
order by inv.INVID
Puede escribir solo un asignador para las dos tablas unidas:
public class InvoiceRowMapper extends AbstractCollectingRowMapper
@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"));
Nota final: yo uso CollectingRowMapper y AbstractCollectingRowMapper principalmente con Spring Batch, en una subclase personalizada de JdbcCursorItemReader: Describí esta solución en otra respuesta. Con Spring Batch puede procesar cada grupo de filas relacionadas antes de obtener la siguiente, por lo que puede evitar cargar todo el resultado de la consulta que podría ser enorme.
Lo que has recreado aquí el 1 + n problema.
Para resolverlo, debe usar cambiar su consulta externa a una combinación y luego crear un bucle para analizar el conjunto de resultados de combinación plana en su Invoice 1 -> * InvLine
List 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
Obviamente no he compilado esto … espero que entiendas la idea. Hay otra opción, use hibernate o cualquier implementación de JPA para el caso, hacen este tipo de cosas de fábrica y le ahorrarán un montón de tiempo.
Corrección: Realmente debería usar el ResultSetExtractor como @gkamal ha usado en su respuesta, pero la lógica general sigue en pie.
Sección de Reseñas y Valoraciones
Recuerda que puedes dar recomendación a este artículo si te valió la pena.