Large list returned from a SimpleJdbcTemplate query

here is my problem : at some point in my Java program, I get a (very) big List of Events from a database using the SimpleJdbcTemplate class from Spring.

List<Event> events = 
            this.simpleJdbcTemplate.query(myQuery,
            myMapper(), 
            new Object[] {
                    filter.getFirst(),
                    filter.getSecond(),
                    filter.getThird()}
            );

The problem is that the list may contain something like 600,000 Events ... Therefore using a lot of memory (and also taking time to be processed).

However I don't really need to retrieve all the Events at once. Actually I would like to be able to iterate over the list, read only a few events (linked to a specific KEY_ID - the sql query myQuery is ordered by KEY_ID), process them and finally get back iterating, letting开发者_如何学Python the garbage collector get rid of the previous and already processed Events so that I never exceed a certain amount of memory.

Is there a nice way to do so using the Spring library (or any library)?

Cheers, Vakimshaar.

---

I think part of your problem is that your query is executing all at once and you're getting the result set in a big lump that hogs memory and network bandwidth. In addition to needing a way to iterate through the result set you need a way to get the results back from the database a bit at a time. Take a look at this answer about lazy-loading resultsets. It looks like you could set the fetch size in combination with using a ResultSetExtractor and possibly get the desired behavior (depending on the database).

---

You should construct your SQL query to return limited set of items starting with particular number. It is database specific operation (in Oracle and MySql you will manipulate rownum in some form) . Then you repeat the call increasing start number until all elements are processed.

Oracle example

SELECT * FROM ([your query]) WHERE rownum>=[start number] 
                               AND rownum<[start number + chunk size];

---

If I understand correctly, you would like to iterate over the result set, but are not interested in building the full list of results.

Just use the query method with a ResultSetExtractor as argument. The ResultSetExtractor can use your mapper to transform the current row into an Event. Put every event into a list until you reach a different KEY_ID or the end of the result set, then proceed with your list of events and clear the list.

---

Maybe the following code might be useful for you?

protected <T> List<T> queryPagingList(final String query, final PagingQueryContext context, final ParameterizedRowMapper<T> mapper, final SqlParameter... parameters) throws DataAccessException {
    final Integer count = context.getCount();
    final Integer beginIndex = context.getBeginIndex();
    final List<SqlParameter> parameterList = Arrays.asList(parameters);
    final PreparedStatementCreatorFactory preparedStatementCreatorFactory = new PreparedStatementCreatorFactory(query, parameterList);
    preparedStatementCreatorFactory.setResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE);
    preparedStatementCreatorFactory.setNativeJdbcExtractor(new NativeJdbcExtractorAdapter() {
        @Override
        public PreparedStatement getNativePreparedStatement(final PreparedStatement ps) throws SQLException {
            ps.setFetchSize(count + 1);
            ps.setMaxRows((beginIndex * count) + 1);
            return ps;
        }

        @Override
        public Statement getNativeStatement(final Statement stmt) throws SQLException {
            stmt.setFetchSize(count + 1);
            stmt.setMaxRows((beginIndex * count) + 1);
            return stmt;
        }
    });
    final PreparedStatementCreator psc = preparedStatementCreatorFactory.newPreparedStatementCreator(parameterList);
    final ResultSetExtractor<List<T>> rse = new ResultSetExtractor<List<T>>() {
        public List<T> extractData(final ResultSet rs) throws SQLException {
            if (count > 0) {
                rs.setFetchSize(count + 1);
                if (beginIndex > 0) {
                    rs.absolute((beginIndex - 1) * count);
                }
            }
            rs.setFetchDirection(ResultSet.FETCH_FORWARD);
            final List<T> results = new ArrayList<T>(count + 1);
            for (int rowNumber = 0; rs.next(); ++rowNumber) {
                if (count > 0 && rowNumber > count) {
                    break;
                }
                results.add(mapper.mapRow(rs, rowNumber));
                rs.last();
                context.setTotalResults(rs.getRow());
           }
            return results;
        }
    };
    return this.simpleJbcTemplate.query(psc, null, rse);
}

Here is PagingQueryContext:

public class PagingQueryContext implements Serializable {
    private static final long serialVersionUID = -1887527330745224117L;

    private Integer beginIndex = 0;
    private Integer count = -1;
    private Integer totalResults = -1;

    public PagingQueryContext() {
    }

    public Integer getBeginIndex() {
        return beginIndex;
    }

    public void setBeginIndex(final Integer beginIndex) {
        this.beginIndex = beginIndex;
    }

    public Integer getCount() {
        return count;
    }

    public void setCount(final Integer count) {
        this.count = count;
    }

    public Integer getTotalResults() {
        return totalResults;
    }

    public void setTotalResults(final Integer totalResults) {
        this.totalResults = totalResults;
    }

    @Override
    public int hashCode() {
        final int prime = 31;
        int result = 1;
        result = prime * result + ((beginIndex == null) ? 0 : beginIndex.hashCode());
        result = prime * result + ((count == null) ? 0 : count.hashCode());
        return result;
    }

    @Override
    public boolean equals(final Object obj) {
        if (this == obj) {
            return true;
        }
        if (obj == null) {
            return false;
        }
        if (!(obj instanceof PagingQueryContext)) {
            return false;
        }
        final PagingQueryContext other = (PagingQueryContext) obj;
        if (beginIndex == null) {
            if (other.beginIndex != null) {
                return false;
            }
        } else if (!beginIndex.equals(other.beginIndex)) {
            return false;
        }
        if (count == null) {
            if (other.count != null) {
                return false;
            }
        } else if (!count.equals(other.count)) {
            return false;
        }
        return true;
    }

}

It adds one to the fetch size so that you can peek to see if there will be more results. Also, depending on how the JDBC driver you are using implements rs.last(), you might not want to use that call in the ResultSetExtractor and forgo using totalRows. Some drivers might load all of the data when last() is invoked.