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