1

Hi I would like to stream a very large table spring-data-jdbc. For this purpose I have set my connection to READ_ONLY I have declared in my repository a method that looks in the following way:

PackageRepository extends Repository<Package,String> {

Stream<Package> findAll();

}

My expectation here would be that the resultset would be of type FORWARD_ONLY and this method will not block indefinatly untill all results are recieved from the database.

Here I would make a comparison with Spring Data JPA where the Stream methods are not blocking and the content of the database is fetched in portions depending on the fetch size.

Have I missed some configuration ? How can I achieve this behaviour with spring-data-jdbc ?

UPDATE: I will put the question in a different form. How can I achieve with spring-data-jdbs the equivalent of:

template.query(new PreparedStatementCreator() {

            @Override
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                PreparedStatement statement =  con.prepareStatement("select * from MYTABLE with UR",ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
                statement.setFetchSize(150000);
                return statement;
            }
        }, new RowCallbackHandler() {

            @Override
            public void processRow(ResultSet rs) throws SQLException {

                // do my processing here
            }
        });
Alexander Petrov
  • 9,204
  • 31
  • 70
  • Judging from the source code it seems as cursors are not supported yet. I noticed that for findAll method in the MyBatisDataAccessStrategy it has been used sqlSession().selectList() shoudn't be used a cursor here ? SqlSession.selectCursor​ ? – Alexander Petrov Apr 06 '19 at 23:02
  • I think I got it, basicly I need to define my own DefaultDataAccessStrategy. If I have got it right. – Alexander Petrov Apr 06 '19 at 23:31
  • 1
    You are right, this isn't supported yet, but I like the idea. And I see you already created a ticket: https://jira.spring.io/browse/DATAJDBC-356 Thanks for that. – Jens Schauder Apr 08 '19 at 15:13
  • I started working on a solution for this issue and added some comments on the Jira issue. – Marcos Vinícius da Silva Oct 19 '19 at 15:05

1 Answers1

0

Just adding thesetFetchSize(Integer.MIN_VALUE) before querying, the queryForStream indeed gives us a stream which load records one by one rather than eagerly load all records into memroy in one shot.

namedTemplate.getJdbcTemplate().setFetchSize(Integer.MIN_VALUE);
Stream<LargeEntity> entities = namedTemplate.queryForStream(sql, params, rowMapper);

dependencies:

spring framework 5.3+
mysql-connector-java 8.0.x (or mariadb-java-client 2.7.x)
Iceberg
  • 2,744
  • 19
  • 19