[FIXED] How to execute join query in spring batch item reader

Issue

I have created program that is taking data from database and write them to csv file but I can't execute sql join query inline jdbcCursorItemReader.setSql() while my query is ok and when I'm running it in sql console it's ok but when I'm ruuning this query in spring batch I get Column 'id' not found. error

my query is :

SELECT iad.order_id, tb.mch_order_id, iad.transaction_time, iad.pay_amount, iad.fee_rate, iad.net_amount
FROM ibg_account_detail iad INNER JOIN ibg_account ia ON iad.ibg_account_id = ia.id
INNER JOIN ibg_settlement is2 ON ia.ibg_settlement_id = is2.id
INNER JOIN tb_bill tb ON iad.order_id = tb.order_id
WHERE is2.created_date >= '2022-05-20 00:00:00' AND is2.created_date <= '2022-05-20 23:59:59'
AND ia.account = '15088888888';

and my ItemReader function is :

public JdbcCursorItemReader<IbgAccountDetailEntity> jdbcCursorItemReader(){
        JdbcCursorItemReader<IbgAccountDetailEntity> jdbcCursorItemReader = new JdbcCursorItemReader<>();
        jdbcCursorItemReader.setDataSource(dataSource);
        jdbcCursorItemReader.setSql(GetEntityFromQuery.SELECT_FILTER_QUERY);
        jdbcCursorItemReader.setRowMapper(mapper);
        return jdbcCursorItemReader;
    }

and it's my mapper:

public IbgAccountDetailEntity mapRow(ResultSet resultSet, int rowNum) throws SQLException {
        IbgAccountDetailEntity ibgAccountDetail = new IbgAccountDetailEntity();
        ibgAccountDetail.setId(resultSet.getLong("id"));
        ibgAccountDetail.setOrderId(resultSet.getString("order_id"));
        ibgAccountDetail.setTransactionTime(resultSet.getTimestamp("transaction_time"));
        ibgAccountDetail.setPayAmount(resultSet.getBigDecimal("pay_amount"));
        ibgAccountDetail.setFeeRate(resultSet.getBigDecimal("fee_rate"));
        ibgAccountDetail.setNetAmount(resultSet.getBigDecimal("net_amount"));

        return ibgAccountDetail;
    }

in my mapper I should have other column from othet table join How can I do it also?

Solution

Your are get a column named id here:

ibgAccountDetail.setId(resultSet.getLong("id"));

But your SQL query does not return a column with this name:

SELECT iad.order_id, tb.mch_order_id, iad.transaction_time, iad.pay_amount, iad.fee_rate, iad.net_amount

There no column named id in your resultSet Try change your query to:

SELECT ia.id, iad.order_id, tb.mch_order_id, iad.transaction_time, iad.pay_amount, iad.fee_rate, iad.net_amount

Answered By - Guilherme
Answer Checked By - Pedro (JavaFixing Volunteer)