jpa中的Pageable分页查询,连接mysql没问题,连接单元测试内置数据库H2时报错
原始接口代码实现类
@Override
public PageVO<AppSelectVO> fetchCasePage(Integer pageNum, Integer pageSize, String appName, String title){
//创建时间倒序
Sort sort = new Sort(Sort.Direction.DESC, "update_date", "create_date");
PageRequest request = new PageRequest(pageNum, pageSize, sort);
if (StringUtils.isEmpty(appName)){
appName = "";
}
if (StringUtils.isEmpty(title)){
title = "";
}
Page<Object []> data = appCaseManagementDao.fetchCasePage(appName,title, request);
int total = Integer.valueOf(data.getTotalElements() + "");
List<AppCaseResponse> list = AppCaseResponse.convertObjectArrList(data.getContent());
return new PageVO(total, list);
}原始接口代码Dao层,查询数据
@Query(
value = " SELECT" +
" a.example_id," +
" a.title," +
" b.app_name" +
" FROM" +
" app_example a" +
" LEFT JOIN app b ON a.app_id = b.app_id" +
" WHERE" +
" a.del_flag = " + AppExampleEntity.DEL_FLAG_NORMAL +
" AND a.title LIKE CONCAT('%', ?2, '%')" +
" AND b.app_name LIKE CONCAT('%', ?1, '%') \n#pageable\n",
countQuery = "select count(1) from ( " +
" SELECT " +
" a.example_id, " +
" a.title, " +
" b.app_name " +
" FROM " +
" app_example a " +
" LEFT JOIN app b ON a.app_id = b.app_id " +
" WHERE " +
" a.del_flag = " + AppExampleEntity.DEL_FLAG_NORMAL +
" AND a.title LIKE CONCAT('%', ?2, '%') " +
" AND b.app_name LIKE CONCAT('%', ?1, '%')) t",
nativeQuery = true)
Page<Object []> fetchCasePage(String appName, String title, Pageable pageable);链接mysql时此接口查询调用正常,切换到h2数据库是报错如下,根据报错内容来看确实是说查询语句出了问题。但是不知道如何解决。
2018-12-10 15:18:25,207 +0800 | WARN | org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 42000, SQLState: 42000
2018-12-10 15:18:25,207 +0800 | ERROR | org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Syntax error in SQL statement " SELECT A.EXAMPLE_ID, A.TITLE, B.APP_NAME FROM APP_EXAMPLE A LEFT JOIN APP B ON A.APP_ID = B.APP_ID WHERE A.DEL_FLAG = 0 AND A.TITLE LIKE CONCAT('%', ?, '%') AND B.APP_NAME LIKE CONCAT('%', ?, '%')-
#PAGEABLE
ORDER BY A.UPDATE_DATE DESC, A.CREATE_DATE DESC LIMIT ? "; SQL statement:
SELECT a.example_id, a.title, b.app_name FROM app_example a LEFT JOIN app b ON a.app_id = b.app_id WHERE a.del_flag = 0 AND a.title LIKE CONCAT - ('%', ?, '%') AND b.app_name LIKE CONCAT('%', ?, '%')
#pageable
order by a.update_date desc, a.create_date desc limit ? [42000-196]
2018-12-10 15:18:25,216 +0800 | ERROR | com.comtop.map.store.exception.GlobalExceptionHandler - org.hibernate.exception.SQLGrammarException: could not prepare statement
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not prepare statement
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1692)
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1602)
at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:492)
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)
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.$Proxy237.fetchCasePage(Unknown Source)
at com.comtop.map.store.app.service.impl.AppCaseManagementServiceImpl.fetchCasePage(AppCaseManagementServiceImpl.java:164)
at com.comtop.map.store.app.web.AppCaseManagementController.selectlist(AppCaseManagementController.java:50)
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)
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.boot.web.filter.ApplicationContextHeaderFilter.doFilterInternal(ApplicationContextHeaderFilter.java:55)
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
at org.springframework.mock.web.MockFilterChain.doFilter(MockFilterChain.java:127)
at org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java:108)
at org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:137)
at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
at org.springframework.mock.web.MockFilterChain.doFilter(MockFilterChain.java:127)
at org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java:108)
at org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:137)
at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
at org.springframework.mock.web.MockFilterChain.doFilter(MockFilterChain.java:127)
at org.apache.shiro.web.servlet.AdviceFilter.executeChain(AdviceFilter.java:108)
at org.apache.shiro.web.servlet.AdviceFilter.doFilterInternal(AdviceFilter.java:137)
at org.apache.shiro.web.servlet.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:125)
at org.springframework.mock.web.MockFilterChain.doFilter(MockFilterChain.java:127)
at org.apache.shiro.web.servlet.ProxiedFilterChain.doFilter(ProxiedFilterChain.java:61)
at org.apache.shiro.web.servlet.AdviceFi
相关推荐
SpringBootThymeleafPagination:使用Spring Boot + Spring Data JPA(或不是)+ Thymeleaf的分页演示
Sprint Boot胸腺分页 怎么跑 运行mvn spring-boot:run 转到“ ” 转到“ ”以查看h2数据库中的模拟数据。
基于SpringBoot+Vue的校企实习管理系统毕业设计实战指南
在软件开发领域,毕业设计是检验学生综合运用所学知识解决实际问题能力的重要环节。一个优秀的毕业设计选题,应具备清晰的业务逻辑、适中的技术复杂度,并能全面展示核心技术栈的应用能力。从技术原理上看,一个典型的Web应用通常采用前后端分离架构,后端负责业务逻辑与数据持久化,前端负责用户交互与数据展示。这种架构的价值在于职责清晰、易于维护和扩展。在众多应用场景中,管理系统因其业务模型通用、需求明确,成为实践上述技术的理想载体。本文聚焦于“校企实习管理系统”这一具体主题,它天然融合了学生、企业、学校三方角色与完整的业务
Spring Boot校内兼职平台:角色驱动的轻量级就业服务中台
高校信息化系统本质上是面向多角色、强安全、高时效的业务中台,其核心在于细粒度权限控制与场景化数据治理。Spring Boot凭借自动配置、内嵌容器和开箱即用的生态,成为毕设级Java Web项目的首选框架;它通过Spring Security实现RBAC权限模型,结合JPA简化持久层开发,并以Token机制保障无状态会话安全。在校园兼职这类中小规模、高交互性、低数据量的典型场景中,系统无需复杂中间件即可支撑角色隔离、报名审核、协同推荐等完整闭环。本文基于真实可运行的Spring Boot项目,详解从数据库设
Spring Boot+Vue网上购物商城系统实战:从前后端分离到订单库存设计
前后端分离架构是当今Web应用开发的主流范式,Spring Boot作为Java后端框架凭借快速构建RESTful API的能力广受青睐,而Vue则通过响应式数据绑定与组件化开发成为前端的热门选择。二者通过JSON完成数据交互,为商城类业务提供了高内聚低耦合的实现方案。在实践中,JWT令牌机制保障了用户认证安全,订单状态机严格规范了业务流转,库存扣减利用数据库条件更新从根源上防止超卖。基于Spring Boot与Vue的网上购物商城系统,完整覆盖注册登录、商品浏览、购物车、下单与订单管理等核心链路,既是Ja
国央企创新负责人在推动科研成果产业化时,如何确保技术成果的匹配质量和落地可行性?.docx
科易网基于行业知识库+行业大数据+科技资源要素+AI模型,以AI,赋能技术转移、成果转化、技术经理人培训、知识产权管理、区域产业分析与布局、地方科技招商、创新创业大赛等场景,推出科技创新领域的AI工具、场景化服务和数智化解决方案,推动科技创新与产业创新智能化发展。


