java - SQLException(无效的列索引) - 页面中没有显示结果

Question

我正在尝试基于数据库中的特定字段应用搜索(选择语句)，但我有一个异常(exception):

java.sql.SQLException: Invalid column index. 

public List<SearchResultDto> search(SearchDto searchDto) {
    Connection connection = null;

    PreparedStatement preparedStatement = null;
    ResultSet searchResultSet = null;

    try {
        connection = getConnection();
        preparedStatement = connection.prepareStatement(
                "SELECT I_ID, I_NO, I_TITLE, I_DESCRIPTION, I_CREATED_DATE, STATUS.S_DESCRIPTION, APPL_USER.U_NAME FROM IDEA IDEA, STATUS STATUS, APPL_USER APPL_USER WHERE IDEA.I_STATUS_CODE = STATUS.S_CODE AND IDEA.I_CREATED_USER_ID = APPL_USER.U_SEQ AND IDEA.I_NO = ? OR IDEA.I_TITLE LIKE '%?%' OR TRUNC(IDEA.I_CREATED_DATE)= ?  OR STATUS.S_CODE = ? ");



        // Assign first value to first parameter
        preparedStatement.setLong(1, searchDto.getIdeaNo());
        preparedStatement.setString(2, searchDto.getIdeaTitle());
        preparedStatement.setDate(3, searchDto.getCreatedDate() == null ? null
                : new java.sql.Date(searchDto.getCreatedDate().getTime()));
        preparedStatement.setObject(4, searchDto.getIdeaStatus());

        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()) {
        ideaSearchResultDto = 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));

        result.add(searchResultDto);

    }

    return result;
}

这是我的 jsf 文件:

                                <table>
                                    <tr>
                                        <td>
                                            <table>
                                                <tr>
                                                    <td width="70px"><h:outputText value="No"></h:outputText>
                                                    </td>
                                                    <td width="5px"> </td>
                                                    <td><h:inputText

                                                            maxlength="10" style="width:150px"
                                                            value="#{searchBean.searchDto.no}"></h:inputText>
                                                    </td>
                                                    <td width="50px"> </td>
                                                    <td width="70px"><h:outputText value="status"></h:outputText>
                                                    </td>
                                                    <td width="5px"> </td>
                                                    <td><h:selectOneMenu
                                                            value="#{searchBean.searchDto.status}"
                                                            style="width:150px">
                                                            <f:selectItems value="#{searchBean.statuses}" />
                                                        </h:selectOneMenu></td>
                                                </tr>
                                            </table>
                                        </td>
                                    </tr>
                                    <tr>
                                        <td>
                                            <table>
                                                <tr>
                                                    <td width="70px"><h:outputText value="Title"></h:outputText>
                                                    </td>
                                                    <td width="5px"> </td>
                                                    <td><h:inputText maxlength="100"
                                                            value="#{searchsBean.searchDto.Title}"
                                                            style="width:150px"></h:inputText></td>
                                                    <td width="50px"> </td>
                                                    <td width="70px"><h:outputText value="created Date"></h:outputText>
                                                    </td>
                                                    <td width="5px"> </td>
                                                    <td><rich:calendar
                                                            value="#{searchBean.searchDto.createdDate}"
                                                            datePattern="yyyy-MM-dd" inputStyle="width:150px"></rich:calendar>
                                                    </td>
                                                </tr>
                                            </table>
                                        </td>
                                    </tr>

                                </table>
                            </rich:panel></td>
                    </tr>
                </table>
            </td>
        </tr>


        <tr>
            <td width="100%" align="center">
                <table>
                    <tr>

                        <td width="3px"><h:commandButton image="search.png"
                                actionListener="#{searchBean.doSearch}"></h:commandButton>
                        <td width="3px"><h:commandButton image="clean.png"
                                actionListener="#{searchBean.doClear}"></h:commandButton>

                    </tr>
                </table>
            </td>
        </tr>





        <tr>
            <td>
                <table>
                    <tr>
                        <td><rich:datascroller renderIfSinglePage="false" align="center" for="ideasTable"
                                id="ideasTableScroller" /> 
                                <rich:dataTable
                                rendered="#{searchBean.showResultsTable}" rows="15"
                                rowClasses="odd-row, even-row"
                                value="#{searchIBean.result}" var="record"
                                id="ideasTable">
                                <rich:column width="80px" style="text-align: center;">
                                    <f:facet name="header">
                                        <h:outputText value="No"></h:outputText>
                                    </f:facet>
                                    <h:outputText value="#{record.no}"></h:outputText>
                                </rich:column>
                                <rich:column width="250px" style="text-align: center;">
                                    <f:facet name="header">
                                        <h:outputText value="title"></h:outputText>
                                    </f:facet>
                                    <h:outputText value="#{record.title}"></h:outputText>
                                </rich:column>
                                <rich:column width="90px" style="text-align: center;">
                                    <f:facet name="header">
                                        <h:outputText value="description"></h:outputText>
                                    </f:facet>
                                    <h:outputText value="#{record.description}"></h:outputText>
                                </rich:column>
                                <rich:column width="90px" style="text-align: center;">
                                    <f:facet name="header">
                                        <h:outputText value="created Date"></h:outputText>
                                    </f:facet>
                                    <h:outputText value="#{record.createdDate}"></h:outputText>
                                </rich:column>
                                <rich:column width="80px" style="text-align: center;">
                                    <f:facet name="header">
                                        <h:outputText value="status"></h:outputText>
                                    </f:facet>
                                    <h:outputText value="#{record.statusDescription}"></h:outputText>
                                </rich:column>
                                <rich:column width="70px" style="text-align: center;">
                                    <f:facet name="header">
                                        <h:outputText value="creator"></h:outputText>
                                    </f:facet>
                                    <h:outputText value="#{record.creator}"></h:outputText>
                                </rich:column>

                                <rich:column width="115px" style="text-align: center;">
                                    <table width="100%">
                                        <tr>
                                            <td><h:commandLink
                                                    action="#{searchBean.goDetails}" value="Details">
                                                    <f:param name="ideaId" value="#{record.id}"></f:param>
                                                </h:commandLink></td>
                                        </tr>

                                        <tr>
                                            <td><h:commandLink
                                                    action="#{searchBean.goAttachments}"
                                                    value="Attachments">
                                                    <f:param name="ideaId" value="#{record.id}"></f:param>
                                                </h:commandLink></td>
                                        </tr>

                                    </table>
                                </rich:column> 
                            </rich:dataTable></td>
                    </tr>

我在 bean 中有这个方法:

public void doSearch(ActionEvent actionEvent) {
    Delegate delegate = new Delegate();

    result = delegate.search(searchDto);

    if (result == null || result.size() == 0) {
        addInformationMessage("no data");
    }
}


public String goDetails() {
    long ideaId = Long.parseLong(FacesContext.getCurrentInstance().getExternalContext().getRequestParameterMap().get("ideaId"));

    FacesContext.getCurrentInstance().getExternalContext().getRequestMap().put("ideaId", 
        ideaId);

    return "goIdeaDetails";
}

faces-config.xml:

<navigation-rule>
    <display-name>Search</display-name>
    <from-view-id>/Search</from-view-id>
    <navigation-case>
        <from-outcome>goIdeaDetails</from-outcome>
        <to-view-id>/IdeaDetails</to-view-id>
    </navigation-case>
</navigation-rule>

异常可能来自于推荐链接的操作吗？

Answer 1

这是因为IDEA.I_TITLE LIKE '%?%'。您不应在通配符上放置任何引号。

您应该将查询调整为:IDEA.I_TITLE LIKE ?

并将参数更改为

preparedStatement.setString(2, "%"+searchDto.getIdeaTitle()+"%");