oracle java.sql.SQLException: 列名无效

执行一段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.java:3319)at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:1926)at oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java:1515)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:396)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(ApplicationFilterChain.java:292)at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)at com.egosystems.webcore.RequestEvent.doFilter(RequestEvent.java:132)at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)at com.egosystems.webmsq.WebMSQService.doFilter(WebMSQService.java:41)at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)at com.egosystems.exkernel.ExRequestEvent.doFilter(ExRequestEvent.java:373)at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106)at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502)at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616)at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:522)at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1095)at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:672)at org.apache..AprEndpoint$SocketProcessor.doRun(AprEndpoint.java:2500)at org.apache..AprEndpoint$SocketProcessor.run(AprEndpoint.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(TaskThread.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,USERNAME) where rowno <=20 and rowno> 0) e left join app_user_pro p on e.userid=p.userid order by NAME,e.USERNAME

把SQL贴到PL/SQL 中执行，没有问题。在网上搜集了几种解决办法：

重启oracle服务。查询出来的列名和getXxx("XXX");使用的列名不一致。如果加了表的别名,在处理结果集的时候,也一定要给查询的列一个别名,不然就会莫名其妙的报"列名无效"的错误提示!给每列都加上个别名,然后jdbc访问别名,执行就OK了。

解决了，是第二个办法，如果pl/sql查没有问题，那么看看结果集列名和你要获取的列名是否一致，问题大多出在这，仔细看。

如果觉得《oracle java.sql.SQLException: 列名无效》对你有帮助，请点赞、收藏，并留下你的观点哦！