java - 如何从 Spring Boot SimpleJdbcCall 对象中提取数据？

Question

我正在尝试将 Java 与 Spring Boot 一起使用来获取数据库中存储过程的输出。

我希望该方法getBillInformation返回一个ArrayList<BillInformation>对象。我认为该SimpleJdbcCall.returningResultSet()方法会与RowMapper<T>界面一起执行此操作。

当我调用该SimpleJdbcCall.execute()方法时，我得到一个Map<String, Object>对象。是Objecttype ArrayList<BillInformation>，但我unchecked在运行时遇到错误。这种方法似乎不能很好地处理多行输出。

我也尝试过使用该SimpleJdbcCall.executeObject()方法，但是每当我尝试过代码return jdbcCall.executeObject(ArrayList.class, callParams);时，我都会得到一个Null Pointer Error.

谁能帮我让这个方法干净地返回一个ArrayList<BillInformation>包含我的数据库存储过程的映射结果？谢谢！

@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");
    }
}

存储过程创建语句的文本（请注意，我截断了上述文本中的一些字段以缩短帖子）：

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 ;

标签： javaspring-bootstored-proceduressimplejdbccall