1 package com.explosion.datastream.exql.gui.dbbrowser;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22 import java.awt.BorderLayout;
23 import java.awt.event.ComponentEvent;
24 import java.sql.Connection;
25 import java.sql.SQLException;
26 import java.util.ArrayList;
27 import java.util.HashMap;
28 import java.util.Iterator;
29 import java.util.List;
30 import java.util.Map;
31
32 import javax.swing.JPanel;
33 import javax.swing.JTable;
34
35 import org.apache.log4j.LogManager;
36 import org.apache.log4j.Logger;
37
38 import com.explosion.datastream.exql.EXQLConstants;
39 import com.explosion.datastream.exql.EXQLModuleManager;
40 import com.explosion.datastream.exql.gui.EXQLBaseTool;
41 import com.explosion.datastream.exql.gui.table.DsProTable;
42 import com.explosion.datastream.exql.processes.QueryProcess;
43 import com.explosion.datastream.exql.processes.RefreshTableProcess;
44 import com.explosion.datastream.exql.processes.UpdateDBWithAllEditsProcess;
45 import com.explosion.expf.Application;
46 import com.explosion.expfmodules.rdbmsconn.dbom.DBEntity;
47 import com.explosion.expfmodules.rdbmsconn.dbom.DBEntityColumn;
48 import com.explosion.expfmodules.rdbmsconn.dbom.DBMetaDataManager;
49 import com.explosion.expfmodules.rdbmsconn.dbom.DataSet;
50 import com.explosion.expfmodules.rdbmsconn.dbom.EntityMetaData;
51 import com.explosion.expfmodules.rdbmsconn.dbom.sql.InsertStatement;
52 import com.explosion.expfmodules.rdbmsconn.dbom.sql.SingleTableSelectStatement;
53 import com.explosion.expfmodules.rdbmsconn.dbom.sql.UpdateStatement;
54 import com.explosion.utilities.GeneralUtils;
55 import com.explosion.utilities.exception.ExceptionHandler;
56 import com.explosion.utilities.exception.ExceptionManagerFactory;
57 import com.explosion.utilities.process.ProcessMonitoringStatusBar;
58 import com.explosion.utilities.process.threads.Finishable;
59
60
61 /***
62 * @author Stephen Cowx
63 */
64
65 public class DBDataViewer extends JPanel implements Finishable
66 {
67 private static Logger log = LogManager
68 .getLogger(DBDataViewer.class);
69 private TabularDataView viewer = null;
70
71 private DBMetaDataManager metaDataManager;
72 private Connection conn;
73 private boolean updatePending = true;
74
75 private ProcessMonitoringStatusBar monitoringStatusBar;
76 private QueryProcess process;
77 private RefreshTableProcess refreshTableProcess;
78 private EXQLBaseTool tool;
79 private Throwable updateError;
80
81 /***
82 * Constructor
83 */
84 public DBDataViewer(EXQLBaseTool tool, Connection conn, ProcessMonitoringStatusBar monitoringStatusBar)
85 {
86 try
87 {
88 this.tool = tool;
89 this.metaDataManager = new DBMetaDataManager(conn);
90 this.monitoringStatusBar = monitoringStatusBar;
91 this.conn = conn;
92 this.viewer = new TabularDataView(tool, true);
93
94 jbInit();
95 }
96 catch (Exception e)
97 {
98 e.printStackTrace();
99 }
100 }
101
102 /***
103 * Initialises the panel
104 */
105 private void jbInit() throws Exception
106 {
107 this.setLayout(new BorderLayout());
108 this.add(viewer, BorderLayout.CENTER);
109
110
111 addComponentListener(new java.awt.event.ComponentAdapter()
112 {
113 public void componentShown(ComponentEvent e)
114 {
115 this_componentShown(e);
116 }
117 });
118 }
119
120 private void popupSQL()
121 {
122
123 }
124
125 /***
126 * Applies the properties for this tool;
127 */
128 public void applyPreferences()
129 {
130 viewer.applyPreferences();
131 }
132
133 /***
134 * This method updates the displayed values
135 */
136 public void update()
137 {
138 Application.ensureLocalCookie(EXQLConstants.MENU_EDIT_SQL,0,tool);
139
140 if (!this.isVisible() )
141 {
142 updatePending = true;
143 return;
144 }
145
146
147 EntityMetaData metadata = tool.getCurrentEntityMetaData();
148
149 if (metadata == null)
150 {
151 try
152 {
153 viewer.getDataTable().setAutoResizeMode(JTable.AUTO_RESIZE_ALL_COLUMNS);
154 viewer.getDataTable().setModel(metaDataManager.getGeneralDBMetaData());
155 }
156 catch (Exception ex)
157 {
158 ExceptionManagerFactory.getExceptionManager().manageException(ex, "Exception caught while obtaining general database information.");
159 }
160 }
161 else
162 {
163 DBEntity dbed = tool.getCurrentEntityMetaData().getDbEntity();
164
165 if ( dbed.getEntityType() == DBEntity.TYPE_TABLE ||
166 dbed.getEntityType() == DBEntity.TYPE_SYNONYM ||
167 dbed.getEntityType() == DBEntity.TYPE_SYSTEM_TABLE||
168 dbed.getEntityType() == DBEntity.TYPE_VIEW )
169 {
170 Application.ensureLocalCookie(EXQLConstants.MENU_EDIT_SQL,1,tool);
171 try
172 {
173 refresh();
174 }
175 catch (Exception ex)
176 {
177 ExceptionManagerFactory.getExceptionManager().manageException(ex, "Exception caught while obtaining column information for entity '" + tool.getCurrentEntityMetaData().getDbEntity().getEntityName() + "'.");
178 }
179 }
180 }
181
182 updatePending = false;
183 }
184
185 /***
186 * Called when this component is made visible
187 */
188 void this_componentShown(ComponentEvent e)
189 {
190 if (updatePending)
191 update();
192 }
193
194 public void disableComponent()
195 {
196 viewer.getDataTable().setEnabled(false);
197 }
198
199 public void enableComponent()
200 {
201
202 viewer.setEnabled(false);
203 }
204
205 /***
206 * displays noneditable results
207 */
208 private void refresh()
209 {
210 try
211 {
212 DBEntity dbed = tool.getCurrentEntityMetaData().getDbEntity();
213 viewer.getDataTable().setEnabled(false);
214 tool.disableComponent();
215
216 if (dbed != null)
217 {
218 boolean prefetched = ((Boolean)EXQLModuleManager.instance().getPreference(EXQLConstants.RDBMS_OPTION_FULL_TABLE_INFO_ON_STARTUP).getValue()).booleanValue();
219
220 if (prefetched)
221 {
222 refreshData(dbed);
223 refreshTableProcess = null;
224 }
225 else
226 {
227 refreshAll(dbed);
228 process = null;
229 }
230 }
231 else
232 {
233 viewer.getDataTable().setModel(GeneralUtils.getEmptyModel("There is no data available to show."));
234 viewer.getDataTable().getColumnModel().getColumn(0).sizeWidthToFit();
235 tool.enableComponent();
236 }
237 }
238 catch (Exception ex)
239 {
240 ExceptionManagerFactory.getExceptionManager().manageException(ex, "Experienced exception while populating view of table data.");
241 viewer.show_NoInfo_Message();
242 viewer.getDataTable().setEnabled(true);
243 tool.enableComponent();
244 }
245 }
246
247 /***
248 * Refreshes the table information and the table data
249 * @throws Exception
250 */
251 private void refreshAll(DBEntity dbed) throws Exception
252 {
253
254 refreshTableProcess = new RefreshTableProcess(this,dbed, tool.getBrowser().getCurrentNode(), conn,tool);
255 monitoringStatusBar.setProcessToMonitor(refreshTableProcess);
256 monitoringStatusBar.setProgressBarEnabled(false);
257 try
258 {
259 monitoringStatusBar.startProcess();
260 }
261 catch (Exception ex)
262 {
263 ExceptionManagerFactory.getExceptionManager().manageException(ex,"Exception caught while fetching tables.");
264 }
265 }
266
267 /***
268 * Refreshes the table information and the table data
269 * @throws Exception
270 */
271 private void refreshData(DBEntity dbed) throws Exception
272 {
273 String command = "";
274
275 if (dbed.getDBEntityBuffer().isUseCustomQyuery())
276 {
277 command = dbed.getDBEntityBuffer().getFetchSQL();
278 }
279 else
280 {
281 SingleTableSelectStatement clause = new SingleTableSelectStatement(dbed);
282 command = clause.getSelectString(conn.getMetaData());
283 }
284
285 if (command != null && command.length() > 0)
286 {
287
288 log.debug(command);
289
290 dbed.getDBEntityBuffer().setFetchSQL(command);
291
292
293 process = new QueryProcess(this,conn,command,false,tool);
294 monitoringStatusBar.setProcessToMonitor(process);
295 monitoringStatusBar.setProgressBarEnabled(false);
296 try
297 {
298 monitoringStatusBar.startProcess();
299 }
300 catch (Exception ex)
301 {
302 ExceptionManagerFactory.getExceptionManager().manageException(ex,"Exception caught while fetching tables.");
303 }
304 }
305 else
306 {
307 viewer.getDataTable().setModel(GeneralUtils.getEmptyModel("There is no data available to show."));
308 viewer.getDataTable().getColumnModel().getColumn(0).sizeWidthToFit();
309 tool.enableComponent();
310 }
311
312 }
313
314 /***
315 * This method clears the data view.
316 *
317 */
318 public void clear()
319 {
320 viewer.getDataTable().setModel(GeneralUtils.getEmptyModel(""));
321 viewer.getDataTable().getColumnModel().getColumn(0).sizeWidthToFit();
322 tool.enableComponent();
323 }
324
325 public void finish()
326 {
327 try
328 {
329 DataSet set = null;
330
331 if (process != null)
332 set = process.getSet();
333 else if (refreshTableProcess != null)
334 set = refreshTableProcess.getSet();
335
336 tool.getCurrentEntityMetaData().setDataSet(set);
337
338 if (set != null)
339 viewer.getDataTable().setAutoResizeMode(JTable.AUTO_RESIZE_OFF);
340
341 viewer.update(tool);
342
343 boolean allowUpdates = ((Boolean)EXQLModuleManager.instance().getPreference(EXQLConstants.RDBMS_OPTION_ALLOW_DB_UPDATES).getValue()).booleanValue();
344 if (allowUpdates)
345 {
346 viewer.getDataTable().setEnabled(true);
347 }
348 else
349 {
350 viewer.getDataTable().setEnabled(false);
351 }
352
353 tool.enableComponent();
354
355 monitoringStatusBar.updateStatus();
356 }
357 catch (Exception ex)
358 {
359 tool.enableComponent();
360 ExceptionManagerFactory.getExceptionManager().manageException(ex, "Experienced exception while populating view of table data.");
361 }
362 }
363
364 /***
365 * @return Returns the viewer.
366 */
367 public TabularDataView getView()
368 {
369 return viewer;
370 }
371
372 /***
373 * @throws SQLException
374 *
375 */
376 public void executeChanges() throws Exception
377 {
378 final DsProTable table = (DsProTable) viewer.getDataTable();
379 Map insertedRows = table.getInsertedRows();
380 Map deletedRows = table.getDeletedRows();
381 Map updatedRows = table.getUpdatedRows();
382
383
384 Finishable finishable = new Finishable()
385 {
386 public void finish()
387 {
388 if (updateError == null)
389 {
390 tool.refreshTool();
391 }
392 else
393 {
394 updateError = null;
395 }
396 }
397 };
398
399 ExceptionHandler ehand = new ExceptionHandler(){
400 public void handleException(Throwable e, String message)
401 {
402 updateError = e;
403 ExceptionManagerFactory.getExceptionManager().manageException(e, message);
404 }
405 };
406
407 List deleteBestRowIdentifiers = getBestRowIdentifiersBindings(table, deletedRows);
408 List insertSets = getInsertBindings(tool.getCurrentEntityMetaData().getDbEntity(),table,insertedRows);
409 List updateBestRowIdentifiers = getBestRowIdentifiersBindings(table, updatedRows);
410 List updateSets = getUpdateBindings(tool.getCurrentEntityMetaData().getDbEntity(), table, updatedRows);
411
412 UpdateDBWithAllEditsProcess updateProcess = new UpdateDBWithAllEditsProcess(finishable, tool.getCurrentEntityMetaData().getDbEntity(), insertSets, deleteBestRowIdentifiers , updateSets, updateBestRowIdentifiers, conn);
413 updateProcess.addExceptionHandler(ehand);
414 monitoringStatusBar.setProcessToMonitor(updateProcess);
415 monitoringStatusBar.setProgressBarEnabled(false);
416 try
417 {
418 monitoringStatusBar.startProcess();
419 }
420 catch (Exception ex)
421 {
422 ExceptionManagerFactory.getExceptionManager().manageException(ex,"Exception caught while updating database.");
423 }
424
425 }
426
427 /***
428 * Gets the bindings to be used for the Delete statement and also for the Update statement to hlp
429 * identify the row which needs to be updated
430 * @param table
431 * @param deletedRows
432 * @throws SQLException
433 */
434 protected List getBestRowIdentifiersBindings(DsProTable table, Map rows) throws SQLException
435 {
436 List columns = tool.getCurrentEntityMetaData().getDbEntity().getDerivedIdentityColumns();
437
438
439 List bindVariableSets = new ArrayList();
440 for (Iterator it = rows.keySet().iterator(); it.hasNext();) {
441 int rowNumber = ((Integer) it.next()).intValue();
442 HashMap bindVariables = new HashMap();
443 for (Iterator it2 = columns.iterator(); it2.hasNext();)
444 {
445 DBEntityColumn column = (DBEntityColumn) it2.next();
446 int modelIndex = table.getColumn(column.getColumnName()).getModelIndex();
447
448 bindVariables.put(column.getColumnName(), table.getOriginalValueAt(rowNumber, modelIndex));
449 }
450
451 bindVariableSets.add(bindVariables);
452 }
453 return bindVariableSets;
454 }
455
456 /***
457 * Gets the bindings to be used for the Insert statement
458 * @param table
459 * @param deletedRows
460 * @throws SQLException
461 */
462 protected List getInsertBindings(DBEntity entity, JTable table, Map insertedRows) throws SQLException
463 {
464
465 List bindVariableSets = new ArrayList();
466 for (Iterator it = insertedRows.keySet().iterator(); it.hasNext();) {
467 int rowNumber = ((Integer) it.next()).intValue();
468 log.debug("Found rownumber key " + rowNumber);
469 int numberOfColumns = table.getColumnCount();
470 HashMap bindVariables = new HashMap();
471 for (int i=0;i<numberOfColumns;i++)
472 {
473 String columnName = table.getColumnName(i);
474 DBEntityColumn column = entity.getColumn(columnName);
475 int modelIndex = table.getColumn(columnName).getModelIndex();
476 if (InsertStatement.includeInInsert(column))
477 {
478 Object insertValue = table.getModel().getValueAt(rowNumber, modelIndex);
479 log.debug("Inserting value " + insertValue + " in column " + column.getColumnName());
480 bindVariables.put(column.getColumnName(), insertValue );
481 }
482 }
483
484 bindVariableSets.add(bindVariables);
485 }
486 return bindVariableSets;
487 }
488
489 /***
490 * This method binds the updated values in the JTable with values in the bind variable sets to be passed
491 * through the update process to the update statement.
492 * @param entity
493 * @param table
494 * @param updatedValues
495 * @return
496 * @throws SQLException
497 */
498 protected List getUpdateBindings(DBEntity entity, JTable table, Map updatedValues) throws SQLException
499 {
500
501 List bindVariableSets = new ArrayList();
502 for (Iterator it = updatedValues.keySet().iterator(); it.hasNext();) {
503 Integer rowNumber = (Integer) it.next();
504 Map updatedValuesInRow = (Map) updatedValues.get(rowNumber);
505
506 HashMap bindVariables = new HashMap();
507 for (Iterator vals = updatedValuesInRow.keySet().iterator(); vals.hasNext();)
508 {
509 Integer columnNumber = (Integer)vals.next();
510 String columnName = table.getColumnName(columnNumber.intValue());
511
512 DBEntityColumn column = entity.getColumn(columnName);
513 int modelIndex = table.getColumn(columnName).getModelIndex();
514 if (UpdateStatement.includeInUpdate(column))
515 {
516 Object updateValue = table.getModel().getValueAt(rowNumber.intValue(), modelIndex);
517 log.debug("Updating value " + updateValue + " in column " + column.getColumnName());
518 bindVariables.put(column.getColumnName(), updateValue );
519 }
520 }
521
522 bindVariableSets.add(bindVariables);
523 }
524 return bindVariableSets;
525 }
526
527 }
528
529
530