带别名的 MySQL select 语句失败，未找到列

Question

开发环境引用:

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)

以下 select 语句失败，并显示“未找到列‘深度’”:

"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";

此 select 语句使用命令级 mysql 成功

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 版本中使用 SQL 查询时，select 语句也会成功。 5.2.4.7。

相关Java代码片段如下:

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 连接字符串以恢复到原始行为，但这并不能解决问题。

这是堆栈跟踪:

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)

Answer 1

好吧，StackOverflow 的成员们，我发现了问题，但现在我因为长时间拔头发而秃头了。

在 Eclipse Juno 上，可以选择“组织导入”Java 源代码。”因此，当我有 undefined variable 时，我使用这个方便的选项来清除导入。Eclipse Juno 导入了 mysql 连接器，“java .sql.connection”而不是“com.mysql.jdbc.connection”连接器。我更改了 java 源的导入，现在我可以继续使用这个 servlet。但在我出去获取一些之后就不行了。保法止或落建还是？...罗伊 W.