001: /*
002: * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
003: *
004: * Copyright 1997-2007 Sun Microsystems, Inc. All rights reserved.
005: *
006: * The contents of this file are subject to the terms of either the GNU
007: * General Public License Version 2 only ("GPL") or the Common
008: * Development and Distribution License("CDDL") (collectively, the
009: * "License"). You may not use this file except in compliance with the
010: * License. You can obtain a copy of the License at
011: * http://www.netbeans.org/cddl-gplv2.html
012: * or nbbuild/licenses/CDDL-GPL-2-CP. See the License for the
013: * specific language governing permissions and limitations under the
014: * License. When distributing the software, include this License Header
015: * Notice in each file and include the License file at
016: * nbbuild/licenses/CDDL-GPL-2-CP. Sun designates this
017: * particular file as subject to the "Classpath" exception as provided
018: * by Sun in the GPL Version 2 section of the License file that
019: * accompanied this code. If applicable, add the following below the
020: * License Header, with the fields enclosed by brackets [] replaced by
021: * your own identifying information:
022: * "Portions Copyrighted [year] [name of copyright owner]"
023: *
024: * Contributor(s):
025: *
026: * The Original Software is NetBeans. The Initial Developer of the Original
027: * Software is Sun Microsystems, Inc. Portions Copyright 1997-2007 Sun
028: * Microsystems, Inc. All Rights Reserved.
029: *
030: * If you wish your version of this file to be governed by only the CDDL
031: * or only the GPL Version 2, indicate your decision by adding
032: * "[Contributor] elects to include this software in this distribution
033: * under the [CDDL or GPL Version 2] license." If you do not indicate a
034: * single choice of license, a recipient has the option to distribute
035: * your version of this file under either the CDDL, the GPL Version 2 or
036: * to extend the choice of license to its licensees as provided above.
037: * However, if you add GPL Version 2 code and therefore, elected the GPL
038: * Version 2 license, then the option applies only if the new code is
039: * made subject to such option by the copyright holder.
040: */
041: package org.netbeans.modules.visualweb.dataconnectivity.sql;
042:
043: import java.sql.Connection;
044: import java.sql.DatabaseMetaData;
045: import java.sql.ResultSet;
046: import java.sql.SQLException;
047: import java.util.ArrayList;
048: import java.util.Hashtable;
049: import java.util.Locale;
050: import java.util.ResourceBundle;
051: import javax.sql.DataSource;
052:
053: /**
054: * just in time database meta information
055: * data cached after retrieval
056: * used by severnavigator and other clients
057: *
058: * @author John Kline
059: */
060: public class DatabaseMetaDataHelper {
061:
062: private static ResourceBundle rb = ResourceBundle
063: .getBundle(
064: "org.netbeans.modules.visualweb.dataconnectivity.sql.Bundle",
065: Locale.getDefault());
066:
067: private DataSource dataSource;
068: private Connection connection;
069: private DatabaseMetaData metaData;
070: private String[] schemaNames;
071: private String[] tableNames;
072: private TableMetaData[] tableMetaData;
073: private String[] viewNames;
074: private TableMetaData[] viewMetaData;
075: private String[] procedureNames;
076: private ProcedureMetaData[] procedureMetaData;
077: private Hashtable columnNames;
078: private boolean instantiatedWithMetaData = false;
079:
080: public DatabaseMetaDataHelper(DataSource dataSource)
081: throws SQLException {
082: init();
083: this .dataSource = dataSource;
084: }
085:
086: public DatabaseMetaDataHelper(Connection connection)
087: throws SQLException {
088: init();
089: this .connection = connection;
090: }
091:
092: public DatabaseMetaDataHelper(DatabaseMetaData metaData)
093: throws SQLException {
094: init();
095: this .metaData = metaData;
096: instantiatedWithMetaData = true;
097: }
098:
099: private void init() {
100: dataSource = null;
101: connection = null;
102: metaData = null;
103: schemaNames = null;
104: tableNames = null;
105: tableMetaData = null;
106: viewNames = null;
107: viewMetaData = null;
108: procedureNames = null;
109: columnNames = new Hashtable();
110: }
111:
112: public TableMetaData[] getTableMetaData() throws SQLException {
113: if (tableMetaData == null) {
114: tableMetaData = getTablesInternal(new String[] { "TABLE" }); // NOI18N
115: }
116: return tableMetaData;
117: }
118:
119: public ProcedureMetaData[] getProcedureMetaData()
120: throws SQLException {
121: if (procedureMetaData == null) {
122: procedureMetaData = getProceduresInternal();
123: }
124: return procedureMetaData;
125: }
126:
127: public TableMetaData getTableMetaData(String tableName)
128: throws SQLException {
129: getTableMetaData();
130: for (int i = 0; i < tableMetaData.length; i++) {
131: if (getFullTableName(tableMetaData[i]).equals(tableName)) {
132: return tableMetaData[i];
133: }
134: }
135: throw new SQLException(rb.getString("TABLE_NOT_FOUND") + ": "
136: + tableName); // NOI18N
137: }
138:
139: public ProcedureMetaData getProcedureMetaData(String procedureName)
140: throws SQLException {
141: getProcedureMetaData();
142: for (int i = 0; i < procedureMetaData.length; i++) {
143: if (getFullProcedureName(procedureMetaData[i]).equals(
144: procedureName)) {
145: return procedureMetaData[i];
146: }
147: }
148: throw new SQLException(rb.getString("PROCEDURE_NOT_FOUND")
149: + ": " + procedureName); // NOI18N
150: }
151:
152: public TableMetaData getViewMetaData(String viewName)
153: throws SQLException {
154: getViewMetaData();
155: for (int i = 0; i < viewMetaData.length; i++) {
156: if (getFullTableName(viewMetaData[i]).equals(viewName)) {
157: return viewMetaData[i];
158: }
159: }
160: throw new SQLException(rb.getString("VIEW_NOT_FOUND") + ": "
161: + viewName); // NOI18N
162: }
163:
164: public TableMetaData getTableOrViewMetaData(String name)
165: throws SQLException {
166: getTableMetaData();
167: for (int i = 0; i < tableMetaData.length; i++) {
168: if (getFullTableName(tableMetaData[i]).equals(name)) {
169: return tableMetaData[i];
170: }
171: }
172: getViewMetaData();
173: for (int i = 0; i < viewMetaData.length; i++) {
174: if (getFullTableName(viewMetaData[i]).equals(name)) {
175: return viewMetaData[i];
176: }
177: }
178: throw new SQLException(rb.getString("TABLE_OR_VIEW_NOT_FOUND")
179: + ": " + name); // NOI18N
180: }
181:
182: private String getFullTableName(TableMetaData tmd)
183: throws SQLException {
184: String schema = tmd.getMetaInfo(TableMetaData.TABLE_SCHEM);
185: if (schema == null || schema.trim().equals("")) {
186: schema = "";
187: } else {
188: schema += "."; // NOI18N
189: }
190: return schema + tmd.getMetaInfo(TableMetaData.TABLE_NAME);
191: }
192:
193: private String getFullProcedureName(ProcedureMetaData pmd)
194: throws SQLException {
195: String schema = pmd
196: .getMetaInfoAsString(ProcedureMetaData.PROCEDURE_SCHEM);
197: if (schema == null || schema.trim().equals("")) {
198: schema = "";
199: } else {
200: schema += "."; // NOI18N
201: }
202: return schema
203: + pmd.getMetaInfo(ProcedureMetaData.PROCEDURE_NAME);
204: }
205:
206: public String getNameForSelect(String tableName)
207: throws SQLException {
208: TableMetaData tmd = null;
209: tmd = getTableOrViewMetaData(tableName);
210: String schema = tmd.getMetaInfo(TableMetaData.TABLE_SCHEM);
211: if (schema == null || schema.trim().equals("")) {
212: schema = "";
213: } else {
214: schema += "."; // NOI18N
215: }
216: return schema + tmd.getMetaInfo(TableMetaData.TABLE_NAME);
217: }
218:
219: public String getProcedureNameForExec(String procedureName)
220: throws SQLException {
221: ProcedureMetaData pmd = null;
222: pmd = getProcedureMetaData(procedureName);
223: String schema = pmd
224: .getMetaInfoAsString(ProcedureMetaData.PROCEDURE_SCHEM);
225: if (schema == null || schema.trim().equals("")) {
226: schema = "";
227: } else {
228: schema += "."; // NOI18N
229: }
230: return schema
231: + pmd.getMetaInfo(ProcedureMetaData.PROCEDURE_NAME);
232: }
233:
234: public String getNoSchemaName(String tableName) throws SQLException {
235: TableMetaData tmd = null;
236: tmd = getTableOrViewMetaData(tableName);
237: return tmd.getMetaInfo(TableMetaData.TABLE_NAME);
238: }
239:
240: public String getDisplayName(String tableName) throws SQLException {
241: TableMetaData tmd = null;
242: tmd = getTableOrViewMetaData(tableName);
243: return tmd.getMetaInfo(TableMetaData.TABLE_NAME);
244: }
245:
246: /** gets all schemas in this database, a 0 length array returned is possible */
247: public String[] getSchemas() throws SQLException {
248: if (schemaNames == null) {
249: ResultSet rs = getMetaData().getSchemas();
250: ArrayList list = new ArrayList();
251: while (rs.next()) {
252: String schema = rs.getString("TABLE_SCHEM"); // NOI18N
253: if (schema != null) {
254: list.add(schema);
255: }
256: }
257: rs.close();
258: schemaNames = (String[]) list.toArray(new String[0]);
259: }
260: return schemaNames;
261: }
262:
263: public String[] getTables(String schema) throws SQLException {
264: return getTablesInternal(getTableMetaData(), schema);
265: }
266:
267: public String[] getViews(String schema) throws SQLException {
268: return getTablesInternal(getViewMetaData(), schema);
269: }
270:
271: public String[] getProcedures(String schema) throws SQLException {
272: return getProceduresInternal(getProcedureMetaData(), schema);
273: }
274:
275: /**
276: * return true is schema has no tables and no views in it
277: */
278: boolean isEmpty(String schema) throws SQLException {
279: return isEmpty(schema, new String[] { "TABLE" })
280: && isEmpty(schema, new String[] { "VIEW" });
281: }
282:
283: boolean isEmpty(String schema, String[] tableTypes)
284: throws SQLException {
285: ResultSet rs = getMetaData().getTables(null, schema, "%",
286: tableTypes); // NOI18N
287: boolean result = !rs.next();
288: rs.close();
289: return result;
290: }
291:
292: /**
293: * an alternative to isEmpty
294: * this will return the first table or view found in a schema (formatted as a SELECT)
295: * if null is returned, the schema is empty
296: * in this way, we can set a validation query
297: *
298: * Note: if schema is null, then the query will be formed from the first table or view
299: * found in any schema
300: */
301: String getValidationQuery(String schema) throws SQLException {
302: ResultSet rs = getMetaData().getTables(null, schema, "%",
303: new String[] { "TABLE", "VIEW" }); // NOI18N
304: String validationQuery = null;
305: if (rs.next()) {
306: String schema_name = rs.getString("TABLE_SCHEM");
307: String table_name = rs.getString("TABLE_NAME");
308:
309: if (schema_name != null) {
310: table_name = schema_name + "." + table_name;
311: }
312: validationQuery = DesignTimeDataSource.composeSelect(
313: table_name, getMetaData());
314: }
315: rs.close();
316: return validationQuery;
317: }
318:
319: private String[] getTablesInternal(TableMetaData[] tmd,
320: String schema) throws SQLException {
321: ArrayList list = new ArrayList();
322: for (int i = 0; i < tmd.length; i++) {
323: String tableSchema = tmd[i]
324: .getMetaInfo(TableMetaData.TABLE_SCHEM);
325: if (schema == null
326: && tableSchema == null
327: || (schema != null && tableSchema != null && tableSchema
328: .equals(schema))) {
329:
330: list.add(getFullTableName(tmd[i]));
331: }
332: }
333: return (String[]) list.toArray(new String[0]);
334: }
335:
336: private String[] getProceduresInternal(ProcedureMetaData[] pmd,
337: String schema) throws SQLException {
338: ArrayList list = new ArrayList();
339: for (int i = 0; i < pmd.length; i++) {
340: String procedureSchema = pmd[i]
341: .getMetaInfoAsString(ProcedureMetaData.PROCEDURE_SCHEM);
342: if (schema == null
343: && procedureSchema == null
344: || (schema != null && procedureSchema != null && procedureSchema
345: .equals(schema))) {
346:
347: list.add(getFullProcedureName(pmd[i]));
348: }
349: }
350: return (String[]) list.toArray(new String[0]);
351: }
352:
353: public String[] getTables() throws SQLException {
354: if (tableNames == null) {
355: tableNames = new String[getTableMetaData().length];
356: for (int i = 0; i < getTableMetaData().length; i++) {
357: tableNames[i] = getFullTableName(getTableMetaData()[i]);
358: }
359: }
360: return tableNames;
361: }
362:
363: public String[] getProcedures() throws SQLException {
364: if (procedureNames == null) {
365: procedureNames = new String[getProcedureMetaData().length];
366: for (int i = 0; i < getProcedureMetaData().length; i++) {
367: procedureNames[i] = getFullProcedureName(getProcedureMetaData()[i]);
368: }
369: }
370: return procedureNames;
371: }
372:
373: public TableMetaData[] getViewMetaData() throws SQLException {
374: if (viewMetaData == null) {
375: viewMetaData = getTablesInternal(new String[] { "VIEW" }); // NOI18N
376: }
377: return viewMetaData;
378: }
379:
380: public String[] getViews() throws SQLException {
381: if (viewNames == null) {
382: viewNames = new String[getViewMetaData().length];
383: for (int i = 0; i < getViewMetaData().length; i++) {
384: viewNames[i] = getFullTableName(getViewMetaData()[i]);
385: }
386: }
387: return viewNames;
388: }
389:
390: public String[] getColumns(String tableName) throws SQLException {
391: return getTableOrViewMetaData(tableName).getColumns();
392: }
393:
394: public String[] getProcedureColumns(String procedureName)
395: throws SQLException {
396: return getProcedureMetaData(procedureName).getColumns();
397: }
398:
399: public ColumnMetaData[] getColumnMetaData(String tableName)
400: throws SQLException {
401:
402: return getTableOrViewMetaData(tableName).getColumnMetaData();
403: }
404:
405: public ColumnMetaData getColumnMetaData(String tableName,
406: String columnName) throws SQLException {
407:
408: return getTableOrViewMetaData(tableName).getColumnMetaData(
409: columnName);
410: }
411:
412: public ProcedureColumnMetaData getProcedureColumnMetaData(
413: String procedureName, String columnName)
414: throws SQLException {
415:
416: return getProcedureMetaData(procedureName)
417: .getProcedureColumnMetaData(columnName);
418: }
419:
420: public TableMetaData[] getTablesInternal(String[] tableTypes)
421: throws SQLException {
422: ResultSet rs = getMetaData().getTables(null, null, "%",
423: tableTypes); // NOI18N
424: ArrayList list = new ArrayList();
425: while (rs.next()) {
426: TableMetaData tmd = new TableMetaData(rs, getMetaData());
427: // HACK bugfix for 5095727, where mysql's getTables() ignores
428: // the "tableTypes" parameter. I.e., we ask for "VIEW" and get "TABLE" too.
429: if (tableTypes == null) {
430: list.add(tmd);
431: } else {
432: for (int icnt = 0; icnt < tableTypes.length; icnt++) {
433: // check if the returned tabletype is in the list originally requested.
434: if (tableTypes[icnt].equals(tmd
435: .getMetaInfo(TableMetaData.TABLE_TYPE))) {
436: list.add(tmd);
437: break;
438: }
439: }
440: }
441: // -- end HACK bugfix for 5095727
442: }
443: rs.close();
444: return (TableMetaData[]) list.toArray(new TableMetaData[0]);
445: }
446:
447: public ProcedureMetaData[] getProceduresInternal()
448: throws SQLException {
449: ResultSet rs = getMetaData().getProcedures(null, null, "%"); // NOI18N
450: ArrayList list = new ArrayList();
451: while (rs.next()) {
452: list.add(new ProcedureMetaData(rs, getMetaData()));
453: }
454: rs.close();
455: return (ProcedureMetaData[]) list
456: .toArray(new ProcedureMetaData[0]);
457: }
458:
459: /*
460: /////////zzz the old String[] getProcedures() follows:
461: if (procedureNames == null) {
462: ResultSet rs = getMetaData().getProcedures(null, null, "%"); // NOI18N
463: ArrayList list = new ArrayList();
464: while (rs.next()) {
465: String schema = rs.getString("PROCEDURE_SCHEM"); // NOI18N
466: if (schema == null || schema.trim().equals("")) {
467: schema = "";
468: } else {
469: schema += "."; // NOI18N
470: }
471: list.add(schema + rs.getString("PROCEDURE_NAME")); // NOI18N
472: }
473: rs.close();
474: procedureNames = (String[])list.toArray(new String[0]);
475: }
476: return procedureNames;
477: }
478: //////////zzzzzzzzzzz
479: */
480:
481: public void refresh() {
482: if (dataSource != null) {
483: if (connection != null) {
484: try {
485: connection.close();
486: } catch (SQLException e) {
487: }
488: connection = null;
489: }
490: }
491: if (!instantiatedWithMetaData) {
492: metaData = null;
493: }
494: schemaNames = null;
495: tableNames = null;
496: tableMetaData = null;
497: viewMetaData = null;
498: viewNames = null;
499: procedureNames = null;
500: columnNames.clear();
501: }
502:
503: private Connection getConnection() throws SQLException {
504: if (connection == null) {
505: connection = dataSource.getConnection();
506: }
507: return connection;
508: }
509:
510: public/*private*/DatabaseMetaData getMetaData()
511: throws SQLException {
512: if (metaData == null) {
513: metaData = getConnection().getMetaData();
514: }
515: return metaData;
516: }
517:
518: public boolean isConnected() {
519: return (connection != null);
520: }
521: }
|