jpa中的Pageable分页查询，连接mysql没问题，连接单元测试内置数据库H2时报错

菜鸟凯一枚 2018-12-10 03:37:02
问题：我实现jpa的Pageable接口以此写了一个查询接口，连接的数据库为mysql。用swagger测试接口没问题，前端页面调用也没问题。但是在编写单元测试的时候连接的是内置数据库H2发现只要是使用了Pageable接口的查询均会报错

原始接口代码实现类
    @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
...全文
98 回复 打赏 收藏 转发到动态 举报
写回复
用AI写文章
回复
切换为时间正序
请发表友善的回复…
发表回复
SpringBoot+MySQL+Jpa实现对数据库的增删改查和分页
使用Springboot+Jpa实现对mysql数据库的增删改查和分页功能   JPA是Java Persistence API的简称，中文名Java持久层API，是JDK 5.0注解或XML描述对象－关系表的映射关系，并将运行期的实体对象持久化到数据库中。 ...
Spring Data JPA native query 分页
Mysql Spring Data JPA and native queries with pagination Spring Data and Native Query with pagination public interface UserRepository extends JpaRepository<User, Long> { @Query(value = "se...
Spring Boot JPA 存储库派生查询示例
–Spring JPA + H2 示例–Spring JPA + MySQL 示例–Spring JPA +对于不区分大小写的查询，在 SQL 中，我们可以强制将值强制为全部大写或小写字母，然后与查询值进行比较。今天，我们已经知道如何使用派生查询在 ...
SpringBoot JPA @Query 注解中进行关联查询、分页、返回 Map 结构
SpringBoot-JPA-`@Query`注解关联查询、分页、返回 Map 结构： 业务需求：多张表关联查询、结果分页、结果直接返回 Map 结构(不进行 DTO 封装)，能根据某字段进行结果过滤筛选，该字段为 null 或者 "" 查询所有。 ...
springboot + Jpa 实现分页功能
Springboot 集成 Jpa 实现分页 由于用的技术并不复杂，所以我们开门见山，直接上代码 先来看下代码结构 pom.xml 引入相关jar包 <?xml version="1.0" encoding="UTF-8"?> <project xmlns=...
Java

50,523

社区成员

85,601

社区内容

发帖
与我相关
我的任务
社区描述
Java相关技术讨论
javaspring bootspring cloud 技术论坛（原bbs）
社区管理员
  • Java相关社区
  • 小虚竹
  • 谙忆
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章