Java tutorial
package org.jumpmind.db.platform; /* * 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 static org.apache.commons.lang.StringUtils.isBlank; import static org.apache.commons.lang.StringUtils.isNotBlank; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.sql.Types; import java.text.Collator; import java.util.ArrayList; import java.util.Collection; import java.util.Collections; import java.util.Comparator; import java.util.HashMap; import java.util.HashSet; import java.util.Iterator; import java.util.LinkedHashMap; import java.util.List; import java.util.Map; import java.util.Set; import javax.sql.DataSource; import org.apache.commons.lang.StringUtils; import org.jumpmind.db.model.Column; import org.jumpmind.db.model.Database; import org.jumpmind.db.model.ForeignKey; import org.jumpmind.db.model.IIndex; import org.jumpmind.db.model.IndexColumn; import org.jumpmind.db.model.NonUniqueIndex; import org.jumpmind.db.model.PlatformColumn; import org.jumpmind.db.model.Reference; import org.jumpmind.db.model.Table; import org.jumpmind.db.model.TypeMap; import org.jumpmind.db.model.UniqueIndex; import org.jumpmind.db.sql.IConnectionCallback; import org.jumpmind.db.sql.JdbcSqlTemplate; import org.jumpmind.db.sql.SqlException; import org.slf4j.Logger; import org.slf4j.LoggerFactory; /* * An utility class to create a Database model from a live database. */ public abstract class AbstractJdbcDdlReader implements IDdlReader { /* The Log to which logging calls will be made. */ protected Logger log = LoggerFactory.getLogger(getClass()); /* The descriptors for the relevant columns in the table meta data. */ private final List<MetaDataColumnDescriptor> _columnsForTable; /* The descriptors for the relevant columns in the table column meta data. */ private final List<MetaDataColumnDescriptor> _columnsForColumn; /* The descriptors for the relevant columns in the primary key meta data. */ private final List<MetaDataColumnDescriptor> _columnsForPK; /* The descriptors for the relevant columns in the foreign key meta data. */ private final List<MetaDataColumnDescriptor> _columnsForFK; /* The descriptors for the relevant columns in the index meta data. */ private final List<MetaDataColumnDescriptor> _columnsForIndex; /* The platform that this model reader belongs to. */ protected IDatabasePlatform platform; /* * Contains default column sizes (minimum sizes that a JDBC-compliant db * must support). */ private HashMap<Integer, String> _defaultSizes = new HashMap<Integer, String>(); /* The default database catalog to read. */ private String _defaultCatalogPattern = "%"; /* The default database schema(s) to read. */ private String _defaultSchemaPattern = "%"; /* The default pattern for reading all tables. */ private String _defaultTablePattern = "%"; /* The default pattern for reading all columns. */ private String _defaultColumnPattern; /* The table types to recognize per default. */ private String[] _defaultTableTypes = { "TABLE" }; protected String wildcardEscapeString; public AbstractJdbcDdlReader(IDatabasePlatform platform) { this.platform = platform; _defaultSizes.put(new Integer(Types.CHAR), "254"); _defaultSizes.put(new Integer(Types.VARCHAR), "254"); _defaultSizes.put(new Integer(Types.LONGVARCHAR), "254"); _defaultSizes.put(new Integer(Types.BINARY), "254"); _defaultSizes.put(new Integer(Types.VARBINARY), "254"); _defaultSizes.put(new Integer(Types.LONGVARBINARY), "254"); _defaultSizes.put(new Integer(Types.INTEGER), "32"); _defaultSizes.put(new Integer(Types.BIGINT), "64"); _defaultSizes.put(new Integer(Types.REAL), "7,0"); _defaultSizes.put(new Integer(Types.FLOAT), "15,0"); _defaultSizes.put(new Integer(Types.DOUBLE), "15,0"); _defaultSizes.put(new Integer(Types.DECIMAL), "15,15"); _defaultSizes.put(new Integer(Types.NUMERIC), "15,15"); _columnsForTable = initColumnsForTable(); _columnsForColumn = initColumnsForColumn(); _columnsForPK = initColumnsForPK(); _columnsForFK = initColumnsForFK(); _columnsForIndex = initColumnsForIndex(); initWildcardString(platform); } protected void initWildcardString(IDatabasePlatform platform) { DataSource ds = platform.getDataSource(); Connection c = null; try { c = ds.getConnection(); wildcardEscapeString = c.getMetaData().getSearchStringEscape(); } catch (SQLException ex) { throw new SqlException(ex); } finally { JdbcSqlTemplate.close(c); } } /* * Returns the platform that this model reader belongs to. * * @return The platform */ public IDatabasePlatform getPlatform() { return platform; } /* * Returns the platform specific settings. * * @return The platform settings */ public DatabaseInfo getPlatformInfo() { return platform.getDatabaseInfo(); } /* * Returns descriptors for the columns that shall be read from the result * set when reading the meta data for a table. Note that the columns are * read in the order defined by this list.<br/> Redefine this method if you * want more columns or a different order. * * @return The descriptors for the result set columns */ protected List<MetaDataColumnDescriptor> initColumnsForTable() { List<MetaDataColumnDescriptor> result = new ArrayList<MetaDataColumnDescriptor>(); result.add(new MetaDataColumnDescriptor("TABLE_NAME", Types.VARCHAR)); result.add(new MetaDataColumnDescriptor("TABLE_TYPE", Types.VARCHAR, "UNKNOWN")); result.add(new MetaDataColumnDescriptor(getResultSetCatalogName(), Types.VARCHAR)); result.add(new MetaDataColumnDescriptor(getResultSetSchemaName(), Types.VARCHAR)); result.add(new MetaDataColumnDescriptor("REMARKS", Types.VARCHAR)); return result; } /* * Returns descriptors for the columns that shall be read from the result * set when reading the meta data for table columns. Note that the columns * are read in the order defined by this list.<br/> Redefine this method if * you want more columns or a different order. * * @return The map column name -> descriptor for the result set columns */ protected List<MetaDataColumnDescriptor> initColumnsForColumn() { List<MetaDataColumnDescriptor> result = new ArrayList<MetaDataColumnDescriptor>(); // As suggested by Alexandre Borgoltz, we're reading the COLUMN_DEF // first because Oracle // has problems otherwise (it seemingly requires a LONG column to be the // first to be read) // See also DDLUTILS-29 result.add(new MetaDataColumnDescriptor("COLUMN_DEF", Types.VARCHAR)); result.add(new MetaDataColumnDescriptor("COLUMN_DEFAULT", Types.VARCHAR)); // we're also reading the table name so that a model reader impl can // filter manually result.add(new MetaDataColumnDescriptor("TABLE_NAME", Types.VARCHAR)); result.add(new MetaDataColumnDescriptor("COLUMN_NAME", Types.VARCHAR)); result.add(new MetaDataColumnDescriptor("TYPE_NAME", Types.VARCHAR)); result.add(new MetaDataColumnDescriptor("DATA_TYPE", Types.INTEGER, new Integer(java.sql.Types.OTHER))); result.add(new MetaDataColumnDescriptor("NUM_PREC_RADIX", Types.INTEGER, new Integer(10))); result.add(new MetaDataColumnDescriptor("DECIMAL_DIGITS", Types.INTEGER, new Integer(0))); result.add(new MetaDataColumnDescriptor("COLUMN_SIZE", Types.VARCHAR)); result.add(new MetaDataColumnDescriptor("IS_NULLABLE", Types.VARCHAR, "YES")); result.add(new MetaDataColumnDescriptor("IS_AUTOINCREMENT", Types.VARCHAR, "YES")); result.add(new MetaDataColumnDescriptor("REMARKS", Types.VARCHAR)); return result; } /* * Returns descriptors for the columns that shall be read from the result * set when reading the meta data for primary keys. Note that the columns * are read in the order defined by this list.<br/> Redefine this method if * you want more columns or a different order. * * @return The map column name -> descriptor for the result set columns */ protected List<MetaDataColumnDescriptor> initColumnsForPK() { List<MetaDataColumnDescriptor> result = new ArrayList<MetaDataColumnDescriptor>(); result.add(new MetaDataColumnDescriptor("COLUMN_NAME", Types.VARCHAR)); // we're also reading the table name so that a model reader impl can // filter manually result.add(new MetaDataColumnDescriptor("TABLE_NAME", Types.VARCHAR)); // the name of the primary key is currently only interesting to the pk // index name resolution result.add(new MetaDataColumnDescriptor("PK_NAME", Types.VARCHAR)); return result; } /* * Returns descriptors for the columns that shall be read from the result * set when reading the meta data for foreign keys originating from a table. * Note that the columns are read in the order defined by this list.<br/> * Redefine this method if you want more columns or a different order. * * @return The map column name -> descriptor for the result set columns */ protected List<MetaDataColumnDescriptor> initColumnsForFK() { List<MetaDataColumnDescriptor> result = new ArrayList<MetaDataColumnDescriptor>(); result.add(new MetaDataColumnDescriptor("PKTABLE_NAME", Types.VARCHAR)); // we're also reading the table name so that a model reader impl can // filter manually result.add(new MetaDataColumnDescriptor("FKTABLE_NAME", Types.VARCHAR)); result.add(new MetaDataColumnDescriptor("KEY_SEQ", Types.TINYINT, new Short((short) 0))); result.add(new MetaDataColumnDescriptor("FK_NAME", Types.VARCHAR)); result.add(new MetaDataColumnDescriptor("FKTABLE_NAME", Types.VARCHAR)); result.add(new MetaDataColumnDescriptor("PKCOLUMN_NAME", Types.VARCHAR)); result.add(new MetaDataColumnDescriptor("FKCOLUMN_NAME", Types.VARCHAR)); return result; } /* * Returns descriptors for the columns that shall be read from the result * set when reading the meta data for indices. Note that the columns are * read in the order defined by this list.<br/> Redefine this method if you * want more columns or a different order. * * @return The map column name -> descriptor for the result set columns */ protected List<MetaDataColumnDescriptor> initColumnsForIndex() { List<MetaDataColumnDescriptor> result = new ArrayList<MetaDataColumnDescriptor>(); result.add(new MetaDataColumnDescriptor("INDEX_NAME", Types.VARCHAR)); // we're also reading the table name so that a model reader impl can // filter manually result.add(new MetaDataColumnDescriptor("TABLE_NAME", Types.VARCHAR)); result.add(new MetaDataColumnDescriptor("NON_UNIQUE", Types.BIT, Boolean.TRUE)); result.add(new MetaDataColumnDescriptor("ORDINAL_POSITION", Types.TINYINT, new Short((short) 0))); result.add(new MetaDataColumnDescriptor("COLUMN_NAME", Types.VARCHAR)); result.add(new MetaDataColumnDescriptor("TYPE", Types.TINYINT)); return result; } /* * Returns the catalog(s) in the database to read per default. * * @return The default catalog(s) */ public String getDefaultCatalogPattern() { return _defaultCatalogPattern; } /* * Sets the catalog(s) in the database to read per default. * * @param catalogPattern The catalog(s) */ public void setDefaultCatalogPattern(String catalogPattern) { _defaultCatalogPattern = catalogPattern; } /* * Returns the schema(s) in the database to read per default. * * @return The default schema(s) */ public String getDefaultSchemaPattern() { return _defaultSchemaPattern; } /* * Sets the schema(s) in the database to read per default. * * @param schemaPattern The schema(s) */ public void setDefaultSchemaPattern(String schemaPattern) { _defaultSchemaPattern = schemaPattern; } /* * Returns the default pattern to read the relevant tables from the * database. * * @return The table pattern */ public String getDefaultTablePattern() { return _defaultTablePattern; } /* * Sets the default pattern to read the relevant tables from the database. * * @param tablePattern The table pattern */ public void setDefaultTablePattern(String tablePattern) { _defaultTablePattern = tablePattern; } /* * Returns the default pattern to read the relevant columns from the * database. * * @return The column pattern */ public String getDefaultColumnPattern() { return _defaultColumnPattern; } /* * Sets the default pattern to read the relevant columns from the database. * * @param columnPattern The column pattern */ public void setDefaultColumnPattern(String columnPattern) { _defaultColumnPattern = columnPattern; } /* * Returns the table types to recognize per default. * * @return The default table types */ public String[] getDefaultTableTypes() { return _defaultTableTypes; } /* * Sets the table types to recognize per default. Typical types are "TABLE", * "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", * "SYNONYM". * * @param types The table types */ public void setDefaultTableTypes(String[] types) { _defaultTableTypes = types; } /* * Returns the descriptors for the columns to be read from the table meta * data result set. * * @return The column descriptors */ protected List<MetaDataColumnDescriptor> getColumnsForTable() { return _columnsForTable; } /* * Returns the descriptors for the columns to be read from the column meta * data result set. * * @return The column descriptors */ protected List<MetaDataColumnDescriptor> getColumnsForColumn() { return _columnsForColumn; } /* * Returns the descriptors for the columns to be read from the primary key * meta data result set. * * @return The column descriptors */ protected List<MetaDataColumnDescriptor> getColumnsForPK() { return _columnsForPK; } /* * Returns the descriptors for the columns to be read from the foreign key * meta data result set. * * @return The column descriptors */ protected List<MetaDataColumnDescriptor> getColumnsForFK() { return _columnsForFK; } /* * Returns the descriptors for the columns to be read from the index meta * data result set. * * @return The column descriptors */ protected List<MetaDataColumnDescriptor> getColumnsForIndex() { return _columnsForIndex; } /* * Reads the database model from the given connection. * * @param connection The connection * * @param name The name of the resulting database; <code>null</code> when * the default name (the catalog) is desired which might be * <code>null</code> itself though * * @return The database model */ public Database getDatabase(Connection connection) throws SQLException { return readTables(null, null, null); } protected String getResultSetSchemaName() { return "TABLE_SCHEM"; } protected String getResultSetCatalogName() { return "TABLE_CAT"; } /* * Reads the database model from the given connection. * * @param catalog The catalog to access in the database; use * <code>null</code> for the default value * * @param schema The schema to access in the database; use <code>null</code> * for the default value * * @param tableTypes The table types to process; use <code>null</code> or an * empty list for the default ones * * @return The database model */ public Database readTables(final String catalog, final String schema, final String[] tableTypes) { JdbcSqlTemplate sqlTemplate = (JdbcSqlTemplate) platform.getSqlTemplate(); return postprocessModelFromDatabase(sqlTemplate.execute(new IConnectionCallback<Database>() { public Database execute(Connection connection) throws SQLException { Database db = new Database(); db.setName(Table.getFullyQualifiedTablePrefix(catalog, schema)); db.setCatalog(catalog); db.setSchema(schema); db.addTables(readTables(connection, catalog, schema, tableTypes)); db.initialize(); return db; } })); } /* * Allows the platform to postprocess the model just read from the database. * * @param model The model */ protected Database postprocessModelFromDatabase(Database model) { // Default values for CHAR/VARCHAR/LONGVARCHAR columns have quotation // marks around them which we'll remove now for (int tableIdx = 0; tableIdx < model.getTableCount(); tableIdx++) { postprocessTableFromDatabase(model.getTable(tableIdx)); } return model; } /* * Reads the tables from the database metadata. * * @param catalog The catalog to acess in the database; use * <code>null</code> for the default value * * @param schemaPattern The schema(s) to acess in the database; use * <code>null</code> for the default value * * @param tableTypes The table types to process; use <code>null</code> or an * empty list for the default ones * * @return The tables */ protected Collection<Table> readTables(Connection connection, String catalog, String schemaPattern, String[] tableTypes) throws SQLException { ResultSet tableData = null; try { DatabaseMetaDataWrapper metaData = new DatabaseMetaDataWrapper(); metaData.setMetaData(connection.getMetaData()); metaData.setCatalog(catalog == null ? getDefaultCatalogPattern() : catalog); metaData.setSchemaPattern(schemaPattern == null ? getDefaultSchemaPattern() : schemaPattern); metaData.setTableTypes( (tableTypes == null) || (tableTypes.length == 0) ? getDefaultTableTypes() : tableTypes); tableData = metaData.getTables(getDefaultTablePattern()); List<Table> tables = new ArrayList<Table>(); while (tableData.next()) { Map<String, Object> values = readMetaData(tableData, getColumnsForTable()); Table table = readTable(connection, metaData, values); if (table != null) { tables.add(table); } } final Collator collator = Collator.getInstance(); Collections.sort(tables, new Comparator<Table>() { public int compare(Table obj1, Table obj2) { return collator.compare(obj1.getName().toUpperCase(), obj2.getName().toUpperCase()); } }); return tables; } finally { if (tableData != null) { tableData.close(); } } } public Table readTable(final String catalog, final String schema, final String table) { try { log.debug("reading table: " + table); JdbcSqlTemplate sqlTemplate = (JdbcSqlTemplate) platform.getSqlTemplate(); return postprocessTableFromDatabase(sqlTemplate.execute(new IConnectionCallback<Table>() { public Table execute(Connection connection) throws SQLException { DatabaseMetaDataWrapper metaData = new DatabaseMetaDataWrapper(); metaData.setMetaData(connection.getMetaData()); metaData.setCatalog(catalog); metaData.setSchemaPattern(schema); metaData.setTableTypes(null); ResultSet tableData = null; try { log.debug("getting table metadata for " + table); tableData = metaData.getTables(getTableNamePattern(table)); log.debug("done getting table metadata for " + table); if (tableData != null && tableData.next()) { Map<String, Object> values = readMetaData(tableData, initColumnsForTable()); return readTable(connection, metaData, values); } else { return null; } } finally { close(tableData); } } })); } catch (SqlException e) { if (e.getMessage() != null && StringUtils.containsIgnoreCase(e.getMessage(), "does not exist")) { return null; } else { throw e; } } } protected Table postprocessTableFromDatabase(Table table) { if (table != null) { for (int columnIdx = 0; columnIdx < table.getColumnCount(); columnIdx++) { Column column = table.getColumn(columnIdx); if (TypeMap.isTextType(column.getMappedTypeCode()) || TypeMap.isDateTimeType(column.getMappedTypeCode())) { String defaultValue = column.getDefaultValue(); if ((defaultValue != null) && (defaultValue.length() >= 2) && defaultValue.startsWith("'") && defaultValue.endsWith("'")) { defaultValue = defaultValue.substring(1, defaultValue.length() - 1); column.setDefaultValue(defaultValue); } } } } return table; } protected void close(ResultSet rs) { if (rs != null) { try { rs.close(); } catch (SQLException ex) { } } } protected void close(Statement stmt) { if (stmt != null) { try { stmt.close(); } catch (SQLException ex) { } } } protected String getTableNamePattern(String tableName) { if (isNotBlank(wildcardEscapeString)) { tableName = tableName.replace("_", wildcardEscapeString + "_"); tableName = tableName.replace("%", wildcardEscapeString + "%"); } return tableName; } /* * Reads the next table from the meta data. * * @param metaData The database meta data * * @param values The table metadata values as defined by {@link * #getColumnsForTable()} * * @return The table or <code>null</code> if the result set row did not * contain a valid table */ protected Table readTable(Connection connection, DatabaseMetaDataWrapper metaData, Map<String, Object> values) throws SQLException { String tableName = (String) values.get("TABLE_NAME"); try { Table table = null; if ((tableName != null) && (tableName.length() > 0)) { String type = (String) values.get("TABLE_TYPE"); String[] unsupportedTableTypes = getUnsupportedTableTypes(); for (String unsupportedTableType : unsupportedTableTypes) { if (StringUtils.isNotBlank(type) && type.equals(unsupportedTableType)) { return null; } } table = new Table(); table.setName(tableName); table.setType(type); String catalog = (String) values.get(getResultSetCatalogName()); table.setCatalog(catalog); metaData.setCatalog(catalog); String schema = (String) values.get(getResultSetSchemaName()); table.setSchema(schema); metaData.setSchemaPattern(schema); table.setDescription((String) values.get("REMARKS")); table.addColumns(readColumns(metaData, tableName)); if (table.getColumnCount() > 0) { table.addForeignKeys(readForeignKeys(connection, metaData, tableName)); table.addIndices(readIndices(connection, metaData, tableName)); Collection<String> primaryKeys = readPrimaryKeyNames(metaData, tableName); for (Iterator<String> it = primaryKeys.iterator(); it.hasNext();) { table.findColumn(it.next(), true).setPrimaryKey(true); } if (getPlatformInfo().isSystemIndicesReturned()) { removeSystemIndices(connection, metaData, table); } } else { table = null; } } return table; } catch (RuntimeException ex) { log.error("Failed to read table: {}", tableName); throw ex; } catch (SQLException ex) { log.error("Failed to read table: {}", tableName); throw ex; } } protected String[] getUnsupportedTableTypes() { return new String[0]; } /* * Removes system indices (generated by the database for primary and foreign * keys) from the table. * * @param metaData The database meta data * * @param table The table */ protected void removeSystemIndices(Connection connection, DatabaseMetaDataWrapper metaData, Table table) throws SQLException { removeInternalPrimaryKeyIndex(connection, metaData, table); for (int fkIdx = 0; fkIdx < table.getForeignKeyCount(); fkIdx++) { removeInternalForeignKeyIndex(connection, metaData, table, table.getForeignKey(fkIdx)); } } /* * Tries to remove the internal index for the table's primary key. * * @param metaData The database meta data * * @param table The table */ protected void removeInternalPrimaryKeyIndex(Connection connection, DatabaseMetaDataWrapper metaData, Table table) throws SQLException { Column[] pks = table.getPrimaryKeyColumns(); List<String> columnNames = new ArrayList<String>(); for (int columnIdx = 0; columnIdx < pks.length; columnIdx++) { columnNames.add(pks[columnIdx].getName()); } for (int indexIdx = 0; indexIdx < table.getIndexCount();) { IIndex index = table.getIndex(indexIdx); if (index.isUnique() && matches(index, columnNames) && isInternalPrimaryKeyIndex(connection, metaData, table, index)) { table.removeIndex(indexIdx); } else { indexIdx++; } } } /* * Tries to remove the internal index for the given foreign key. * * @param metaData The database meta data * * @param table The table where the table is defined * * @param fk The foreign key */ protected void removeInternalForeignKeyIndex(Connection connection, DatabaseMetaDataWrapper metaData, Table table, ForeignKey fk) throws SQLException { List<String> columnNames = new ArrayList<String>(); for (int columnIdx = 0; columnIdx < fk.getReferenceCount(); columnIdx++) { columnNames.add(fk.getReference(columnIdx).getLocalColumnName()); } for (int indexIdx = 0; indexIdx < table.getIndexCount();) { IIndex index = table.getIndex(indexIdx); if (matches(index, columnNames) && isInternalForeignKeyIndex(connection, metaData, table, fk, index)) { fk.setAutoIndexPresent(true); table.removeIndex(indexIdx); } else { indexIdx++; } } } /* * Checks whether the given index matches the column list. * * @param index The index * * @param columnsToSearchFor The names of the columns that the index should * be for * * @return <code>true</code> if the index matches the columns */ protected boolean matches(IIndex index, List<String> columnsToSearchFor) { for (String column : columnsToSearchFor) { boolean found = false; for (int i = 0; i < index.getColumnCount(); i++) { if (column != null && column.equals(index.getColumn(i).getName())) { found = true; } } if (!found) { return false; } } return true; } /* * Tries to determine whether the index is the internal database-generated * index for the given table's primary key. Note that only unique indices * with the correct columns are fed to this method. Redefine this method for * specific platforms if there are better ways to determine internal * indices. * * @param metaData The database meta data * * @param table The table owning the index * * @param index The index to check * * @return <code>true</code> if the index seems to be an internal primary * key one */ protected boolean isInternalPrimaryKeyIndex(Connection connection, DatabaseMetaDataWrapper metaData, Table table, IIndex index) throws SQLException { return false; } /* * Tries to determine whether the index is the internal database-generated * index for the given foreign key. Note that only non-unique indices with * the correct columns are fed to this method. Redefine this method for * specific platforms if there are better ways to determine internal * indices. * * @param metaData The database meta data * * @param table The table owning the index and foreign key * * @param fk The foreign key * * @param index The index to check * * @return <code>true</code> if the index seems to be an internal primary * key one */ protected boolean isInternalForeignKeyIndex(Connection connection, DatabaseMetaDataWrapper metaData, Table table, ForeignKey fk, IIndex index) throws SQLException { return false; } /* * Reads the column definitions for the indicated table. * * @param metaData The database meta data * * @param tableName The name of the table * * @return The columns */ protected Collection<Column> readColumns(DatabaseMetaDataWrapper metaData, String tableName) throws SQLException { ResultSet columnData = null; try { Set<String> columnNames = new HashSet<String>(); columnData = metaData.getColumns(getTableNamePattern(tableName), getDefaultColumnPattern()); List<Column> columns = new ArrayList<Column>(); while (columnData.next()) { Map<String, Object> values = readMetaData(columnData, getColumnsForColumn()); Column column = readColumn(metaData, values); if (!columnNames.contains(column.getName())) { columnNames.add(column.getName()); columns.add(column); } genericizeDefaultValuesAndUpdatePlatformColumn(column); } return columns; } finally { close(columnData); } } protected void genericizeDefaultValuesAndUpdatePlatformColumn(Column column) { PlatformColumn platformColumn = column.findPlatformColumn(platform.getName()); platformColumn.setDefaultValue(column.getDefaultValue()); /* * Translate from platform specific functions to ansi sql functions */ if ("getdate()".equalsIgnoreCase(column.getDefaultValue())) { column.setDefaultValue("CURRENT_TIMESTAMP"); } } protected Integer mapUnknownJdbcTypeForColumn(Map<String, Object> values) { return null; } /* * Extracts a column definition from the result set. * * @param metaData The database meta data * * @param values The column meta data values as defined by {@link * #getColumnsForColumn()} * * @return The column */ protected Column readColumn(DatabaseMetaDataWrapper metaData, Map<String, Object> values) throws SQLException { Column column = new Column(); PlatformColumn platformColumn = new PlatformColumn(); platformColumn.setName(platform.getName()); column.setName((String) values.get("COLUMN_NAME")); String defaultValue = (String) values.get("COLUMN_DEF"); if (defaultValue == null) { defaultValue = (String) values.get("COLUMN_DEFAULT"); } if (defaultValue != null) { defaultValue = defaultValue.trim(); column.setDefaultValue(defaultValue); } String typeName = (String) values.get("TYPE_NAME"); column.setJdbcTypeName(typeName); Integer mappedType = mapUnknownJdbcTypeForColumn(values); if (mappedType != null) { column.setMappedTypeCode(mappedType); } else { column.setMappedTypeCode((Integer) values.get("DATA_TYPE")); } column.setJdbcTypeCode((Integer) values.get("DATA_TYPE")); column.setPrecisionRadix(((Integer) values.get("NUM_PREC_RADIX")).intValue()); String columnSize = (String) values.get("COLUMN_SIZE"); int decimalDigits = ((Integer) values.get("DECIMAL_DIGITS")).intValue(); try { platformColumn.setType(typeName); if (isNotBlank(columnSize)) { platformColumn.setSize(Integer.parseInt(columnSize)); } platformColumn.setDecimalDigits(decimalDigits); column.addPlatformColumn(platformColumn); } catch (Exception ex) { log.warn("", ex); } if (columnSize == null) { columnSize = (String) _defaultSizes.get(new Integer(column.getMappedTypeCode())); } // we're setting the size after the precision and radix in case // the database prefers to return them in the size value column.setSize(columnSize); if (decimalDigits != 0) { // if there is a scale value, set it after the size (which probably // did not contain // a scale specification) column.setScale(decimalDigits); } column.setRequired("NO".equalsIgnoreCase(((String) values.get("IS_NULLABLE")).trim())); column.setDescription((String) values.get("REMARKS")); return column; } /* * Retrieves the names of the columns that make up the primary key for a * given table. * * @param metaData The database meta data * * @param tableName The name of the table from which to retrieve PK * information * * @return The primary key column names */ protected Collection<String> readPrimaryKeyNames(DatabaseMetaDataWrapper metaData, String tableName) throws SQLException { List<String> pks = new ArrayList<String>(); ResultSet pkData = null; try { pkData = metaData.getPrimaryKeys(tableName); while (pkData.next()) { Map<String, Object> values = readMetaData(pkData, getColumnsForPK()); pks.add(readPrimaryKeyName(metaData, values)); } } finally { close(pkData); } return pks; } /* * Extracts a primary key name from the result set. * * @param metaData The database meta data * * @param values The primary key meta data values as defined by {@link * #getColumnsForPK()} * * @return The primary key name */ protected String readPrimaryKeyName(DatabaseMetaDataWrapper metaData, Map<String, Object> values) throws SQLException { return (String) values.get("COLUMN_NAME"); } /* * Retrieves the foreign keys of the indicated table. * * @param metaData The database meta data * * @param tableName The name of the table from which to retrieve FK * information * * @return The foreign keys */ protected Collection<ForeignKey> readForeignKeys(Connection connection, DatabaseMetaDataWrapper metaData, String tableName) throws SQLException { Map<String, ForeignKey> fks = new LinkedHashMap<String, ForeignKey>(); if (getPlatformInfo().isForeignKeysSupported()) { ResultSet fkData = null; try { fkData = metaData.getForeignKeys(tableName); while (fkData.next()) { Map<String, Object> values = readMetaData(fkData, getColumnsForFK()); String fkTableName = (String) values.get("FKTABLE_NAME"); if (isBlank(fkTableName) || fkTableName.equalsIgnoreCase(tableName)) { readForeignKey(metaData, values, fks); } } } finally { close(fkData); } } return fks.values(); } /* * Reads the next foreign key spec from the result set. * * @param metaData The database meta data * * @param values The foreign key meta data as defined by {@link * #getColumnsForFK()} * * @param knownFks The already read foreign keys for the current table */ protected void readForeignKey(DatabaseMetaDataWrapper metaData, Map<String, Object> values, Map<String, ForeignKey> knownFks) throws SQLException { String fkName = (String) values.get("FK_NAME"); ForeignKey fk = (ForeignKey) knownFks.get(fkName); if (fk == null) { fk = new ForeignKey(fkName); fk.setForeignTableName((String) values.get("PKTABLE_NAME")); knownFks.put(fkName, fk); } Reference ref = new Reference(); ref.setForeignColumnName((String) values.get("PKCOLUMN_NAME")); ref.setLocalColumnName((String) values.get("FKCOLUMN_NAME")); if (values.containsKey("KEY_SEQ")) { ref.setSequenceValue(((Short) values.get("KEY_SEQ")).intValue()); } fk.addReference(ref); } /* * Determines the indices for the indicated table. * * @param metaData The database meta data * * @param tableName The name of the table * * @return The list of indices */ protected Collection<IIndex> readIndices(Connection connection, DatabaseMetaDataWrapper metaData, String tableName) throws SQLException { Map<String, IIndex> indices = new LinkedHashMap<String, IIndex>(); if (getPlatformInfo().isIndicesSupported()) { ResultSet indexData = null; try { indexData = metaData.getIndices(tableName, false, false); while (indexData.next()) { Map<String, Object> values = readMetaData(indexData, getColumnsForIndex()); readIndex(metaData, values, indices); } } finally { close(indexData); } } return indices.values(); } /* * Reads the next index spec from the result set. * * @param metaData The database meta data * * @param values The index meta data as defined by {@link * #getColumnsForIndex()} * * @param knownIndices The already read indices for the current table */ protected void readIndex(DatabaseMetaDataWrapper metaData, Map<String, Object> values, Map<String, IIndex> knownIndices) throws SQLException { Short indexType = (Short) values.get("TYPE"); // we're ignoring statistic indices if ((indexType != null) && (indexType.shortValue() == DatabaseMetaData.tableIndexStatistic)) { return; } String indexName = (String) values.get("INDEX_NAME"); if (indexName != null) { IIndex index = (IIndex) knownIndices.get(indexName); if (index == null) { if (((Boolean) values.get("NON_UNIQUE")).booleanValue()) { index = new NonUniqueIndex(); } else { index = new UniqueIndex(); } index.setName(indexName); knownIndices.put(indexName, index); } IndexColumn indexColumn = new IndexColumn(); String columnName = (String) values.get("COLUMN_NAME"); if (columnName.startsWith("\"") && columnName.endsWith("\"")) { columnName = columnName.substring(1, columnName.length() - 1); } indexColumn.setName(columnName); if (values.containsKey("ORDINAL_POSITION")) { indexColumn.setOrdinalPosition(((Short) values.get("ORDINAL_POSITION")).intValue()); } index.addColumn(indexColumn); } } /* * Reads the indicated columns from the result set. * * @param resultSet The result set * * @param columnDescriptors The descriptors of the columns to read * * @return The read values keyed by the column name */ protected Map<String, Object> readMetaData(ResultSet resultSet, List<MetaDataColumnDescriptor> columnDescriptors) throws SQLException { HashMap<String, Object> values = new HashMap<String, Object>(); ResultSetMetaData meta = resultSet.getMetaData(); int columnCount = meta.getColumnCount(); Set<String> processed = new HashSet<String>(columnCount); for (int i = 1; i <= columnCount; i++) { boolean foundMetaDataDescriptor = false; String columnName = meta.getColumnName(i); for (MetaDataColumnDescriptor metaDataColumnDescriptor : columnDescriptors) { if (metaDataColumnDescriptor.getName().equals(columnName)) { foundMetaDataDescriptor = true; values.put(metaDataColumnDescriptor.getName(), metaDataColumnDescriptor.readColumn(resultSet)); processed.add(columnName); break; } } /* * Put all metadata values into the map for easy debugging * of drivers that return nonstandard names */ if (!foundMetaDataDescriptor) { values.put(columnName, resultSet.getObject(i)); } } for (MetaDataColumnDescriptor metaDataColumnDescriptor : columnDescriptors) { if (!processed.contains(metaDataColumnDescriptor.getName())) { values.put(metaDataColumnDescriptor.getName(), metaDataColumnDescriptor.readColumn(resultSet)); } } return values; } protected void determineAutoIncrementFromResultSetMetaData(Connection conn, Table table, final Column columnsToCheck[]) throws SQLException { determineAutoIncrementFromResultSetMetaData(conn, table, columnsToCheck, "."); } /* * Helper method that determines the auto increment status for the given * columns via the {@link ResultSetMetaData#isAutoIncrement(int)} method. * * Fix problems following problems: 1) identifiers that use keywords 2) * different catalog and schema 3) different catalog separator character * * * @param table The table * * @param columnsToCheck The columns to check (e.g. the primary key columns) */ protected void determineAutoIncrementFromResultSetMetaData(Connection conn, Table table, final Column columnsToCheck[], String catalogSeparator) throws SQLException { StringBuilder query = new StringBuilder(); try { if (columnsToCheck == null || columnsToCheck.length == 0) { return; } query.append("SELECT "); for (int idx = 0; idx < columnsToCheck.length; idx++) { if (idx > 0) { query.append(","); } query.append("t."); appendIdentifier(query, columnsToCheck[idx].getName()); } query.append(" FROM "); if (table.getCatalog() != null && !table.getCatalog().trim().equals("")) { appendIdentifier(query, table.getCatalog()); query.append(catalogSeparator); } if (table.getSchema() != null && !table.getSchema().trim().equals("")) { appendIdentifier(query, table.getSchema()).append("."); } appendIdentifier(query, table.getName()).append(" t WHERE 1 = 0"); Statement stmt = null; try { stmt = conn.createStatement(); if (log.isDebugEnabled()) { log.debug( "Running the following query to get metadata about whether a column is an auto increment column: \n{}", query); } ResultSet rs = null; try { rs = stmt.executeQuery(query.toString()); ResultSetMetaData rsMetaData = rs.getMetaData(); for (int idx = 0; idx < columnsToCheck.length; idx++) { if (log.isDebugEnabled()) { log.debug(columnsToCheck[idx] + " is auto increment? " + rsMetaData.isAutoIncrement(idx + 1)); } if (rsMetaData.isAutoIncrement(idx + 1)) { columnsToCheck[idx].setAutoIncrement(true); } } } finally { close(rs); } } finally { close(stmt); } } catch (SQLException ex) { StringBuilder msg = new StringBuilder("Failed to determine auto increment columns using this query: '" + query + "'. This is probably not harmful, but should be fixed. "); msg.append("\n"); msg.append(table.toString()); if (columnsToCheck != null) { for (Column col : columnsToCheck) { msg.append("\n"); msg.append(col.toString()); } } log.warn(msg.toString(), ex); } } public StringBuilder appendIdentifier(StringBuilder query, String identifier) { if (getPlatform().getDdlBuilder().isDelimitedIdentifierModeOn()) { query.append(getPlatformInfo().getDelimiterToken()); } query.append(identifier); if (getPlatform().getDdlBuilder().isDelimitedIdentifierModeOn()) { query.append(getPlatformInfo().getDelimiterToken()); } return query; } /* * Replaces a specific character sequence in the given text with the * character sequence whose escaped version it is. * * @param text The text * * @param unescaped The unescaped string, e.g. "'" * * @param escaped The escaped version, e.g. "''" * * @return The resulting text */ protected String unescape(String text, String unescaped, String escaped) { // we need special handling if the single quote is escaped via a double // single quote if (text != null && !"''".equals(text)) { if (escaped.equals("''")) { if ((text.length() > 2) && text.startsWith("'") && text.endsWith("'")) { text = "'" + StringUtils.replace(text.substring(1, text.length() - 1), escaped, unescaped) + "'"; } else { text = StringUtils.replace(text, escaped, unescaped); } } else { text = StringUtils.replace(text, escaped, unescaped); } } return text; } public List<String> getTableTypes() { JdbcSqlTemplate sqlTemplate = (JdbcSqlTemplate) platform.getSqlTemplate(); return sqlTemplate.execute(new IConnectionCallback<List<String>>() { public List<String> execute(Connection connection) throws SQLException { ArrayList<String> types = new ArrayList<String>(); DatabaseMetaData meta = connection.getMetaData(); ResultSet rs = null; try { rs = meta.getTableTypes(); while (rs.next()) { types.add(rs.getString(1)); } return types; } finally { JdbcSqlTemplate.close(rs); } } }); } public List<String> getCatalogNames() { JdbcSqlTemplate sqlTemplate = (JdbcSqlTemplate) platform.getSqlTemplate(); return sqlTemplate.execute(new IConnectionCallback<List<String>>() { public List<String> execute(Connection connection) throws SQLException { ArrayList<String> catalogs = new ArrayList<String>(); DatabaseMetaData meta = connection.getMetaData(); ResultSet rs = null; try { rs = meta.getCatalogs(); while (rs.next()) { catalogs.add(rs.getString(1)); } return catalogs; } finally { JdbcSqlTemplate.close(rs); } } }); } public List<String> getSchemaNames(final String catalog) { JdbcSqlTemplate sqlTemplate = (JdbcSqlTemplate) platform.getSqlTemplate(); return sqlTemplate.execute(new IConnectionCallback<List<String>>() { public List<String> execute(Connection connection) throws SQLException { ArrayList<String> schemas = new ArrayList<String>(); DatabaseMetaData meta = connection.getMetaData(); ResultSet rs = null; try { rs = meta.getSchemas(); while (rs.next()) { int columnCount = rs.getMetaData().getColumnCount(); String schema = rs.getString(1); String schemaCatalog = null; if (columnCount > 1) { schemaCatalog = rs.getString(2); } if ((StringUtils.isBlank(schemaCatalog) || StringUtils.isBlank(catalog)) && !schemas.contains(schema)) { schemas.add(schema); } else if (StringUtils.isNotBlank(schemaCatalog) && schemaCatalog.equals(catalog)) { schemas.add(schema); } } return schemas; } finally { close(rs); } } }); } public List<String> getTableNames(final String catalog, final String schema, final String[] tableTypes) { JdbcSqlTemplate sqlTemplate = (JdbcSqlTemplate) platform.getSqlTemplate(); return sqlTemplate.execute(new IConnectionCallback<List<String>>() { public List<String> execute(Connection connection) throws SQLException { ArrayList<String> list = new ArrayList<String>(); DatabaseMetaData meta = connection.getMetaData(); ResultSet rs = null; try { rs = meta.getTables(catalog, schema, null, tableTypes); while (rs.next()) { String tableName = rs.getString("TABLE_NAME"); list.add(tableName); } return list; } finally { close(rs); } } }); } public List<String> getColumnNames(final String catalog, final String schema, final String tableName) { JdbcSqlTemplate sqlTemplate = (JdbcSqlTemplate) platform.getSqlTemplate(); return sqlTemplate.execute(new IConnectionCallback<List<String>>() { public List<String> execute(Connection connection) throws SQLException { ArrayList<String> list = new ArrayList<String>(); DatabaseMetaData meta = connection.getMetaData(); ResultSet rs = null; try { rs = meta.getColumns(catalog, schema, tableName, null); while (rs.next()) { String tableName = rs.getString("COLUMN_NAME"); list.add(tableName); } return list; } finally { close(rs); } } }); } }