Java tutorial
package org.apache.ddlutils.task; /* * Licensed to the Apache Software Foundation (ASF) under one * or more contributor license agreements. See the NOTICE file * distributed with this work for additional information * regarding copyright ownership. The ASF licenses this file * to you under the Apache License, Version 2.0 (the * "License"); you may not use this file except in compliance * with the License. You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, * software distributed under the License is distributed on an * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY * KIND, either express or implied. See the License for the * specific language governing permissions and limitations * under the License. */ import java.io.File; import java.io.FileOutputStream; import java.lang.reflect.InvocationTargetException; import java.lang.reflect.Method; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.util.ArrayList; import java.util.Arrays; import java.util.HashSet; import java.util.Set; import java.util.StringTokenizer; import org.apache.commons.collections.set.ListOrderedSet; import org.apache.commons.dbcp.BasicDataSource; import org.apache.tools.ant.BuildException; import org.apache.tools.ant.Project; import org.apache.tools.ant.Task; import org.dom4j.Document; import org.dom4j.DocumentFactory; import org.dom4j.Element; import org.dom4j.io.OutputFormat; import org.dom4j.io.XMLWriter; /** * A simple helper task that dumps information about a database using JDBC. * * @version $Revision: 289996 $ * @ant.task name="dumpMetadata" */ public class DumpMetadataTask extends Task { /** Methods that are filtered when enumerating the properties. */ private static final String[] IGNORED_PROPERTY_METHODS = { "getConnection", "getCatalogs", "getSchemas" }; /** The data source to use for accessing the database. */ private BasicDataSource _dataSource; /** The file to write the dump to. */ private File _outputFile = null; /** The encoding of the XML output file. */ private String _outputEncoding = "UTF-8"; /** The database catalog(s) to read. */ private String _catalogPattern = "%"; /** The database schema(s) to read. */ private String _schemaPattern = "%"; /** The pattern for reading all tables. */ private String _tablePattern = "%"; /** The pattern for reading all procedures. */ private String _procedurePattern = "%"; /** The pattern for reading all columns. */ private String _columnPattern = "%"; /** The tables types to read; <code>null</code> or an empty list means that we shall read every type. */ private String[] _tableTypes = null; /** Whether to read tables. */ private boolean _dumpTables = true; /** Whether to read procedures. */ private boolean _dumpProcedures = true; /** * Adds the data source to use for accessing the database. * * @param dataSource The data source */ public void addConfiguredDatabase(BasicDataSource dataSource) { _dataSource = dataSource; } /** * Specifies the output file to which the database metadata is written to. * * @param outputFile The output file * @ant.required */ public void setOutputFile(File outputFile) { _outputFile = outputFile; } /** * Specifies the encoding of the output file. * * @param encoding The encoding * @ant.not-required Per default, <code>UTF-8</code> is used. */ public void setOutputEncoding(String encoding) { _outputEncoding = encoding; } /** * Sets the catalog pattern used when accessing the database. * * @param catalogPattern The catalog pattern * @ant.not-required Per default, no specific catalog is used (value <code>%</code>). */ public void setCatalogPattern(String catalogPattern) { _catalogPattern = ((catalogPattern == null) || (catalogPattern.length() == 0) ? null : catalogPattern); } /** * Sets the schema pattern used when accessing the database. * * @param schemaPattern The schema pattern * @ant.not-required Per default, no specific schema is used (value <code>%</code>). */ public void setSchemaPattern(String schemaPattern) { _schemaPattern = ((schemaPattern == null) || (schemaPattern.length() == 0) ? null : schemaPattern); } /** * Specifies the table to be processed. For details see * <a href="http://java.sun.com/j2se/1.4.2/docs/api/java/sql/DatabaseMetaData.html#getTables(java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String[])">java.sql.DatabaseMetaData#getTables</a>. * * @param tablePattern The table pattern * @ant.not-required By default, all tables are read (value <code>%</code>). */ public void setTablePattern(String tablePattern) { _tablePattern = ((tablePattern == null) || (tablePattern.length() == 0) ? null : tablePattern); } /** * Specifies the procedures to be processed. For details and typical table types see * <a href="http://java.sun.com/j2se/1.4.2/docs/api/java/sql/DatabaseMetaData.html#getProcedures(java.lang.String,%20java.lang.String,%20java.lang.String)">java.sql.DatabaseMetaData#getProcedures</a>. * * @param procedurePattern The procedure pattern * @ant.not-required By default, all procedures are read (value <code>%</code>). */ public void setProcedurePattern(String procedurePattern) { _procedurePattern = ((procedurePattern == null) || (procedurePattern.length() == 0) ? null : procedurePattern); } /** * Specifies the columns to be processed. For details and typical table types see * <a href="http://java.sun.com/j2se/1.4.2/docs/api/java/sql/DatabaseMetaData.html#getColumns(java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String[])">java.sql.DatabaseMetaData#getColumns</a>. * * @param columnPattern The column pattern * @ant.not-required By default, all columns are read (value <code>%</code>). */ public void setColumnPattern(String columnPattern) { _columnPattern = ((columnPattern == null) || (columnPattern.length() == 0) ? null : columnPattern); } /** * Specifies the table types to be processed. For details and typical table types see * <a href="http://java.sun.com/j2se/1.4.2/docs/api/java/sql/DatabaseMetaData.html#getTables(java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String[])">java.sql.DatabaseMetaData#getTables</a>. * * @param tableTypes The table types to read * @ant.not-required By default, all types of tables are read. */ public void setTableTypes(String tableTypes) { ArrayList types = new ArrayList(); if (tableTypes != null) { StringTokenizer tokenizer = new StringTokenizer(tableTypes, ","); while (tokenizer.hasMoreTokens()) { String token = tokenizer.nextToken().trim(); if (token.length() > 0) { types.add(token); } } } _tableTypes = (String[]) types.toArray(new String[types.size()]); } /** * Specifies whether procedures shall be read from the database. * * @param readProcedures <code>true</code> if procedures shall be read * @ant.not-required By default, procedures are read. */ public void setDumpProcedures(boolean readProcedures) { _dumpProcedures = readProcedures; } /** * Specifies whether tables shall be read from the database. * * @param readTables <code>true</code> if tables shall be read * @ant.not-required By default, tables are read. */ public void setDumpTables(boolean readTables) { _dumpTables = readTables; } /** * {@inheritDoc} */ public void execute() throws BuildException { if (_dataSource == null) { log("No data source specified, so there is nothing to do.", Project.MSG_INFO); return; } Connection connection = null; try { Document document = DocumentFactory.getInstance().createDocument(); Element root = document.addElement("metadata"); root.addAttribute("driverClassName", _dataSource.getDriverClassName()); connection = _dataSource.getConnection(); dumpMetaData(root, connection.getMetaData()); OutputFormat outputFormat = OutputFormat.createPrettyPrint(); XMLWriter xmlWriter = null; outputFormat.setEncoding(_outputEncoding); if (_outputFile == null) { xmlWriter = new XMLWriter(System.out, outputFormat); } else { xmlWriter = new XMLWriter(new FileOutputStream(_outputFile), outputFormat); } xmlWriter.write(document); xmlWriter.close(); } catch (Exception ex) { throw new BuildException(ex); } finally { if (connection != null) { try { connection.close(); } catch (SQLException ex) { } } } } /** * Dumps the database meta data into XML elements under the given element. * * @param element The XML element * @param metaData The meta data */ private void dumpMetaData(Element element, DatabaseMetaData metaData) throws NoSuchMethodException, IllegalAccessException, InvocationTargetException, SQLException { // We rather iterate over the methods because most metadata properties // do not follow the bean naming standard Method[] methods = metaData.getClass().getMethods(); Set filtered = new HashSet(Arrays.asList(IGNORED_PROPERTY_METHODS)); for (int idx = 0; idx < methods.length; idx++) { // only no-arg methods that return something and that are not defined in Object // we also filter certain methods if ((methods[idx].getParameterTypes().length == 0) && (methods[idx].getReturnType() != null) && (Object.class != methods[idx].getDeclaringClass()) && !filtered.contains(methods[idx].getName())) { dumpProperty(element, metaData, methods[idx]); } } dumpCatalogsAndSchemas(element, metaData); if (_dumpTables) { dumpTables(element, metaData); } if (_dumpProcedures) { dumpProcedures(element, metaData); } } /** * Dumps the property represented by the given method. * * @param parent The parent XML element * @param obj The instance we're working on * @param propGetter The method for accessing the property */ private void dumpProperty(Element parent, Object obj, Method propGetter) { try { addProperty(parent, getPropertyName(propGetter.getName()), propGetter.invoke(obj, null)); } catch (Throwable ex) { log("Could not dump property " + propGetter.getName() + " because of " + ex.getMessage(), Project.MSG_WARN); } } /** * Adds a property to the given element, either as an attribute (primitive value or * string) or as a sub element. * * @param element The XML element * @param name The name of the property * @param value The value of the property */ private void addProperty(Element element, String name, Object value) { if (value != null) { if (value.getClass().isArray()) { addArrayProperty(element, name, (Object[]) value); } else if (value.getClass().isPrimitive() || (value instanceof String)) { element.addAttribute(name, value.toString()); } else if (value instanceof ResultSet) { addResultSetProperty(element, name, (ResultSet) value); } } } /** * Adds a property to the given XML element that is represented as an array. * * @param element The XML element * @param name The name of the property * @param values The values of the property */ private void addArrayProperty(Element element, String name, Object[] values) { String propName = name; if (propName.endsWith("s")) { propName = propName.substring(0, propName.length() - 1); } Element arrayElem = element.addElement(propName + "s"); for (int idx = 0; idx < values.length; idx++) { addProperty(arrayElem, "value", values[idx]); } } /** * Adds a property to the given XML element that is represented as a result set. * * @param element The XML element * @param name The name of the property * @param result The values of the property as a result set */ private void addResultSetProperty(Element element, String name, ResultSet result) { try { String propName = name; if (propName.endsWith("s")) { propName = propName.substring(0, propName.length() - 1); } Element resultSetElem = element.addElement(propName + "s"); ResultSetMetaData metaData = result.getMetaData(); while (result.next()) { Element curRow = resultSetElem.addElement(propName); for (int idx = 1; idx <= metaData.getColumnCount(); idx++) { Object value = result.getObject(idx); addProperty(curRow, metaData.getColumnLabel(idx), value); } } } catch (SQLException ex) { ex.printStackTrace(); } } /** * Derives the property name from the given method name. * * @param methodName The method name * @return The property name */ private String getPropertyName(String methodName) { if (methodName.startsWith("get")) { if (Character.isLowerCase(methodName.charAt(4))) { return Character.toLowerCase(methodName.charAt(3)) + methodName.substring(4); } else { return methodName.substring(3); } } else if (methodName.startsWith("is")) { if (Character.isLowerCase(methodName.charAt(3))) { return Character.toLowerCase(methodName.charAt(2)) + methodName.substring(3); } else { return methodName.substring(2); } } else { return methodName; } } /** * Dumps the catalogs and schemas of the database. * * @param parent The parent element * @param metaData The database meta data */ private void dumpCatalogsAndSchemas(Element parent, DatabaseMetaData metaData) throws SQLException { // Next we determine and dump the catalogs Element catalogsElem = parent.addElement("catalogs"); ResultSet result = metaData.getCatalogs(); try { while (result.next()) { String catalogName = getString(result, "TABLE_CAT"); if ((catalogName != null) && (catalogName.length() > 0)) { Element catalogElem = catalogsElem.addElement("catalog"); catalogElem.addAttribute("name", catalogName); } } } finally { if (result != null) { result.close(); } } Element schemasElem = parent.addElement("schemas"); // We also dump the schemas (some dbs only support one of the two) result = metaData.getSchemas(); try { while (result.next()) { String schemaName = getString(result, "TABLE_SCHEM"); if ((schemaName != null) && (schemaName.length() > 0)) { Element schemaElem = schemasElem.addElement("schema"); schemaElem.addAttribute("name", schemaName); } } } finally { if (result != null) { result.close(); } } } /** * Dumps all tables. * * @param parent The parent element * @param metaData The database metadata */ private void dumpTables(Element parent, DatabaseMetaData metaData) throws SQLException { String[] tableTypes = _tableTypes; ResultSet result = null; if ((tableTypes == null) || (tableTypes.length == 0)) { // First we need the list of supported table types ArrayList tableTypeList = new ArrayList(); result = metaData.getTableTypes(); try { while (result.next()) { tableTypeList.add(getString(result, "TABLE_TYPE")); } } finally { if (result != null) { result.close(); } } tableTypes = (String[]) tableTypeList.toArray(new String[tableTypeList.size()]); } try { result = metaData.getTables(_catalogPattern, _schemaPattern, _tablePattern, tableTypes); } catch (SQLException ex) { log("Could not determine the tables: " + ex.getMessage(), Project.MSG_ERR); return; } Element tablesElem = parent.addElement("tables"); Set columns = getColumnsInResultSet(result); try { while (result.next()) { String tableName = getString(result, "TABLE_NAME"); if ((tableName == null) || (tableName.length() == 0)) { continue; } Element tableElem = tablesElem.addElement("table"); String catalog = getString(result, "TABLE_CAT"); String schema = getString(result, "TABLE_SCHEM"); log("Reading table " + ((schema != null) && (schema.length() > 0) ? schema + "." : "") + tableName, Project.MSG_INFO); tableElem.addAttribute("name", tableName); if (catalog != null) { tableElem.addAttribute("catalog", catalog); } if (schema != null) { tableElem.addAttribute("schema", schema); } addStringAttribute(result, columns, "TABLE_TYPE", tableElem, "type"); addStringAttribute(result, columns, "REMARKS", tableElem, "remarks"); addStringAttribute(result, columns, "TYPE_NAME", tableElem, "typeName"); addStringAttribute(result, columns, "TYPE_CAT", tableElem, "typeCatalog"); addStringAttribute(result, columns, "TYPE_SCHEM", tableElem, "typeSchema"); addStringAttribute(result, columns, "SELF_REFERENCING_COL_NAME", tableElem, "identifierColumn"); addStringAttribute(result, columns, "REF_GENERATION", tableElem, "identifierGeneration"); dumpColumns(tableElem, metaData, catalog, schema, tableName); dumpPKs(tableElem, metaData, catalog, schema, tableName); dumpVersionColumns(tableElem, metaData, catalog, schema, tableName); dumpFKs(tableElem, metaData, catalog, schema, tableName); dumpIndices(tableElem, metaData, catalog, schema, tableName); } } finally { if (result != null) { result.close(); } } } /** * Dumps the columns of the indicated table. * * @param tableElem The XML element for the table * @param metaData The database metadata * @param catalogName The catalog name * @param schemaName The schema name * @param tableName The table name */ private void dumpColumns(Element tableElem, DatabaseMetaData metaData, String catalogName, String schemaName, String tableName) throws SQLException { ResultSet result = null; try { result = metaData.getColumns(catalogName, schemaName, tableName, _columnPattern); } catch (SQLException ex) { log("Could not determine the columns for table '" + tableName + "': " + ex.getMessage(), Project.MSG_ERR); return; } Set columns = getColumnsInResultSet(result); try { while (result.next()) { String columnName = getString(result, "COLUMN_NAME"); if ((columnName == null) || (columnName.length() == 0)) { continue; } Element columnElem = tableElem.addElement("column"); columnElem.addAttribute("name", columnName); addIntAttribute(result, columns, "DATA_TYPE", columnElem, "typeCode"); addStringAttribute(result, columns, "TYPE_NAME", columnElem, "type"); addIntAttribute(result, columns, "COLUMN_SIZE", columnElem, "size"); addIntAttribute(result, columns, "DECIMAL_DIGITS", columnElem, "digits"); addIntAttribute(result, columns, "NUM_PREC_RADIX", columnElem, "precision"); if (columns.contains("NULLABLE")) { switch (result.getInt("NULLABLE")) { case DatabaseMetaData.columnNoNulls: columnElem.addAttribute("nullable", "false"); break; case DatabaseMetaData.columnNullable: columnElem.addAttribute("nullable", "true"); break; default: columnElem.addAttribute("nullable", "unknown"); break; } } addStringAttribute(result, columns, "REMARKS", columnElem, "remarks"); addStringAttribute(result, columns, "COLUMN_DEF", columnElem, "defaultValue"); addIntAttribute(result, columns, "CHAR_OCTET_LENGTH", columnElem, "maxByteLength"); addIntAttribute(result, columns, "ORDINAL_POSITION", columnElem, "index"); if (columns.contains("IS_NULLABLE")) { String value = getString(result, "IS_NULLABLE"); if ("no".equalsIgnoreCase(value)) { columnElem.addAttribute("isNullable", "false"); } else if ("yes".equalsIgnoreCase(value)) { columnElem.addAttribute("isNullable", "true"); } else { columnElem.addAttribute("isNullable", "unknown"); } } addStringAttribute(result, columns, "SCOPE_CATLOG", columnElem, "refCatalog"); addStringAttribute(result, columns, "SCOPE_SCHEMA", columnElem, "refSchema"); addStringAttribute(result, columns, "SCOPE_TABLE", columnElem, "refTable"); addShortAttribute(result, columns, "SOURCE_DATA_TYPE", columnElem, "sourceTypeCode"); } } finally { if (result != null) { result.close(); } } } /** * Dumps the primary key columns of the indicated table. * * @param tableElem The XML element for the table * @param metaData The database metadata * @param catalogName The catalog name * @param schemaName The schema name * @param tableName The table name */ private void dumpPKs(Element tableElem, DatabaseMetaData metaData, String catalogName, String schemaName, String tableName) throws SQLException { ResultSet result = null; try { result = metaData.getPrimaryKeys(catalogName, schemaName, tableName); } catch (SQLException ex) { log("Could not determine the primary key columns for table '" + tableName + "': " + ex.getMessage(), Project.MSG_ERR); return; } Set columns = getColumnsInResultSet(result); try { while (result.next()) { String columnName = getString(result, "COLUMN_NAME"); if ((columnName == null) || (columnName.length() == 0)) { continue; } Element pkElem = tableElem.addElement("primaryKey"); pkElem.addAttribute("column", columnName); addStringAttribute(result, columns, "PK_NAME", pkElem, "name"); addShortAttribute(result, columns, "KEY_SEQ", pkElem, "sequenceNumberInPK"); } } finally { if (result != null) { result.close(); } } } /** * Dumps the versioned (auto-updating) columns of the indicated table. * * @param tableElem The XML element for the table * @param metaData The database metadata * @param catalogName The catalog name * @param schemaName The schema name * @param tableName The table name */ private void dumpVersionColumns(Element tableElem, DatabaseMetaData metaData, String catalogName, String schemaName, String tableName) throws SQLException { ResultSet result = null; try { result = metaData.getVersionColumns(catalogName, schemaName, tableName); } catch (SQLException ex) { log("Could not determine the versioned columns for table '" + tableName + "': " + ex.getMessage(), Project.MSG_ERR); return; } Set columns = getColumnsInResultSet(result); try { while (result.next()) { String columnName = getString(result, "COLUMN_NAME"); if ((columnName == null) || (columnName.length() == 0)) { continue; } Element columnElem = tableElem.addElement("versionedColumn"); columnElem.addAttribute("column", columnName); addIntAttribute(result, columns, "DATA_TYPE", columnElem, "typeCode"); addStringAttribute(result, columns, "TYPE_NAME", columnElem, "type"); addIntAttribute(result, columns, "BUFFER_LENGTH", columnElem, "size"); addIntAttribute(result, columns, "COLUMN_SIZE", columnElem, "precision"); addShortAttribute(result, columns, "DECIMAL_DIGITS", columnElem, "scale"); if (columns.contains("PSEUDO_COLUMN")) { switch (result.getShort("PSEUDO_COLUMN")) { case DatabaseMetaData.versionColumnPseudo: columnElem.addAttribute("columnType", "pseudo column"); break; case DatabaseMetaData.versionColumnNotPseudo: columnElem.addAttribute("columnType", "real column"); break; default: columnElem.addAttribute("columnType", "unknown"); break; } } } } finally { if (result != null) { result.close(); } } } /** * Dumps the foreign key columns of the indicated table to other tables. * * @param tableElem The XML element for the table * @param metaData The database metadata * @param catalogName The catalog name * @param schemaName The schema name * @param tableName The table name */ private void dumpFKs(Element tableElem, DatabaseMetaData metaData, String catalogName, String schemaName, String tableName) throws SQLException { ResultSet result = null; try { result = metaData.getImportedKeys(catalogName, schemaName, tableName); } catch (SQLException ex) { log("Could not determine the foreign keys for table '" + tableName + "': " + ex.getMessage(), Project.MSG_ERR); return; } Set columns = getColumnsInResultSet(result); try { while (result.next()) { Element fkElem = tableElem.addElement("foreignKey"); addStringAttribute(result, columns, "FK_NAME", fkElem, "name"); addStringAttribute(result, columns, "PK_NAME", fkElem, "primaryKeyName"); addStringAttribute(result, columns, "PKCOLUMN_NAME", fkElem, "column"); addStringAttribute(result, columns, "FKTABLE_CAT", fkElem, "foreignCatalog"); addStringAttribute(result, columns, "FKTABLE_SCHEM", fkElem, "foreignSchema"); addStringAttribute(result, columns, "FKTABLE_NAME", fkElem, "foreignTable"); addStringAttribute(result, columns, "FKCOLUMN_NAME", fkElem, "foreignColumn"); addShortAttribute(result, columns, "KEY_SEQ", fkElem, "sequenceNumberInFK"); if (columns.contains("UPDATE_RULE")) { switch (result.getShort("UPDATE_RULE")) { case DatabaseMetaData.importedKeyNoAction: fkElem.addAttribute("updateRule", "no action"); break; case DatabaseMetaData.importedKeyCascade: fkElem.addAttribute("updateRule", "cascade PK change"); break; case DatabaseMetaData.importedKeySetNull: fkElem.addAttribute("updateRule", "set FK to NULL"); break; case DatabaseMetaData.importedKeySetDefault: fkElem.addAttribute("updateRule", "set FK to default"); break; default: fkElem.addAttribute("updateRule", "unknown"); break; } } if (columns.contains("DELETE_RULE")) { switch (result.getShort("DELETE_RULE")) { case DatabaseMetaData.importedKeyNoAction: case DatabaseMetaData.importedKeyRestrict: fkElem.addAttribute("deleteRule", "no action"); break; case DatabaseMetaData.importedKeyCascade: fkElem.addAttribute("deleteRule", "cascade PK change"); break; case DatabaseMetaData.importedKeySetNull: fkElem.addAttribute("deleteRule", "set FK to NULL"); break; case DatabaseMetaData.importedKeySetDefault: fkElem.addAttribute("deleteRule", "set FK to default"); break; default: fkElem.addAttribute("deleteRule", "unknown"); break; } } if (columns.contains("DEFERRABILITY")) { switch (result.getShort("DEFERRABILITY")) { case DatabaseMetaData.importedKeyInitiallyDeferred: fkElem.addAttribute("deferrability", "initially deferred"); break; case DatabaseMetaData.importedKeyInitiallyImmediate: fkElem.addAttribute("deferrability", "immediately deferred"); break; case DatabaseMetaData.importedKeyNotDeferrable: fkElem.addAttribute("deferrability", "not deferred"); break; default: fkElem.addAttribute("deferrability", "unknown"); break; } } } } finally { if (result != null) { result.close(); } } } /** * Dumps the indices of the indicated table. * * @param tableElem The XML element for the table * @param metaData The database metadata * @param catalogName The catalog name * @param schemaName The schema name * @param tableName The table name */ private void dumpIndices(Element tableElem, DatabaseMetaData metaData, String catalogName, String schemaName, String tableName) throws SQLException { ResultSet result = null; try { result = metaData.getIndexInfo(catalogName, schemaName, tableName, false, false); } catch (SQLException ex) { log("Could not determine the indices for table '" + tableName + "': " + ex.getMessage(), Project.MSG_ERR); return; } Set columns = getColumnsInResultSet(result); try { while (result.next()) { Element indexElem = tableElem.addElement("index"); addStringAttribute(result, columns, "INDEX_NAME", indexElem, "name"); addBooleanAttribute(result, columns, "NON_UNIQUE", indexElem, "nonUnique"); addStringAttribute(result, columns, "INDEX_QUALIFIER", indexElem, "indexCatalog"); if (columns.contains("TYPE")) { switch (result.getShort("TYPE")) { case DatabaseMetaData.tableIndexStatistic: indexElem.addAttribute("type", "table statistics"); break; case DatabaseMetaData.tableIndexClustered: indexElem.addAttribute("type", "clustered"); break; case DatabaseMetaData.tableIndexHashed: indexElem.addAttribute("type", "hashed"); break; case DatabaseMetaData.tableIndexOther: indexElem.addAttribute("type", "other"); break; default: indexElem.addAttribute("type", "unknown"); break; } } addStringAttribute(result, columns, "COLUMN_NAME", indexElem, "column"); addShortAttribute(result, columns, "ORDINAL_POSITION", indexElem, "sequenceNumberInIndex"); if (columns.contains("ASC_OR_DESC")) { String value = getString(result, "ASC_OR_DESC"); if ("A".equalsIgnoreCase(value)) { indexElem.addAttribute("sortOrder", "ascending"); } else if ("D".equalsIgnoreCase(value)) { indexElem.addAttribute("sortOrder", "descending"); } else { indexElem.addAttribute("sortOrder", "unknown"); } } addIntAttribute(result, columns, "CARDINALITY", indexElem, "cardinality"); addIntAttribute(result, columns, "PAGES", indexElem, "pages"); addStringAttribute(result, columns, "FILTER_CONDITION", indexElem, "filter"); } } finally { if (result != null) { result.close(); } } } /** * Dumps all procedures. * * @param parent The parent element * @param metaData The database metadata */ private void dumpProcedures(Element parent, DatabaseMetaData metaData) throws SQLException { ResultSet result = null; try { result = metaData.getProcedures(_catalogPattern, _schemaPattern, _procedurePattern); } catch (SQLException ex) { log("Could not determine the procedures: " + ex.getMessage(), Project.MSG_ERR); return; } Element proceduresElem = parent.addElement("procedures"); Set columns = getColumnsInResultSet(result); try { while (result.next()) { String procedureName = getString(result, "PROCEDURE_NAME"); if ((procedureName == null) || (procedureName.length() == 0)) { continue; } Element procedureElem = proceduresElem.addElement("procedure"); String catalog = getString(result, "PROCEDURE_CAT"); String schema = getString(result, "PROCEDURE_SCHEM"); log("Reading procedure " + ((schema != null) && (schema.length() > 0) ? schema + "." : "") + procedureName, Project.MSG_INFO); procedureElem.addAttribute("name", procedureName); if (catalog != null) { procedureElem.addAttribute("catalog", catalog); } if (schema != null) { procedureElem.addAttribute("schema", schema); } addStringAttribute(result, columns, "REMARKS", procedureElem, "remarks"); if (columns.contains("PROCEDURE_TYPE")) { switch (result.getShort("PROCEDURE_TYPE")) { case DatabaseMetaData.procedureReturnsResult: procedureElem.addAttribute("type", "returns result"); break; case DatabaseMetaData.procedureNoResult: procedureElem.addAttribute("type", "doesn't return result"); break; case DatabaseMetaData.procedureResultUnknown: procedureElem.addAttribute("type", "may return result"); break; default: procedureElem.addAttribute("type", "unknown"); break; } } dumpProcedure(procedureElem, metaData, "%", "%", procedureName); } } finally { if (result != null) { result.close(); } } } /** * Dumps the contents of the indicated procedure. * * @param procedureElem The XML element for the procedure * @param metaData The database metadata * @param catalogName The catalog name * @param schemaName The schema name * @param procedureName The procedure name */ private void dumpProcedure(Element procedureElem, DatabaseMetaData metaData, String catalogName, String schemaName, String procedureName) throws SQLException { ResultSet result = null; try { result = metaData.getProcedureColumns(catalogName, schemaName, procedureName, _columnPattern); } catch (SQLException ex) { log("Could not determine the columns for procedure '" + procedureName + "': " + ex.getMessage(), Project.MSG_ERR); return; } Set columns = getColumnsInResultSet(result); try { while (result.next()) { String columnName = getString(result, "COLUMN_NAME"); if ((columnName == null) || (columnName.length() == 0)) { continue; } Element columnElem = procedureElem.addElement("column"); columnElem.addAttribute("name", columnName); if (columns.contains("COLUMN_TYPE")) { switch (result.getShort("COLUMN_TYPE")) { case DatabaseMetaData.procedureColumnIn: columnElem.addAttribute("type", "in parameter"); break; case DatabaseMetaData.procedureColumnInOut: columnElem.addAttribute("type", "in/out parameter"); break; case DatabaseMetaData.procedureColumnOut: columnElem.addAttribute("type", "out parameter"); break; case DatabaseMetaData.procedureColumnReturn: columnElem.addAttribute("type", "return value"); break; case DatabaseMetaData.procedureColumnResult: columnElem.addAttribute("type", "result column in ResultSet"); break; default: columnElem.addAttribute("type", "unknown"); break; } } addIntAttribute(result, columns, "DATA_TYPE", columnElem, "typeCode"); addStringAttribute(result, columns, "TYPE_NAME", columnElem, "type"); addIntAttribute(result, columns, "LENGTH", columnElem, "length"); addIntAttribute(result, columns, "PRECISION", columnElem, "precision"); addShortAttribute(result, columns, "SCALE", columnElem, "short"); addShortAttribute(result, columns, "RADIX", columnElem, "radix"); if (columns.contains("NULLABLE")) { switch (result.getInt("NULLABLE")) { case DatabaseMetaData.procedureNoNulls: columnElem.addAttribute("nullable", "false"); break; case DatabaseMetaData.procedureNullable: columnElem.addAttribute("nullable", "true"); break; default: columnElem.addAttribute("nullable", "unknown"); break; } } addStringAttribute(result, columns, "REMARKS", columnElem, "remarks"); } } finally { if (result != null) { result.close(); } } } /** * If the result set contains the indicated column, extracts its value and sets an attribute at the given element. * * @param result The result set * @param columns The columns in the result set * @param columnName The name of the column in the result set * @param element The element to add the attribute * @param attrName The name of the attribute to set * @return The string value or <code>null</code> */ private String addStringAttribute(ResultSet result, Set columns, String columnName, Element element, String attrName) throws SQLException { String value = null; if (columns.contains(columnName)) { value = getString(result, columnName); element.addAttribute(attrName, value); } return value; } /** * If the result set contains the indicated column, extracts its int value and sets an attribute at the given element. * * @param result The result set * @param columns The columns in the result set * @param columnName The name of the column in the result set * @param element The element to add the attribute * @param attrName The name of the attribute to set * @return The string value or <code>null</code> */ private String addIntAttribute(ResultSet result, Set columns, String columnName, Element element, String attrName) throws SQLException { String value = null; if (columns.contains(columnName)) { try { value = String.valueOf(result.getInt(columnName)); } catch (SQLException ex) { // A few databases do not comply with the jdbc spec and return a string (or null), // so lets try this just in case value = result.getString(columnName); if (value != null) { try { Integer.parseInt(value); } catch (NumberFormatException parseEx) { // its no int returned as a string, so lets re-throw the original exception throw ex; } } } element.addAttribute(attrName, value); } return value; } /** * If the result set contains the indicated column, extracts its short value and sets an attribute at the given element. * * @param result The result set * @param columns The columns in the result set * @param columnName The name of the column in the result set * @param element The element to add the attribute * @param attrName The name of the attribute to set * @return The string value or <code>null</code> */ private String addShortAttribute(ResultSet result, Set columns, String columnName, Element element, String attrName) throws SQLException { String value = null; if (columns.contains(columnName)) { try { value = String.valueOf(result.getShort(columnName)); } catch (SQLException ex) { // A few databases do not comply with the jdbc spec and return a string (or null), // so lets try strings this just in case value = result.getString(columnName); if (value != null) { try { Short.parseShort(value); } catch (NumberFormatException parseEx) { // its no short returned as a string, so lets re-throw the original exception throw ex; } } } element.addAttribute(attrName, value); } return value; } /** * If the result set contains the indicated column, extracts its boolean value and sets an attribute at the given element. * * @param result The result set * @param columns The columns in the result set * @param columnName The name of the column in the result set * @param element The element to add the attribute * @param attrName The name of the attribute to set * @return The string value or <code>null</code> */ private String addBooleanAttribute(ResultSet result, Set columns, String columnName, Element element, String attrName) throws SQLException { String value = null; if (columns.contains(columnName)) { value = String.valueOf(result.getBoolean(columnName)); element.addAttribute(attrName, value); } return value; } /** * Extracts a string from the result set. * * @param result The result set * @param columnName The name of the column in the result set * @return The string value */ private String getString(ResultSet result, String columnName) throws SQLException { return result.getString(columnName); } /** * Determines the columns that are present in the given result set. * * @param resultSet The result set * @return The columns */ private Set getColumnsInResultSet(ResultSet resultSet) throws SQLException { ListOrderedSet result = new ListOrderedSet(); ResultSetMetaData metaData = resultSet.getMetaData(); for (int idx = 1; idx <= metaData.getColumnCount(); idx++) { result.add(metaData.getColumnName(idx).toUpperCase()); } return result; } }