本文详细解析了在执行复杂SQL查询时遇到的“列名无效”异常问题，并提供了有效的解决方案。通过对比PL/SQL查询结果与Java JDBC访问结果，发现列名不一致是导致异常的主要原因。文章最后强调了在使用表别名时，对查询列添加别名的重要性。
执行一段sql，
String sql="select e.*,nvl(p.userid,0) as proid from (select * from (select ROWNUM as rowno,ee.* from (select * from SYS_USER_SIMPLE where (SYS_USER_SIMPLE.userid not in (select SYS_USER.userid from SYS_USER)) union select * from SYS_USER) ee " + " where nvl(ORGNAME,'*') like '%"+dept+"%' and username like '%"+name+"%' order by ORGNAME,USERNAME) where rowno <="+maxSize+" and rowno> " + pageSize + ") e"
System.out.println("sql========"+sql);
dao.rs=dao.query(DbType.Oracle, sql, null);
while (dao.rs.next()) {
map = new LinkedHashMap<Object, Object>();
map.put("userid", dao.rs.getString("USERID")==null?"":dao.rs.getString("USERID"));
map.put("username", dao.rs.getString("USERNAME")==null?"":dao.rs.getString("USERNAME"));
map.put("deptname", dao.rs.getString("ORGNAME")==null?"":dao.rs.getString("ORGNAME"));
map.put("orgcode", dao.rs.getString("ORDERID")==null?"":dao.rs.getString("ORDERID"));
map.put("ispro", dao.rs.getString("ispro")==null?"":dao.rs.getString("ispro"));
map.put("startlevel", dao.rs.getString("startlevel")==null?"":dao.rs.getString("startlevel"));
map.put("proid", dao.rs.getString("proid")==null?"":dao.rs.getString("proid"));
//map.put("appgrant", dao.rs.getString("appgrant")==null?"":dao.rs.getString("appgrant"));
if("0".equals(dao.rs.getString("appgrant"))){
map.put("appgrant", "不授权");
}else if("1".equals(dao.rs.getString("appgrant"))){
map.put("appgrant", "授权");
}
list.add(map);
}
异常信息：
java.sql.SQLException: 列名无效
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java
:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java
:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java
:208)
at oracle.jdbc.driver.OracleStatement.getColumnIndex(OracleStatement.jav
a:3319)
at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl
.java:1926)
at oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java:151
5)
at org.apache.jsp.mobileweb.qwer_jsp._jspService(qwer_jsp.java:502)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper
.java:438)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:3
96)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:340)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:292)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:207)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52
)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:207)
at com.egosystems.webcore.RequestEvent.doFilter(RequestEvent.java:132)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:207)
at com.egosystems.webmsq.WebMSQService.doFilter(WebMSQService.java:41)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:207)
at com.egosystems.exkernel.ExRequestEvent.doFilter(ExRequestEvent.java:3
73)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(Appl
icationFilterChain.java:240)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationF
ilterChain.java:207)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperV
alve.java:212)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextV
alve.java:106)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(Authentica
torBase.java:502)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.j
ava:141)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.j
ava:79)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAcce
ssLogValve.java:616)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineVal
ve.java:88)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.jav
a:522)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp
11Processor.java:1095)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(
AbstractProtocol.java:672)
at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.doRun(AprEndpo
int.java:2500)
at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoin
t.java:2489)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.
java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor
.java:615)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskTh
read.java:61)
at java.lang.Thread.run(Thread.java:745)
后台sql打印为
select e.*,nvl(p.userid,0) as proid from (select * from (select ROWNUM as rowno,ee.* from (select * from SYS_USER_SIMPLE where (SYS_USER_SIMPLE.userid not in (select SYS_USER.userid from SYS_USER)) union select * from SYS_USER) ee where nvl(ORGNAME,'*') like '%%' and username like '%%' order by ORGNAME,USER
NAME) where rowno <=20 and rowno> 0) e left join app_user_pro p on e.userid=p.userid order by e.ORGNAME,e.USERNAME
把SQL贴到PL/SQL 中执行，没有问题。在网上搜集了几种解决办法：
- 重启oracle服务。
- 查询出来的列名和getXxx("XXX");使用的列名不一致。
- 如果加了表的别名,在处理结果集的时候,也一定要给查询的列一个别名,不然就会莫名其妙的报"列名无效"的错误提示!给每列都加上个别名,然后jdbc访问别名,执行就OK了。
解决了，是第二个办法，如果pl/sql查没有问题，那么看看结果集列名和你要获取的列名是否一致，问题大多出在这，仔细看。
扫一扫
3197
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
