001: /*
002: * Copyright 2007 Madhav Pulipaka
003: *
004: * This file is part of Vela.
005:
006: * Vela is free software; you can redistribute it and/or modify
007: * it under the terms of the GNU General Public License as published by
008: * the Free Software Foundation; either version 2 of the License, or
009: * (at your option) any later version.
010: *
011: * Vela is distributed in the hope that it will be useful,
012: * but WITHOUT ANY WARRANTY; without even the implied warranty of
013: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
014: * GNU General Public License for more details.
015: *
016: * You should have received a copy of the GNU General Public License
017: * along with Vela; if not, write to the Free Software
018: * Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
019: */
020: package vela.db;
021:
022: import java.sql.*;
023: import java.util.*;
024: import java.io.*;
025:
026: import javax.swing.JOptionPane;
027:
028: import vela.model.*;
029: import vela.common.*;
030:
031: public class OraDBManager implements Constants, DBManager {
032:
033: private static DBManager dbManager;
034: Connection connection;
035:
036: private OraDBManager() {
037: }
038:
039: public static DBManager getInstance() {
040: if (dbManager != null)
041: return dbManager;
042: dbManager = new OraDBManager();
043: return dbManager;
044: }
045:
046: public void init(ConnectionProperties connProps) throws Exception {
047: try {
048: Class.forName("oracle.jdbc.driver.OracleDriver");
049: } catch (Exception ex) {
050: ex.printStackTrace();
051: throw ex;
052: }
053: try {
054: if (connection == null) {
055: connection = DriverManager
056: .getConnection("jdbc:oracle:thin:@"
057: + connProps.getHost() + ":"
058: + connProps.getPort() + ":"
059: + connProps.getSid(), connProps
060: .getUserName(), connProps.getPassword());
061: if (connection != null) {
062: System.out
063: .println("Successfuly connected to the database.");
064: connection.setAutoCommit(false);
065: }
066: }
067: } catch (SQLException sqlEx) {
068: throw sqlEx;
069: } catch (Exception ex) {
070: ex.printStackTrace();
071: throw ex;
072: }
073: }
074:
075: public void cleanup() {
076: try {
077: if (connection != null) {
078: connection.close();
079: connection = null;
080: dbManager = null;
081: System.out
082: .println("Database Connection is closed successfully.");
083: }
084: } catch (Exception ex) {
085: ex.printStackTrace();
086: }
087: }
088:
089: public Hashtable getDBObjects() {
090: Hashtable hAllDBObjects = new Hashtable();
091: ArrayList listDBObjects = new ArrayList(0);
092: ArrayList allObjectNames = new ArrayList(0);
093: hAllDBObjects.put("DB_OBJECTS", listDBObjects);
094: hAllDBObjects.put("DB_OBJECT_NAMES", allObjectNames);
095: Statement stmt = null;
096: ResultSet rs = null;
097: try {
098: if (connection == null) {
099: System.out.println("Error: No valid connection.");
100: return hAllDBObjects;
101: }
102: stmt = connection.createStatement();
103: String sqlDBObjects = " SELECT OBJECT_ID, OBJECT_TYPE, STATUS, OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE!='SYNONYM'";
104: sqlDBObjects = sqlDBObjects + " UNION ";
105: sqlDBObjects = sqlDBObjects
106: + " SELECT OBJECT_ID, OBJECT_TYPE, STATUS, OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT_TYPE = 'SYNONYM' AND OBJECT_NAME IN (SELECT OBJECT_NAME FROM USER_OBJECTS) ORDER BY 4";
107: rs = stmt.executeQuery(sqlDBObjects);
108: while (rs.next()) {
109: DBObject dbObject = new DBObject();
110: dbObject.setObjectId(rs.getString("OBJECT_ID"));
111: dbObject.setObjectName(rs.getString("OBJECT_NAME"));
112: allObjectNames.add(rs.getString("OBJECT_NAME"));
113: dbObject.setObjectStatus(rs.getString("STATUS"));
114: dbObject.setObjectType(rs.getString("OBJECT_TYPE"));
115: listDBObjects.add(dbObject);
116: }
117: } catch (Exception ex) {
118: ex.printStackTrace();
119: } finally {
120: if (rs != null) {
121: try {
122: rs.close();
123: } catch (Exception ex) {
124: }
125: rs = null;
126: }
127: if (stmt != null) {
128: try {
129: stmt.close();
130: } catch (Exception ex) {
131: }
132: stmt = null;
133: }
134: }
135: return hAllDBObjects;
136: }
137:
138: public ArrayList getSource(String objectName, String objectType) {
139: ArrayList source = new ArrayList(0);
140: if (objectName == null || objectName.trim().length() == 0)
141: return source;
142: Statement stmt = null;
143: ResultSet rs = null;
144: try {
145: if (connection == null) {
146: System.out.println("Error: No valid connection.");
147: return source;
148: }
149: stmt = connection.createStatement();
150: String sql = "";
151: if (objectType != null
152: && objectType.equalsIgnoreCase("TRIGGER"))
153: sql = "SELECT DESCRIPTION, TRIGGER_BODY FROM USER_TRIGGERS WHERE TRIGGER_NAME = '"
154: + objectName + "'";
155: else if (objectType != null
156: && objectType.equalsIgnoreCase("VIEW"))
157: sql = "SELECT VIEW_NAME, TEXT FROM USER_VIEWS WHERE VIEW_NAME = '"
158: + objectName + "'";
159: else
160: sql = "SELECT TEXT SOURCE FROM USER_SOURCE WHERE NAME = '"
161: + objectName
162: + "' AND TYPE='"
163: + objectType
164: + "'";
165: rs = stmt.executeQuery(sql);
166: int rowCount = 0;
167: while (rs.next()) {
168: rowCount++;
169: if (objectType != null
170: && objectType.equalsIgnoreCase("TRIGGER")) {
171: if (rowCount == 1) {
172: source.add("CREATE OR REPLACE TRIGGER "
173: + rs.getString("DESCRIPTION"));
174: source.add(rs.getString("TRIGGER_BODY"));
175: } else
176: source.add(rs.getString("TRIGGER_BODY"));
177: } else if (objectType != null
178: && objectType.equalsIgnoreCase("VIEW")) {
179: if (rowCount == 1) {
180: source.add("CREATE OR REPLACE VIEW "
181: + rs.getString("VIEW_NAME"));
182: source.add(" AS ");
183: source.add(rs.getString("TEXT"));
184: System.out.println("View Source = " + source);
185: } else {
186: source.add(rs.getString("TEXT"));
187: System.out.println("View Source = " + source);
188: }
189: } else {
190: if (rowCount == 1) {
191: source.add("CREATE OR REPLACE "
192: + (String) rs.getString("SOURCE"));
193: } else
194: source.add((String) rs.getString("SOURCE"));
195: }
196: }
197: } catch (Exception ex) {
198: ex.printStackTrace();
199: } finally {
200: if (rs != null) {
201: try {
202: rs.close();
203: } catch (Exception ex) {
204: }
205: rs = null;
206: }
207: if (stmt != null) {
208: try {
209: stmt.close();
210: } catch (Exception ex) {
211: }
212: stmt = null;
213: }
214: }
215: return source;
216: }
217:
218: public Vector getTabDesc(String selectedObject) {
219: Vector tabDescVector = new Vector(0, 1);
220: Statement stmt = null;
221: ResultSet rs = null;
222: try {
223: if (connection == null) {
224: System.out.println("Error: No valid connection.");
225: return tabDescVector;
226: }
227: stmt = connection.createStatement();
228: rs = stmt
229: .executeQuery("SELECT COLUMN_NAME, DATA_TYPE, DECODE(DATA_TYPE,'DATE','',DATA_LENGTH) DATA_LENGTH, NULLABLE, DATA_DEFAULT FROM USER_TAB_COLUMNS WHERE TABLE_NAME = '"
230: + selectedObject + "' ORDER BY 1");
231: while (rs.next()) {
232: Vector rowVector = new Vector(0, 1);
233: rowVector.addElement(rs.getString("COLUMN_NAME"));
234: rowVector.addElement(rs.getString("DATA_TYPE"));
235: rowVector.addElement(rs.getString("DATA_LENGTH"));
236: rowVector.addElement(rs.getString("NULLABLE"));
237: rowVector.addElement(rs.getString("DATA_DEFAULT"));
238: tabDescVector.addElement(rowVector);
239: }
240: } catch (Exception ex) {
241: ex.printStackTrace();
242: } finally {
243: if (rs != null) {
244: try {
245: rs.close();
246: } catch (Exception ex) {
247: }
248: rs = null;
249: }
250: if (stmt != null) {
251: try {
252: stmt.close();
253: } catch (Exception ex) {
254: }
255: stmt = null;
256: }
257: }
258: return tabDescVector;
259: }
260:
261: public Vector getSequenceDetails(String sequenceName) {
262: Vector seqDtls = new Vector(0, 1);
263: Statement stmt = null;
264: ResultSet rs = null;
265: try {
266: if (connection == null) {
267: System.out.println("Error: No valid connection.");
268: return seqDtls;
269: }
270: stmt = connection.createStatement();
271: rs = stmt
272: .executeQuery("SELECT MIN_VALUE, MAX_VALUE, INCREMENT_BY, LAST_NUMBER FROM USER_SEQUENCES WHERE SEQUENCE_NAME = '"
273: + sequenceName + "'");
274: while (rs.next()) {
275: Vector rowVector = new Vector(0, 1);
276: rowVector.addElement(rs.getString("MIN_VALUE"));
277: rowVector.addElement(rs.getString("MAX_VALUE"));
278: rowVector.addElement(rs.getString("INCREMENT_BY"));
279: rowVector.addElement(rs.getString("LAST_NUMBER"));
280: seqDtls.addElement(rowVector);
281: }
282: } catch (Exception ex) {
283: ex.printStackTrace();
284: } finally {
285: if (rs != null) {
286: try {
287: rs.close();
288: } catch (Exception ex) {
289: }
290: rs = null;
291: }
292: if (stmt != null) {
293: try {
294: stmt.close();
295: } catch (Exception ex) {
296: }
297: stmt = null;
298: }
299: }
300: return seqDtls;
301: }
302:
303: public Hashtable getIndexDetails(String indxName) {
304: Hashtable hIndxDtls = new Hashtable();
305: Vector indxDtls = new Vector(0, 1);
306: String tabName = "";
307: hIndxDtls.put("DATA", indxDtls);
308: hIndxDtls.put("TABLE_NAME", tabName);
309: Statement stmt = null;
310: ResultSet rs = null;
311: try {
312: if (connection == null) {
313: System.out.println("Error: No valid connection.");
314: return hIndxDtls;
315: }
316: stmt = connection.createStatement();
317: rs = stmt
318: .executeQuery("SELECT TABLE_NAME, COLUMN_NAME, COLUMN_POSITION FROM USER_IND_COLUMNS WHERE INDEX_NAME = '"
319: + indxName + "'");
320: while (rs.next()) {
321: Vector rowVector = new Vector(0, 1);
322: tabName = rs.getString("TABLE_NAME");
323: rowVector.addElement(rs.getString("COLUMN_NAME"));
324: rowVector.addElement(rs.getString("COLUMN_POSITION"));
325: indxDtls.addElement(rowVector);
326: }
327: hIndxDtls.put("TABLE_NAME", tabName);
328: } catch (Exception ex) {
329: ex.printStackTrace();
330: } finally {
331: if (rs != null) {
332: try {
333: rs.close();
334: } catch (Exception ex) {
335: }
336: rs = null;
337: }
338: if (stmt != null) {
339: try {
340: stmt.close();
341: } catch (Exception ex) {
342: }
343: stmt = null;
344: }
345: }
346: return hIndxDtls;
347: }
348:
349: public Vector getSynDetails(String synName) {
350: Vector synDtls = new Vector(0, 1);
351: Statement stmt = null;
352: ResultSet rs = null;
353: try {
354: if (connection == null) {
355: System.out.println("Error: No valid connection.");
356: return synDtls;
357: }
358: stmt = connection.createStatement();
359: rs = stmt
360: .executeQuery("SELECT OWNER, TABLE_NAME, TABLE_OWNER FROM ALL_SYNONYMS WHERE SYNONYM_NAME = '"
361: + synName + "'");
362: while (rs.next()) {
363: Vector rowVector = new Vector(0, 1);
364: rowVector.addElement(rs.getString("OWNER"));
365: rowVector.addElement(rs.getString("TABLE_NAME"));
366: rowVector.addElement(rs.getString("TABLE_OWNER"));
367: synDtls.addElement(rowVector);
368: }
369: } catch (Exception ex) {
370: ex.printStackTrace();
371: } finally {
372: if (rs != null) {
373: try {
374: rs.close();
375: } catch (Exception ex) {
376: }
377: rs = null;
378: }
379: if (stmt != null) {
380: try {
381: stmt.close();
382: } catch (Exception ex) {
383: }
384: stmt = null;
385: }
386: }
387: return synDtls;
388: }
389:
390: public Hashtable getTableData(DBObject table, Vector columnNames,
391: int startIndx, int endIndx) throws SQLException {
392: String tableName = table.getObjectName();
393: String filterString = table.getTableFilter();
394: String sortString = table.getTableSort();
395: if (sortString != null && sortString.trim().length() > 0)
396: sortString = "ORDER BY " + sortString;
397: else
398: sortString = "";
399: Hashtable hTabData = new Hashtable();
400: Vector tableData = new Vector(0, 1);
401: String recordCount = "0";
402: hTabData.put("DATA", tableData);
403: hTabData.put("RECORD_COUNT", recordCount);
404: if (columnNames == null || columnNames.size() == 0)
405: return hTabData;
406: String tableDataSQL = "SELECT ";
407: int noOfCols = columnNames.size();
408: for (int i = 0; i < noOfCols; i++) {
409: if (i == 0)
410: tableDataSQL = tableDataSQL + columnNames.elementAt(i);
411: else
412: tableDataSQL = tableDataSQL + ", "
413: + columnNames.elementAt(i);
414: }
415:
416: if (filterString != null && filterString.trim().length() > 0)
417: tableDataSQL = "SELECT * FROM((" + tableDataSQL
418: + ", ROWNUM RN FROM (" + tableDataSQL + " FROM "
419: + tableName + " WHERE " + filterString + " "
420: + sortString + " ) WHERE ROWNUM<=" + endIndx
421: + ")) WHERE RN>=" + startIndx;
422: else
423: tableDataSQL = "SELECT * FROM((" + tableDataSQL
424: + ", ROWNUM RN FROM (" + tableDataSQL + " FROM "
425: + tableName + " " + sortString
426: + " ) WHERE ROWNUM<=" + endIndx + ")) WHERE RN>="
427: + startIndx;
428: Statement stmt = null;
429: ResultSet rs = null;
430: try {
431: if (connection == null) {
432: System.out.println("Error: No valid connection.");
433: return null;
434: }
435: stmt = connection.createStatement();
436: rs = stmt.executeQuery(tableDataSQL);
437: while (rs.next()) {
438: Vector rowVector = new Vector(0, 1);
439: for (int i = 1; i <= noOfCols; i++) {
440: rowVector.addElement(rs.getString(i));
441: }
442: tableData.addElement(rowVector);
443: }
444: } catch (SQLException ex) {
445: throw ex;
446: } catch (Exception ex) {
447: ex.printStackTrace();
448: } finally {
449: if (rs != null) {
450: try {
451: rs.close();
452: } catch (Exception ex) {
453: }
454: rs = null;
455: }
456: if (stmt != null) {
457: try {
458: stmt.close();
459: } catch (Exception ex) {
460: }
461: stmt = null;
462: }
463: }
464: if (tableData.size() == 0)
465: return null;
466: stmt = null;
467: rs = null;
468: try {
469: if (connection == null) {
470: System.out.println("Error: No valid connection.");
471: return null;
472: }
473: stmt = connection.createStatement();
474: if (filterString != null
475: && filterString.trim().length() > 0)
476: rs = stmt
477: .executeQuery("SELECT NVL(COUNT(*),0) RECORD_COUNT FROM "
478: + tableName + " WHERE " + filterString);
479: else
480: rs = stmt
481: .executeQuery("SELECT NVL(COUNT(*),0) RECORD_COUNT FROM "
482: + tableName);
483: if (rs.next()) {
484: hTabData.put("RECORD_COUNT", rs.getString(1));
485: }
486: } catch (Exception ex) {
487: ex.printStackTrace();
488: } finally {
489: if (rs != null) {
490: try {
491: rs.close();
492: } catch (Exception ex) {
493: }
494: rs = null;
495: }
496: if (stmt != null) {
497: try {
498: stmt.close();
499: } catch (Exception ex) {
500: }
501: stmt = null;
502: }
503: }
504: return hTabData;
505: }
506:
507: public String compile(String script, String objectName) {
508: String result = "Successfully compiled without errors.";
509: Statement stmt = null;
510: try {
511: if (connection == null) {
512: System.out.println("Error: No valid connection.");
513: return null;
514: }
515: stmt = connection.createStatement();
516: int res = stmt.executeUpdate(script);
517: } catch (SQLException ex) {
518: return ex.getMessage();
519: } catch (Exception ex) {
520: ex.printStackTrace();
521: } finally {
522: if (stmt != null) {
523: try {
524: stmt.close();
525: } catch (Exception ex) {
526: }
527: stmt = null;
528: }
529: }
530:
531: stmt = null;
532: ResultSet rs = null;
533: try {
534: if (connection == null) {
535: System.out.println("Error: No valid connection.");
536: return null;
537: }
538: stmt = connection.createStatement();
539: String sqlErrors = "SELECT (TEXT || '. Error at [Line: '|| LINE || ', Position: ' || POSITION ||']') ERROR FROM USER_ERRORS WHERE NAME = '"
540: + objectName + "' AND SEQUENCE = 1";
541: rs = stmt.executeQuery(sqlErrors);
542: if (rs.next())
543: result = rs.getString("ERROR");
544: } catch (SQLException ex) {
545: return ex.getMessage();
546: } catch (Exception ex) {
547: ex.printStackTrace();
548: } finally {
549: if (stmt != null) {
550: try {
551: stmt.close();
552: } catch (Exception ex) {
553: }
554: stmt = null;
555: }
556: }
557:
558: return result;
559: }
560:
561: public Hashtable getQueryData(String query) throws SQLException {
562: Hashtable hTabData = new Hashtable();
563: Vector vColumns = new Vector(0, 1);
564: Vector tableData = new Vector(0, 1);
565: Vector colSizes = new Vector(0, 1);
566: hTabData.put("DATA", tableData);
567: hTabData.put("COLUMNS", vColumns);
568: hTabData.put("COLUMN_SIZES", colSizes);
569: String sql = "SELECT * FROM (" + query + ") WHERE ROWNUM<="
570: + Constants.PAGING_RECORD_COUNT;
571: Statement stmt = null;
572: ResultSet rs = null;
573: try {
574: if (connection == null) {
575: System.out.println("Error: No valid connection.");
576: return null;
577: }
578: stmt = connection.createStatement();
579: rs = stmt.executeQuery(sql);
580: ResultSetMetaData rsmd = rs.getMetaData();
581: int szCols = rsmd.getColumnCount();
582: for (int i = 1; i <= szCols; i++) {
583: if (rsmd.getColumnLabel(i) != null)
584: vColumns.addElement(rsmd.getColumnLabel(i));
585: else
586: vColumns.addElement(rsmd.getColumnName(i));
587:
588: if (rsmd.getColumnDisplaySize(i) > 0)
589: colSizes.addElement(""
590: + rsmd.getColumnDisplaySize(i));
591: else
592: colSizes
593: .addElement("" + (rsmd.getPrecision(i) + 2));
594: }
595: while (rs.next()) {
596: Vector rowVector = new Vector(0, 1);
597: for (int i = 1; i <= szCols; i++) {
598: rowVector.addElement(rs.getString(i));
599: }
600: tableData.addElement(rowVector);
601: }
602: } catch (SQLException ex) {
603: throw ex;
604: } catch (Exception ex) {
605: ex.printStackTrace();
606: } finally {
607: if (rs != null) {
608: try {
609: rs.close();
610: } catch (Exception ex) {
611: }
612: rs = null;
613: }
614: if (stmt != null) {
615: try {
616: stmt.close();
617: } catch (Exception ex) {
618: }
619: stmt = null;
620: }
621: }
622:
623: if (tableData.size() == 0)
624: return null;
625: stmt = null;
626: rs = null;
627: try {
628: if (connection == null) {
629: System.out.println("Error: No valid connection.");
630: return null;
631: }
632: stmt = connection.createStatement();
633: rs = stmt.executeQuery("SELECT COUNT(*) FROM (" + query
634: + ")");
635:
636: if (rs.next()) {
637: hTabData.put("RECORD_COUNT", rs.getString(1));
638: }
639: } catch (Exception ex) {
640: ex.printStackTrace();
641: } finally {
642: if (rs != null) {
643: try {
644: rs.close();
645: } catch (Exception ex) {
646: }
647: rs = null;
648: }
649: if (stmt != null) {
650: try {
651: stmt.close();
652: } catch (Exception ex) {
653: }
654: stmt = null;
655: }
656: }
657: return hTabData;
658: }
659:
660: public void commit() {
661: try {
662: connection.commit();
663: } catch (SQLException e) {
664: // TODO Auto-generated catch block
665: e.printStackTrace();
666: }
667: }
668:
669: public void rollback() {
670: try {
671: connection.rollback();
672: } catch (SQLException e) {
673: // TODO Auto-generated catch block
674: e.printStackTrace();
675: }
676: }
677:
678: public boolean isConnectionOpen() {
679: try {
680: if (connection != null) {
681: if (connection.isClosed())
682: return false;
683: else
684: return true;
685: }
686: } catch (SQLException e) {
687: // TODO Auto-generated catch block
688: e.printStackTrace();
689: }
690: return false;
691: }
692:
693: }
|