001: package org.jsqltool.conn;
002:
003: import java.sql.*;
004: import javax.swing.*;
005: import javax.swing.table.*;
006: import java.util.*;
007: import java.io.*;
008: import org.jsqltool.model.*;
009: import org.jsqltool.gui.*;
010: import org.jsqltool.gui.tableplugins.datatable.filter.*;
011: import org.jsqltool.utils.Options;
012:
013: /**
014: * <p>Title: JSqlTool Project</p>
015: * <p>Description: Utility Class used to connect to the database and fetch data.
016: * </p>
017: * <p>Copyright: Copyright (C) 2006 Mauro Carniel</p>
018: *
019: * <p> This file is part of JSqlTool project.
020: * This library is free software; you can redistribute it and/or
021: * modify it under the terms of the (LGPL) Lesser General Public
022: * License as published by the Free Software Foundation;
023: *
024: * GNU LESSER GENERAL PUBLIC LICENSE
025: * Version 2.1, February 1999
026: *
027: * This library is distributed in the hope that it will be useful,
028: * but WITHOUT ANY WARRANTY; without even the implied warranty of
029: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
030: * Library General Public License for more details.
031: *
032: * You should have received a copy of the GNU Library General Public
033: * License along with this library; if not, write to the Free
034: * Software Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
035: *
036: * The author may be contacted at:
037: * maurocarniel@tin.it</p>
038: *
039: * @author Mauro Carniel
040: * @version 1.0
041: */
042: public class DbConnectionUtil {
043:
044: /** database connection */
045: private Connection conn = null;
046:
047: /** database connection descriptor */
048: private DbConnection c = null;
049:
050: /** isolation levels */
051: private int[] isolationLivels = new int[] {
052: Connection.TRANSACTION_NONE, // 0
053: Connection.TRANSACTION_READ_COMMITTED, // 2
054: Connection.TRANSACTION_READ_UNCOMMITTED, // 1
055: Connection.TRANSACTION_REPEATABLE_READ, // 4
056: Connection.TRANSACTION_SERIALIZABLE // 8
057: };
058:
059: /** MDI frame; this reference is used on JDialog creation */
060: private JFrame parent = null;
061:
062: /** flag used to create the connection */
063: private boolean createConn = true;
064:
065: /**
066: * Create a database connection.
067: * @param c databse connection descriptor
068: */
069: public DbConnectionUtil(JFrame parent, DbConnection c) {
070: this .parent = parent;
071: this .c = c;
072: }
073:
074: /**
075: * @return database connection
076: */
077: public Connection getConn() {
078: try {
079: if (!createConn)
080: return conn;
081: createConn = false;
082:
083: ProgressDialog.getInstance().startProgress();
084:
085: Class.forName(c.getClassName());
086: this .conn = DriverManager.getConnection(c.getUrl(), c
087: .getUsername(), c.getPassword());
088: this .conn.setAutoCommit(c.isAutoCommit());
089: this .conn.setTransactionIsolation(isolationLivels[c
090: .getIsolationLevel()]);
091: this .conn.setReadOnly(c.isReadOnly());
092: } catch (Throwable ex) {
093: JOptionPane.showMessageDialog(parent, Options.getInstance()
094: .getResource("error when creating connection")
095: + ":\n" + ex.getMessage(), Options.getInstance()
096: .getResource("error"), JOptionPane.ERROR_MESSAGE);
097: throw new RuntimeException(ex.getMessage());
098: } finally {
099: ProgressDialog.getInstance().stopProgress();
100: }
101: return conn;
102: }
103:
104: public void saveProfile(boolean isEdit) {
105: new ConnectionProfile().saveProfile(parent, c, isEdit);
106: }
107:
108: /**
109: * @return tables list, filtered by schema
110: */
111: public synchronized List getTables(String schema, String tableType) {
112: ArrayList list = new ArrayList();
113: ResultSet rset = null;
114: try {
115: rset = getConn().getMetaData().getTables(
116: null, //schema.length()==0?null:schema.toUpperCase(),
117: schema.length() == 0 ? null : schema.toUpperCase(),
118: null, new String[] { tableType });
119:
120: // ResultSet rset = conn.getMetaData().getTables(schema.toUpperCase(),schema.toUpperCase(),null,new String[]{tableType});
121: /*
122: Retrieves a description of the tables available in the given catalog.
123: Only table descriptions matching the catalog, schema, table name and type criteria are returned.
124: They are ordered by TABLE_TYPE, TABLE_SCHEM and TABLE_NAME.
125: Each table description has the following columns:
126: TABLE_CAT String => table catalog (may be null)
127: TABLE_SCHEM String => table schema (may be null)
128: TABLE_NAME String => table name
129: TABLE_TYPE String => table type. Typical types are "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM".
130: REMARKS String => explanatory comment on the table
131: TYPE_CAT String => the types catalog (may be null)
132: TYPE_SCHEM String => the types schema (may be null)
133: TYPE_NAME String => type name (may be null)
134: SELF_REFERENCING_COL_NAME String => name of the designated "identifier" column of a typed table (may be null)
135: REF_GENERATION String => specifies how values in SELF_REFERENCING_COL_NAME are created. Values are "SYSTEM", "USER", "DERIVED". (may be null) Note: Some databases may not return information for all tables.
136: Parameters:
137: catalog - a catalog name; must match the catalog name as it is stored in the database; "" retrieves those without a catalog; null means that the catalog name should not be used to narrow the search
138: schemaPattern - a schema name pattern; must match the schema name as it is stored in the database; "" retrieves those without a schema; null means that the schema name should not be used to narrow the search
139: tableNamePattern - a table name pattern; must match the table name as it is stored in the database
140: types - a list of table types to include; null returns all types
141: */
142: while (rset.next())
143: try {
144: // list.add((schema.length()>0?schema.toUpperCase()+".":"")+rset.getString(3));
145: list.add(rset.getString(3));
146: } catch (SQLException ex1) {
147: ex1.printStackTrace();
148: }
149: } catch (Exception ex) {
150: ex.printStackTrace();
151: JOptionPane.showMessageDialog(parent, Options.getInstance()
152: .getResource("error during tables fetching")
153: + ":\n" + ex.getMessage(), Options.getInstance()
154: .getResource("error"), JOptionPane.ERROR_MESSAGE);
155: } finally {
156: try {
157: Statement stmt = rset == null ? null : rset
158: .getStatement();
159: try {
160: rset.close();
161: } catch (Exception ex3) {
162: }
163: try {
164: stmt.close();
165: } catch (Exception ex4) {
166: }
167: } catch (Exception ex2) {
168: }
169: }
170: return list;
171: }
172:
173: /**
174: * @return catalogs list
175: */
176: public synchronized List getSchemas() {
177: ArrayList list = new ArrayList();
178: ResultSet rset = null;
179: try {
180: /*
181: HashSet h = new HashSet();
182: ResultSet rset = conn.getMetaData().getTables(schema,null,null,new String[]{"TABLE"});
183: while(rset.next())
184: h.add(rset.getString(2));
185: rset.close();
186: list.addAll(h);
187: if (list.size()==0)
188: list.add(c.getCatalog());
189: */
190: // ResultSet rset = getConn().getMetaData().getCatalogs();
191: rset = getConn().getMetaData().getSchemas();
192: while (rset.next())
193: list.add(rset.getString(1));
194: } catch (Exception ex) {
195: if (ex.getMessage().indexOf(
196: "Caratteristica opzionale non implementata") == -1)
197: ex.printStackTrace();
198: // JOptionPane.showMessageDialog(
199: // parent,
200: // Options.getInstance().getResource("error during catalogs fetching")+":\n"+ex.getMessage(),
201: // Options.getInstance().getResource("error"),
202: // JOptionPane.ERROR_MESSAGE
203: // );
204: } finally {
205: try {
206: Statement stmt = rset == null ? null : rset
207: .getStatement();
208: try {
209: rset.close();
210: } catch (Exception ex3) {
211: }
212: try {
213: stmt.close();
214: } catch (Exception ex4) {
215: }
216: } catch (Exception ex1) {
217: }
218: }
219: return list;
220: }
221:
222: /**
223: * @param query query to execute
224: * @return table model which contains a block of records
225: */
226: public synchronized TableModel getQuery(String query,
227: Vector parameters) {
228: return getQuery(query, parameters, 0, Integer.MAX_VALUE);
229: }
230:
231: /**
232: * @param tableName table name
233: * @return table columns
234: */
235: public synchronized TableModel getTableColumns(String tableName) {
236: CustomTableModel model = new CustomTableModel(new String[] {
237: Options.getInstance().getResource("column"),
238: Options.getInstance().getResource("data type"),
239: Options.getInstance().getResource("pk"),
240: Options.getInstance().getResource("null?"),
241: Options.getInstance().getResource("default") },
242: new Class[] { String.class, String.class,
243: Integer.class, Boolean.class, String.class });
244: try {
245: Hashtable pk = new Hashtable();
246: String tName = tableName;
247: String schema = null;
248: if (tName.indexOf(".") > -1) {
249: schema = tName.substring(0, tName.indexOf("."));
250: tName = tName.substring(tName.indexOf(".") + 1);
251: }
252: ResultSet rset0 = null;
253: try {
254: rset0 = getConn().getMetaData().getPrimaryKeys(null,
255: schema, tName.toString());
256: while (rset0.next()) {
257: pk.put(rset0.getString(4), rset0.getString(5));
258: }
259: } catch (SQLException ex1) {
260: // JOptionPane.showMessageDialog(parent,"Error while fetching PKs:\n"+ex1.getMessage(),"Error",JOptionPane.ERROR_MESSAGE);
261: if (ex1.getMessage().indexOf(
262: "Driver does not support this function") == -1)
263: ex1.printStackTrace();
264: } finally {
265: try {
266: Statement stmt = rset0 == null ? null : rset0
267: .getStatement();
268: try {
269: rset0.close();
270: } catch (Exception ex3) {
271: }
272: try {
273: stmt.close();
274: } catch (Exception ex4) {
275: }
276: } catch (Exception ex1) {
277: }
278: }
279:
280: Hashtable defaults = new Hashtable();
281: ResultSet rset1 = null;
282: try {
283: rset1 = getConn().getMetaData().getColumns(null,
284: schema, tName, null);
285: String colValue = null;
286: String colName = null;
287: while (rset1.next()) {
288: try {
289: colName = rset1.getString(4);
290: colValue = rset1.getString(13);
291: if (colValue != null) {
292: defaults.put(colName, colValue);
293: }
294: } catch (SQLException ex2) {
295: }
296: }
297: } catch (SQLException ex1) {
298: // JOptionPane.showMessageDialog(parent,"Error while fetching PKs:\n"+ex1.getMessage(),"Error",JOptionPane.ERROR_MESSAGE);
299: if (ex1.getMessage().indexOf(
300: "Driver does not support this function") == -1)
301: ex1.printStackTrace();
302: } finally {
303: try {
304: Statement stmt = rset1 == null ? null : rset1
305: .getStatement();
306: try {
307: rset1.close();
308: } catch (Exception ex3) {
309: }
310: try {
311: stmt.close();
312: } catch (Exception ex4) {
313: }
314: } catch (Exception ex1) {
315: }
316: }
317:
318: ResultSet rset = null;
319: try {
320: rset = getConn().createStatement().executeQuery(
321: "select * from " + tableName);
322: Vector data = new Vector();
323:
324: String type = null;
325: for (int i = 0; i < rset.getMetaData().getColumnCount(); i++) {
326: Vector row = new Vector();
327: row.add(rset.getMetaData().getColumnName(i + 1));
328: type = rset.getMetaData().getColumnTypeName(i + 1);
329: if ((rset.getMetaData().getColumnType(i + 1) == Types.VARCHAR
330: || rset.getMetaData().getColumnType(i + 1) == Types.LONGVARCHAR || rset
331: .getMetaData().getColumnType(i + 1) == Types.CHAR)
332: && rset.getMetaData().getPrecision(i + 1) == 0) // case MySQL...
333: type += "("
334: + rset.getMetaData()
335: .getColumnDisplaySize(i + 1)
336: + ")";
337: else if (rset.getMetaData().getColumnType(i + 1) == Types.BIGINT
338: || rset.getMetaData().getColumnType(i + 1) == Types.CHAR
339: || rset.getMetaData().getColumnType(i + 1) == Types.INTEGER
340: || rset.getMetaData().getColumnType(i + 1) == Types.LONGVARBINARY
341: || rset.getMetaData().getColumnType(i + 1) == Types.NUMERIC
342: && rset.getMetaData().getPrecision(i + 1) > 0
343: && rset.getMetaData().getScale(i + 1) == 0
344: || rset.getMetaData().getColumnType(i + 1) == Types.SMALLINT
345: || rset.getMetaData().getColumnType(i + 1) == Types.VARCHAR
346: || rset.getMetaData().getColumnType(i + 1) == Types.LONGVARCHAR)
347: type += "("
348: + rset.getMetaData()
349: .getPrecision(i + 1) + ")";
350: else if (rset.getMetaData().getColumnType(i + 1) == Types.DECIMAL
351: || rset.getMetaData().getColumnType(i + 1) == Types.DOUBLE
352: || rset.getMetaData().getColumnType(i + 1) == Types.FLOAT
353: || rset.getMetaData().getColumnType(i + 1) == Types.NUMERIC
354: && rset.getMetaData().getPrecision(i + 1) > 0
355: || rset.getMetaData().getColumnType(i + 1) == Types.REAL)
356: type += "("
357: + rset.getMetaData()
358: .getPrecision(i + 1) + ","
359: + rset.getMetaData().getScale(i + 1)
360: + ")";
361: row.add(type);
362: row.add(pk.containsKey(rset.getMetaData()
363: .getColumnName(i + 1)) ? new Integer(pk
364: .get(
365: rset.getMetaData().getColumnName(
366: i + 1)).toString().trim())
367: : null);
368: row
369: .add(new Boolean(
370: rset.getMetaData()
371: .isNullable(i + 1) == ResultSetMetaData.columnNullable));
372: row.add(defaults.get(rset.getMetaData()
373: .getColumnName(i + 1)));
374: data.add(row);
375: }
376: model.setDataVector(data);
377: return model;
378: } catch (Exception ex1) {
379: // JOptionPane.showMessageDialog(parent,"Error while fetching PKs:\n"+ex1.getMessage(),"Error",JOptionPane.ERROR_MESSAGE);
380: if (ex1.getMessage().indexOf(
381: "Driver does not support this function") == -1)
382: ex1.printStackTrace();
383: } finally {
384: try {
385: Statement stmt = rset == null ? null : rset
386: .getStatement();
387: try {
388: rset.close();
389: } catch (Exception ex3) {
390: }
391: try {
392: stmt.close();
393: } catch (Exception ex4) {
394: }
395: } catch (Exception ex1) {
396: }
397: }
398: } catch (Exception ex) {
399: JOptionPane.showMessageDialog(parent, Options.getInstance()
400: .getResource("error while fetching columns info")
401: + ":\n" + ex.getMessage(), Options.getInstance()
402: .getResource("error"), JOptionPane.ERROR_MESSAGE);
403: ex.printStackTrace();
404: }
405: return model;
406: }
407:
408: /**
409: * @param tableName table name used to retrieve its pk
410: * @return collection of links (pk field name,table model column index)
411: */
412: public synchronized Hashtable getPK(String tableName) {
413: Hashtable pk = new Hashtable();
414: ResultSet rset0 = null;
415: ResultSet rset = null;
416: try {
417: String tName = tableName;
418: if (tName.indexOf(".") > -1)
419: tName = tName.substring(tName.indexOf(".") + 1);
420: rset0 = getConn().getMetaData().getPrimaryKeys(null, null,
421: tName.toString());
422: while (rset0.next())
423: pk.put(rset0.getString(4), rset0.getString(5));
424: rset = getConn().createStatement().executeQuery(
425: "select * from " + tableName + " where 1=0");
426: Vector data = new Vector();
427:
428: String type = null;
429: for (int i = 0; i < rset.getMetaData().getColumnCount(); i++) {
430: if (pk.containsKey(rset.getMetaData().getColumnName(
431: i + 1)))
432: pk.put(rset.getMetaData().getColumnName(i + 1),
433: new Integer(i));
434: }
435: } catch (Exception ex) {
436: if (ex.getMessage().indexOf(
437: "Driver does not support this function") == -1)
438: ex.printStackTrace();
439: // JOptionPane.showMessageDialog(parent,"Error while fetching pk keys:\n"+ex.getMessage(),"Error",JOptionPane.ERROR_MESSAGE);
440: } finally {
441: try {
442: Statement stmt = rset0 == null ? null : rset0
443: .getStatement();
444: try {
445: rset0.close();
446: } catch (Exception ex3) {
447: }
448: try {
449: stmt.close();
450: } catch (Exception ex4) {
451: }
452:
453: } catch (Exception ex1) {
454: }
455: try {
456: Statement stmt = rset == null ? null : rset
457: .getStatement();
458: try {
459: rset.close();
460: } catch (Exception ex3) {
461: }
462: try {
463: stmt.close();
464: } catch (Exception ex4) {
465: }
466: } catch (Exception ex1) {
467: }
468: }
469:
470: return pk;
471: }
472:
473: /**
474: * @param tableName table name used to retrieve its indexes
475: * @return table indexes
476: */
477: public synchronized TableModel getTableIndexes(String tableName) {
478: ResultSet rset = null;
479: try {
480: String schema = null;
481: if (tableName.indexOf(".") != -1) {
482: schema = tableName.substring(0, tableName.indexOf("."));
483: tableName = tableName
484: .substring(tableName.indexOf(".") + 1);
485: }
486: rset = this .getConn().getMetaData().getIndexInfo(null,
487: schema, tableName, false, true);
488: /*
489: TABLE_CAT String => table catalog (may be null)
490: TABLE_SCHEM String => table schema (may be null)
491: TABLE_NAME String => table name
492: NON_UNIQUE boolean => Can index values be non-unique. false when TYPE is tableIndexStatistic
493: INDEX_QUALIFIER String => index catalog (may be null); null when TYPE is tableIndexStatistic
494: INDEX_NAME String => index name; null when TYPE is tableIndexStatistic
495: TYPE short => index type: tableIndexStatistic - this identifies table statistics that are returned in conjuction with a table's index descriptions tableIndexClustered - this is a clustered index tableIndexHashed - this is a hashed index tableIndexOther - this is some other style of index
496: ORDINAL_POSITION short => column sequence number within index; zero when TYPE is tableIndexStatistic
497: COLUMN_NAME String => column name; null when TYPE is tableIndexStatistic
498: ASC_OR_DESC String => column sort sequence, "A" => ascending, "D" => descending, may be null if sort sequence is not supported; null when TYPE is tableIndexStatistic
499: CARDINALITY int => When TYPE is tableIndexStatistic, then this is the number of rows in the table; otherwise, it is the number of unique values in the index.
500: PAGES int => When TYPE is tableIndexStatisic then this is the number of pages used for the table, otherwise it is the number of pages used for the current index.
501: FILTER_CONDITION String => Filter condition, if any. (may be null)
502: */
503: int num = rset.getMetaData().getColumnCount();
504: String[] colNames = new String[num];
505: Class[] classNames = new Class[num];
506: int[] typeNames = new int[num];
507: for (int i = 0; i < num; i++) {
508: try {
509: colNames[i] = rset.getMetaData().getColumnName(
510: i + 1);
511: classNames[i] = Class.forName(getColumnClassName(
512: rset, i + 1));
513: typeNames[i] = rset.getMetaData().getColumnType(
514: i + 1);
515: } catch (Exception ex) {
516: } catch (Error er) {
517: }
518: }
519: CustomTableModel model = new CustomTableModel(colNames,
520: classNames, typeNames);
521: Object[] row = null;
522: while (rset.next()) {
523: row = new Object[num];
524: for (int i = 0; i < num; i++)
525: row[i] = rset.getObject(i + 1);
526: model.addRow(row);
527: }
528: return model;
529: } catch (Exception ex) {
530: if (ex.getMessage().indexOf(
531: "Driver does not support this function") == -1)
532: ex.printStackTrace();
533: // JOptionPane.showMessageDialog(parent,"Error while fetching pk keys:\n"+ex.getMessage(),"Error",JOptionPane.ERROR_MESSAGE);
534: } finally {
535: try {
536: Statement stmt = rset == null ? null : rset
537: .getStatement();
538: try {
539: rset.close();
540: } catch (Exception ex3) {
541: }
542: try {
543: stmt.close();
544: } catch (Exception ex4) {
545: }
546: } catch (Exception ex1) {
547: }
548: }
549: return new DefaultTableModel(0, 3);
550: }
551:
552: /**
553: * @param tableName table name used to retrieve its fk
554: * @return table fks
555: */
556: public synchronized TableModel getCrossReference(String tableName) {
557: ResultSet rset = null;
558: try {
559: String schema = null;
560: if (tableName.indexOf(".") != -1) {
561: schema = tableName.substring(0, tableName.indexOf("."));
562: tableName = tableName
563: .substring(tableName.indexOf(".") + 1);
564: }
565: rset = this .getConn().getMetaData().getCrossReference(null,
566: schema, null, null, schema, tableName);
567: /*
568: PKTABLE_CAT String => primary key table catalog (may be null)
569: PKTABLE_SCHEM String => primary key table schema (may be null)
570: PKTABLE_NAME String => primary key table name
571: PKCOLUMN_NAME String => primary key column name
572: FKTABLE_CAT String => foreign key table catalog (may be null) being exported (may be null)
573: FKTABLE_SCHEM String => foreign key table schema (may be null) being exported (may be null)
574: FKTABLE_NAME String => foreign key table name being exported
575: FKCOLUMN_NAME String => foreign key column name being exported
576: KEY_SEQ short => sequence number within foreign key
577: UPDATE_RULE short => What happens to foreign key when primary is updated: importedNoAction - do not allow update of primary key if it has been imported importedKeyCascade - change imported key to agree with primary key update importedKeySetNull - change imported key to NULL if its primary key has been updated importedKeySetDefault - change imported key to default values if its primary key has been updated importedKeyRestrict - same as importedKeyNoAction (for ODBC 2.x compatibility)
578: DELETE_RULE short => What happens to the foreign key when primary is deleted. importedKeyNoAction - do not allow delete of primary key if it has been imported importedKeyCascade - delete rows that import a deleted key importedKeySetNull - change imported key to NULL if its primary key has been deleted importedKeyRestrict - same as importedKeyNoAction (for ODBC 2.x compatibility) importedKeySetDefault - change imported key to default if its primary key has been deleted
579: FK_NAME String => foreign key name (may be null)
580: PK_NAME String => primary key name (may be null)
581: DEFERRABILITY short => can the evaluation of foreign key constraints be deferred until commit importedKeyInitiallyDeferred - see SQL92 for definition importedKeyInitiallyImmediate - see SQL92 for definition importedKeyNotDeferrable - see SQL92 for definition
582: */
583: int num = rset.getMetaData().getColumnCount();
584: String[] colNames = new String[num];
585: Class[] classNames = new Class[num];
586: int[] typeNames = new int[num];
587: for (int i = 0; i < num; i++) {
588: try {
589: colNames[i] = rset.getMetaData().getColumnName(
590: i + 1);
591: classNames[i] = Class.forName(getColumnClassName(
592: rset, i + 1));
593: typeNames[i] = rset.getMetaData().getColumnType(
594: i + 1);
595: } catch (Exception ex) {
596: } catch (Error er) {
597: }
598: }
599: CustomTableModel model = new CustomTableModel(colNames,
600: classNames, typeNames);
601: Object[] row = null;
602: while (rset.next()) {
603: row = new Object[num];
604: for (int i = 0; i < num; i++)
605: row[i] = rset.getObject(i + 1);
606: model.addRow(row);
607: }
608: return model;
609: } catch (Exception ex) {
610: if (ex.getMessage().indexOf(
611: "Driver does not support this function") == -1)
612: ex.printStackTrace();
613: // JOptionPane.showMessageDialog(parent,"Error while fetching pk keys:\n"+ex.getMessage(),"Error",JOptionPane.ERROR_MESSAGE);
614: } finally {
615: try {
616: Statement stmt = rset == null ? null : rset
617: .getStatement();
618: try {
619: rset.close();
620: } catch (Exception ex3) {
621: }
622: try {
623: stmt.close();
624: } catch (Exception ex4) {
625: }
626: } catch (Exception ex1) {
627: }
628: }
629:
630: return new DefaultTableModel(0, 3);
631: }
632:
633: /**
634: * @param query query to execute
635: * @param startPos first record to read
636: * @param maxRows max number of records to read
637: * @return table model which contains the records
638: */
639: public synchronized TableModel getQuery(String query,
640: Vector parameters, int startPos, int maxRows) {
641: Statement stmt = null;
642: ResultSet rset = null;
643: try {
644: if (parameters.size() == 0) {
645: stmt = getConn().createStatement();
646: rset = stmt.executeQuery(query);
647: } else {
648: stmt = getConn().prepareStatement(query);
649: for (int i = 0; i < parameters.size(); i++)
650: ((PreparedStatement) stmt).setObject(i + 1,
651: parameters.get(i));
652: rset = ((PreparedStatement) stmt).executeQuery();
653: }
654: Vector data = new Vector();
655: String className = null;
656: String[] colNames = new String[rset.getMetaData()
657: .getColumnCount()];
658: Class[] classTypes = new Class[rset.getMetaData()
659: .getColumnCount()];
660: int[] colSizes = new int[rset.getMetaData()
661: .getColumnCount()];
662: for (int i = 0; i < rset.getMetaData().getColumnCount(); i++) {
663: boolean isBlob = false;
664: colNames[i] = rset.getMetaData().getColumnName(i + 1);
665: try {
666: className = getColumnClassName(rset, i + 1);
667: if (className == null)
668: className = "java.lang.String";
669: else if (className.equals("byte[]")
670: || className.equals("oracle.sql.BLOB")) {
671: className = "java.sql.Blob";
672: isBlob = true;
673: }
674: classTypes[i] = Class.forName(className);
675: } catch (NullPointerException ex) {
676: classTypes[i] = String.class;
677: }
678: try {
679: if (isBlob)
680: colSizes[i] = 150;
681: else
682: colSizes[i] = Math
683: .min(
684: (rset.getMetaData()
685: .getPrecision(i + 1) == 0 ? // case MySQL...
686: Math
687: .max(
688: rset
689: .getMetaData()
690: .getColumnDisplaySize(
691: i + 1) * 10,
692: colNames[i]
693: .length() * 10)
694: : Math
695: .max(
696: rset
697: .getMetaData()
698: .getPrecision(
699: i + 1) * 10,
700: colNames[i]
701: .length() * 10)),
702: Math.max(200, colNames[i]
703: .length() * 10));
704: } catch (SQLException ex1) {
705: colSizes[i] = colNames[i].length() * 10;
706: }
707: }
708: CustomTableModel model = new CustomTableModel(colNames,
709: classTypes, colSizes);
710: int j = 0;
711: if (startPos > 0)
712: while (rset.next() && j < startPos)
713: j++;
714: j = 0;
715: Vector row = null;
716: while (rset.next() && j < maxRows) {
717: row = new Vector();
718: for (int i = 0; i < rset.getMetaData().getColumnCount(); i++)
719: try {
720: row.add(rset.getObject(i + 1));
721: } catch (Throwable ex) {
722: row.add(null);
723: ex.printStackTrace();
724: }
725: j++;
726: data.add(row);
727: // model.addRow(row);
728: }
729: model.setDataVector(data);
730: model.setEditMode(c.isReadOnly() ? model.DETAIL_REC
731: : model.EDIT_REC);
732: return model;
733: } catch (Exception ex) {
734: ex.printStackTrace();
735: JOptionPane.showMessageDialog(parent, Options.getInstance()
736: .getResource("error while executing query")
737: + ":\n" + ex.getMessage(), Options.getInstance()
738: .getResource("error"), JOptionPane.ERROR_MESSAGE);
739: } finally {
740: try {
741: rset.close();
742: } catch (Exception ex2) {
743: }
744: try {
745: stmt.close();
746: } catch (Exception ex3) {
747: }
748: }
749: return new CustomTableModel(new String[0], new Class[0],
750: new int[0]);
751: }
752:
753: /**
754: * @param query query to execute
755: * @param maxRows max number of records to read
756: * @return result set index of the first record of the last block
757: */
758: public synchronized int getLastQueryIndex(String query,
759: Vector parameters, int maxRows) {
760: ResultSet rset = null;
761: Statement stmt = null;
762: try {
763: if (parameters.size() == 0) {
764: stmt = getConn().createStatement(
765: ResultSet.TYPE_SCROLL_SENSITIVE,
766: ResultSet.CONCUR_READ_ONLY);
767: } else {
768: stmt = getConn().prepareStatement(query);
769: for (int i = 0; i < parameters.size(); i++)
770: ((PreparedStatement) stmt).setObject(i + 1,
771: parameters.get(i));
772: }
773: rset = stmt.executeQuery(query);
774: int j = 0;
775: rset.last();
776: j = rset.getRow();
777: j = j - maxRows;
778: if (j < 0)
779: j = 0;
780: return j;
781: } catch (Exception ex) {
782: ex.printStackTrace();
783: JOptionPane.showMessageDialog(parent, Options.getInstance()
784: .getResource("error while executing query")
785: + ":\n" + ex.getMessage(), Options.getInstance()
786: .getResource("error"), JOptionPane.ERROR_MESSAGE);
787: } finally {
788: try {
789: rset.close();
790: } catch (Exception ex1) {
791: }
792: try {
793: stmt.close();
794: } catch (Exception ex2) {
795: }
796: }
797: return 0;
798: }
799:
800: /**
801: * Execute a SQL Script.
802: * @param stmt SQL statement to execute
803: * @return number of rows updated
804: */
805: public synchronized int executeStmt(String stmt, Vector parameters) {
806: Statement pstmt = null;
807: try {
808: if (parameters.size() == 0) {
809: pstmt = getConn().createStatement();
810: } else {
811: pstmt = getConn().prepareStatement(stmt);
812: for (int i = 0; i < parameters.size(); i++)
813: ((PreparedStatement) pstmt).setObject(i + 1,
814: parameters.get(i));
815: }
816:
817: return pstmt.executeUpdate(stmt);
818: } catch (SQLException ex) {
819: ex.printStackTrace();
820: JOptionPane.showMessageDialog(parent, Options.getInstance()
821: .getResource("error while executing statement")
822: + ":\n" + ex.getMessage(), Options.getInstance()
823: .getResource("error"), JOptionPane.ERROR_MESSAGE);
824: System.out.println(stmt);
825: return 0;
826: } finally {
827: try {
828: pstmt.close();
829: } catch (Exception ex1) {
830: }
831: }
832: }
833:
834: /*
835: public void createGraph(GraphFrame gf) {
836: try {
837: String q = "select table_name from all_tables";
838: ResultSet rset = conn.createStatement().executeQuery(q);
839: while(rset.next())
840: gf.addNode(rset.getString(1));
841: rset.close();
842: q = "select s.table_name,d.table_name from all_constraints s,all_constraints d "+
843: " where s.r_constraint_name=d.constraint_name and s.constraint_type='R' ";
844: rset = conn.createStatement().executeQuery(q);
845: while(rset.next()) {
846: // gf.addNode(rset.getString(1));
847: // gf.addNode(rset.getString(2));
848: gf.addEdge(rset.getString(1),rset.getString(2),20);
849: }
850: rset.close();
851: } catch (SQLException ex) {
852: JOptionPane.showMessageDialog(new JFrame(),"Error while executing statement:\n"+ex.getMessage(),"Error",JOptionPane.ERROR_MESSAGE);
853: ex.printStackTrace();
854: }
855: }
856: */
857:
858: public DbConnection getDbConnection() {
859: return c;
860: }
861:
862: public String convertDateToString(java.util.Date date) {
863: Calendar cal = Calendar.getInstance();
864: cal.setTime(date);
865: if (c.getDbType() == c.ORACLE_TYPE)
866: return "TO_DATE('" + cal.get(cal.DAY_OF_MONTH) + "-"
867: + cal.get(cal.MONTH) + "-" + cal.get(cal.YEAR)
868: + " " + cal.get(cal.HOUR_OF_DAY) + ":"
869: + cal.get(cal.MINUTE) + ":" + cal.get(cal.SECOND)
870: + "','dd-mm-yyyy HH:MMM:SS')";
871: else if (c.getDbType() == c.SQLSERVER_TYPE)
872: return "SELECT CAST('" + cal.get(cal.DAY_OF_MONTH) + "-"
873: + cal.get(cal.MONTH) + "-" + cal.get(cal.YEAR)
874: + " " + cal.get(cal.HOUR_OF_DAY) + ":"
875: + cal.get(cal.MINUTE) + ":" + cal.get(cal.SECOND)
876: + "' AS datetime)";
877: else
878: return "'" + cal.get(cal.YEAR) + "-" + cal.get(cal.MONTH)
879: + "-" + cal.get(cal.DAY_OF_MONTH) + "'";
880: }
881:
882: /**
883: * Some JDBC Drivers don't support JDBC method: in that case java class type is derived by java.sql.Types
884: */
885: private String getColumnClassName(ResultSet rset, int colIndex) {
886: try {
887: return rset.getMetaData().getColumnClassName(colIndex);
888: } catch (SQLException ex) {
889: try {
890: int colType = rset.getMetaData()
891: .getColumnType(colIndex);
892: if (colType == Types.BIGINT || colType == Types.INTEGER
893: || colType == Types.SMALLINT
894: || colType == Types.TINYINT)
895: return "java.lang.Integer";
896: if (colType == Types.BINARY || colType == Types.BLOB
897: || colType == Types.LONGVARBINARY)
898: return "java.sql.Blob";
899: if (colType == Types.BIT || colType == Types.BOOLEAN)
900: return "java.lang.Boolean";
901: if (colType == Types.CLOB)
902: return "java.sql.Clob";
903: if (colType == Types.DATE || colType == Types.TIME
904: || colType == Types.TIMESTAMP)
905: return "java.sql.Timestamp";
906: if (colType == Types.DECIMAL || colType == Types.DOUBLE
907: || colType == Types.FLOAT
908: || colType == Types.NUMERIC
909: || colType == Types.REAL)
910: return "java.math.BigDecimal";
911: } catch (SQLException ex1) {
912: }
913: return "java.lang.String";
914: }
915: }
916:
917: /**
918: * Store a byte[] a BLOB field.
919: * @param bytes byte[] to store
920: * @param stmt statement whose first element is a BLOB field
921: */
922: public final void writeBlob(byte[] bytes, PreparedStatement stmt)
923: throws Exception {
924: if (getDbConnection().getDbType() == DbConnection.ORACLE_TYPE) {
925: oracle.sql.BLOB blob = oracle.sql.BLOB.getDBAccess(conn)
926: .createTemporaryBlob(conn, true, bytes.length);
927: OutputStream out = blob.getBinaryOutputStream();
928: out.write(bytes);
929: out.flush();
930: out.close();
931: stmt.setBlob(1, blob);
932: } else {
933: throw new RuntimeException(
934: Options
935: .getInstance()
936: .getResource(
937: "database type not supported for blob type."));
938: }
939: }
940:
941: }
|