Spring Jdbctemplate one to many data retrieving

Question

I'm working in a spring jdbc template project. There I want to load all the records from an one to many relationship. Following is the my database structure,

class ReceiptHeader{
 int rcptid,
 String rcptname;
 List<ReceiptDetails> receiptDetails;
}

class ReceiptDetails{
 int detid;
 String comment;
}

I want to load all the records in ReceiptHeader to a list<> and all the ReceiptDetails records must attached to it.

My code is,

@Override   
    public <E, U, V> List<E> getData(U param, V invId) throws Exception {
        List<ReceiptHdr> result = null;

        try {
            logger.info("--------Excute getData -->");
            JdbcTemplate jdbcTemplate = new JdbcTemplate(this.dataSource);

            final Map<Integer, ReceiptHdr> rcptHdr = new HashMap<Integer, ReceiptHdr>();

            jdbcTemplate.query("select h.rcpt_id, h.remark, d.det_id, d.comment from receipt_hdr h inner join receipt_det on h.rcpt_id = d.rcpt_id", new RowMapper<ReceiptDet>() {
                public ReceiptHdr mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Integer rcptId = rs.getInt("rcpt_id");
                    ReceiptHdr rcptHeader = (ReceiptHdr) rcptHdr.get(rcptId);
                    if (rcptHeader == null) {
                        String hdrRemark = rs.getString("remark");
                        rcptHeader = new ReceiptHdr();
                        rcptHeader.setRcptId(rcptId);
                        rcptHeader.setHdrRemark(hdrRemark);
                        rcptHdr.put(rcptId, rcptHeader);
                    }
                    ReceiptDet rcptDet = new ReceiptDet();
                    rcptDet.setRcptDetId(rs.getInt("det_id"));
                    rcptDet.setDeleteComment(rs.getString("comment"));
                    rcptHeader.getReceiptDets().add(rcptDet);
                    return rcptDet;
                }
            });
            result = new ArrayList<ReceiptHdr>(rcptHdr.values());

            logger.info("--------Excute ReceiptHdr -->" + rcptHdr);

        } catch (Exception e) {
            logger.error("Error : Get Data-->" + e);
        }
        return (List<E>) result;
    }

The screen shot of the code is here

I tried following two methods, but unsuccessfull, this link

Answer 1

Finally I found the solution, I missed some syntax errors.

Any way also It is not good practice to use anonymous classes to get data as above answer.

@Override
    public <E, U, V> E getObject(U arg0, V invId) throws Exception {
        ReceiptHdr receiptHdr = null;
        try {
            logger.info("--------Excute getData -->");

            JdbcTemplate jdbcTemplate = new JdbcTemplate(this.dataSource);              
            receiptHdr = jdbcTemplate.queryForObject("select rcpt_id, hdr_remark, currency, rate_value, rcpt_st_date, rcpt_end_date, grand_total, tot_discount_amt, net_total, tot_paid_amt, cust_id, so_id, sale_inv_id, stat_code, current_inst_no,added_date, added_by from" + SchemaName.MIS + "receipt_hdr",
                    new RowMapper<ReceiptHdr>() {

                        public ReceiptHdr mapRow(ResultSet rs, int rowNum) throws SQLException {
                            logger.info("--------Excute check 2 -->");
                            ReceiptHdr rcptHdr = new ReceiptHdr();
                            rcptHdr.setRcptId(rs.getInt("rcpt_id"));
                            rcptHdr.setHdrRemark(rs.getString("hdr_remark"));
                            rcptHdr.setCurrency(rs.getString("currency"));
                            rcptHdr.setRateValue(rs.getBigDecimal("rate_value"));
                            rcptHdr.setRcptStDate(rs.getDate("rcpt_st_date"));
                            rcptHdr.setRcptEndDate(rs.getDate("rcpt_end_date"));
                            rcptHdr.setGrandTotal(rs.getBigDecimal("grand_total"));
                            rcptHdr.setTotDiscountAmt(rs.getBigDecimal("tot_discount_amt"));
                            rcptHdr.setNetTotal(rs.getBigDecimal("net_total"));
                            rcptHdr.setTotPaidAmt(rs.getBigDecimal("tot_paid_amt"));                        
                            rcptHdr.setAddedBy(rs.getString("added_by"));

                            SaleInvHdr salesInvHdr = new SaleInvHdr();
                            salesInvHdr.setSaleInvId(rs.getShort("sale_inv_id"));
                            rcptHdr.setSaleInvHdr(salesInvHdr);

                            SoHdr soHdr = new SoHdr();
                            soHdr.setSoId(rs.getShort("so_id"));
                            rcptHdr.setSoHdr(soHdr);

                            Customer customer = new Customer();
                            customer.setCustId(rs.getShort("cust_id"));
                            rcptHdr.setCustomer(customer);

                            rcptHdr.setAddedDate(rs.getDate("added_date"));
                            rcptHdr.setStatCode(rs.getShort("stat_code"));
                            rcptHdr.setCurrentInstNo(rs.getShort("current_inst_no"));

                            return rcptHdr;
                        }
                    });

            receiptHdr.setReceiptDets((jdbcTemplate.query("select rcpt_det_id,rcpt_date,paid_amt,instmnt_no,outstnd_amt, delete_comment,added_date from" + SchemaName.MIS + "receipt_det", new RowMapper<ReceiptDet>() {
                public ReceiptDet mapRow(ResultSet rs, int rowNum) throws SQLException {
                    ReceiptDet receiptDet = new ReceiptDet();
                    receiptDet.setRcptDetId(rs.getInt("rcpt_det_id"));
                    receiptDet.setRcptDate(rs.getDate("rcpt_date"));
                    receiptDet.setPaidAmt(rs.getBigDecimal("paid_amt"));
                    receiptDet.setInstmntNo(rs.getShort("instmnt_no"));
                    receiptDet.setOutstndAmt(rs.getBigDecimal("outstnd_amt"));
                    receiptDet.setDeleteComment(rs.getString("delete_comment"));
                    receiptDet.setAddedDate(rs.getDate("added_date"));
                    return receiptDet;
                }
            })));

            logger.info("--------getData ReceiptHdr--> " + receiptHdr);
            logger.info("--------getData ReceiptHdr--> " + receiptHdr.getReceiptDets().size());
        } catch (EmptyResultDataAccessException e) {
            return null;
        } catch (Exception e) {
            logger.error("Error : Get Data-->" + e);
        }

        return (E) receiptHdr;
    }

Answer 2

Use JdbcTemplate's overloaded query method with ResultSetExtractor.

public <T> T query(String sql,
                   ResultSetExtractor<T> rse)
            throws DataAccessException

And never access and modify data from an anonymous class. It is not good practice.

       final Map<Integer, ReceiptHdr> rcptHdr = jdbcTemplate.query("select h.rcpt_id, h.remark, d.det_id, d.comment from receipt_hdr h inner join receipt_det on h.rcpt_id = d.rcpt_id", new ResultSetExtractor<Map<Integer, ReceiptHdr>>() {
            public Map<Integer, ReceiptHdr> extractData(ResultSet rs) throws SQLException, DataAccessException {
                Map<Integer, ReceiptHdr> rcptHdr = new HashMap<Integer, ReceiptHdr>();
                while(rs.next()) {
                    Integer rcptId = rs.getInt("rcpt_id");
                    ReceiptHdr rcptHeader = (ReceiptHdr) rcptHdr.get(rcptId);
                    if (rcptHeader == null) {
                        String hdrRemark = rs.getString("remark");
                        rcptHeader = new ReceiptHdr();
                        rcptHeader.setRcptId(rcptId);
                        rcptHeader.setHdrRemark(hdrRemark);
                        rcptHdr.put(rcptId, rcptHeader);
                    }
                    ReceiptDet rcptDet = new ReceiptDet();
                    rcptDet.setRcptDetId(rs.getInt("det_id"));
                    rcptDet.setDeleteComment(rs.getString("comment"));
                    rcptHeader.getReceiptDets().add(rcptDet);
                }
                return rcptHdr;
            }
        });