How to execute join query in spring batch item reader

Question

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?

Guilherme · Accepted Answer · 2022-06-17 17:32:24Z

1

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 Jun 17, 2022 at 17:32

Guilherme

9159 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to execute join query in spring batch item reader

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related