/**
   * helper function to create a WHERE clause to search the DB for matching rows. If the col number
   * is < 0, then the colValue is ignored and the WHERE clause is constructed using only the
   * values[].
   */
  private List<IWhereClausePart> getWhereClause(
      Object[] values, ColumnDisplayDefinition[] colDefs, int col, Object colValue) {
    try {

      // For tables that have a lot of columns, the user may have limited the set of columns
      // to use in the where clause, so see if there is a table of col names
      HashMap<String, String> colNames = (EditWhereCols.get(getFullTableName()));

      ColumnDisplayDefinition editedCol = null;
      if (-1 != col) {
        editedCol = colDefs[col];
      }

      List<IWhereClausePart> clauseParts = new ArrayList<IWhereClausePart>();

      for (int i = 0; i < colDefs.length; i++) {

        if (i != col
            && null != editedCol
            && colDefs[i]
                .getFullTableColumnName()
                .equalsIgnoreCase(editedCol.getFullTableColumnName())) {
          // The edited column is in the resultset twice (example: SELECT MyName,* FROM MyTable).
          // We won't add the this col to the where clause.
          continue;
        }

        // if the user has said to not use this column, then skip it
        if (colNames != null) {
          // the user has restricted the set of columns to use.
          // If this name is NOT in the list, then skip it; otherwise we fall through
          // and use the column in the WHERE clause
          if (colNames.get(colDefs[i].getColumnName()) == null) continue; // go on to the next item
        }

        // for the column that is being changed, use the value
        // passed in by the caller (which may be either the
        // current value or the new replacement value)
        Object value = values[i];
        if (i == col) value = colValue;

        // convert user representation of null into an actual null
        if (value != null && value.toString().equals("<null>")) value = null;

        // do different things depending on data type
        ISQLDatabaseMetaData md = _session.getMetaData();
        IWhereClausePart clausePart =
            CellComponentFactory.getWhereClauseValue(colDefs[i], value, md);

        if (clausePart.shouldBeUsed())
          // Now we know that the part should not we ignoredshould
          clauseParts.add(clausePart);
      }

      return clauseParts;

    } catch (Exception e) {
      throw new RuntimeException(e);
    }
  }
    private TableColumnModel createColumnModel(ColumnDisplayDefinition[] colDefs) {
      // _colDefs = hdgs;
      TableColumnModel cm = new DefaultTableColumnModel();

      _rntc = new RowNumberTableColumn();

      for (int i = 0; i < colDefs.length; ++i) {
        ColumnDisplayDefinition colDef = colDefs[i];

        int colWidth;

        if (null == colDef.getAbsoluteWidth()) {
          colWidth = colDef.getDisplayWidth() * _multiplier;
          if (colWidth > MAX_COLUMN_WIDTH * _multiplier) {
            colWidth = MAX_COLUMN_WIDTH * _multiplier;
          } else if (colWidth < MIN_COLUMN_WIDTH * _multiplier) {
            colWidth = MIN_COLUMN_WIDTH * _multiplier;
          }
        } else {
          colWidth = colDef.getAbsoluteWidth();
        }

        ExtTableColumn col =
            new ExtTableColumn(
                i, colWidth, CellComponentFactory.getTableCellRenderer(colDefs[i]), null);

        String headerValue = colDef.getColumnHeading();
        col.setHeaderValue(headerValue);
        col.setColumnDisplayDefinition(colDef);
        cm.addColumn(col);
      }

      return cm;
    }
    /*
     * When user leaves a cell after editing it, the contents of
     * that cell need to be converted from a string into an
     * object of the appropriate type before updating the table.
     * However, when the call comes from the Popup window, the data
     * has already been converted and validated.
     * We assume that a String being passed in here is a value from
     * a text field that needs to be converted to an object, and
     * a non-string object has already been validated and converted.
     */
    public void setValueAt(Object newValueString, int row, int col) {
      if (!(newValueString instanceof java.lang.String)) {
        // data is an object - assume already validated
        super.setValueAt(newValueString, row, col);
        return;
      }

      // data is a String, so we need to convert to real object
      StringBuffer messageBuffer = new StringBuffer();

      int modelIndex = getColumnModel().getColumn(col).getModelIndex();
      ColumnDisplayDefinition colDef = getColumnDefinitions()[modelIndex];
      Object newValueObject =
          CellComponentFactory.validateAndConvert(
              colDef, getValueAt(row, col), (String) newValueString, messageBuffer);

      if (messageBuffer.length() > 0) {

        // i18n[dataSetViewerTablePanel.textCantBeConverted=The given text cannot be converted into
        // the internal object.\nThe database has not been changed.\nThe conversion error was:\n{0}]
        String msg =
            s_stringMgr.getString("dataSetViewerTablePanel.textCantBeConverted", messageBuffer);

        if (s_log.isDebugEnabled()) {
          s_log.debug("setValueAt: msg from DataTypeComponent was: " + msg);
        }

        // display error message and do not update the table
        JOptionPane.showMessageDialog(
            this,
            msg,
            // i18n[dataSetViewerTablePanel.conversionError=Conversion Error]
            s_stringMgr.getString("dataSetViewerTablePanel.conversionError"),
            JOptionPane.ERROR_MESSAGE);

      } else {
        // data converted ok, so update the table
        super.setValueAt(newValueObject, row, col);
      }
    }
Exemplo n.º 4
0
  private Cell getXlsCell(ColumnDisplayDefinition colDef, int colIdx, int curRow, Object cellObj) {
    Row row = sheet.getRow(curRow);
    if (row == null) {
      row = sheet.createRow(curRow);
    }
    Cell retVal = row.createCell(colIdx);

    if (null == cellObj || null == colDef) {
      retVal.setCellValue(getDataXLSAsString(cellObj));
      return retVal;
    }

    int colType = colDef.getSqlType();

    switch (colType) {
      case Types.BIT:
      case Types.BOOLEAN:
        if (null == cellObj) {
          // retVal.setCellValue((Boolean)null);
        } else {
          retVal.setCellValue((Boolean) cellObj);
        }
        break;
      case Types.INTEGER:
        if (null == cellObj) {
          // retVal.setCellValue((Integer)null);
        } else {
          retVal.setCellValue(((Number) cellObj).intValue());
        }
        break;
      case Types.SMALLINT:
      case Types.TINYINT:
        if (null == cellObj) {
          // retVal.setCellValue(((Short) null));
        } else {
          retVal.setCellValue(((Number) cellObj).shortValue());
        }
        break;
      case Types.NUMERIC:
      case Types.DECIMAL:
      case Types.FLOAT:
      case Types.DOUBLE:
      case Types.REAL:
        if (null == cellObj) {
          // retVal.setCellValue((Double) null);
        } else {
          retVal.setCellValue(((Number) cellObj).doubleValue());
        }
        break;
      case Types.BIGINT:
        if (null == cellObj) {
          // retVal.setCellValue((Long)null);
        } else {
          retVal.setCellValue(Long.parseLong(cellObj.toString()));
        }
        break;
      case Types.DATE:
        makeTemporalCell(retVal, (Date) cellObj, "m/d/yy");
        break;
      case Types.TIMESTAMP:
        makeTemporalCell(retVal, (Date) cellObj, "m/d/yy h:mm");
        break;
      case Types.TIME:
        makeTemporalCell(retVal, (Date) cellObj, "h:mm");
        break;
      case Types.CHAR:
      case Types.VARCHAR:
      case Types.LONGVARCHAR:
        cellObj = CellComponentFactory.renderObject(cellObj, colDef);
        retVal.setCellValue(getDataXLSAsString(cellObj));
        break;
      default:
        cellObj = CellComponentFactory.renderObject(cellObj, colDef);
        retVal.setCellValue(getDataXLSAsString(cellObj));
    }

    return retVal;
  }
  /** Insert a row into the DB. If the insert succeeds this returns a null string. */
  public String insertRow(Object[] values, ColumnDisplayDefinition[] colDefs) {

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

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

    int count = -1;

    try {
      // start the string for use in the prepared statment
      StringBuilder buf = new StringBuilder("INSERT INTO ");
      buf.append(ti.getQualifiedName());

      // Add the list of column names we will be inserting into - be sure
      // to skip the rowId column and any auto increment columns.
      buf.append(" ( ");
      for (int i = 0; i < colDefs.length; i++) {
        if (i == _rowIDcol) {
          continue;
        }
        if (colDefs[i].isAutoIncrement()) {
          if (s_log.isInfoEnabled()) {
            s_log.info("insertRow: skipping auto-increment column " + colDefs[i].getColumnName());
          }
          continue;
        }
        buf.append(colDefs[i].getColumnName());
        buf.append(",");
      }
      buf.setCharAt(buf.length() - 1, ')');
      buf.append(" VALUES (");

      // add a variable position for each of the columns
      for (int i = 0; i < colDefs.length; i++) {
        if (i != _rowIDcol && !colDefs[i].isAutoIncrement()) buf.append(" ?,");
      }

      // replace the last "," with ")"
      buf.setCharAt(buf.length() - 1, ')');

      String pstmtSQL = buf.toString();
      if (s_log.isInfoEnabled()) {
        s_log.info("insertRow: pstmt sql = " + pstmtSQL);
      }
      final PreparedStatement pstmt = conn.prepareStatement(pstmtSQL);

      try {
        // We need to keep track of the bind var index separately, since
        // the number of column defs may not be the number of bind vars
        // (For example: auto-increment columns are excluded)
        int bindVarIdx = 1;

        // have the DataType object fill in the appropriate kind of value
        // into the appropriate variable position in the prepared stmt
        for (int i = 0; i < colDefs.length; i++) {
          if (i != _rowIDcol && !colDefs[i].isAutoIncrement()) {
            CellComponentFactory.setPreparedStatementValue(
                colDefs[i], pstmt, values[i], bindVarIdx);
            bindVarIdx++;
          }
        }
        count = pstmt.executeUpdate();
      } finally {
        pstmt.close();
      }
    } catch (SQLException ex) {
      // i18n[DataSetUpdateableTableModelImpl.error.duringInsert=Exception seen during check on DB.
      // Exception was:\n{0}\nInsert was probably not completed correctly.  DB may be corrupted!]
      return s_stringMgr.getString(
          "DataSetUpdateableTableModelImpl.error.duringInsert", ex.getMessage());
    }

    if (count != 1)
      // i18n[DataSetUpdateableTableModelImpl.error.unknownerrorupdate=Unknown problem during
      // update.\nNo count of inserted rows was returned.\nDatabase may be corrupted!]
      return s_stringMgr.getString("DataSetUpdateableTableModelImpl.error.unknownerrorupdate");

    // insert succeeded
    try {
      IObjectTreeAPI api = _session.getObjectTreeAPIOfActiveSessionWindow();
      api.refreshSelectedTab();
    } catch (Exception e) {
      e.printStackTrace();
    }

    return null;
  }
  /** 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;
  }
  /**
   * 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;
  };
 /**
  * See if a value in a column has been limited in some way and needs to be re-read before being
  * used for editing. For read-only tables this may actually return true since we want to be able
  * to view the entire contents of the cell even if it was not completely loaded during the initial
  * table setup.
  */
 public boolean needToReRead(int col, Object originalValue) {
   // call the DataType object for this column and have it check the current value
   return CellComponentFactory.needToReRead(_colDefs[col], originalValue);
 }