现在的问题是关于我们想要使用了Spring JDBC预先抓取信息的主/从场景的最佳实践使用情况的RowMapper。
假设我们有两个发票和InvoiceLine类。
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;
}
当使用Spring JDBC和一个排映射器，我们通常有一个
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;
}
}
现在的问题是我要预先抓取InvoiceLine的有关与此帐单实例。 难道是确定的，如果我在的RowMapper类查询数据库？ 或者有人喜欢另一种方式？ 我用下面的模式，但并不高兴。
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;
}
}
我感觉到什么是错的这种方法，但不能得到一个更好的办法。 我会比更高兴，如果有人能告诉我为什么这是一个糟糕的设计如果是的话这将是正确的用法。
该ResultSetExtractor类是这样做更好的选择。 执行一个查询联接两个表，然后在结果集进行迭代。 你需要有一定的逻辑聚集属于同一张发票上多行 - 无论是通过发票编号排序，并检查当ID改变或使用地图一样显示在下面的例子。
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();
}
});
你在这里重建了1 + n问题。
为了解决这个问题，你需要使用改变你的外部查询到一起，然后手工工艺循环来解析平板连接结果设定到您的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) );
}
}
我显然还没有编译这个...希望你的想法。 还有另一种选择，使用休眠或任何JPA实现对于这个问题，他们做这种事情开箱，并会为你节省大量的时间。
更正：真正应该使用ResultSetExtractor如@gkamal在他的回答已经使用，但在所有的逻辑仍然有效。
根据该接受的解决方案ResultSetExtractor可以更加模块化和可重用：在我的应用程序创建一个CollectingRowMapper接口和一个抽象的实现。 请参见下面的代码，它包含Javadoc注释。
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
* <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();
}
摘要实现类：
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类实现：
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;
}
}
所有上面的代码可以重复使用的库。 你只有子类AbstractCollectingRowMapper和实施两个抽象方法。
用法示例：
给定一个查询，如：
SELECT * FROM INVOICE inv
JOIN INVOICELINES lines
on inv.INVID = lines.INVOICE_ID
order by inv.INVID
你可以写两个连接表只有一个映射：
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"));
}
}
最后说明一点：我用CollectingRowMapper和AbstractCollectingRowMapper主要与Spring Batch的，在一个自定义子类JdbcCursorItemReader ：我描述了这种解决方案的另一个答案 。 随着Spring Batch的您可以处理各组相关行的你在下单前，这样就可以避免加载整个查询的结果可能是巨大的。
