Spring boot pagination with native query throws NumberFormatException

Question

The error started to show up when I added pagination (using Pageable) to the search query with ORDER BY ?#{#pageable}. I'm using H2 database for unit-testing with database-platform: org.hibernate.dialect.H2Dialect.

Spring boot version: 1.5.9.RELEASE Java version: 1.8

Repository:

@Repository
public interface VacancyRepository extends PagingAndSortingRepository<Vacancy, Long> {

    @Query(value = "SELECT * FROM vacancies WHERE location ILIKE %:location% AND CONCAT(title, ' ', description) ILIKE %:keyword% ORDER BY ?#{#pageable}",
            countQuery = "SELECT COUNT(*) FROM vacancies WHERE location ILIKE %:location% AND CONCAT(title, ' ', description) ILIKE %:keyword%",
            nativeQuery = true)
    Page<Vacancy> search(@Param("location") String location, @Param("keyword") String keyword, Pageable pageable);

}

Controller method (controller is prefixed with /vacancy/):

@RequestMapping(method = RequestMethod.GET, value = "/search/location/{location}/keyword/{keyword}", produces = MediaType.APPLICATION_JSON_VALUE)
public ResponseEntity<Page<Vacancy>> search(@PathVariable String location, @PathVariable String keyword, Pageable pageable) {
    Page<Vacancy> vacancies = vacancyRepository.search(location, keyword, pageable);
    return ResponseEntity.ok().body(vacancies);
}

How it is used in tests:

    // Given
    String path = "/vacancy/search/location/searchQueryLocation/keyword/search?page=0&size=1&sort=id";

    // When
    ResultActions sendRequest = mvc.perform(get(path));

Exception thrown:

org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.DataException: could not extract ResultSet

    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:982)
    at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:635)
    at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
    at org.springframework.test.web.servlet.TestDispatcherServlet.service(TestDispatcherServlet.java:65)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
    at org.springframework.mock.web.MockFilterChain$ServletFilterProxy.doFilter(MockFilterChain.java:160)
    at org.springframework.mock.web.MockFilterChain.doFilter(MockFilterChain.java:127)
    at org.springframework.test.web.servlet.MockMvc.perform(MockMvc.java:155)
    at uk.gov.cshr.vcm.controller.VacancyControllerTest.testSearch(VacancyControllerTest.java:336)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:104)
    at org.testng.internal.MethodInvocationHelper$1.runTestMethod(MethodInvocationHelper.java:205)
    at org.springframework.test.context.testng.AbstractTestNGSpringContextTests.run(AbstractTestNGSpringContextTests.java:175)
    at org.testng.internal.MethodInvocationHelper.invokeHookable(MethodInvocationHelper.java:217)
    at org.testng.internal.Invoker.invokeMethod(Invoker.java:641)
    at org.testng.internal.Invoker.invokeTestMethod(Invoker.java:851)
    at org.testng.internal.Invoker.invokeTestMethods(Invoker.java:1177)
    at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:129)
    at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:112)
    at org.testng.TestRunner.privateRun(TestRunner.java:756)
    at org.testng.TestRunner.run(TestRunner.java:610)
    at org.testng.SuiteRunner.runTest(SuiteRunner.java:387)
    at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:382)
    at org.testng.SuiteRunner.privateRun(SuiteRunner.java:340)
    at org.testng.SuiteRunner.run(SuiteRunner.java:289)
    at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:52)
    at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:86)
    at org.testng.TestNG.runSuitesSequentially(TestNG.java:1293)
    at org.testng.TestNG.runSuitesLocally(TestNG.java:1218)
    at org.testng.TestNG.runSuites(TestNG.java:1133)
    at org.testng.TestNG.run(TestNG.java:1104)
    at org.testng.IDEARemoteTestNG.run(IDEARemoteTestNG.java:72)
    at org.testng.RemoteTestNGStarter.main(RemoteTestNGStarter.java:123)
Caused by: org.springframework.dao.DataIntegrityViolationException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.DataException: could not extract ResultSet
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:282)
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:244)
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:503)
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59)
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:209)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:133)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:57)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
    at com.sun.proxy.$Proxy111.search(Unknown Source)
    at uk.gov.cshr.vcm.controller.VacancyController.search(VacancyController.java:82)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
    at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
    at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967)
    at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901)
    at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
    ... 36 more
Caused by: org.hibernate.exception.DataException: could not extract ResultSet
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:118)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2117)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1900)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1876)
    at org.hibernate.loader.Loader.doQuery(Loader.java:919)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336)
    at org.hibernate.loader.Loader.doList(Loader.java:2617)
    at org.hibernate.loader.Loader.doList(Loader.java:2600)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2429)
    at org.hibernate.loader.Loader.list(Loader.java:2424)
    at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:336)
    at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:1967)
    at org.hibernate.internal.AbstractSessionImpl.list(AbstractSessionImpl.java:322)
    at org.hibernate.internal.SQLQueryImpl.list(SQLQueryImpl.java:125)
    at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:606)
    at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:483)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$PagedExecution.doExecute(JpaQueryExecution.java:188)
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:87)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:116)
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:106)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:499)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:477)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:56)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:282)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136)
    ... 59 more
Caused by: org.h2.jdbc.JdbcSQLException: Data conversion error converting "aced00057372002b6f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e5061676552657175657374c0f950c5c09dc7260200014c0004736f72747400264c6f72672f737072696e676672616d65776f726b2f646174612f646f6d61696e2f536f72743b787200336f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e41627374726163745061676552657175657374111be055001d2dc70200024900047061676549000473697a6578700000000000000001737200246f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e536f72744f9e94bc46c7fa210200014c00066f72646572737400104c6a6176612f7574696c2f4c6973743b7870737200136a6176612e7574696c2e41727261794c6973747881d21d99c7619d03000149000473697a657870000000017704000000017372002a6f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e536f7274244f7264657215210bb2a35266bb0200045a000a69676e6f7265436173654c0009646972656374696f6e7400304c6f72672f737072696e676672616d65776f726b2f646174612f646f6d61696e2f536f727424446972656374696f6e3b4c000c6e756c6c48616e646c696e677400334c6f72672f737072696e676672616d65776f726b2f646174612f646f6d61696e2f536f7274244e756c6c48616e646c696e673b4c000870726f70657274797400124c6a6176612f6c616e672f537472696e673b7870007e72002e6f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e536f727424446972656374696f6e00000000000000001200007872000e6a6176612e6c616e672e456e756d000000000000000012000078707400034153437e7200316f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e536f7274244e756c6c48616e646c696e6700000000000000001200007871007e000f7400064e4154495645740002696478"; SQL statement:
SELECT * FROM vacancies WHERE location ILIKE ? AND CONCAT(title, ' ', description) ILIKE ? ORDER BY ?, id asc limit ? [22018-196]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
    at org.h2.message.DbException.get(DbException.java:168)
    at org.h2.value.Value.convertTo(Value.java:996)
    at org.h2.value.Value.getInt(Value.java:459)
    at org.h2.command.dml.Query.prepareOrder(Query.java:518)
    at org.h2.command.dml.Select.prepare(Select.java:834)
    at org.h2.command.CommandContainer.recompileIfRequired(CommandContainer.java:88)
    at org.h2.command.CommandContainer.query(CommandContainer.java:109)
    at org.h2.command.Command.executeQuery(Command.java:201)
    at org.h2.jdbc.JdbcPreparedStatement.executeQuery(JdbcPreparedStatement.java:111)
    at sun.reflect.GeneratedMethodAccessor62.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114)
    at com.sun.proxy.$Proxy124.executeQuery(Unknown Source)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70)
    ... 88 more
Caused by: java.lang.NumberFormatException: For input string: "aced00057372002b6f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e5061676552657175657374c0f950c5c09dc7260200014c0004736f72747400264c6f72672f737072696e676672616d65776f726b2f646174612f646f6d61696e2f536f72743b787200336f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e41627374726163745061676552657175657374111be055001d2dc70200024900047061676549000473697a6578700000000000000001737200246f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e536f72744f9e94bc46c7fa210200014c00066f72646572737400104c6a6176612f7574696c2f4c6973743b7870737200136a6176612e7574696c2e41727261794c6973747881d21d99c7619d03000149000473697a657870000000017704000000017372002a6f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e536f7274244f7264657215210bb2a35266bb0200045a000a69676e6f7265436173654c0009646972656374696f6e7400304c6f72672f737072696e676672616d65776f726b2f646174612f646f6d61696e2f536f727424446972656374696f6e3b4c000c6e756c6c48616e646c696e677400334c6f72672f737072696e676672616d65776f726b2f646174612f646f6d61696e2f536f7274244e756c6c48616e646c696e673b4c000870726f70657274797400124c6a6176612f6c616e672f537472696e673b7870007e72002e6f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e536f727424446972656374696f6e00000000000000001200007872000e6a6176612e6c616e672e456e756d000000000000000012000078707400034153437e7200316f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e536f7274244e756c6c48616e646c696e6700000000000000001200007871007e000f7400064e4154495645740002696478"
    at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
    at java.lang.Long.parseLong(Long.java:592)
    at org.h2.value.Value.convertTo(Value.java:641)

Judging by the "aced" in the beginnig, it looks like a serialized Java object, but I don't see where it is serialized.

Generated Hibernate log:

Hibernate: SELECT * FROM vacancies WHERE location ILIKE ? AND CONCAT(title, ' ', description) ILIKE ? ORDER BY ?, id asc limit ?
2018-01-04 15:08:02.940  WARN 6872 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 22018, SQLState: 22018
2018-01-04 15:08:02.940 ERROR 6872 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : Data conversion error converting "aced00057372002b6f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e5061676552657175657374c0f950c5c09dc7260200014c0004736f72747400264c6f72672f737072696e676672616d65776f726b2f646174612f646f6d61696e2f536f72743b787200336f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e41627374726163745061676552657175657374111be055001d2dc70200024900047061676549000473697a6578700000000000000001737200246f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e536f72744f9e94bc46c7fa210200014c00066f72646572737400104c6a6176612f7574696c2f4c6973743b7870737200136a6176612e7574696c2e41727261794c6973747881d21d99c7619d03000149000473697a657870000000017704000000017372002a6f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e536f7274244f7264657215210bb2a35266bb0200045a000a69676e6f7265436173654c0009646972656374696f6e7400304c6f72672f737072696e676672616d65776f726b2f646174612f646f6d61696e2f536f727424446972656374696f6e3b4c000c6e756c6c48616e646c696e677400334c6f72672f737072696e676672616d65776f726b2f646174612f646f6d61696e2f536f7274244e756c6c48616e646c696e673b4c000870726f70657274797400124c6a6176612f6c616e672f537472696e673b7870007e72002e6f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e536f727424446972656374696f6e00000000000000001200007872000e6a6176612e6c616e672e456e756d000000000000000012000078707400034153437e7200316f72672e737072696e676672616d65776f726b2e646174612e646f6d61696e2e536f7274244e756c6c48616e646c696e6700000000000000001200007871007e000f7400064e4154495645740002696478"; SQL statement:
SELECT * FROM vacancies WHERE location ILIKE ? AND CONCAT(title, ' ', description) ILIKE ? ORDER BY ?, id asc limit ? [22018-196]

The same query without Pageable works as expected. I tried different ways of writing the same Pageable thing (including ORDER BY /*#pageable*/ or ORDER BY t.id DESC \n-- #pageable\n or by trying to comment the #pageable in the query (/*#pageable*/) and passing my own page and page size parameters) but nothing works.

I know that I can replace this particular query with somethig that uses to_upper() instead of ILIKE and use JPA queries, but the following steps will imply something that is not implemented in JPA queries and requires native queries.

Any help would be much appreciated.

Can you edit your question to add a bit more to the stacktrace ? Would be good to see what bit of code further up is throwing the exception. — PaulNUK, Jan 04 '18 at 14:57
Added more stack trace to the question. I don't have a direct test for the repository yet, I will try it and uptade the question. — Mironor, Jan 04 '18 at 15:05
@PaulNUK I tested the method directly with `Page search = vacancyRepository.search("searchQueryLocation", "search", new PageRequest(0, 1));` and, to my regret, it yields exactly the same exception (without the "request" stack trace level, obviously). I will also update the question with hibernate's log. — Mironor, Jan 04 '18 at 15:09
This may be of use https://github.com/h2database/h2database/issues/402 It looks like HQL guesses a parameter to be an int if you don't specify it and then you get a conversion error. Could it be that HQL doesn't like the limit parameter, or, do you have data that when CONCAT(title, ' ', description) occurs, results in your string starting with ace ? — PaulNUK, Jan 04 '18 at 15:16
I replaced `CONCAT(title, ' ', description)` with just `description` and it didn't change anything (btw, whad did you mean by "starting with ace"? Did you mean "asterisk"?), the testing String data is alpha-numeric with spaces, nothing else. The limit parameter is generated by the "pageable magic" and I don't have any control over it. I will try to run it through Postgres database to find out if it's just H2's problem. — Mironor, Jan 04 '18 at 15:27
Starting with ace - ie this string "aced00057......." In other words, any titles in your db that when concatentated with description start with "aced00057" ? May well be an HQL funny, — PaulNUK, Jan 04 '18 at 15:32
Please verify that you get the same error when connecting to actual Postgres instance, this error may be related to unimplemented feature in H2. — 11thdimension, Jan 04 '18 at 15:41
And yes, it is working with Postgresql, so the problem is with H2. Now there is still a question on why H2 is not happy, but at least I see where the problem is. What should I do for the question? Update it? Provide an answer with "The problem is with H2"? — Mironor, Jan 04 '18 at 15:49
AFAIK you need some SQL hackery to have a dynamic Order By. See for example https://stackoverflow.com/a/8139871/66686 — Jens Schauder, Jan 05 '18 at 07:03

Spring boot pagination with native query throws NumberFormatException

0 Answers0