Issue with Spring Data JPA positional parameters

[marceloverdijk]

I'm having an issue with Spring Data JPA positional parameters on Google AppEngine.
The basic infrastructure with repositories works nicely btw.

public interface UserRepository extends JpaRepository<User, Long>, JpaSpecificationExecutor<User> {

	User findByUsername(String username);
	
	Page<User> findByUsernameStartingWith(String username, Pageable pageable);
}

Calling above defined findByUsernameStartingWith method causes the following exception:

Numbered parameter syntax starting ? but isnt followed by numeric!; nested exception is javax.persistence.PersistenceException: Numbered parameter syntax starting ? but isnt followed by numeric!

So it seems the queries contain ? but not something like ?1


Is this true how Spring Data JPA works?


I found this related ticket on GAE http://code.google.com/p/googleappen...detail?id=1724 which states that positional parameters need position indication.


Any idead?

[Oliver Gierke]

Could you please post the entire stack trace? We're not using string query building at all actually, so I guess there's a manually defined query involved somewhere, maybe through some named query?

[marceloverdijk]

This is the whole stack trace I get:

Numbered parameter syntax starting ? but isnt followed by numeric!; nested exception is javax.persistence.PersistenceException: Numbered parameter syntax starting ? but isnt followed by numeric!

org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:326)
org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:403)
org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:58)
org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213)
org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:163)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
org.springframework.data.jpa.repository.support.LockModeRepositoryPostProcessor$LockModePopulatingMethodIntercceptor.invoke(LockModeRepositoryPostProcessor.java:91)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:90)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
java.lang.reflect.Method.invoke(Method.java:597)
org.zeroturnaround.javarebel.integration.util.ReloadingProxyFactory$ReloadingMethodHandler.invoke(JRebel:73)
nl.valid.minerva.web.UserController.list(UserController.java:29)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
java.lang.reflect.Method.invoke(Method.java:597)
com.google.appengine.tools.development.agent.runtime.Runtime.invoke(Runtime.java:115)
org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219)
org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132)
org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:717)
org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:660)
org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925)
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856)
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:915)
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:804)
javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:789)
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:511)
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1166)
org.springframework.orm.jpa.support.OpenEntityManagerInViewFilter.doFilterInternal(OpenEntityManagerInViewFilter.java:170)
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:90)
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:330)
org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118)
org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:113)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:150)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:183)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:192)
org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:160)
org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:259)

[marceloverdijk]

Continued:

org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:77)
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:90)
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:90)
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
com.google.appengine.api.socket.dev.DevSocketFilter.doFilter(DevSocketFilter.java:74)
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
com.google.appengine.tools.development.ResponseRewriterFilter.doFilter(ResponseRewriterFilter.java:110)
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
com.google.appengine.tools.development.HeaderVerificationFilter.doFilter(HeaderVerificationFilter.java:34)
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
com.google.appengine.api.blobstore.dev.ServeBlobFilter.doFilter(ServeBlobFilter.java:61)
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
com.google.apphosting.utils.servlet.TransactionCleanupFilter.doFilter(TransactionCleanupFilter.java:43)
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
com.google.appengine.tools.development.StaticFileFilter.doFilter(StaticFileFilter.java:125)
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
com.google.appengine.tools.development.BackendServersFilter.doFilter(BackendServersFilter.java:97)
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1157)
org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:388)
org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:216)
org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:182)
org.mortbay.jetty.handler.ContextHandler.__handle(ContextHandler.java:765)
org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java)
org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:418)
com.google.appengine.tools.development.DevAppEngineWebAppContext.handle(DevAppEngineWebAppContext.java:94)
org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
com.google.appengine.tools.development.JettyContainerService$ApiProxyHandler.handle(JettyContainerService.java:380)
org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
org.mortbay.jetty.Server.handle(Server.java:326)
org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:542)
org.mortbay.jetty.HttpConnection$RequestHandler.headerComplete(HttpConnection.java:923)
org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:547)
org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:212)
org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404)
org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:409)
org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:582)

UserController.java:29 calls:
Page<User> page = userRepository.findByUsernameStartingWith(q, pageable);

which is defined in the UserRepository as:
Page<User> findByUsernameStartingWith(String username, Pageable pageable);

I have no named query or @Query nowhere in my codebase.

Note that I'm running this locally on Google App Engine infrastructure which also uses the DataNucleus enhancer. So I'm trying to figure out if it's a problem with Spring Data or the App Engine environment.

Note that enabled log4j logging using:
log4j.category.org.springframework.data = DEBUG

but I don't see anything. I was trying to log the actual query being executed by Spring Data.

[marceloverdijk]

I also performed similar query using the entity manager like:

uery query = em.createQuery("select from " + User.class.getName() + " user where user.username like ?1");
query.setParameter(1, q + "%");
List<User> users = query.getResultList();

and this works perfectly.

The DataNucleus logging displays:

09:20:17,867 DEBUG [DataNucleus.Query] - JPQL Single-String with "select from nl.valid.minerva.domain.User user where user.username like ?1"
09:20:17,868 DEBUG [DataNucleus.Query] - Query "SELECT FROM nl.valid.minerva.domain.User user WHERE user.username like ?1" of language "JPQL" has been run before so reusing existing generic compilation
09:20:17,869 DEBUG [DataNucleus.Query] - JPQL Query : Executing "SELECT FROM nl.valid.minerva.domain.User user WHERE user.username like ?1" ...
09:20:17,869 DEBUG [DataNucleus.Query] - Query compiled as : Kind=nl.valid.minerva.domain.User Filter : username>=ad AND username<ae
09:20:17,870 DEBUG [DataNucleus.Datastore.Native] - Executing query in datastore for SELECT FROM nl.valid.minerva.domain.User user WHERE user.username like ?1

When I check the logging for the UserRepository Page<User> findByUsernameStartingWith(String username, Pageable pageable) method I see:

09:24:10,475 DEBUG [DataNucleus.Query] - JPQL Single-String with "SELECT DN_THIS FROM nl.valid.minerva.domain.User DN_THIS WHERE DN_THIS.username LIKE ?-1ORDER BY DN_THIS.username ASC"
09:24:10,477 DEBUG [DataNucleus.Query] - QueryCompilation:
  [from:ClassExpression(alias=DN_THIS)]
  [filter:InvokeExpression{[PrimaryExpression{DN_THIS.username}].matches(ParameterExpression{null})}]
  [ordering:OrderExpression{PrimaryExpression{DN_THIS.username} ascending}]
  [symbols: null type=unknown, DN_THIS type=nl.valid.minerva.domain.User]
09:24:10,482 DEBUG [DataNucleus.Query] - JPQL Query : Compiling "SELECT FROM nl.valid.minerva.domain.User DN_THIS WHERE DN_THIS.username LIKE ?-1ORDER BY DN_THIS.username ASC"

Note the - (minus) between the ? and the positional parameter index.
I can imagine this causes the exception "Numbered parameter syntax starting ? but isnt followed by numeric", but I'm still not use what the origin is.

[Oliver Gierke]

It might make sense to involve the DataNucleus team at this point. We're not dealing with positional parameters for derived queries at all, essentially calling CriteriaBuilder.like(pathExpression, stringExpression) and later on binding a massaged String value to bind to the stringExpression.

Would you mind trying to set up the query using the Criteria API? I don't think you have to go into the pagination stuff here as it doesn't seem to affect the case here.

[marceloverdijk]

Yes I will.

However I found out it's related to the paging stuff.

Both

User findByUsernameStartingWith(String username);
List<User> findByUsernameStartingWith(String username);

without any problem.

But when adding paging (via Pageable) like:

Page<User> findByUsernameStartingWith(String username, Pageable pageable);

or also:

List<User> findByUsername(String username, Pageable pageable);
List<User> findByUsernameStartingWith(String username, Pageable pageable);

then I get this error.

[marceloverdijk]

Yes, I reproduced a similar issue with a custom Criteria Query. That one fails as well, so this confirms it's a DataNucleus issue.
For reference I created the following jira issue http://www.datanucleus.org/servlet/j...wse/NUCJPA-190 which reproduces the issue without using Spring Data (though I get a little bit of different error message).

Thanks for your help!

[marceloverdijk]

Oliver,

Need to come back on this one.
In my Spring Data independent test case I was using:

ParameterExpression<String> username = cb.parameter(String.class);

which results in a JPQL query like:

SELECT FROM org.mycomp.domain.User DN_THIS WHERE DN_THIS.username LIKE ?-1

This is a similar query that I got using Spring Data, but this indicates Positional Parameters ARE used or am I wrong.

Queries with Positional Parameters are indeed not supported by DataNucleus as this is not a requirement by the JPA spec:

3.8.13 Positional Parameters

Only positional parameter binding and positional access to result items may be portably used for native
queries, except for stored procedure queries for which named parameters have been defined. When
binding the values of positional parameters, the numbering starts as “1”. It is assumed that for native
queries the parameters themselves use the SQL syntax (i.e., “?”, rather than “?1”).

The use of positional parameters is not supported for criteria queries.

As commented by Andy Jefferson from Datanucleus project: Presumably what that means is it is left to an implementation to decide if they want to support it; anyway its not supported.

As soon as I change above criteria code to use named parameters:

ParameterExpression<String> username = cb.parameter(String.class, "username");

Then my criteria query works fine.

Now the question is off course if Spring Data is using Position Parameters under the covers?

I dived into the source code and what I could find was the following code in ParameterMetadataProvider:

private <T> ParameterMetadata<T> next(Part part, Class<T> type, String name) {

	Assert.notNull(type);

	ParameterExpression<T> expression = name == null ? builder.parameter(type) : builder.parameter(type, name);

Now the builder.parameter(type) call if the name is null could be interesting, as this would cause implicit use of Positional Parameters.

I'm wondering if I'm haunting ghosts right now :-)

[marceloverdijk]

Yes I might getting insane now.

Tried a simple

User findByUsernameStartingWith(String username);

inside my UserRepository and this leads to the following JPQL: "SELECT DN_THIS FROM org.mycomp.minerva.domain.User DN_THIS WHERE DN_THIS.username LIKE ?-1ORDER BY"

Which also contains the ?-1 and but also retrieves the user...

As I noticed before it only goes wrong when adding the Pageable argument, is this maybe doing something special then?