/** * 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); } }
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); }