コード例 #1
0
  /**
   * Counts the number of affected rows, using this where clause.
   *
   * @param whereClauseParts where clause to use
   * @param conn connection to use
   * @return number of rows in the database, which will be selected by the given whereClauseParts
   * @throws SQLException if an SQLExcetpion occurs.
   */
  private int count(List<IWhereClausePart> whereClauseParts, final ISQLConnection conn)
      throws SQLException {
    int count;
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      String whereClause = whereClausePartUtil.createWhereClause(whereClauseParts);
      String countSql = "select count(*) from " + ti.getQualifiedName() + whereClause;
      pstmt = conn.prepareStatement(countSql);
      whereClausePartUtil.setParameters(pstmt, whereClauseParts, 1);

      rs = pstmt.executeQuery();
      rs.next();
      count = rs.getInt(1);
    } finally {
      // We don't care if these throw an SQLException.  Just squelch them
      // and report to the user what the outcome of the previous statements
      // were.
      SQLUtilities.closeResultSet(rs);
      SQLUtilities.closeStatement(pstmt);
    }
    return count;
  }
コード例 #2
0
  /**
   * Delete a set of rows from the DB. If the delete succeeded this returns a null string. The
   * deletes are done within a transaction so they are either all done or all not done.
   */
  public String deleteRows(Object[][] rowData, ColumnDisplayDefinition[] colDefs) {

    // if we could not identify which table to edit, tell user
    if (ti == null) return TI_ERROR_MESSAGE;

    // get the SQL session
    final ISession session = _session;
    final ISQLConnection conn = session.getSQLConnection();

    // string used as error indicator and description of problems seen
    // when checking for 0 or mulitple matches in DB
    String rowCountErrorMessage = "";

    // for each row in table, count how many rows match where clause
    // if not exactly one, generate message describing situation
    for (int i = 0; i < rowData.length; i++) {
      // get WHERE clause for the selected row
      // the -1 says to just use the contents of the values without
      // any substitutions
      List<IWhereClausePart> whereClauseParts = getWhereClause(rowData[i], colDefs, -1, null);

      // count how many rows this WHERE matches
      try {

        int count = count(whereClauseParts, conn);
        if (count != 1) {
          if (count == 0) {
            // i18n[DataSetUpdateableTableModelImpl.error.rownotmatch=\n   Row {0}  did not match
            // any row in DB]
            rowCountErrorMessage +=
                s_stringMgr.getString(
                    "DataSetUpdateableTableModelImpl.error.rownotmatch", Integer.valueOf(i + 1));
          } else {
            // i18n[DataSetUpdateableTableModelImpl.error.rowmatched=\n   Row {0} matched {1} rows
            // in DB]
            rowCountErrorMessage +=
                s_stringMgr.getString(
                    "DataSetUpdateableTableModelImpl.error.rowmatched",
                    new Object[] {Integer.valueOf(i + 1), Integer.valueOf(count)});
          }
        }
      } catch (Exception e) {
        // some kind of problem - tell user
        // i18n[DataSetUpdateableTableModelImpl.error.preparingdelete=While preparing for delete,
        // saw exception:\n{0}]
        return s_stringMgr.getString("DataSetUpdateableTableModelImpl.error.preparingdelete", e);
      }
    }

    // if the rows do not match 1-for-1 to DB, ask user if they
    // really want to do delete
    if (rowCountErrorMessage.length() > 0) {
      // i18n[DataSetUpdateableTableModelImpl.error.tabledbmismatch=There may be a mismatch between
      // the table and the DB:\n{0}\nDo you wish to proceed with the deletes anyway?]
      String msg =
          s_stringMgr.getString(
              "DataSetUpdateableTableModelImpl.error.tabledbmismatch", rowCountErrorMessage);

      int option =
          JOptionPane.showConfirmDialog(
              null, msg, "Warning", JOptionPane.YES_NO_OPTION, JOptionPane.WARNING_MESSAGE);

      if (option != JOptionPane.YES_OPTION) {
        // i18n[DataSetUpdateableTableModelImpl.info.deletecancelled=Delete canceled at user
        // request.]
        return s_stringMgr.getString("DataSetUpdateableTableModelImpl.info.deletecancelled");
      }
    }

    // for each row in table, do delete and add to number of rows deleted from DB
    for (int i = 0; i < rowData.length; i++) {
      // get WHERE clause for the selected row
      // the -1 says to just use the contents of the values without
      // any substitutions
      List<IWhereClausePart> whereClauseParts = getWhereClause(rowData[i], colDefs, -1, null);
      String whereClause = whereClausePartUtil.createWhereClause(whereClauseParts);
      // try to delete
      try {
        // do the delete and add the number of rows deleted to the count
        String sql = "DELETE FROM " + ti.getQualifiedName() + whereClause;
        final PreparedStatement pstmt = conn.prepareStatement(sql);
        whereClausePartUtil.setParameters(pstmt, whereClauseParts, 1);
        try {
          pstmt.executeUpdate();
        } finally {
          pstmt.close();
        }
      } catch (Exception e) {
        // some kind of problem - tell user
        // i18n[DataSetUpdateableTableModelImpl.error.deleteFailed=One of the delete operations
        // failed with exception:\n{0}\nDatabase is in an unknown state and may be corrupted.]
        return s_stringMgr.getString("DataSetUpdateableTableModelImpl.error.deleteFailed", e);
      }
    }

    return null; // hear no evil, see no evil
  }
コード例 #3
0
  /** link from fw to this for updating data */
  public String updateTableComponent(
      Object[] values,
      ColumnDisplayDefinition[] colDefs,
      int col,
      Object oldValue,
      Object newValue) {
    // if we could not identify which table to edit, tell user
    if (ti == null) return TI_ERROR_MESSAGE;

    // get WHERE clause using original value
    List<IWhereClausePart> whereClauseParts = getWhereClause(values, colDefs, col, oldValue);
    String whereClause = whereClausePartUtil.createWhereClause(whereClauseParts);
    if (s_log.isDebugEnabled()) {
      s_log.debug("updateTableComponent: whereClause = " + whereClause);
    }

    final ISession session = _session;
    final ISQLConnection conn = session.getSQLConnection();

    int count = -1;

    final String sql =
        constructUpdateSql(ti.getQualifiedName(), colDefs[col].getColumnName(), whereClause);

    if (s_log.isDebugEnabled()) {
      s_log.debug("updateTableComponent: executing SQL - " + sql);
    }
    PreparedStatement pstmt = null;
    try {
      pstmt = conn.prepareStatement(sql);

      /*
       * have the DataType object fill in the appropriate kind of value of the changed data
       * into the first variable position in the prepared stmt
       */
      CellComponentFactory.setPreparedStatementValue(colDefs[col], pstmt, newValue, 1);

      // Fill the parameters of the where clause - start at position 2 because the data which is
      // updated is at position 1
      whereClausePartUtil.setParameters(pstmt, whereClauseParts, 2);
      count = pstmt.executeUpdate();
    } catch (SQLException ex) {
      // i18n[DataSetUpdateableTableModelImpl.error.updateproblem=There
      // was a problem reported during the update.
      // The DB message was:\n{0}\nThis may or may not be serious depending
      // on the above message.\nThe data was probably not changed in the
      // database.\nYou may need to refresh the table to get an accurate
      // view of the current data.]
      String errMsg =
          s_stringMgr.getString(
              "DataSetUpdateableTableModelImpl.error.updateproblem", ex.getMessage());
      s_log.error(
          "updateTableComponent: unexpected exception - "
              + ex.getMessage()
              + " while executing SQL: "
              + sql);

      return errMsg;
    } finally {
      SQLUtilities.closeStatement(pstmt);
    }

    if (count == -1) {
      // i18n[DataSetUpdateableTableModelImpl.error.unknownupdateerror=Unknown problem during
      // update.\nNo count of updated rows was returned.\nDatabase may be corrupted!]
      return s_stringMgr.getString("DataSetUpdateableTableModelImpl.error.unknownupdateerror");
    }
    if (count == 0) {
      // i18n[DataSetUpdateableTableModelImpl.info.norowsupdated=No rows updated.]
      return s_stringMgr.getString("DataSetUpdateableTableModelImpl.info.norowsupdated");
    }
    // everything seems to have worked ok
    return null;
  }
コード例 #4
0
  /**
   * Re-read the value for a single cell in the table, if possible. If there is a problem, the
   * message has a non-zero length when this returns.
   */
  public Object reReadDatum(
      Object[] values, ColumnDisplayDefinition[] colDefs, int col, StringBuffer message) {

    // if we could not identify which table to edit, tell user
    if (ti == null) return TI_ERROR_MESSAGE;

    // get WHERE clause
    // The -1 says to ignore the last arg and use the contents of the values array
    // for the column that we care about.  However, since the data in
    // that column has been limited, when getWhereClause calls that
    // DataType with that value, the DataType will see that the data has
    // been limited and therefore cannnot be used in the WHERE clause.
    // In some cases it may be possible for the DataType to use the
    // partial data, such as "matches <data>*", but that may not be
    // standard accross all Databases and thus may be risky.

    List<IWhereClausePart> whereClauseParts = getWhereClause(values, colDefs, -1, null);
    String whereClause = whereClausePartUtil.createWhereClause(whereClauseParts);
    final ISession session = _session;
    final ISQLConnection conn = session.getSQLConnection();

    Object wholeDatum = null;

    try {
      final String queryString =
          "SELECT " + colDefs[col].getColumnName() + " FROM " + ti.getQualifiedName() + whereClause;

      final PreparedStatement pstmt = conn.prepareStatement(queryString);
      whereClausePartUtil.setParameters(pstmt, whereClauseParts, 1);

      try {
        ResultSet rs = pstmt.executeQuery(queryString);

        // There should be one row in the data, so try to move to it
        if (rs.next() == false) {
          // no first row, so we cannot retrieve the data
          // i18n[DataSetUpdateableTableModelImpl.error.nomatchingrow=Could not find any row in DB
          // matching current row in table]
          throw new SQLException(
              s_stringMgr.getString("DataSetUpdateableTableModelImpl.error.nomatchingrow"));
        }

        // we have at least one row, so try to retrieve the object
        // Do Not limit the read of this data
        wholeDatum = CellComponentFactory.readResultSet(colDefs[col], rs, 1, false);

        //  There should not be more than one row in the DB that matches
        // the table, and if there is we cannot determine which one to read,
        // so check that there are no more
        if (rs.next() == true) {
          // multiple rows - not good
          wholeDatum = null;
          // i18n[DataSetUpdateableTableModelImpl.error.multimatchingrows=Muliple rows in DB match
          // current row in table - cannot re-read data.]
          throw new SQLException(
              s_stringMgr.getString("DataSetUpdateableTableModelImpl.error.multimatchingrows"));
        }
      } finally {
        pstmt.close();
      }
    } catch (Exception ex) {
      // i18n[DataSetUpdateableTableModelImpl.error.rereadingdb=There was a problem reported while
      // re-reading the DB.  The DB message was:\n{0}]
      message.append(
          s_stringMgr.getString(
              "DataSetUpdateableTableModelImpl.error.rereadingdb", ex.getMessage()));

      // It would be nice to tell the user what happened, but if we try to
      // put up a dialog box at this point, we run into trouble in some
      // cases where the field continually tries to re-read after the dialog
      // closes (because it is being re-painted).
    }

    // return the whole contents of this column in the DB
    return wholeDatum;
  };