Question: How to properly use WindowIterator with KeySetScrollPosition

[drakiula]

Hi,

I will post a reproducer of the error. (I have checked out the 3.3.4 tag of the spring-data-relational project.

Add the following query method to org.springframework.data.jdbc.repository.JdbcRepositoryIntegrationTests.DummyEntityRepository

Window<DummyEntity> findFirst2ByIdPropGreaterThanOrderByName(Long idProp, ScrollPosition position);

and the following test method to org.springframework.data.jdbc.repository.JdbcRepositoryIntegrationTests

@Test
void findByKeysetPosition() {
    DummyEntity one = new DummyEntity("one");
    one.setFlag(true);

    DummyEntity two = new DummyEntity("two");
    two.setFlag(true);

    DummyEntity three = new DummyEntity("three");
    three.setFlag(true);

    DummyEntity four = new DummyEntity("four");
    four.setFlag(false);

    repository.saveAll(Arrays.asList(one, two, three, four));

    WindowIterator<DummyEntity> iteratorK = WindowIterator.of(
                    position ->
                            repository.findFirst2ByIdPropGreaterThanOrderByName(1L, position)
            )
            .startingAt(ScrollPosition.keyset());

    List<DummyEntity> lsEnt = new ArrayList<>();

    iteratorK.forEachRemaining(lsEnt::add);

    System.out.println(lsEnt);
}

When running the test, the following exception is thrown, I might be misunderstanding how to use WindowIterator and keyset properly.

Thank you in advance for your help.

org.springframework.dao.IncorrectResultSizeDataAccessException: Incorrect result size: expected 1, actual 2

	at org.springframework.dao.support.DataAccessUtils.nullableSingleResult(DataAccessUtils.java:193)
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForObject(NamedParameterJdbcTemplate.java:253)
	at org.springframework.data.jdbc.repository.query.AbstractJdbcQuery.lambda$singleObjectQuery$2(AbstractJdbcQuery.java:131)
	at org.springframework.data.jdbc.repository.query.PartTreeJdbcQuery.execute(PartTreeJdbcQuery.java:134)
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:170)
	at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:158)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:169)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:148)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.data.repository.core.support.MethodInvocationValidator.invoke(MethodInvocationValidator.java:95)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:223)
	at org.springframework.data.jdbc.repository.$Proxy102.findFirst2ByIdPropGreaterThanOrderByName(Unknown Source)
	at org.springframework.data.jdbc.repository.JdbcRepositoryIntegrationTests.lambda$findByKeysetPosition$15(JdbcRepositoryIntegrationTests.java:1169)
	at org.springframework.data.support.WindowIterator.hasNext(WindowIterator.java:81)
	at java.base/java.util.Iterator.forEachRemaining(Iterator.java:132)
	at org.springframework.data.jdbc.repository.JdbcRepositoryIntegrationTests.findByKeysetPosition(JdbcRepositoryIntegrationTests.java:1175)

[mp911de]

Keyset-scrolling isn't supported yet, see #1605

[drakiula]

@mp911de Is the team considering adding support for keyset-scrolling in Spring Data JDBC?

As per this blog post, this is supported with Spring Data JPA.

Thank you for your answer.

[mp911de]

We do, see #1605 for tracking progress. We just don't know when it will happen. It will also have a restriction on working for derived queries only as we do not want to get into SQL parsing.