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