我是个老六 2022-05-24 17:11 采纳率: 61.1%
浏览 112
已结题

原因:java.sql.SQLSyntaxErrorException:每个派生表都必须有自己的别名

关于多数据源切换,我把oracle切换为mysql,切换成功了,但是index查询却报这个错误,sql语句是通用的,并且我起了别名

Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@71f853ea] was not registered for synchronization because synchronization is not active
2022-05-24 17:02:30.641  WARN 17660 --- [nio-9031-exec-2] c.a.druid.pool.DruidAbstractDataSource   : discard long time none received connection. , jdbcUrl : jdbc:mysql://127.0.0.1:3306/javaweb.elevue.pro?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2b8&useSSL=true&tinyInt1isBit=false, version : 1.2.4, lastPacketReceivedIdleMillis : 75187
2022-05-24 17:02:30.642  WARN 17660 --- [nio-9031-exec-2] c.a.druid.pool.DruidAbstractDataSource   : discard long time none received connection. , jdbcUrl : jdbc:mysql://127.0.0.1:3306/javaweb.elevue.pro?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2b8&useSSL=true&tinyInt1isBit=false, version : 1.2.4, lastPacketReceivedIdleMillis : 75338
2022-05-24 17:02:30.643  WARN 17660 --- [nio-9031-exec-2] c.a.druid.pool.DruidAbstractDataSource   : discard long time none received connection. , jdbcUrl : jdbc:mysql://127.0.0.1:3306/javaweb.elevue.pro?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2b8&useSSL=true&tinyInt1isBit=false, version : 1.2.4, lastPacketReceivedIdleMillis : 75342
2022-05-24 17:02:30.644  WARN 17660 --- [nio-9031-exec-2] c.a.druid.pool.DruidAbstractDataSource   : discard long time none received connection. , jdbcUrl : jdbc:mysql://127.0.0.1:3306/javaweb.elevue.pro?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2b8&useSSL=true&tinyInt1isBit=false, version : 1.2.4, lastPacketReceivedIdleMillis : 75346
2022-05-24 17:02:30.645  WARN 17660 --- [nio-9031-exec-2] c.a.druid.pool.DruidAbstractDataSource   : discard long time none received connection. , jdbcUrl : jdbc:mysql://127.0.0.1:3306/javaweb.elevue.pro?useUnicode=true&characterEncoding=UTF-8&serverTimezone=GMT%2b8&useSSL=true&tinyInt1isBit=false, version : 1.2.4, lastPacketReceivedIdleMillis : 75356
JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@d0bc120] will not be managed by Spring
JsqlParserCountOptimize sql=select a.id, a.table_name, a.table_prefix, a.table_comment, a.class_name, a.tpl_category, a.package_name, a.module_name, a.business_name, a.function_name, a.function_author, a.options, a.note, a.create_user, a.create_time, a.update_user, a.update_time, a.mark,
        a.is_gen_entity,a.is_gen_mapper,
        a.is_gen_dao,a.is_gen_query,a.is_gen_service,
        a.is_gen_service_impl,a.is_gen_list_vo,a.is_gen_info_vo,
        a.is_gen_constant,a.is_gen_controller,a.is_gen_index_vue,
        a.is_gen_edit_vue,a.is_gen_menu_permission,a.is_use_swagger,a.vue_path
        from gen_table a
     
         WHERE  a.mark = 1
==>  Preparing: SELECT COUNT(1) FROM gen_table a WHERE a.mark = 1 
==> Parameters: 
<==    Columns: COUNT(1)
<==        Row: 1
==>  Preparing: SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM ( select a.id, a.table_name, a.table_prefix, a.table_comment, a.class_name, a.tpl_category, a.package_name, a.module_name, a.business_name, a.function_name, a.function_author, a.options, a.note, a.create_user, a.create_time, a.update_user, a.update_time, a.mark, a.is_gen_entity,a.is_gen_mapper, a.is_gen_dao,a.is_gen_query,a.is_gen_service, a.is_gen_service_impl,a.is_gen_list_vo,a.is_gen_info_vo, a.is_gen_constant,a.is_gen_controller,a.is_gen_index_vue, a.is_gen_edit_vue,a.is_gen_menu_permission,a.is_use_swagger,a.vue_path from gen_table a WHERE a.mark = 1 ) TMP WHERE ROWNUM <=?) WHERE ROW_ID > ? 
==> Parameters: 1(Long), 0(Long)
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@71f853ea]
2022-05-24 17:02:30.652 ERROR 17660 --- [nio-9031-exec-2] o.a.c.c.C.[.[.[.[dispatcherServlet]      : Servlet.service() for servlet [dispatcherServlet] in context with path [/api] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: java.sql.SQLSyntaxErrorException: Every derived table must have its own alias
### The error may exist in file [C:\Users\Administrator\Desktop\Oraclevue\Oracle\PiDataSecuritySystem\javaweb-generator\target\classes\mapper\GenTableMapper.xml]
### The error may involve com.javaweb.generator.mapper.GenTableMapper.selectGenTableList-Inline
### The error occurred while setting parameters
### SQL: SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM ( select a.id, a.table_name, a.table_prefix, a.table_comment, a.class_name, a.tpl_category, a.package_name, a.module_name, a.business_name, a.function_name, a.function_author, a.options, a.note, a.create_user, a.create_time, a.update_user, a.update_time, a.mark,   a.is_gen_entity,a.is_gen_mapper,         a.is_gen_dao,a.is_gen_query,a.is_gen_service,         a.is_gen_service_impl,a.is_gen_list_vo,a.is_gen_info_vo,         a.is_gen_constant,a.is_gen_controller,a.is_gen_index_vue,         a.is_gen_edit_vue,a.is_gen_menu_permission,a.is_use_swagger,a.vue_path   from gen_table a                WHERE  a.mark = 1 ) TMP WHERE ROWNUM <=?) WHERE ROW_ID > ?
### Cause: java.sql.SQLSyntaxErrorException: Every derived table must have its own alias
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Every derived table must have its own alias] with root cause

java.sql.SQLSyntaxErrorException: Every derived table must have its own alias
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.22.jar:8.0.22]
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.22.jar:8.0.22]
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.22.jar:8.0.22]
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953) ~[mysql-connector-java-8.0.22.jar:8.0.22]
    at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370) ~[mysql-connector-java-8.0.22.jar:8.0.22]
    at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497) ~[druid-1.2.4.jar:1.2.4]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_131]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_131]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_131]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_131]
    at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59) ~[mybatis-3.5.2.jar:3.5.2]
    at com.sun.proxy.$Proxy227.execute(Unknown Source) ~[na:na]
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64) ~[mybatis-3.5.2.jar:3.5.2]
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79) ~[mybatis-3.5.2.jar:3.5.2]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_131]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_131]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_131]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_131]
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63) ~[mybatis-3.5.2.jar:3.5.2]
    at com.sun.proxy.$Proxy225.query(Unknown Source) ~[na:na]
    at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doQuery(MybatisSimpleExecutor.java:67) ~[mybatis-plus-core-3.2.0.jar:3.2.0]
    at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324) ~[mybatis-3.5.2.jar:3.5.2]
    at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) ~[mybatis-3.5.2.jar:3.5.2]
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109) ~[mybatis-3.5.2.jar:3.5.2]
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:83) ~[mybatis-3.5.2.jar:3.5.2]
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147) ~[mybatis-3.5.2.jar:3.5.2]
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140) ~[mybatis-3.5.2.jar:3.5.2]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_131]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_131]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_131]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_131]
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426) ~[mybatis-spring-2.0.2.jar:2.0.2]
    at com.sun.proxy.$Proxy68.selectList(Unknown Source) ~[na:na]
    at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:223) ~[mybatis-spring-2.0.2.jar:2.0.2]
    at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForIPage(MybatisMapperMethod.java:115) ~[mybatis-plus-core-3.2.0.jar:3.2.0]
    at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:86) ~[mybatis-plus-core-3.2.0.jar:3.2.0]
    at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:61) ~[mybatis-plus-core-3.2.0.jar:3.2.0]
    at com.sun.proxy.$Proxy161.selectGenTableList(Unknown Source) ~[na:na]
    at com.javaweb.generator.service.impl.GenTableServiceImpl.getList(GenTableServiceImpl.java:75) ~[classes/:na]
    at com.javaweb.generator.service.impl.GenTableServiceImpl$$FastClassBySpringCGLIB$$72d52426.invoke(<generated>) ~[classes/:na]
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.2.10.RELEASE.jar:5.2.10.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:687) ~[spring-aop-5.2.10.RELEASE.jar:5.2.10.RELEASE]
    at com.javaweb.generator.service.impl.GenTableServiceImpl$$EnhancerBySpringCGLIB$$bc24fab2.getList(<generated>) ~[classes/:na]
    at com.javaweb.generator.service.impl.GenTableServiceImpl$$FastClassBySpringCGLIB$$72d52426.invoke(<generated>) ~[classes/:na]
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.2.10.RELEASE.jar:5.2.10.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:687) ~[spring-aop-5.2.10.RELEASE.jar:5.2.10.RELEASE]
    at com.javaweb.generator.service.impl.GenTableServiceImpl$$EnhancerBySpringCGLIB$$2d3780d4.getList(<generated>) ~[classes/:na]
    at com.javaweb.generator.controller.GenTableController.index(GenTableController.java:70) ~[classes/:na]
    at com.javaweb.generator.controller.GenTableController$$FastClassBySpringCGLIB$$83a25008.invoke(<generated>) ~[classes/:na]
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.2.10.RELEASE.jar:5.2.10.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771) ~[spring-aop-5.2.10.RELEASE.jar:5.2.10.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) ~[spring-aop-5.2.10.RELEASE.jar:5.2.10.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749) ~[spring-aop-5.2.10.RELEASE.jar:5.2.10.RELEASE]



  • 写回答

3条回答 默认 最新

  • 皮蛋不吃粥 2022-05-24 17:31
    关注

    可能是mp分页插件的问题

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论 编辑记录
查看更多回答(2条)

报告相同问题?

问题事件

  • 系统已结题 6月2日
  • 已采纳回答 5月25日
  • 创建了问题 5月24日

悬赏问题

  • ¥15 luckysheet
  • ¥15 ZABBIX6.0L连接数据库报错,如何解决?(操作系统-centos)
  • ¥15 找一位技术过硬的游戏pj程序员
  • ¥15 matlab生成电测深三层曲线模型代码
  • ¥50 随机森林与房贷信用风险模型
  • ¥50 buildozer打包kivy app失败
  • ¥30 在vs2022里运行python代码
  • ¥15 不同尺寸货物如何寻找合适的包装箱型谱
  • ¥15 求解 yolo算法问题
  • ¥15 虚拟机打包apk出现错误