java - SUM 函数和 GROUP BY 的列索引无效

Question

我有这个异常 (java.lang.RuntimeException: java.lang.RuntimeException: java.sql.SQLException: Invalid column index) 我试图解决它但它对我不起作用。

Jan 27, 2019 1:20:17 PM org.ajax4jsf.component.AjaxViewRoot processEvents
SEVERE: Error processing faces event for the component 
SearchIdeaForm:j_id_jsp_550475895_12
javax.faces.event.AbortProcessingException: /Search.jsp(155,24) '# 
{searchIdeasBean.doSearchMyIdea}' java.lang.RuntimeException: 
java.lang.RuntimeException: java.sql.SQLException: Invalid column index
at javax.faces.event.MethodExpressionActionListener.processAction(MethodExpressionActionListener.java:118)
at javax.faces.event.ActionEvent.processListener(ActionEvent.java:88)
at javax.faces.component.UIComponentBase.broadcast(UIComponentBase.java:771)
at javax.faces.component.UICommand.broadcast(UICommand.java:372)
at org.ajax4jsf.component.AjaxViewRoot.processEvents(AjaxViewRoot.java:329)
at org.ajax4jsf.component.AjaxViewRoot.broadcastEventsForPhase(AjaxViewRoot.java:304)
at org.ajax4jsf.component.AjaxViewRoot.processPhase(AjaxViewRoot.java:261)
at org.ajax4jsf.component.AjaxViewRoot.processApplication(AjaxViewRoot.java:474)
at com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:82)
at com.sun.faces.lifecycle.Phase.doPhase(Phase.java:100)
at com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:118)
at javax.faces.webapp.FacesServlet.service(FacesServlet.java:265)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
at org.ajax4jsf.webapp.BaseXMLFilter.doXmlFilter(BaseXMLFilter.java:206)
at org.ajax4jsf.webapp.BaseFilter.handleRequest(BaseFilter.java:290)
at org.ajax4jsf.webapp.BaseFilter.processUploadsAndHandleRequest(BaseFilter.java:388)
at org.ajax4jsf.webapp.BaseFilter.doFilter(BaseFilter.java:515)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
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 org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:94)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:492)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:80)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:620)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:502)
at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1152)
at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:684)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1539)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.run(NioEndpoint.java:1495)
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Unknown Source)
Caused by: java.lang.RuntimeException: java.lang.RuntimeException: java.sql.SQLException: Invalid column index
at gov.adjd.iim.delegate.IIMDelegate.searchMyIdeas(IIMDelegate.java:325)
at iim.backbean.SearchIdeasBean.doSearchMyIdea(SearchIdeasBean.java:54)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.el.parser.AstValue.invoke(AstValue.java:247)
at org.apache.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:267)
at org.apache.jasper.el.JspMethodExpression.invoke(JspMethodExpression.java:74)
at javax.faces.event.MethodExpressionActionListener.processAction(MethodExpressionActionListener.java:99)
... 38 more
Caused by: java.lang.RuntimeException: java.sql.SQLException: Invalid column index
at im.dao.IIMDao.search(IIMDao.java:859)
at iim.delegate.IIMDelegate.search(IIMDelegate.java:320)
... 47 more
Caused by: java.sql.SQLException: Invalid column index
at oracle.jdbc.driver.OracleResultSetImpl.getInt(OracleResultSetImpl.java:876)
at iim.dao.IIMDao.search(IIMDao.java:888)
at iim.dao.IIMDao.search(IIMDao.java:856)
... 48 more

这是我的查询方法:

public List<SearchResultDto> search(SearchDto searchDto, int userId) {

    Connection connection = null;

    PreparedStatement preparedStatement = null;
    ResultSet searchResultSet = null;

    try {
        connection = getConnection();
        String mainSql = "SELECT IDEA.I_ID,IDEA.I_NO,IDEA.I_TITLE,IDEA.I_DESCRIPITION,IDEA.I_CREATED_DATE,STATUS.S_DESCRIPTION,APPL_USER.U_NAME,SUM(EARNED_POINTS.EP_POINTS) AS TOTAL "
                + "FROM IDEA IDEA,STATUS STATUS,APPL_USER APPL_USER,EARNED_POINTS EARNED_POINTS "
                + "WHERE IDEA.I_STATUS_CODE = STATUS.S_CODE AND EARNED_POINTS.IDEA_ID = IDEA.I_ID "
                + "AND IDEA.I_CREATED_USER_ID = APPL_USER.U_SEQ AND EARNED_POINTS.USER_ID = ? AND IDEA.I_CREATED_USER_ID = ? "
                + "GROUP BY (IDEA.I_ID,IDEA.I_NO,IDEA.I_TITLE,IDEA.I_DESCRIPITION,IDEA.I_CREATED_DATE,STATUS.S_DESCRIPTION,APPL_USER.U_NAME,EARNED_POINTS.EP_POINTS)";

        if (searchDto.getIdeaNo() == null && (searchDto.getIdeaTitle() == null)
                && searchDto.getIdeaStatus() == null) {
            preparedStatement = connection.prepareStatement(mainSql);
            preparedStatement.setObject(1, userId);
            preparedStatement.setObject(2, userId);
        }


        searchResultSet = preparedStatement.executeQuery();

        return search(searchResultSet);

    } catch (Exception e) {
        throw new RuntimeException(e);
    } finally {
        try {
            if (searchResultSet != null) {
                searchResultSet.close();
            }

            preparedStatement.close();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

private List<SearchResultDto> search(ResultSet searchResultSet) throws SQLException {
    List<SearchResultDto> result = new ArrayList<SearchResultDto>();

    SearchResultDto searchResultDto = null;

    while (searchResultSet.next()) {
        searchResultDto = new SearchResultDto();

        searchResultDto.setIdeaId(searchResultSet.getLong(1));
        searchResultDto.setIdeaNo(searchResultSet.getLong(2));
        searchResultDto.setTitle(searchResultSet.getString(3));
        searchResultDto.setDescription(searchResultSet.getString(4));
        searchResultDto.setCreatedDate(searchResultSet.getDate(5));
        searchResultDto.setStatusDescription(searchResultSet.getString(6));
        searchResultDto.setIdeaCreator(searchResultSet.getString(7));
        searchResultDto.setPoint(searchResultSet.getInt(8));

        result.add(searchResultDto);

    }

    return result;
}

我知道在查询或我得到搜索结果的地方有小错误。

任何评论!

Answer 1

谢谢大家

这是正确的答案，只是我从 GROUP BY 中删除了 (EARNED_POINTS.EP_POINT)。

GROUP BY IDEA.I_ID,IDEA.I_NO,IDEA.I_TITLE,IDEA.I_DESCRIPITION,IDEA.I_CREATED_DATE,STATUS.S_DESCRIPTION,APPL_USER.U_NAME;