You cannot use Pageable as method parameter if your persistence provider cannot extra

[mappy]

Hi Im trying use the Paging feature of Spring Data and I get the following exception when I create a methods such as

Page<User> findByLastName(String lastName, Pageable pageable);

I have other queries (non paging) which work fine

If I comment out the above method then the code in unit tests works fine, but
soon as I start to use paging I get the error below.

Caused by: java.lang.IllegalArgumentException: You cannot use Pageable as method parameter if your persistence provider cannot extract queries!

Im using STS, Spring Data, Hibernate, DB2

My user Repoistory is defined as

public interface UserRepository extends JpaRepository<User, Long> {

User findByUserName(String userName);

Page<User> findByLastName(String lastName, Pageable pageable);
}

Peristence.xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" version="2.0" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd">
<persistence-unit name="userPersistenceUnit" transaction-type="RESOURCE_LOCAL">
<provider>org.hibernate.ejb.HibernatePersistence </provider>
<properties>
<property name="hibernate.dialect" value="org.hibernate.dialect.DB2Dialect"/>
<!-- value="create" to build a new database on each run; value="update" to modify an existing database; value="create-drop" means the same as "create" but also drops tables when Hibernate closes; value="validate" makes no changes to the database -->
<property name="hibernate.hbm2ddl.auto" value="validate"/>
<property name="hibernate.ejb.naming_strategy" value="org.hibernate.cfg.ImprovedNamingStrategy"/>
<!-- Uncomment the following two properties for JBoss only -->
<!-- property name="hibernate.validator.apply_to_ddl" value="false" /-->
<!-- property name="hibernate.validator.autoregister_listeners" value="false" /-->
</properties>
</persistence-unit>
</persistence>


Error..

... 40 more
Caused by: java.lang.IllegalArgumentException: You cannot use Pageable as method parameter if your persistence provider cannot extract queries!
at org.springframework.data.jpa.repository.query.JpaQ ueryMethod.<init>(JpaQueryMethod.java:80)
at org.springframework.data.jpa.repository.query.JpaQ ueryLookupStrategy$AbstractQueryLookupStrategy.res olveQuery(JpaQueryLookupStrategy.java:72)
at org.springframework.data.repository.support.Reposi toryFactorySupport$QueryExecuterMethodInterceptor. <init>(RepositoryFactorySupport.java:367)
at org.springframework.data.repository.support.Reposi toryFactorySupport.getRepository(RepositoryFactory Support.java:131)
at org.springframework.data.repository.support.Reposi toryFactoryBeanSupport.getObject(RepositoryFactory BeanSupport.java:89)
at org.springframework.data.repository.support.Reposi toryFactoryBeanSupport.getObject(RepositoryFactory BeanSupport.java:37)
at org.springframework.beans.factory.support.FactoryB eanRegistrySupport.doGetObjectFromFactoryBean(Fact oryBeanRegistrySupport.java:142)
... 48 more

[mappy]

Changed in the app context

From

<bean id="entityManagerFactory"
class="org.springframework.orm.jpa.LocalContainerE ntityManagerFactoryBean"
p:dataSource-ref="dataSource" />


To


<bean id="entityManagerFactory" class="org.springframework.orm.jpa.LocalContainerE ntityManagerFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="jpaVendorAdapter">
<bean class="org.springframework.orm.jpa.vendor.Hibernat eJpaVendorAdapter" />
</property>
</bean>


Phew !

[Oliver Gierke]

It seems you were struck by this one… https://jira.springsource.org/browse/SPR-7752. I'll see if I can get this pushed a bit more.

Cheers,
Ollie

[christoph.e]

hi,
is there any chance that this will be supported in future releases?
i'm currently testing spring-data-jpa with objectdb(.com). already found many pitfalls

i'm currently using a custom implementation as extension for the automatic repository to implement pageable queries. so i basically had to copy some code from SimpleJpaRepository, which can not be extended easily (should have more protected members and methods ).

the fact that PersistenceProvider is an enum makes it also impossible to add a custom provider implementation.

i have not looked at the complete code, but i'm not sure why "canExtractQuery" is really needed for pageable/sortable queries ... but i guess there is a good reason for it

anyway, let me know what you think.
thanks

[Oliver Gierke]

As the amount of JPA persistence providers is not something growing very fast, we haven't considered extensibility a key thing at that place. We support any JPA provider in general (through GENERIC_JPA enum). We run a huge chunk of our integration tests against all major JPA implementations (Hibernate, EclipseLink, OpenJPA). As most of these providers ship with their strengths, weaknesses and bugs we have to workaround, adding fully fledged support for an additional persistence provider would probably need some tweaks to our PersistenceProvider interface anyway which raises the question whether it's even feasible to try provide user extensibility here.

Long story short: if you feel, there's a custom provider out there we should integrate with, feel free to open a JIRA and I'll happily take a look. I spent some minutes browsing the objectdb.com website but was unable to find some javadoc of it to look for a custom EntityManager interface, the query implementation and the like.

Now why do we actually need canExtractQuery()? If you're using @Query annotation or simply derive the query from the method name, coming up with the count query necessary for pagination is not a big deal. However if you're using named queries everything we get is a JPA Query object that we cannot derive a count query from unless we can extract the String query from it. JPA 2 does not allow that (which might change for 2.1) so we cast the Query object to the provider specific Query type, which we can only derive from a inspecting the EntityManager.

I think we can do two things to improve the experience here. We could defer the error message until we actually discover a named query to be used. Beyond that I think the error message we give right now only makes sense if you know about the internals, so we should give hints which steps to take actually (use @Query and/or explicitly set a JpaDialect on LCEMFB). Would you mind opening two JIRA issues for that?

[christoph.e]

As the amount of JPA persistence providers is not something growing very fast, we haven't considered extensibility a key thing at that place. We support any JPA provider in general (through GENERIC_JPA enum). We run a huge chunk of our integration tests against all major JPA implementations (Hibernate, EclipseLink, OpenJPA). As most of these providers ship with their strengths, weaknesses and bugs we have to workaround, adding fully fledged support for an additional persistence provider would probably need some tweaks to our PersistenceProvider interface anyway which raises the question whether it's even feasible to try provide user extensibility here.

yes, this makes sense

Long story short: if you feel, there's a custom provider out there we should integrate with, feel free to open a JIRA and I'll happily take a look. I spent some minutes browsing the objectdb.com website but was unable to find some javadoc of it to look for a custom EntityManager interface, the query implementation and the like.

the documentation is quit good for a user point of view, i don't know if it's enough to integrate it into spring-jpa-data, but what i can tell, the support is very active and responses quickly to forum threads and issue reports. one big disadvantage, it's an closed source project :-/

Now why do we actually need canExtractQuery()? If you're using @Query annotation or simply derive the query from the method name, coming up with the count query necessary for pagination is not a big deal. However if you're using named queries everything we get is a JPA Query object that we cannot derive a count query from unless we can extract the String query from it. JPA 2 does not allow that (which might change for 2.1) so we cast the Query object to the provider specific Query type, which we can only derive from a inspecting the EntityManager.

i prefered jpa's @NamedQuery annotation, but i could try with springs @Query.
maybe this fixes another problem i had with namedqueries, it was not possible to have (max, count) queries returning f.e. Long.

I think we can do two things to improve the experience here. We could defer the error message until we actually discover a named query to be used. Beyond that I think the error message we give right now only makes sense if you know about the internals, so we should give hints which steps to take actually (use @Query and/or explicitly set a JpaDialect on LCEMFB). Would you mind opening two JIRA issues for that?

https://jira.springsource.org/browse/DATAJPA-59
https://jira.springsource.org/browse/DATAJPA-60

i hope that's ok.

thanks

[christoph.e]

fyi, switching to snapshot and using @Query fixed all my problems.
great work, thanks

also thanks for resolving the jira issues

[Oliver Gierke]

You're welcome… thx for taking the time to report the issues .