ResultSet to a POJO that contains and a arraylist of another POJO

Question

hope somebody can hgelp me. I have a POJO with the following structure:

public class Invoice{

private String docNum;
private String customer;
private ArrayList<InvoiceDetails> invoiceDetails;

/* Getters and setters*/
}

And another POJO with the following

public class InvoiceDetails{

private String taxRate;
private Double taxAmount;
private Double amount;

/* Getters and setters*/
}

What I would like to know is the best way to fill the Pojo Invoice from a resultset like this:

String sql= "SELECT  InvoiceNumber, Customer, TaxRate, TaxAmount,Amount FROM TAX_VIEW WHERE Date=?"
PreparedStatement pst = cn.prepareStatement(Config.SQL_FACTURAS, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
     pst.setDate(1, date);
     ResultSet rs = pst.executeQuery();

Asuming the ResultSet can return multiple records for the same invoice, is it posible to do it once and for all, without the need of a second iteration? Thanks a lot

Answer 1

In your view I guess there is already a join. I suppose Invoice.docNum is the same as InvoiceNumber in the SELECT. (Why are there different names?) So, you should add an "ORDER BY InvoiceNumber" to your select, and in the processing loop verify the change of it. Code is pseudo, not tested:

ResultSet rs = pst.executeQuery();
Invoice invoice=new Invoice();
while(rs.next()){
    if (invoice.getDocNum().compareTo(rs.getString(1))!=0) {
         // This is a new Invoice, so process previous if not the first one
         // and create a new instance
         invoice=new Invoice();
         invoice.setDocNum(rs.getString(1));
         invoice.setCustomer(rs.getString(2));
         invoice.setInvoiceDetails(new ArrayList<InvoiceDetails>());
    }
    InvoiceDetails invoiceDetails = new InvoiceDetails();
    invoiceDetails.setTaxRate(rs.getString(3));
    // ... further member settings, and finally add to list:
    invoice.getInvoiceDetails.add(invoiceDetails);
}
// finally process the last Invoice if it is not empty