带有别名的MySQL select语句失败，找不到列 [英] MySQL select statement with alias fails with column not found

查看：294
本文介绍了带有别名的MySQL select语句失败，找不到列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

开发环境参考:

Fedora FC18
Tomcat Server 7.0.39 (localhost config)
Eclipse Juno Release 2
Mysql-connecor-java Ver. 5.1.26 (jar)
Mysql-server Ver. 5.5.32 (jar)

以下选择语句失败，并显示未找到列'depth'":

The following select statement fails with "column 'depth' not found":

"select node.subEntityID, node.lft, node.rgt, (count(parent.subEntityID) - 1) as depth
 from ENTITY as node, ENTITY as parent
 where node.lft between parent.lft and parent.rgt
 group by node.subEntityID order by node.lft";

此选择语句使用命令级mysql作为

This select statement succeeds using command-level mysql as 

use dbName;
select node.subEntityID, node.lft, node.rgt, (count(parent.subEntityID) - 1) as depth
  from ENTITY as node, ENTITY as parent
  where node.lft between parent.lft and parent.rgt
  group by node.subEntityID order by node.lft;

在MySQL Workbench ver中使用SQL查询时，select语句也会成功. 5.2.4.7.

The select statement also succeeds when using an SQL query in MySQL Workbench ver. 5.2.4.7.

相关的Java代码片段如下:

The relevant Java code fragment is given below:

rs = stmt.executeQuery(typeEntityList);
// The depth alias does not print out in this for loop
for (int i=1; i<rs.getMetaData().getColumnCount()+1; i++) {
    System.out.println(rs.getMetaData().getColumnName(i));
    System.out.println(rs.getMetaData().getColumnLabel(i));
}
while (rs.next()) {
    // "depth" fails at the following statement
    System.out.println("depth: " + rs.getInt("depth"));
    String s = rs.getString("lft") + "  "
        + rs.getString("subEntityID") + "  "
        + rs.getString("rgt");
    System.out.println(s);
    entityList.add(s);
}

此外，我在Google搜索中发现MySQL的别名行为在最新版本中已更改.建议的解决方案是将"?useOldAliasMetadataBehavior=true"附加到MySQL连接字符串以恢复到原始行为，但这不能解决问题.

Also, I found on a Google search that MySQL's alias behavior changed in recent versions. A suggested fix was to append "?useOldAliasMetadataBehavior=true" to the MySQL connection string to revert back to the original behavior, but that did not correct the problem.

这是堆栈跟踪:

java.sql.SQLException:找不到列深度".在com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)在com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)在com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975) )com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1167)com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1167)处5733)在 EntityAssessmentEntityActions.EntityAssessmentEntityListAction.execute(EntityAssessmentEntityListAction.execute(EntityAssessmentEntityListAction.java:69)在sun.reflect.NativeMethod.AccessorImpl.invoke0(Native. 57)位于com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:450)处java.lang.reflect.Method.invoke(Method.java:606)处的sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)处)com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.java:289)com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:252)org.apache.struts2.interceptor.debugging.DebuggingInterceptor.intercept( com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvoc)上的DebuggingInterceptor.java:256) ation.java:246)com.opensymphony.xwork2.interceptor.DefaultWorkflowInterceptor.doIntercept(DefaultWorkflowInterceptor.java:176)com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)com.opensymphony.xwork2 com.opensymphony.xwork2.validator.ValidationInterceptor.doIntercept(ValidationInterceptor.java:265)处的. )在com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)在com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)在com.opensymphony.xwork2.interceptor.ConversionErrorInterceptor.intercept( com.opensymphony.xwork2.DefaultActionInvocation.invoke上的ConversionErrorInterceptor.java:138)(com.opensymphony.xwork2.intercep上的DefaultActionInvocation.java:246) com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)上com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)上的tor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:249) com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:249)在com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.com)处的com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98) :246)，位于com.opensymphony.xwork2.interceptor.StaticParametersInterceptor.intercept(StaticParametersInterceptor.java:191)，位于com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)，位于org.apache.struts2.interceptor.MultiselectInterceptor.在org.apache.struts2.intercept处com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)处拦截(MultiselectInterceptor.java:73)或com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)位于org.apache.struts2.interceptor.FileUploadInterceptor.intercept(FileUploadInterceptor.java:252)处的. .opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)位于com.opensymphony.xwork2.interceptor.ModelDrivenInterceptor.intercept(ModelDrivenInterceptor.java:100)位于com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) )com.opensymphony.xwork2.interceptor.ScopedModelDrivenInterceptor.intercept(ScopedModelDrivenInterceptor.java:141)处com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)com.opensymphony.xwork2.interceptor.ChainingInterceptor. com.opensymphony.xwork2.DefaultActionInvocation.invoke的ChainingInterceptor.java:145)com.opensymphony.xwork2.interceptor.PrepareInterceptor.d的调用(DefaultActionInvocation.java:246) com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98)的com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)的com.opensymphony.xwork2的oIntercept(PrepareInterceptor.java:171)位于org.apache.struts2.interceptor.ServletConfigInterceptor.intercept(Servlet.Interceptor(ServletConfigInterceptor.java:164))的com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)处的.interceptor.I18nInterceptor.intercept(I18nInterceptor.java:176) com.opensymphony.xwork2.interceptor.AliasInterceptor.intercept(AliasInterceptor.java:193)处的com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246)com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java: 246)at com.opensymphony.xwork2.interceptor.ExceptionMappingInterceptor.intercept(ExceptionMappingInterceptor.java:187)at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.j ava:246)在org.apache.struts2.impl.StrutsActionProxy.execute(StrutsActionProxy.java:54)在org.apache.struts2.dispatcher.Dispatcher.serviceAction(Dispatcher.java:546)在org.apache.struts2.dispatcher org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter.doFilter(StrutsPrepareAndExecuteFilter.java:91)的ng.ExecuteOperations.executeAction(ExecuteOperations.java:77)org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain .java:243)，位于org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)，位于org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222)，位于org.apache.catalina. org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171)处的org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472)处的core.StandardContextValve.invoke(StandardContextValve.java:123)在org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:9 9)位于org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)的org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118)的org.apache.catalina.connector.CoyoteAdapter org.apache.coyote.http11上的.service(CoyoteAdapter.java:408).org上org.apache.coyote.AbstractProtocol $ AbstractConnectionHandler.process(AbstractProtocol.java:589)上的org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1009). apache.tomcat.util.net.JIoEndpoint $ SocketProcessor.run(JIoEndpoint.java:312)在java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)在java.util.concurrent.ThreadPoolExecutor $ Worker.run( ThreadPoolExecutor.java:615)，位于java.lang.Thread.run(Thread.java:724)

java.sql.SQLException: Column 'depth' not found. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920) at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1167) at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5733) at EntityAssessmentEntityActions.EntityAssessmentEntityListAction.detailedEntityList(EntityAssessmentEntityListAction.java:184) at EntityAssessmentEntityActions.EntityAssessmentEntityListAction.execute(EntityAssessmentEntityListAction.java:69) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:450) at com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.java:289) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:252) at org.apache.struts2.interceptor.debugging.DebuggingInterceptor.intercept(DebuggingInterceptor.java:256) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at com.opensymphony.xwork2.interceptor.DefaultWorkflowInterceptor.doIntercept(DefaultWorkflowInterceptor.java:176) at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at com.opensymphony.xwork2.validator.ValidationInterceptor.doIntercept(ValidationInterceptor.java:265) at org.apache.struts2.interceptor.validation.AnnotationValidationInterceptor.doIntercept(AnnotationValidationInterceptor.java:68) at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at com.opensymphony.xwork2.interceptor.ConversionErrorInterceptor.intercept(ConversionErrorInterceptor.java:138) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:249) at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:249) at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at com.opensymphony.xwork2.interceptor.StaticParametersInterceptor.intercept(StaticParametersInterceptor.java:191) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at org.apache.struts2.interceptor.MultiselectInterceptor.intercept(MultiselectInterceptor.java:73) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at org.apache.struts2.interceptor.CheckboxInterceptor.intercept(CheckboxInterceptor.java:91) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at org.apache.struts2.interceptor.FileUploadInterceptor.intercept(FileUploadInterceptor.java:252) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at com.opensymphony.xwork2.interceptor.ModelDrivenInterceptor.intercept(ModelDrivenInterceptor.java:100) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at com.opensymphony.xwork2.interceptor.ScopedModelDrivenInterceptor.intercept(ScopedModelDrivenInterceptor.java:141) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at com.opensymphony.xwork2.interceptor.ChainingInterceptor.intercept(ChainingInterceptor.java:145) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at com.opensymphony.xwork2.interceptor.PrepareInterceptor.doIntercept(PrepareInterceptor.java:171) at com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:98) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at com.opensymphony.xwork2.interceptor.I18nInterceptor.intercept(I18nInterceptor.java:176) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at org.apache.struts2.interceptor.ServletConfigInterceptor.intercept(ServletConfigInterceptor.java:164) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at com.opensymphony.xwork2.interceptor.AliasInterceptor.intercept(AliasInterceptor.java:193) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at com.opensymphony.xwork2.interceptor.ExceptionMappingInterceptor.intercept(ExceptionMappingInterceptor.java:187) at com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:246) at org.apache.struts2.impl.StrutsActionProxy.execute(StrutsActionProxy.java:54) at org.apache.struts2.dispatcher.Dispatcher.serviceAction(Dispatcher.java:546) at org.apache.struts2.dispatcher.ng.ExecuteOperations.executeAction(ExecuteOperations.java:77) at org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter.doFilter(StrutsPrepareAndExecuteFilter.java:91) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:947) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408) at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1009) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:724)

推荐答案

我在我的项目中遇到了类似的问题(我正在使用Eclipse Luna).

I encounter the similar problem in my project (i'm using Eclipse Luna).

我克服它的方法是将 MysqlDataSource 设置为 setUseOldAliasMetadataBehavior(true);

The way i overcome it, is to set the MysqlDataSource to setUseOldAliasMetadataBehavior(true);

这篇关于带有别名的MySQL select语句失败，找不到列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆