示例#1
0
  @Override
  public AnswerList readDistinctValuesByCriteria(
      String searchTerm, Map<String, List<String>> individualSearch, String columnName) {
    AnswerList answer = new AnswerList();
    MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
    msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", ""));
    List<String> distinctValues = new ArrayList<>();
    StringBuilder searchSQL = new StringBuilder();
    List<String> individalColumnSearchValues = new ArrayList<String>();

    StringBuilder query = new StringBuilder();

    query.append("SELECT distinct sol.");
    query.append(columnName);
    query.append(" as distinctValues FROM soaplibrary sol");
    query.append(" where 1=1");

    if (!StringUtil.isNullOrEmpty(searchTerm)) {
      searchSQL.append(" and (sol.Name like ?");
      searchSQL.append(" or sol.Type like ?");
      searchSQL.append(" or sol.ServicePath like ?");
      searchSQL.append(" or sol.Method like ?");
      searchSQL.append(" or sol.ParsingAnswer like ?");
      searchSQL.append(" or sol.Description like ?");
      searchSQL.append(" or sol.Envelope like ?)");
    }
    if (individualSearch != null && !individualSearch.isEmpty()) {
      searchSQL.append(" and ( 1=1 ");
      for (Map.Entry<String, List<String>> entry : individualSearch.entrySet()) {
        searchSQL.append(" and sol.");
        searchSQL.append(
            SqlUtil.getInSQLClauseForPreparedStatement(entry.getKey(), entry.getValue()));
        individalColumnSearchValues.addAll(entry.getValue());
      }
      searchSQL.append(" )");
    }
    query.append(searchSQL);
    query.append(" group by ifnull(sol.").append(columnName).append(",'')");
    query.append(" order by sol.").append(columnName).append(" asc");

    // Debug message on SQL.
    if (LOG.isDebugEnabled()) {
      LOG.debug("SQL : " + query.toString());
    }
    try (Connection connection = databaseSpring.connect();
        PreparedStatement preStat = connection.prepareStatement(query.toString())) {

      int i = 1;
      if (!StringUtil.isNullOrEmpty(searchTerm)) {
        preStat.setString(i++, "%" + searchTerm + "%");
        preStat.setString(i++, "%" + searchTerm + "%");
        preStat.setString(i++, "%" + searchTerm + "%");
        preStat.setString(i++, "%" + searchTerm + "%");
        preStat.setString(i++, "%" + searchTerm + "%");
        preStat.setString(i++, "%" + searchTerm + "%");
        preStat.setString(i++, "%" + searchTerm + "%");
      }
      for (String individualColumnSearchValue : individalColumnSearchValues) {
        preStat.setString(i++, individualColumnSearchValue);
      }

      ResultSet resultSet = preStat.executeQuery();

      // gets the data
      while (resultSet.next()) {
        distinctValues.add(
            resultSet.getString("distinctValues") == null
                ? ""
                : resultSet.getString("distinctValues"));
      }

      // get the total number of rows
      resultSet = preStat.executeQuery("SELECT FOUND_ROWS()");
      int nrTotalRows = 0;

      if (resultSet != null && resultSet.next()) {
        nrTotalRows = resultSet.getInt(1);
      }

      if (distinctValues.size()
          >= MAX_ROW_SELECTED) { // Result of SQl was limited by MAX_ROW_SELECTED constrain. That
                                 // means that we may miss some lines in the resultList.
        LOG.error("Partial Result in the query.");
        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_WARNING_PARTIAL_RESULT);
        msg.setDescription(
            msg.getDescription()
                .replace("%DESCRIPTION%", "Maximum row reached : " + MAX_ROW_SELECTED));
        answer = new AnswerList(distinctValues, nrTotalRows);
      } else if (distinctValues.size() <= 0) {
        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_NO_DATA_FOUND);
        answer = new AnswerList(distinctValues, nrTotalRows);
      } else {
        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);
        msg.setDescription(
            msg.getDescription().replace("%ITEM%", OBJECT_NAME).replace("%OPERATION%", "SELECT"));
        answer = new AnswerList(distinctValues, nrTotalRows);
      }
    } catch (Exception e) {
      LOG.warn("Unable to execute query : " + e.toString());
      msg =
          new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED)
              .resolveDescription("DESCRIPTION", e.toString());
    } finally {
      // We always set the result message
      answer.setResultMessage(msg);
    }

    answer.setResultMessage(msg);
    answer.setDataList(distinctValues);
    return answer;
  }
示例#2
0
  @Override
  public AnswerList readByCriteria(
      int start,
      int amount,
      String column,
      String dir,
      String searchTerm,
      Map<String, List<String>> individualSearch) {

    AnswerList response = new AnswerList();
    MessageEvent msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
    msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", ""));
    List<SoapLibrary> objectList = new ArrayList<SoapLibrary>();
    StringBuilder searchSQL = new StringBuilder();
    List<String> individalColumnSearchValues = new ArrayList<String>();

    StringBuilder query = new StringBuilder();
    // SQL_CALC_FOUND_ROWS allows to retrieve the total number of columns by disrearding the limit
    // clauses that
    // were applied -- used for pagination p
    query.append("SELECT SQL_CALC_FOUND_ROWS * FROM soaplibrary sol ");

    query.append(" WHERE 1=1");

    if (!StringUtil.isNullOrEmpty(searchTerm)) {
      searchSQL.append(" and (sol.Name like ?");
      searchSQL.append(" or sol.Type like ?");
      searchSQL.append(" or sol.ServicePath like ?");
      searchSQL.append(" or sol.Method like ?");
      searchSQL.append(" or sol.ParsingAnswer like ?");
      searchSQL.append(" or sol.Description like ?");
      searchSQL.append(" or sol.Envelope like ?)");
    }
    if (individualSearch != null && !individualSearch.isEmpty()) {
      searchSQL.append(" and ( 1=1 ");
      for (Map.Entry<String, List<String>> entry : individualSearch.entrySet()) {
        searchSQL.append(" and ");
        String key = "IFNULL(sol." + entry.getKey() + ",'')";
        String q = SqlUtil.getInSQLClauseForPreparedStatement(key, entry.getValue());
        if (q == null || q == "") {
          q = "(sol." + entry.getKey() + " IS NULL OR " + entry.getKey() + " = '')";
        }
        searchSQL.append(q);
        individalColumnSearchValues.addAll(entry.getValue());
      }
      searchSQL.append(" )");
    }

    query.append(searchSQL);

    if (!StringUtil.isNullOrEmpty(column)) {
      query.append(" order by sol.").append(column).append(" ").append(dir);
    }

    if ((amount <= 0) || (amount >= MAX_ROW_SELECTED)) {
      query.append(" limit ").append(start).append(" , ").append(MAX_ROW_SELECTED);
    } else {
      query.append(" limit ").append(start).append(" , ").append(amount);
    }

    // Debug message on SQL.
    if (LOG.isDebugEnabled()) {
      LOG.debug("SQL : " + query.toString());
    }
    Connection connection = this.databaseSpring.connect();
    try {
      PreparedStatement preStat = connection.prepareStatement(query.toString());
      try {
        int i = 1;

        if (!StringUtil.isNullOrEmpty(searchTerm)) {
          preStat.setString(i++, "%" + searchTerm + "%");
          preStat.setString(i++, "%" + searchTerm + "%");
          preStat.setString(i++, "%" + searchTerm + "%");
          preStat.setString(i++, "%" + searchTerm + "%");
          preStat.setString(i++, "%" + searchTerm + "%");
          preStat.setString(i++, "%" + searchTerm + "%");
          preStat.setString(i++, "%" + searchTerm + "%");
        }
        for (String individualColumnSearchValue : individalColumnSearchValues) {
          preStat.setString(i++, individualColumnSearchValue);
        }

        ResultSet resultSet = preStat.executeQuery();
        try {
          // gets the data
          while (resultSet.next()) {
            objectList.add(this.loadFromResultSet(resultSet));
          }

          // get the total number of rows
          resultSet = preStat.executeQuery("SELECT FOUND_ROWS()");
          int nrTotalRows = 0;

          if (resultSet != null && resultSet.next()) {
            nrTotalRows = resultSet.getInt(1);
          }

          if (objectList.size()
              >= MAX_ROW_SELECTED) { // Result of SQl was limited by MAX_ROW_SELECTED constrain.
                                     // That means that we may miss some lines in the resultList.
            LOG.error("Partial Result in the query.");
            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_WARNING_PARTIAL_RESULT);
            msg.setDescription(
                msg.getDescription()
                    .replace("%DESCRIPTION%", "Maximum row reached : " + MAX_ROW_SELECTED));
            response = new AnswerList(objectList, nrTotalRows);
          } else if (objectList.size() <= 0) {
            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_NO_DATA_FOUND);
            response = new AnswerList(objectList, nrTotalRows);
          } else {
            msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_OK);
            msg.setDescription(
                msg.getDescription()
                    .replace("%ITEM%", OBJECT_NAME)
                    .replace("%OPERATION%", "SELECT"));
            response = new AnswerList(objectList, nrTotalRows);
          }

        } catch (SQLException exception) {
          LOG.error("Unable to execute query : " + exception.toString());
          msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
          msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));

        } finally {
          if (resultSet != null) {
            resultSet.close();
          }
        }

      } catch (SQLException exception) {
        LOG.error("Unable to execute query : " + exception.toString());
        msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
        msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));
      } finally {
        if (preStat != null) {
          preStat.close();
        }
      }

    } catch (SQLException exception) {
      LOG.error("Unable to execute query : " + exception.toString());
      msg = new MessageEvent(MessageEventEnum.DATA_OPERATION_ERROR_UNEXPECTED);
      msg.setDescription(msg.getDescription().replace("%DESCRIPTION%", exception.toString()));
    } finally {
      try {
        if (!this.databaseSpring.isOnTransaction()) {
          if (connection != null) {
            connection.close();
          }
        }
      } catch (SQLException exception) {
        LOG.warn("Unable to close connection : " + exception.toString());
      }
    }

    response.setResultMessage(msg);
    response.setDataList(objectList);
    return response;
  }