I am trying to use Java with Spring Boot to get the output of a stored procedure in a database.
I want the method getBillInformation to return an ArrayList<BillInformation> object. I thought the SimpleJdbcCall.returningResultSet() method would do that in tandem with the RowMapper<T> interface.
When I call the SimpleJdbcCall.execute() method, I get a Map<String, Object> object back. The Object is of type ArrayList<BillInformation>, but I get unchecked errors at runtime. This method doesn't seem well equipped to handle multiple rows of output.
I have tried using the SimpleJdbcCall.executeObject() method as well, but whenever I've tried the code return jdbcCall.executeObject(ArrayList.class, callParams);, I've gotten a Null Pointer Error.
Can anyone help me get this method to cleanly return an ArrayList<BillInformation> containing the mapped results of my database stored procedure? Thanks!
@Service
public class BillInformationService {
@Autowired
DataSource dataSource;
public ArrayList<BillInformation> getBillInformation(Integer loadId, String trackingNumber) {
JdbcTemplate template = new JdbcTemplate(dataSource);
SimpleJdbcCall jdbcCall = new SimpleJdbcCall(template)
.withProcedureName("getBillInformationByLcIdAndTrackingNumber")
.returningResultSet("billInformation", (RowMapper<BillInformation>) (rs, rowNum) -> {
BillInformation billInformation = new BillInformation();
billInformation.setAccountNumber(rs.getString("ldc_acct"));
billInformation.setStartDate(rs.getDate("start_date").toLocalDate());
billInformation.setEndDate(rs.getDate("end_date").toLocalDate());
billInformation.setConsumption(rs.getInt("cons"));
billInformation.setTrackingNumber(rs.getString("tracking_no"));
billInformation.setLoadId(rs.getInt("lc_id"));
billInformation.setCrossReferenceNumber(rs.getString("xref_num"));
billInformation.setTransactionDate(rs.getDate("tran_date").toLocalDate());
billInformation.setReportMonth(rs.getDate("report_month").toLocalDate());
billInformation.setBillParty(rs.getString("bill_party"));
return billInformation;
});
System.out.println("CLASS: " + jdbcCall.getClass());
jdbcCall.addDeclaredParameter(new SqlParameter("lcId", Types.INTEGER));
jdbcCall.addDeclaredParameter(new SqlParameter("trackingNumber", Types.VARCHAR));
Map<String, String> callParams = new HashMap<>();
callParams.put("lcId", String.valueOf(loadId));
callParams.put("trackingNumber", trackingNumber);
return (ArrayList<BillInformation>) jdbcCall.execute(callParams).get("billInformation");
}
}
Text of stored procedure create statement (note that I truncated a few of the fields in the above text to shorten the post):
DELIMITER $$
CREATE DEFINER=`database`@`%` PROCEDURE `getBillingByLcIdAndTrackingNumber`(lcId INT,trackingNumber VARCHAR(30))
BEGIN
(
SELECT m.ldc_acct, m.rate_class, m.load_profile, m.start_date,
m.end_date, SUM(m.cons) as cons,
CASE WHEN tp.peak IS NULL THEN 'TOTAL' ELSE tp.peak END as season_map,
l.ldc_id,
n.nyiso_zone, m.tracking_no, m.lc_id, m.meas_id, m.xref_num,
(CASE WHEN mbt.rate_option = 'MHP' THEN true ELSE false END) AS is_hourly,
u.drop_end,
u.ldc_name,
mbt.rate_option,
u.iso_id,
m.tran_date,
mbt.report_month,
mbt.bill_party
FROM
load_tab l
JOIN (select m.m_id,m.ldc_acct, m.rate_class, m.load_profile, m.start_date, m.end_date, m.cons, m.ptd_code, m.tod,
m.tracking_no, m.lc_id, m.meas_id, m.xref_num,m.tran_date
from meter_usage m
WHERE
m.tracking_no = trackingNumber AND m.uom = 'KH' AND m.lc_id = lc_id AND m.lc_id = lcId
) as m ON l.lc_id = m.lc_id
JOIN nyiso_subzones n ON l.zone = n.zone_id
LEFT OUTER JOIN
fedata.tod_peak tp ON tp.tod = m.tod
join ldcs u on l.ldc_id = u.ldc_id
INNER JOIN
load_info li
ON li.lc_id = l.lc_id
LEFT OUTER JOIN
fedata.meter_bill_type mbt
ON mbt.tracking_no = m.tracking_no
AND mbt.lc_id = m.lc_id
where u.retail_rate in (1,2)
GROUP BY
m.ldc_acct, m.rate_class, m.load_profile, m.start_date,
m.end_date,m.tran_date,
CASE WHEN tp.peak IS NULL THEN 'TOTAL' ELSE tp.peak END,
l.ldc_id,
n.nyiso_zone, m.tracking_no, m.lc_id, m.meas_id, m.xref_num,
u.drop_end,
u.ldc_name,
u.iso_id,
mbt.report_month,
mbt.bill_party
ORDER BY m.meas_id,m.m_id
)
union
(
SELECT m.ldc_acct, m.rate_class, m.load_profile, m.start_date, m.end_date, m.cons, s.season_map, l.ldc_id,
n.nyiso_zone, m.tracking_no, m.lc_id, m.meas_id, m.xref_num,
(CASE WHEN mbt.rate_option = 'MHP' THEN true ELSE false END) AS is_hourly,
u.drop_end,
u.ldc_name,
mbt.rate_option,
u.iso_id,
m.tran_date,
mbt.report_month,
mbt.bill_party
FROM load_tab l
JOIN
(select m.m_id,m.ldc_acct, m.rate_class, m.load_profile, m.start_date, m.end_date, m.cons, m.ptd_code, m.tod,
m.tracking_no, m.lc_id, m.meas_id, m.xref_num,m.tran_date
from meter_usage m
WHERE
m.tracking_no = trackingNumber AND m.lc_id = lcId AND m.uom = 'KH' and m.meas_id IN ('00','01','52') and tod = 51 and meter_num = ''
) as m ON l.lc_id = m.lc_id
JOIN nyiso_subzones n ON l.zone = n.zone_id
JOIN season s ON (case m.tod when '' then m.ptd_code else m.tod end) = s.season_code
join ldcs u on l.ldc_id = u.ldc_id
INNER JOIN
load_info li
ON li.lc_id = l.lc_id
LEFT OUTER JOIN
fedata.meter_bill_type mbt
ON mbt.tracking_no = m.tracking_no
AND mbt.lc_id = m.lc_id
where u.retail_rate = 3
ORDER BY m.meas_id,m.m_id
)
UNION
(
SELECT m.ldc_acct, m.rate_class, m.load_profile, m.start_date,
m.end_date, SUM(m.cons) as cons,
NULL AS season_map,
l.ldc_id,
NULL as nyiso_zone,
m.tracking_no,
m.lc_id,
m.meas_id,
m.xref_num,
(CASE WHEN l.hist_type = 4 THEN true ELSE false END) AS is_hourly,
u.drop_end,
u.ldc_name,
mbt.rate_option,
u.iso_id,
m.tran_date,
mbt.report_month,
mbt.bill_party
FROM
load_tab l
JOIN (select m.m_id,m.ldc_acct, m.rate_class, m.load_profile, m.start_date, m.end_date, m.cons, m.ptd_code, m.tod,
m.tracking_no, m.lc_id, m.meas_id, m.xref_num,m.tran_date
from meter_usage m
WHERE
m.uom = 'KH'
AND m.meas_id IN ('00','01','52')
AND m.tod_desc != 'I'
AND m.tod = '51'
AND m.tracking_no = trackingNumber
AND m.lc_id = lcId
) as m ON l.lc_id = m.lc_id
join ldcs u on l.ldc_id = u.ldc_id
INNER JOIN
load_info li
ON li.lc_id = l.lc_id
LEFT OUTER JOIN
fedata.meter_bill_type mbt
ON mbt.tracking_no = m.tracking_no
AND mbt.lc_id = m.lc_id
where u.iso_id = 2
GROUP BY
m.ldc_acct, m.rate_class, m.load_profile, m.start_date,
m.end_date,
l.ldc_id,
m.tracking_no, m.lc_id, m.meas_id, m.xref_num,
u.drop_end,
u.ldc_name,
u.iso_id,
m.tran_date,
mbt.report_month,
mbt.bill_party
ORDER BY m.m_id
)
ORDER BY
meas_id,
tracking_no;
END$$
DELIMITER ;
