Spring Integration Jdbc OutboundGateway returning 1 record ONLY even with MaxRows(0)

Question

Have an application which has some 30,000+ records in a table and for an analytic use-case we need to fetch all and keep iterating over the returned result for some computation. However, the Jdbc OutboundGateway is returning ONLY 1 record even with MaxRows(0) though there are 30,000+ records in the DB. The same returns n number of records as a List when we explicitly set the MaxRows() with a non-zero value.

Please share how this can be made to return all rows with MaxRows(0)?

Answer 1

That's probably how your JDBC driver works or how your RDBMS is configured for maxRows.

The logic there in JdbcOutboundGateway is like this:

    if (this.maxRows != null) {
        Assert.notNull(this.poller, "If you want to set 'maxRows', then you must provide a 'selectQuery'.");
        this.poller.setMaxRows(this.maxRows);
    }

where that JdbcPollingChannelAdapter has this logic:

1. By default it is private int maxRows = 0;

2.  return new PreparedStatementCreatorWithMaxRows(preparedStatementCreator,
                        JdbcPollingChannelAdapter.this.maxRows);
   

3. And that one:

        public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
            PreparedStatement preparedStatement = this.delegate.createPreparedStatement(con);
            preparedStatement.setMaxRows(this.maxRows); // We can't mutate provided JdbOperations for this option
            return preparedStatement;
        }
   

4. Then PreparedStatement:

         /**
     * Sets the limit for the maximum number of rows that any
     * {@code ResultSet} object  generated by this {@code Statement}
     * object can contain to the given number.
     * If the limit is exceeded, the excess
     * rows are silently dropped.
     *
     * @param max the new max rows limit; zero means there is no limit
     * @throws SQLException if a database access error occurs,
     * this method is called on a closed {@code Statement}
     *            or the condition {@code max >= 0} is not satisfied
     * @see #getMaxRows
     */
    void setMaxRows(int max) throws SQLException;
   

zero means there is no limit

The logic in the JdbcOutboundGateway in the end is like this:

if (list.size() == 1 && (this.maxRows == null || this.maxRows == 1)) {
        payload = list.get(0);
}

So, we return one record only if ResultSet has only one element.

I doubt we can do anything from Spring Integration perspective, unless you want to try with an Integer.MAX_VALUE for this property since your JDBC communication does not honor PreparedStatement.setMaxRows() contract.