001: package net.sourceforge.squirrel_sql.client.session;
002:
003: import java.sql.PreparedStatement;
004: import java.sql.ResultSet;
005: import java.sql.SQLException;
006: import java.sql.Statement;
007: import java.util.HashMap;
008: import java.util.Vector;
009:
010: import javax.swing.JOptionPane;
011:
012: import net.sourceforge.squirrel_sql.client.session.properties.EditWhereCols;
013: import net.sourceforge.squirrel_sql.fw.datasetviewer.ColumnDisplayDefinition;
014: import net.sourceforge.squirrel_sql.fw.datasetviewer.DataSetUpdateableTableModelListener;
015: import net.sourceforge.squirrel_sql.fw.datasetviewer.IDataSetUpdateableTableModel;
016: import net.sourceforge.squirrel_sql.fw.datasetviewer.cellcomponent.CellComponentFactory;
017: import net.sourceforge.squirrel_sql.fw.sql.ISQLConnection;
018: import net.sourceforge.squirrel_sql.fw.sql.ISQLDatabaseMetaData;
019: import net.sourceforge.squirrel_sql.fw.sql.ITableInfo;
020: import net.sourceforge.squirrel_sql.fw.sql.SQLDatabaseMetaData;
021: import net.sourceforge.squirrel_sql.fw.sql.SQLUtilities;
022: import net.sourceforge.squirrel_sql.fw.sql.TableColumnInfo;
023: import net.sourceforge.squirrel_sql.fw.util.StringManager;
024: import net.sourceforge.squirrel_sql.fw.util.StringManagerFactory;
025: import net.sourceforge.squirrel_sql.fw.util.log.ILogger;
026: import net.sourceforge.squirrel_sql.fw.util.log.LoggerController;
027:
028: public class DataSetUpdateableTableModelImpl implements
029: IDataSetUpdateableTableModel {
030:
031: /** Internationalized strings for this class. */
032: private static final StringManager s_stringMgr = StringManagerFactory
033: .getStringManager(DataSetUpdateableTableModelImpl.class);
034:
035: /** string to be passed to user when table name is not found or is ambiguous */
036: // i18n[DataSetUpdateableTableModelImpl.error.tablenotfound=Cannot edit table because table cannot be found\nor table name is not unique in DB.]
037: private final String TI_ERROR_MESSAGE = s_stringMgr
038: .getString("DataSetUpdateableTableModelImpl.error.tablenotfound");
039:
040: /** Logger for this class. */
041: private static final ILogger s_log = LoggerController
042: .createLogger(DataSetUpdateableTableModelImpl.class);
043:
044: /**
045: * This is the long name of the current table including everything that might be able to distinguish it
046: * from another table of the same name in a different DB.
047: */
048: private String fullTableName = null;
049: private ITableInfo ti;
050: private ISession _session;
051:
052: /**
053: * Remember whether or not the user has forced us into editing mode
054: * when the SessionProperties says to use read-only mode.
055: */
056: private boolean editModeForced = false;
057:
058: /**
059: * We need to save the name of the SessionProperties display class at the time
060: * that the table was forced into edit mode so that if the properties get changed
061: * while we are in forced edit mode, we will change back to match the new
062: * Session Properties.
063: */
064: String sqlOutputClassNameAtTimeOfForcedEdit = "";
065:
066: private Vector<DataSetUpdateableTableModelListener> _dataSetUpdateableTableModelListener = new Vector<DataSetUpdateableTableModelListener>();
067:
068: /**
069: * Remember which column contains the rowID; if no rowID, this is -1
070: * which does not match any legal column index.
071: * Note that for this class, since the list of columns to include is given
072: * by the user, we never include any pseudo-column automatically in the
073: * ResultSet, and thus we never have any legal column index here.
074: */
075: int _rowIDcol = -1;
076:
077: public void setTableInfo(ITableInfo ti) {
078: this .ti = ti;
079: // re-calculate fullTablename the next time it's requested.
080: fullTableName = null;
081: }
082:
083: public void setSession(ISession session) {
084: this ._session = session;
085: }
086:
087: /**
088: * return the name of the table that is unambiguous across DB accesses,
089: * including the same DB on different machines.
090: * This function is static because it is used elsewhere to generate the same
091: * name as is used within instances of this class.
092: *
093: * @return the name of the table that is unique for this DB access
094: */
095: public static String getUnambiguousTableName(ISession session,
096: String name) {
097: return session.getAlias().getUrl() + ":" + name;
098: }
099:
100: /**
101: * Get the full name of this table, creating that name the first time we are called
102: */
103: public String getFullTableName() {
104: if (fullTableName == null) {
105: try {
106: final String name = ti.getQualifiedName();
107: fullTableName = getUnambiguousTableName(_session, name);
108: } catch (Exception e) {
109: s_log.error("getFullTableName: Unexpected exception - "
110: + e.getMessage(), e);
111: }
112: }
113: return fullTableName;
114: }
115:
116: /**
117: * If the user forces us into edit mode, remember that they did so for this table.
118: */
119: public void forceEditMode(boolean mode) {
120: editModeForced = mode;
121: sqlOutputClassNameAtTimeOfForcedEdit = _session.getProperties()
122: .getTableContentsOutputClassName();
123:
124: DataSetUpdateableTableModelListener[] listeners = _dataSetUpdateableTableModelListener
125: .toArray(new DataSetUpdateableTableModelListener[0]);
126:
127: for (int i = 0; i < listeners.length; i++) {
128: listeners[i].forceEditMode(mode);
129: }
130:
131: /**
132: * Tell the GUI to rebuild itself.
133: * This is not a clean way to do that, since we are telling the
134: * SessionProperties listeners that a property has changed when
135: * in reality none of them have done so, but this does cause the
136: * GUI to be rebuilt.
137: */
138: // _session.getProperties().forceTableContentsOutputClassNameChange();
139: }
140:
141: /**
142: * The fw needs to know whether we are in forced edit mode or not
143: * so it can decide whether or not to let the user undo that mode.
144: */
145: public boolean editModeIsForced() {
146: return editModeForced;
147: }
148:
149: /**
150: * If the user has forced us into editing mode, use the EDITABLE_TABLE form, but
151: * otherwise use whatever form the user specified in the Session Preferences.
152: */
153: public String getDestinationClassName() {
154: if (editModeForced) {
155: if (_session.getProperties()
156: .getTableContentsOutputClassName().equals(
157: sqlOutputClassNameAtTimeOfForcedEdit)) {
158: return _session.getProperties()
159: .getEditableTableOutputClassName();
160: }
161: // forced edit mode ended because user changed the Session Properties
162: editModeForced = false;
163: }
164:
165: // if the user selected Editable Table in the Session Properties,
166: // then the display will be an editable table; otherwise the display is read-only
167: return _session.getProperties()
168: .getTableContentsOutputClassName();
169: }
170:
171: /**
172: * Link from fw to check on whether there are any unusual conditions
173: * in the current data that the user needs to be aware of before updating.
174: */
175: public String getWarningOnCurrentData(Object[] values,
176: ColumnDisplayDefinition[] colDefs, int col, Object oldValue) {
177:
178: // if we could not identify which table to edit, tell user
179: if (ti == null)
180: return TI_ERROR_MESSAGE;
181:
182: String whereClause = getWhereClause(values, colDefs, col,
183: oldValue);
184:
185: // It is possible for a table to contain only columns of types that
186: // we cannot process or do selects on, so check for that.
187: // Since this check is on the structure of the table rather than the contents,
188: // we only need to do it once (ie: it is not needed in getWarningOnProjectedUpdate)
189: if (whereClause.length() == 0)
190: // i18n[DataSetUpdateableTableModelImpl.confirmupdateallrows=The table has no columns that can be SELECTed on.\nAll rows will be updated.\nDo you wish to proceed?]
191: return s_stringMgr
192: .getString("DataSetUpdateableTableModelImpl.confirmupdateallrows");
193:
194: final ISession session = _session;
195: final ISQLConnection conn = session.getSQLConnection();
196:
197: int count = -1; // start with illegal number of rows matching query
198:
199: try {
200: Statement stmt = null;
201: ResultSet rs = null;
202: try {
203: stmt = conn.createStatement();
204: String countSql = "select count(*) from "
205: + ti.getQualifiedName() + whereClause;
206: rs = stmt.executeQuery(countSql);
207: rs.next();
208: count = rs.getInt(1);
209: } finally {
210: // We don't care if these throw an SQLException. Just squelch them
211: // and report to the user what the outcome of the previous statements
212: // were.
213: SQLUtilities.closeResultSet(rs);
214: SQLUtilities.closeStatement(stmt);
215: }
216: } catch (SQLException ex) {
217: //i18n[DataSetUpdateableTableModelImpl.error.exceptionduringcheck=Exception
218: //seen during check on DB. Exception was:\n{0}\nUpdate is probably not
219: //safe to do.\nDo you wish to proceed?]
220: String msg = s_stringMgr
221: .getString(
222: "DataSetUpdateableTableModelImpl.error.exceptionduringcheck",
223: ex.getMessage());
224: s_log.error(msg, ex);
225: return msg;
226: }
227:
228: if (count == -1) {
229: // i18n[DataSetUpdateableTableModelImpl.error.unknownerror=Unknown error during check on DB. Update is probably not safe.\nDo you wish to proceed?]
230: return s_stringMgr
231: .getString("DataSetUpdateableTableModelImpl.error.unknownerror");
232: }
233: if (count == 0) {
234: // i18n[DataSetUpdateableTableModelImpl.error.staleupdaterow=This row in the Database has been changed since you refreshed the data.\nNo rows will be updated by this operation.\nDo you wish to proceed?]
235: return s_stringMgr
236: .getString("DataSetUpdateableTableModelImpl.error.staleupdaterow");
237: }
238: if (count > 1) {
239: // i18n[DataSetUpdateableTableModelImpl.info.updateidenticalrows=This operation will update {0} identical rows.\nDo you wish to proceed?]
240: return s_stringMgr
241: .getString(
242: "DataSetUpdateableTableModelImpl.info.updateidenticalrows",
243: Long.valueOf(count));
244: }
245: // no problems found, so do not return a warning message.
246: return null; // nothing for user to worry about
247: }
248:
249: /**
250: * Link from fw to check on whether there are any unusual conditions
251: * that will occur after the update has been done.
252: */
253: public String getWarningOnProjectedUpdate(Object[] values,
254: ColumnDisplayDefinition[] colDefs, int col, Object newValue) {
255: try {
256: // if we could not identify which table to edit, tell user
257: if (ti == null)
258: return TI_ERROR_MESSAGE;
259:
260: String whereClause = getWhereClause(values, colDefs, col,
261: newValue);
262:
263: final ISession session = _session;
264: final ISQLConnection conn = session.getSQLConnection();
265:
266: int count = -1; // start with illegal number of rows matching query
267:
268: try {
269: final Statement stmt = conn.createStatement();
270: try {
271: final ResultSet rs = stmt
272: .executeQuery("select count(*) from "
273: + ti.getQualifiedName()
274: + whereClause);
275: rs.next();
276: count = rs.getInt(1);
277: } finally {
278: stmt.close();
279: }
280: } catch (SQLException ex) {
281: // i18n[DataSetUpdateableTableModelImpl.error.exceptionduringcheck=Exception seen during check on DB. Exception was:\n{0}\nUpdate is probably not safe to do.\nDo you wish to proceed?]
282: s_stringMgr
283: .getString(
284: "DataSetUpdateableTableModelImpl.error.exceptionduringcheck",
285: ex.getMessage());
286: }
287:
288: if (count == -1) {
289: // i18n[DataSetUpdateableTableModelImpl.error.unknownerror=Unknown error during check on DB. Update is probably not safe.\nDo you wish to proceed?]
290: return s_stringMgr
291: .getString("DataSetUpdateableTableModelImpl.error.unknownerror");
292: }
293: // There are some fields that cannot be used in a WHERE clause, either
294: // because there cannot be an exact match (e.g. REAL, FLOAT), or
295: // because we may not have the actual data in hand (BLOB/CLOB), or
296: // because the data cannot be expressed in a string form (e.g. BINARY).
297: // An update to one of those fields
298: // will look like we are replacing one row with an identical row (because
299: // we can only "see" the fields that we know how to do WHEREs on). Therefore,
300: // when we are updating them, there should be exactly one row that matches
301: // all of our other fields, and when we are not updating one of these
302: // special types of fields, there should be
303: // no rows that exactly match our criteria (we hope).
304: //
305: // We determine whether this field is one that cannot be used in the WHERE
306: // clause by checking the value returned for that field to use in the
307: // WHERE clause. Any field that can be used there will return something
308: // of the form "<fieldName> = <value>", and a field that cannot be
309: // used will return a null or zero-length string.
310:
311: if (count > 1) {
312: // i18n[DataSetUpdateableTableModelImpl.info.identicalrows=This
313: //operation will result in {0} identical rows.\nDo you wish
314: //to proceed?]
315: return s_stringMgr
316: .getString(
317: "DataSetUpdateableTableModelImpl.info.identicalrows",
318: Long.valueOf(count));
319: }
320:
321: // no problems found, so do not return a warning message.
322: return null; // nothing for user to worry about
323: } catch (Exception e) {
324: throw new RuntimeException(e);
325: }
326:
327: }
328:
329: /**
330: * Re-read the value for a single cell in the table, if possible.
331: * If there is a problem, the message has a non-zero length when this returns.
332: */
333: public Object reReadDatum(Object[] values,
334: ColumnDisplayDefinition[] colDefs, int col,
335: StringBuffer message) {
336:
337: // if we could not identify which table to edit, tell user
338: if (ti == null)
339: return TI_ERROR_MESSAGE;
340:
341: // get WHERE clause
342: // The -1 says to ignore the last arg and use the contents of the values array
343: // for the column that we care about. However, since the data in
344: // that column has been limited, when getWhereClause calls that
345: // DataType with that value, the DataType will see that the data has
346: // been limited and therefore cannnot be used in the WHERE clause.
347: // In some cases it may be possible for the DataType to use the
348: // partial data, such as "matches <data>*", but that may not be
349: // standard accross all Databases and thus may be risky.
350: String whereClause = getWhereClause(values, colDefs, -1, null);
351:
352: final ISession session = _session;
353: final ISQLConnection conn = session.getSQLConnection();
354:
355: Object wholeDatum = null;
356:
357: try {
358: final Statement stmt = conn.createStatement();
359: final String queryString = "SELECT "
360: + colDefs[col].getLabel() + " FROM "
361: + ti.getQualifiedName() + whereClause;
362:
363: try {
364: ResultSet rs = stmt.executeQuery(queryString);
365:
366: // There should be one row in the data, so try to move to it
367: if (rs.next() == false) {
368: // no first row, so we cannot retrieve the data
369: // i18n[DataSetUpdateableTableModelImpl.error.nomatchingrow=Could not find any row in DB matching current row in table]
370: throw new SQLException(
371: s_stringMgr
372: .getString("DataSetUpdateableTableModelImpl.error.nomatchingrow"));
373: }
374:
375: // we have at least one row, so try to retrieve the object
376: // Do Not limit the read of this data
377: wholeDatum = CellComponentFactory.readResultSet(
378: colDefs[col], rs, 1, false);
379:
380: // There should not be more than one row in the DB that matches
381: // the table, and if there is we cannot determine which one to read,
382: // so check that there are no more
383: if (rs.next() == true) {
384: // multiple rows - not good
385: wholeDatum = null;
386: // i18n[DataSetUpdateableTableModelImpl.error.multimatchingrows=Muliple rows in DB match current row in table - cannot re-read data.]
387: throw new SQLException(
388: s_stringMgr
389: .getString("DataSetUpdateableTableModelImpl.error.multimatchingrows"));
390: }
391: } finally {
392: stmt.close();
393: }
394: } catch (Exception ex) {
395: // i18n[DataSetUpdateableTableModelImpl.error.rereadingdb=There was a problem reported while re-reading the DB. The DB message was:\n{0}]
396: message
397: .append(s_stringMgr
398: .getString(
399: "DataSetUpdateableTableModelImpl.error.rereadingdb",
400: ex.getMessage()));
401:
402: // It would be nice to tell the user what happened, but if we try to
403: // put up a dialog box at this point, we run into trouble in some
404: // cases where the field continually tries to re-read after the dialog
405: // closes (because it is being re-painted).
406: }
407:
408: // return the whole contents of this column in the DB
409: return wholeDatum;
410: };
411:
412: /**
413: * link from fw to this for updating data
414: */
415: public String updateTableComponent(Object[] values,
416: ColumnDisplayDefinition[] colDefs, int col,
417: Object oldValue, Object newValue) {
418: // if we could not identify which table to edit, tell user
419: if (ti == null)
420: return TI_ERROR_MESSAGE;
421:
422: // get WHERE clause using original value
423: String whereClause = getWhereClause(values, colDefs, col,
424: oldValue);
425:
426: if (s_log.isDebugEnabled()) {
427: s_log.debug("updateTableComponent: whereClause = "
428: + whereClause);
429: }
430:
431: final ISession session = _session;
432: final ISQLConnection conn = session.getSQLConnection();
433:
434: int count = -1;
435:
436: final String sql = constructUpdateSql(ti.getQualifiedName(),
437: colDefs[col].getLabel(), whereClause);
438:
439: if (s_log.isDebugEnabled()) {
440: s_log.debug("updateTableComponent: executing SQL - " + sql);
441: }
442: PreparedStatement pstmt = null;
443: try {
444: pstmt = conn.prepareStatement(sql);
445:
446: // have the DataType object fill in the appropriate kind of value
447: // into the first (and only) variable position in the prepared stmt
448: CellComponentFactory.setPreparedStatementValue(
449: colDefs[col], pstmt, newValue, 1);
450: count = pstmt.executeUpdate();
451: } catch (SQLException ex) {
452: //i18n[DataSetUpdateableTableModelImpl.error.updateproblem=There
453: //was a problem reported during the update.
454: //The DB message was:\n{0}\nThis may or may not be serious depending
455: //on the above message.\nThe data was probably not changed in the
456: //database.\nYou may need to refresh the table to get an accurate
457: //view of the current data.]
458: String errMsg = s_stringMgr
459: .getString(
460: "DataSetUpdateableTableModelImpl.error.updateproblem",
461: ex.getMessage());
462: s_log.error("updateTableComponent: unexpected exception - "
463: + ex.getMessage() + " while executing SQL: " + sql);
464:
465: return errMsg;
466: } finally {
467: SQLUtilities.closeStatement(pstmt);
468: }
469:
470: if (count == -1) {
471: // i18n[DataSetUpdateableTableModelImpl.error.unknownupdateerror=Unknown problem during update.\nNo count of updated rows was returned.\nDatabase may be corrupted!]
472: return s_stringMgr
473: .getString("DataSetUpdateableTableModelImpl.error.unknownupdateerror");
474: }
475: if (count == 0) {
476: // i18n[DataSetUpdateableTableModelImpl.info.norowsupdated=No rows updated.]
477: return s_stringMgr
478: .getString("DataSetUpdateableTableModelImpl.info.norowsupdated");
479: }
480: // everything seems to have worked ok
481: return null;
482: }
483:
484: /**
485: * Build the update SQL from the specified components.
486: *
487: * @param table the fully qualified name of the table
488: * @param column the name of the column to update
489: * @param whereClause the where clause that restricts the update to one row.
490: *
491: * @return the SQL to execute
492: */
493: private String constructUpdateSql(String table, String column,
494: String whereClause) {
495: StringBuilder result = new StringBuilder();
496: result.append("UPDATE ");
497: result.append(table);
498: result.append(" SET ");
499: result.append(column);
500: result.append(" = ? ");
501: result.append(whereClause);
502: return result.toString();
503: }
504:
505: /**
506: * Let fw get the rowIDcol
507: */
508: public int getRowidCol() {
509: return _rowIDcol;
510: }
511:
512: /**
513: * helper function to create a WHERE clause to search the DB for matching rows.
514: * If the col number is < 0, then the colValue is ignored
515: * and the WHERE clause is constructed using only the values[].
516: */
517: private String getWhereClause(Object[] values,
518: ColumnDisplayDefinition[] colDefs, int col, Object colValue) {
519: try {
520: StringBuffer whereClause = new StringBuffer("");
521:
522: // For tables that have a lot of columns, the user may have limited the set of columns
523: // to use in the where clause, so see if there is a table of col names
524: HashMap<String, String> colNames = (EditWhereCols
525: .get(getFullTableName()));
526:
527: ColumnDisplayDefinition editedCol = null;
528: if (-1 != col) {
529: editedCol = colDefs[col];
530: }
531:
532: for (int i = 0; i < colDefs.length; i++) {
533:
534: if (i != col
535: && null != editedCol
536: && colDefs[i]
537: .getFullTableColumnName()
538: .equalsIgnoreCase(
539: editedCol
540: .getFullTableColumnName())) {
541: // The edited column is in the resultset twice (example: SELECT MyName,* FROM MyTable).
542: // We won't add the this col to the where clause.
543: continue;
544: }
545:
546: // if the user has said to not use this column, then skip it
547: if (colNames != null) {
548: // the user has restricted the set of columns to use.
549: // If this name is NOT in the list, then skip it; otherwise we fall through
550: // and use the column in the WHERE clause
551: if (colNames.get(colDefs[i].getLabel()) == null)
552: continue; // go on to the next item
553: }
554:
555: // for the column that is being changed, use the value
556: // passed in by the caller (which may be either the
557: // current value or the new replacement value)
558: Object value = values[i];
559: if (i == col)
560: value = colValue;
561:
562: // convert user representation of null into an actual null
563: if (value != null && value.toString().equals("<null>"))
564: value = null;
565:
566: // do different things depending on data type
567: ISQLDatabaseMetaData md = _session.getMetaData();
568: String clause = CellComponentFactory
569: .getWhereClauseValue(colDefs[i], value, md);
570:
571: if (clause != null && clause.length() > 0)
572: if (whereClause.length() == 0) {
573: whereClause.append(clause);
574: } else {
575: whereClause.append(" AND ");
576: whereClause.append(clause);
577: }
578: }
579:
580: // insert the "WHERE" at the front if there is anything in the clause
581: if (whereClause.length() == 0)
582: return "";
583:
584: whereClause.insert(0, " WHERE ");
585: return whereClause.toString();
586: } catch (Exception e) {
587: throw new RuntimeException(e);
588: }
589: }
590:
591: /**
592: * Delete a set of rows from the DB.
593: * If the delete succeeded this returns a null string.
594: * The deletes are done within a transaction
595: * so they are either all done or all not done.
596: */
597: public String deleteRows(Object[][] rowData,
598: ColumnDisplayDefinition[] colDefs) {
599:
600: // if we could not identify which table to edit, tell user
601: if (ti == null)
602: return TI_ERROR_MESSAGE;
603:
604: // get the SQL session
605: final ISession session = _session;
606: final ISQLConnection conn = session.getSQLConnection();
607:
608: // string used as error indicator and description of problems seen
609: // when checking for 0 or mulitple matches in DB
610: String rowCountErrorMessage = "";
611:
612: // for each row in table, count how many rows match where clause
613: // if not exactly one, generate message describing situation
614: for (int i = 0; i < rowData.length; i++) {
615: // get WHERE clause for the selected row
616: // the -1 says to just use the contents of the values without
617: // any substitutions
618: String whereClause = getWhereClause(rowData[i], colDefs,
619: -1, null);
620:
621: // count how many rows this WHERE matches
622: try {
623: // do the delete and add the number of rows deleted to the count
624: final Statement stmt = conn.createStatement();
625: try {
626: ResultSet rs = stmt
627: .executeQuery("SELECT count(*) FROM "
628: + ti.getQualifiedName()
629: + whereClause);
630:
631: rs.next();
632: if (rs.getInt(1) != 1) {
633: if (rs.getInt(1) == 0) {
634: // i18n[DataSetUpdateableTableModelImpl.error.rownotmatch=\n Row {0} did not match any row in DB]
635: rowCountErrorMessage += s_stringMgr
636: .getString(
637: "DataSetUpdateableTableModelImpl.error.rownotmatch",
638: Integer.valueOf(i + 1));
639: } else {
640: //i18n[DataSetUpdateableTableModelImpl.error.rowmatched=\n Row {0} matched {1} rows in DB]
641: rowCountErrorMessage += s_stringMgr
642: .getString(
643: "DataSetUpdateableTableModelImpl.error.rowmatched",
644: new Object[] {
645: Integer
646: .valueOf(i + 1),
647: Integer.valueOf(rs
648: .getInt(1)) });
649: }
650: }
651: } finally {
652: stmt.close();
653: }
654: } catch (Exception e) {
655: // some kind of problem - tell user
656: // i18n[DataSetUpdateableTableModelImpl.error.preparingdelete=While preparing for delete, saw exception:\n{0}]
657: return s_stringMgr
658: .getString(
659: "DataSetUpdateableTableModelImpl.error.preparingdelete",
660: e);
661: }
662: }
663:
664: // if the rows do not match 1-for-1 to DB, ask user if they
665: // really want to do delete
666: if (rowCountErrorMessage.length() > 0) {
667: // 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?]
668: String msg = s_stringMgr
669: .getString(
670: "DataSetUpdateableTableModelImpl.error.tabledbmismatch",
671: rowCountErrorMessage);
672:
673: int option = JOptionPane.showConfirmDialog(null, msg,
674: "Warning", JOptionPane.YES_NO_OPTION,
675: JOptionPane.WARNING_MESSAGE);
676:
677: if (option != JOptionPane.YES_OPTION) {
678: // i18n[DataSetUpdateableTableModelImpl.info.deletecancelled=Delete canceled at user request.]
679: return s_stringMgr
680: .getString("DataSetUpdateableTableModelImpl.info.deletecancelled");
681: }
682: }
683:
684: // for each row in table, do delete and add to number of rows deleted from DB
685: for (int i = 0; i < rowData.length; i++) {
686: // get WHERE clause for the selected row
687: // the -1 says to just use the contents of the values without
688: // any substitutions
689: String whereClause = getWhereClause(rowData[i], colDefs,
690: -1, null);
691:
692: // try to delete
693: try {
694: // do the delete and add the number of rows deleted to the count
695: final Statement stmt = conn.createStatement();
696: try {
697: stmt.executeUpdate("DELETE FROM "
698: + ti.getQualifiedName() + whereClause);
699: } finally {
700: stmt.close();
701: }
702: } catch (Exception e) {
703: // some kind of problem - tell user
704: // i18n[DataSetUpdateableTableModelImpl.error.deleteFailed=One of the delete operations failed with exception:\n{0}\nDatabase is in an unknown state and may be corrupted.]
705: return s_stringMgr
706: .getString(
707: "DataSetUpdateableTableModelImpl.error.deleteFailed",
708: e);
709: }
710: }
711:
712: return null; // hear no evil, see no evil
713: }
714:
715: /**
716: * Let fw get the list of default values for the columns
717: * to be used when creating a new row
718: */
719: public String[] getDefaultValues(ColumnDisplayDefinition[] colDefs) {
720:
721: // we return something valid even if there is a DB error
722: final String[] defaultValues = new String[colDefs.length];
723:
724: // if we could not identify which table to edit, just return
725: if (ti == null) {
726: return defaultValues;
727: }
728:
729: final ISession session = _session;
730: final ISQLConnection conn = session.getSQLConnection();
731:
732: try {
733: SQLDatabaseMetaData md = conn.getSQLMetaData();
734: TableColumnInfo[] infos = md.getColumnInfo(ti);
735:
736: // read the DB MetaData info and fill in the value, if any
737: // Note that the ResultSet info and the colDefs should be
738: // in the same order, but we cannot guarantee that.
739: int expectedColDefIndex = 0;
740:
741: for (int idx = 0; idx < infos.length; idx++) {
742: String colName = infos[idx].getColumnName();
743: String defValue = infos[idx].getDefaultValue();
744:
745: // if value was null, we do not need to do
746: // anything else with this column.
747: // Also assume that a value of "" is equivilent to null
748: if (defValue != null && defValue.length() > 0) {
749: // find the entry in colDefs matching this column
750: if (colDefs[expectedColDefIndex].getLabel().equals(
751: colName)) {
752: // DB cols are in same order as colDefs
753: defaultValues[expectedColDefIndex] = defValue;
754: } else {
755: // colDefs not in same order as DB, so search for
756: // matching colDef entry
757: // Note: linear search here will NORMALLY be not too bad
758: // because most tables do not have huge numbers of columns.
759: for (int i = 0; i < colDefs.length; i++) {
760: if (colDefs[i].getLabel().equals(colName)) {
761: defaultValues[i] = defValue;
762: break;
763: }
764: }
765: }
766: }
767:
768: // assuming that the columns in table match colDefs,
769: // bump the index to point to the next colDef entry
770: expectedColDefIndex++;
771:
772: }
773: } catch (Exception ex) {
774: // i18n[DataSetUpdateableTableModelImpl.error.retrievingdefaultvalues=Error retrieving default column values]
775: s_log
776: .error(
777: s_stringMgr
778: .getString("DataSetUpdateableTableModelImpl.error.retrievingdefaultvalues"),
779: ex);
780: }
781:
782: return defaultValues;
783: }
784:
785: /**
786: * Insert a row into the DB.
787: * If the insert succeeds this returns a null string.
788: */
789: public String insertRow(Object[] values,
790: ColumnDisplayDefinition[] colDefs) {
791:
792: // if we could not identify which table to edit, tell user
793: if (ti == null) {
794: return TI_ERROR_MESSAGE;
795: }
796:
797: final ISession session = _session;
798: final ISQLConnection conn = session.getSQLConnection();
799:
800: int count = -1;
801:
802: try {
803: // start the string for use in the prepared statment
804: StringBuilder buf = new StringBuilder("INSERT INTO ");
805: buf.append(ti.getQualifiedName());
806:
807: // Add the list of column names we will be inserting into - be sure
808: // to skip the rowId column and any auto increment columns.
809: buf.append(" ( ");
810: for (int i = 0; i < colDefs.length; i++) {
811: if (i == _rowIDcol) {
812: continue;
813: }
814: if (colDefs[i].isAutoIncrement()) {
815: if (s_log.isInfoEnabled()) {
816: s_log
817: .info("insertRow: skipping auto-increment column "
818: + colDefs[i].getColumnName());
819: }
820: continue;
821: }
822: buf.append(colDefs[i].getColumnName());
823: buf.append(",");
824: }
825: buf.setCharAt(buf.length() - 1, ')');
826: buf.append(" VALUES (");
827:
828: // add a variable position for each of the columns
829: for (int i = 0; i < colDefs.length; i++) {
830: if (i != _rowIDcol && !colDefs[i].isAutoIncrement())
831:
832: buf.append(" ?,");
833: }
834:
835: // replace the last "," with ")"
836: buf.setCharAt(buf.length() - 1, ')');
837:
838: String pstmtSQL = buf.toString();
839: if (s_log.isInfoEnabled()) {
840: s_log.info("insertRow: pstmt sql = " + pstmtSQL);
841: }
842: final PreparedStatement pstmt = conn
843: .prepareStatement(pstmtSQL);
844:
845: try {
846: // We need to keep track of the bind var index separately, since
847: // the number of column defs may not be the number of bind vars
848: // (For example: auto-increment columns are excluded)
849: int bindVarIdx = 1;
850:
851: // have the DataType object fill in the appropriate kind of value
852: // into the appropriate variable position in the prepared stmt
853: for (int i = 0; i < colDefs.length; i++) {
854: if (i != _rowIDcol && !colDefs[i].isAutoIncrement()) {
855: CellComponentFactory.setPreparedStatementValue(
856: colDefs[i], pstmt, values[i],
857: bindVarIdx);
858: bindVarIdx++;
859: }
860: }
861: count = pstmt.executeUpdate();
862: } finally {
863: pstmt.close();
864: }
865: } catch (SQLException ex) {
866: // i18n[DataSetUpdateableTableModelImpl.error.duringInsert=Exception seen during check on DB. Exception was:\n{0}\nInsert was probably not completed correctly. DB may be corrupted!]
867: return s_stringMgr
868: .getString(
869: "DataSetUpdateableTableModelImpl.error.duringInsert",
870: ex.getMessage());
871: }
872:
873: if (count != 1)
874: // i18n[DataSetUpdateableTableModelImpl.error.unknownerrorupdate=Unknown problem during update.\nNo count of inserted rows was returned.\nDatabase may be corrupted!]
875: return s_stringMgr
876: .getString("DataSetUpdateableTableModelImpl.error.unknownerrorupdate");
877:
878: // insert succeeded
879: try {
880: IObjectTreeAPI api = _session
881: .getObjectTreeAPIOfActiveSessionWindow();
882: api.refreshSelectedTab();
883: } catch (Exception e) {
884: e.printStackTrace();
885: }
886:
887: return null;
888: }
889:
890: public void addListener(DataSetUpdateableTableModelListener l) {
891: _dataSetUpdateableTableModelListener.add(l);
892: }
893:
894: public void removeListener(DataSetUpdateableTableModelListener l) {
895: _dataSetUpdateableTableModelListener.remove(l);
896: }
897:
898: public void setEditModeForced(boolean b) {
899: editModeForced = b;
900: }
901:
902: public void setRowIDCol(int rowIDCol) {
903: _rowIDcol = rowIDCol;
904: }
905: }
|