Spring JDBC RowMapper用于急切获取的用法 [英] Spring JDBC RowMapper usage for eager fetches

问题描述

问题在于在主/详细方案中RowMapper的最佳实践用法，在该方案中我们希望使用Spring jdbc来获取详细信息.

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

假设我们同时拥有Invoice和InvoiceLine类.

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;
}

在将Spring Jdbc与行映射器一起使用时，我们通常会有一个

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;
    }
}

现在的问题是，我想急切地获取与此发票实例相关的InvoiceLine的相关信息. 如果我在rowmapper类中查询数据库，可以吗?还是有人喜欢另一种方式?我使用下面的模式，但对此不满意.

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.

推荐答案

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();
    }


});

这篇关于Spring JDBC RowMapper用于急切获取的用法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！