java - 查询工作时sql命令未正确结束是什么意思

Question

我有这个查询，它在sql开发人员中工作，我得到了结果，但在java中它给我的sql命令没有正确结束。此查询将在 3 个字段上进行搜索，如果所有字段都为空，则会在 where 线索上生成所有字段，如果输入的任何一个字段都会生成输入字段的结果。

这是查询:

 public List<IdeaSearchResultDto> searchAwardTeamUI(IdeaSearchDto ideaSearchDto) {

          Connection connection = null;

          PreparedStatement preparedStatement = null;
          ResultSet searchResultSet = null;

          try {
                 connection = getConnection();

                 if (ideaSearchDto.getIdeaNo() == null && ideaSearchDto.getIdeaStatus() == null
                              && ideaSearchDto.getIdeaTitle() == null) {
                       preparedStatement = connection.prepareStatement("SELECT I_ID,I_NO,I_TITLE,I_DESCRIPITION,I_CREATED_DATE,STATUS.S_DESCRIPTION,CREATOR_USER.U_NAME,APPROVER_USER.U_NAME,OFFICER_USER.U_NAME FROM IDEA IDEA,STATUS STATUS,APPL_USER CREATOR_USER,APPL_USER APPROVER_USER,APPL_USER OFFICER_USER WHERE IDEA.I_STATUS_CODE = STATUS.S_CODE AND IDEA.I_CREATED_USER_ID = CREATOR_USER.U_SEQ AND IDEA.I_APPROVER_NAME_CODE = APPROVER_USER.U_SEQ AND IDEA.I_IMMPLEMENTATION_OFFICER = OFFICER_USER.U_SEQ");
                 }

                 if (ideaSearchDto.getIdeaNo() != null && ideaSearchDto.getIdeaTitle() != null
                              && ideaSearchDto.getIdeaStatus() != null) {
                       preparedStatement = connection.prepareStatement(
                                     "SELECT I_ID,I_NO,I_TITLE,I_DESCRIPITION,I_CREATED_DATE,STATUS.S_DESCRIPTION,CREATOR_USER.U_NAME,APPROVER_USER.U_NAME,OFFICER_USER.U_NAME FROM IDEA IDEA,STATUS STATUS,APPL_USER CREATOR_USER,APPL_USER APPROVER_USER,APPL_USER OFFICER_USER  WHERE IDEA.I_STATUS_CODE= STATUS.S_CODE AND IDEA.I_CREATED_USER_ID  = CREATOR_USER.U_SEQ AND IDEA.I_APPROVER_NAME_CODE= APPROVER_USER.U_SEQ AND IDEA.I_IMMPLEMENTATION_OFFICER = OFFICER_USER.U_SEQ"
                                                   + "AND STATUS.S_CODE = ? AND IDEA.I_NO = ? AND IDEA.I_TITLE like ?");
                       preparedStatement.setObject(1, ideaSearchDto.getIdeaStatus());

                       preparedStatement.setObject(2, ideaSearchDto.getIdeaNo());
                       preparedStatement.setString(3, "%" + ideaSearchDto.getIdeaTitle() + "%");

                 }
                 if (ideaSearchDto.getIdeaNo() != null && ideaSearchDto.getIdeaTitle() == null
                              && ideaSearchDto.getIdeaStatus() == null) {

                       preparedStatement = connection.prepareStatement(
                                     "SELECT I_ID,I_NO,I_TITLE,I_DESCRIPITION,I_CREATED_DATE,STATUS.S_DESCRIPTION,CREATOR_USER.U_NAME,APPROVER_USER.U_NAME,OFFICER_USER.U_NAME FROM IDEA IDEA,STATUS STATUS,APPL_USER CREATOR_USER,APPL_USER APPROVER_USER,APPL_USER OFFICER_USER  WHERE IDEA.I_STATUS_CODE= STATUS.S_CODE AND IDEA.I_CREATED_USER_ID  = CREATOR_USER.U_SEQ AND IDEA.I_APPROVER_NAME_CODE= APPROVER_USER.U_SEQ AND IDEA.I_IMMPLEMENTATION_OFFICER = OFFICER_USER.U_SEQ"
                                                   + "AND IDEA.I_NO = ?");
                       preparedStatement.setObject(1, ideaSearchDto.getIdeaNo());

                 }
                 if (ideaSearchDto.getIdeaNo() == null && ideaSearchDto.getIdeaTitle() != null
                              && ideaSearchDto.getIdeaStatus() == null) {
                       preparedStatement = connection.prepareStatement(
                                     "SELECT I_ID,I_NO,I_TITLE,I_DESCRIPITION,I_CREATED_DATE,STATUS.S_DESCRIPTION,CREATOR_USER.U_NAME,APPROVER_USER.U_NAME,OFFICER_USER.U_NAME FROM IDEA IDEA,STATUS STATUS,APPL_USER CREATOR_USER,APPL_USER APPROVER_USER,APPL_USER OFFICER_USER  WHERE IDEA.I_STATUS_CODE= STATUS.S_CODE AND IDEA.I_CREATED_USER_ID  = CREATOR_USER.U_SEQ AND IDEA.I_APPROVER_NAME_CODE= APPROVER_USER.U_SEQ AND IDEA.I_IMMPLEMENTATION_OFFICER = OFFICER_USER.U_SEQ"
                                                   + "AND IDEA.I_TITLE LIKE ?");
                       preparedStatement.setObject(1, "%" + ideaSearchDto.getIdeaTitle() + "%");
                 }
                 if (ideaSearchDto.getIdeaNo() == null && ideaSearchDto.getIdeaTitle() == null
                              && ideaSearchDto.getIdeaStatus() != null) {
                       preparedStatement = connection.prepareStatement(
                                     "SELECT I_ID,I_NO,I_TITLE,I_DESCRIPITION,I_CREATED_DATE,STATUS.S_DESCRIPTION,CREATOR_USER.U_NAME,APPROVER_USER.U_NAME,OFFICER_USER.U_NAME FROM IDEA IDEA,STATUS STATUS,APPL_USER CREATOR_USER,APPL_USER APPROVER_USER,APPL_USER OFFICER_USER  WHERE IDEA.I_STATUS_CODE= STATUS.S_CODE AND IDEA.I_CREATED_USER_ID  = CREATOR_USER.U_SEQ AND IDEA.I_APPROVER_NAME_CODE= APPROVER_USER.U_SEQ AND IDEA.I_IMMPLEMENTATION_OFFICER = OFFICER_USER.U_SEQ"
                                                   + "AND STATUS.S_CODE = ?");
                       preparedStatement.setObject(1, ideaSearchDto.getIdeaStatus());
                 }
                 searchResultSet = preparedStatement.executeQuery();

                 return searchAwardTeamUI(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<IdeaSearchResultDto> searchAwardTeamUI(ResultSet searchResultSet) throws SQLException {
          List<IdeaSearchResultDto> result = new ArrayList<IdeaSearchResultDto>();

          IdeaSearchResultDto ideaSearchResultDto = null;

          while (searchResultSet.next()) {
                 ideaSearchResultDto = new IdeaSearchResultDto();

                 ideaSearchResultDto.setIdeaId(searchResultSet.getLong(1));
                 ideaSearchResultDto.setIdeaNo(searchResultSet.getLong(2));
                 ideaSearchResultDto.setTitle(searchResultSet.getString(3));
                 ideaSearchResultDto.setDescription(searchResultSet.getString(4));
                 ideaSearchResultDto.setCreatedDate(searchResultSet.getDate(5));
                 ideaSearchResultDto.setStatusDescription(searchResultSet.getString(6));
                 ideaSearchResultDto.setIdeaCreator(searchResultSet.getString(7));
                 ideaSearchResultDto.setIdeaApprover(searchResultSet.getString(8));
                 ideaSearchResultDto.setIdeaImmplementationOffName(searchResultSet.getString(9));


                 result.add(ideaSearchResultDto);

          }

          return result;
   }

我更新了代码

Answer 1

您是否尝试过添加“;”最后？