Add support for queries returning Page and Slice

[spring-projects-issues]

hwolf opened DATAJDBC-554 and commented

I have a JDBC respository with a method 

findAllByToken2(String token2, Pageable pageable)

When invoking this methid I get an exception

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

	at org.springframework.dao.support.DataAccessUtils.nullableSingleResult(DataAccessUtils.java:100)
	at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.queryForObject(NamedParameterJdbcTemplate.java:237)
	at org.springframework.data.jdbc.repository.query.AbstractJdbcQuery.lambda$singleObjectQuery$1(AbstractJdbcQuery.java:115)
	at org.springframework.data.jdbc.repository.query.PartTreeJdbcQuery.execute(PartTreeJdbcQuery.java:98)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor$QueryMethodInvoker.invoke(QueryExecutorMethodInterceptor.java:195)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:152)
	at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:130)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:366)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
	at com.sun.proxy.$Proxy71.findAllByToken2(Unknown Source)
	at xx.DeviceRepositoryTest.test3(DeviceRepositoryTest.java:59)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:686)
	at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)
	at org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:149)
	at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:140)
	at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:84)
	at org.junit.jupiter.engine.execution.ExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(ExecutableInvoker.java:115)
	at org.junit.jupiter.engine.execution.ExecutableInvoker.lambda$invoke$0(ExecutableInvoker.java:105)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45)
	at org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37)
	at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:104)
	at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:98)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$6(TestMethodTestDescriptor.java:212)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:208)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:137)
	at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:71)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:135)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
	at org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
	at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
	at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)

I exspected either

• an error during creation of the repository, that this feature is not supported or
• that this feature is implemented as for Spring Data JPA

Do you habe a hint how I can fix it ?

---

Affects: 2.0 GA (Neumann)

Reference URL: https://gitlab.com/hwolf/xx

6 votes, 8 watchers

[spring-projects-issues]

Mark Paluch commented

We currently support only pure collection and single-object queries. Query methods returning slices and Page objects are not yet supported

[spring-projects-issues]

Dirk Luijk commented

Will this be supported in a future version of Spring Data JDBC?

[spring-projects-issues]

kremerkp commented

is there a workaround to get a filtered, pageable object based on a queried List? 

For Example, I got following query that provides me a list of Customers.
    @Query("select * from person where firstname = :firstname")
    List<Customer> findByFirstname(@Param("firstname") String firstname, Pageable page);
I then use
Page<Customer> test = new PageImpl<Customer>(custList, pageable, totSize);
but to result of Page<Customer> is not filtered.

 

[spring-projects-issues]

Dirk Luijk commented

kremerkp: my workaround was like this, and it worked fine:

interface FooRepository extends PagingAndSortingRepository<FooEntity, Long> {
    List<FooEntity> findAllByBar(String bar, Pageable pageable);
    Long countAllByBar(String bar);
}

And then combining those 2 queries like this:

List<FooEntity> fooList = repository.findAllByBar("...", pageable);
Long fooTotalCount = repository.countAllByBar("...");

Page<FooEntity> fooPage = PageableExecutionUtils.getPage(fooList, pageable, () -> fooTotalCount);

 

[spring-projects-issues]

Dirk Luijk commented

kremerkp: the mistake in your workaround is your custom query. In Spring Data JDBC 2.0 you don't need to use that, except for special queries but they won't support pageables

[spring-projects-issues]

kremerkp commented

Thx Dirk, that helps

[spring-projects-issues]

Mark Paluch commented

PageableExecutionUtils are in place to optimize for count queries, hence the supplier for count. See the MongoDB code for further reference.

We want to include such a feature in a future release, submitting a pull request would actually help in getting support for returning Page/Slice sooner

[hc24]

Any update about such a feature? I found there are examples of query methods returning slices and Page objects in reference documentation. But it doesn't work. It confuses me. I think it an important feature. Using PageableExecutionUtils is troublesome.

[RasAlhague]

+1
Feature is documented but does not work (2.1.5)