Java tutorial
/* * Copyright (C) 2010-2101 Alibaba Group Holding Limited. * * Licensed 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. */ package com.alibaba.otter.shared.common.utils.meta; import java.sql.Connection; import java.sql.DatabaseMetaData; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Types; import java.util.ArrayList; import java.util.Collection; import java.util.HashMap; import java.util.Iterator; import java.util.List; import java.util.Map; import org.apache.commons.lang.StringUtils; import org.apache.commons.lang.builder.ToStringBuilder; import org.apache.commons.lang.builder.ToStringStyle; import org.apache.commons.lang.math.NumberUtils; import org.apache.ddlutils.model.Column; import org.apache.ddlutils.model.Table; import org.apache.ddlutils.platform.DatabaseMetaDataWrapper; import org.apache.ddlutils.platform.MetaDataColumnDescriptor; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.ConnectionCallback; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.SingleColumnRowMapper; import org.springframework.jdbc.support.JdbcUtils; import org.springframework.util.Assert; /** * copy from otter3.0 * * @author xiaoqing.zhouxq 2012-3-30 ?10:43:04 * @author zebin.xuzb add filter for data */ public class DdlUtils { private static final Logger logger = LoggerFactory.getLogger(DdlUtils.class); private static TableType[] SUPPORTED_TABLE_TYPES = new TableType[] { TableType.view, TableType.table }; private final static Map<Integer, String> _defaultSizes = new HashMap<Integer, String>(); static { _defaultSizes.put(new Integer(1), "254"); _defaultSizes.put(new Integer(12), "254"); _defaultSizes.put(new Integer(-1), "254"); _defaultSizes.put(new Integer(-2), "254"); _defaultSizes.put(new Integer(-3), "254"); _defaultSizes.put(new Integer(-4), "254"); _defaultSizes.put(new Integer(4), "32"); _defaultSizes.put(new Integer(-5), "64"); _defaultSizes.put(new Integer(7), "7,0"); _defaultSizes.put(new Integer(6), "15,0"); _defaultSizes.put(new Integer(8), "15,0"); _defaultSizes.put(new Integer(3), "15,15"); _defaultSizes.put(new Integer(2), "15,15"); } /** * !!! Only supports MySQL */ @SuppressWarnings("unchecked") public static List<String> findSchemas(JdbcTemplate jdbcTemplate, final String schemaPattern) { try { if (StringUtils.isEmpty(schemaPattern)) { return jdbcTemplate.query("show databases", new SingleColumnRowMapper(String.class)); } return jdbcTemplate.query("show databases like ?", new Object[] { schemaPattern }, new SingleColumnRowMapper(String.class)); } catch (Exception e) { logger.error(e.getMessage(), e); return new ArrayList<String>(); } } /** * !!! Only supports MySQL */ public static List<String> findSchemas(JdbcTemplate jdbcTemplate, final String schemaPattern, final DdlSchemaFilter ddlSchemaFilter) { List<String> schemas = findSchemas(jdbcTemplate, schemaPattern); if (ddlSchemaFilter == null) { return schemas; } List<String> filterSchemas = new ArrayList<String>(); for (String schema : schemas) { if (ddlSchemaFilter.accept(schema)) { filterSchemas.add(schema); } } return filterSchemas; } public static Table findTable(JdbcTemplate jdbcTemplate, final String catalogName, final String schemaName, final String tableName) throws Exception { return findTable(jdbcTemplate, catalogName, schemaName, tableName, null); } public static Table findTable(final JdbcTemplate jdbcTemplate, final String catalogName, final String schemaName, final String tableName, final DdlUtilsFilter filter) throws Exception { return (Table) jdbcTemplate.execute(new ConnectionCallback() { public Object doInConnection(Connection con) throws SQLException, DataAccessException { Table table = null; DatabaseMetaDataWrapper metaData = new DatabaseMetaDataWrapper(); try { if (filter != null) { con = filter.filterConnection(con); Assert.notNull(con); } DatabaseMetaData databaseMetaData = con.getMetaData(); if (filter != null) { databaseMetaData = filter.filterDataBaseMetaData(jdbcTemplate, con, databaseMetaData); Assert.notNull(databaseMetaData); } metaData.setMetaData(databaseMetaData); metaData.setTableTypes(TableType.toStrings(SUPPORTED_TABLE_TYPES)); metaData.setCatalog(catalogName); metaData.setSchemaPattern(schemaName); String convertTableName = tableName; if (databaseMetaData.storesUpperCaseIdentifiers()) { metaData.setCatalog(catalogName.toUpperCase()); metaData.setSchemaPattern(schemaName.toUpperCase()); convertTableName = tableName.toUpperCase(); } if (databaseMetaData.storesLowerCaseIdentifiers()) { metaData.setCatalog(catalogName.toLowerCase()); metaData.setSchemaPattern(schemaName.toLowerCase()); convertTableName = tableName.toLowerCase(); } ResultSet tableData = null; try { tableData = metaData.getTables(convertTableName); while ((tableData != null) && tableData.next()) { Map<String, Object> values = readColumns(tableData, initColumnsForTable()); table = readTable(metaData, values); if (table.getName().equalsIgnoreCase(tableName)) { break; } } } finally { JdbcUtils.closeResultSet(tableData); } } catch (Exception e) { logger.error(e.getMessage(), e); } makeAllColumnsPrimaryKeysIfNoPrimaryKeysFound(table); return table; } }); } @SuppressWarnings("unchecked") public static List<Table> findTables(final JdbcTemplate jdbcTemplate, final String catalogName, final String schemaName, final String tableNamePattern, final DdlUtilsFilter filter, final DdlTableNameFilter tableNameFilter) throws Exception { return (List<Table>) jdbcTemplate.execute(new ConnectionCallback() { public Object doInConnection(Connection con) throws SQLException, DataAccessException { List<Table> tables = new ArrayList<Table>(); DatabaseMetaDataWrapper metaData = new DatabaseMetaDataWrapper(); try { if (filter != null) { con = filter.filterConnection(con); Assert.notNull(con); } DatabaseMetaData databaseMetaData = con.getMetaData(); if (filter != null) { databaseMetaData = filter.filterDataBaseMetaData(jdbcTemplate, con, databaseMetaData); Assert.notNull(databaseMetaData); } metaData.setMetaData(databaseMetaData); metaData.setTableTypes(TableType.toStrings(SUPPORTED_TABLE_TYPES)); metaData.setCatalog(catalogName); metaData.setSchemaPattern(schemaName); String convertTableName = tableNamePattern; if (databaseMetaData.storesUpperCaseIdentifiers()) { metaData.setCatalog(catalogName.toUpperCase()); metaData.setSchemaPattern(schemaName.toUpperCase()); convertTableName = tableNamePattern.toUpperCase(); } if (databaseMetaData.storesLowerCaseIdentifiers()) { metaData.setCatalog(catalogName.toLowerCase()); metaData.setSchemaPattern(schemaName.toLowerCase()); convertTableName = tableNamePattern.toLowerCase(); } ResultSet tableData = null; try { tableData = metaData.getTables(convertTableName); while ((tableData != null) && tableData.next()) { Map<String, Object> values = readColumns(tableData, initColumnsForTable()); Table table = readTable(metaData, values); if ((tableNameFilter == null) || tableNameFilter.accept(catalogName, schemaName, table.getName())) { tables.add(table); } } } finally { JdbcUtils.closeResultSet(tableData); } } catch (Exception e) { logger.error(e.getMessage(), e); } for (Table table : tables) { makeAllColumnsPrimaryKeysIfNoPrimaryKeysFound(table); } return tables; } }); } /** * Treat tables with no primary keys as a table with all primary keys. */ private static void makeAllColumnsPrimaryKeysIfNoPrimaryKeysFound(Table table) { if ((table != null) && (table.getPrimaryKeyColumns() != null) && (table.getPrimaryKeyColumns().length == 0)) { Column[] allCoumns = table.getColumns(); for (Column column : allCoumns) { column.setPrimaryKey(true); } } } private static Table readTable(DatabaseMetaDataWrapper metaData, Map<String, Object> values) throws SQLException { String tableName = (String) values.get("TABLE_NAME"); Table table = null; if ((tableName != null) && (tableName.length() > 0)) { table = new Table(); table.setName(tableName); table.setType((String) values.get("TABLE_TYPE")); table.setCatalog((String) values.get("TABLE_CAT")); table.setSchema((String) values.get("TABLE_SCHEM")); table.setDescription((String) values.get("REMARKS")); table.addColumns(readColumns(metaData, tableName)); Collection<String> primaryKeys = readPrimaryKeyNames(metaData, tableName); for (Object key : primaryKeys) { Column col = table.findColumn((String) key, true); if (col != null) { col.setPrimaryKey(true); } else { throw new NullPointerException(String.format("%s pk %s is null - %s %s", tableName, key, ToStringBuilder.reflectionToString(metaData, ToStringStyle.SIMPLE_STYLE), ToStringBuilder.reflectionToString(values, ToStringStyle.SIMPLE_STYLE))); } } } return table; } private static 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("TABLE_CAT", Types.VARCHAR)); result.add(new MetaDataColumnDescriptor("TABLE_SCHEM", Types.VARCHAR)); result.add(new MetaDataColumnDescriptor("REMARKS", Types.VARCHAR)); return result; } private static 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)); // 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("REMARKS", Types.VARCHAR)); return result; } private static 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; } private static List<Column> readColumns(DatabaseMetaDataWrapper metaData, String tableName) throws SQLException { ResultSet columnData = null; try { columnData = metaData.getColumns(tableName, null); List<Column> columns = new ArrayList<Column>(); Map<String, Object> values = null; for (; columnData.next(); columns.add(readColumn(metaData, values))) { Map<String, Object> tmp = readColumns(columnData, initColumnsForColumn()); if (tableName.equalsIgnoreCase((String) tmp.get("TABLE_NAME"))) { values = tmp; } else { break; } } // if (true == columns.isEmpty()) { // Connection con = metaData.getMetaData().getConnection(); // String propIncludeSynonyms = "includeSynonyms"; // // if (PropertyUtils.isWriteable(con, propIncludeSynonyms)) { // try { // String includeSynonymsMethodName = "setIncludeSynonyms"; // boolean previousSynonyms = (Boolean) // PropertyUtils.getProperty(con, propIncludeSynonyms); // // if (logger.isInfoEnabled()) { // logger.info("ORACLE: switch includeSynonyms to " + // previousSynonyms); // } // // try { // MethodUtils.invokeMethod(con, includeSynonymsMethodName, // !previousSynonyms); // columns = readColumns(metaData, tableName); // // if (false == columns.isEmpty()) { // return columns; // } // } finally { // MethodUtils.invokeMethod(con, includeSynonymsMethodName, // previousSynonyms); // } // // throw new SQLException("ORACLE: no column found for " + // tableName); // } catch (IllegalAccessException e) { // logger.error(e.getMessage(), e); // } catch (InvocationTargetException e) { // logger.error(e.getMessage(), e); // } catch (NoSuchMethodException e) { // logger.error(e.getMessage(), e); // } // } // } return columns; } finally { JdbcUtils.closeResultSet(columnData); } } private static Column readColumn(DatabaseMetaDataWrapper metaData, Map<String, Object> values) throws SQLException { Column column = new Column(); column.setName((String) values.get("COLUMN_NAME")); column.setDefaultValue((String) values.get("COLUMN_DEF")); column.setTypeCode(((Integer) values.get("DATA_TYPE")).intValue()); String typeName = (String) values.get("TYPE_NAME"); // column.setType(typeName); if ((typeName != null) && typeName.startsWith("TIMESTAMP")) { column.setTypeCode(Types.TIMESTAMP); } // modify 2013-09-25?unsigned if ((typeName != null) && StringUtils.containsIgnoreCase(typeName, "UNSIGNED")) { // unsigned???? switch (column.getTypeCode()) { case Types.TINYINT: column.setTypeCode(Types.SMALLINT); break; case Types.SMALLINT: column.setTypeCode(Types.INTEGER); break; case Types.INTEGER: column.setTypeCode(Types.BIGINT); break; case Types.BIGINT: column.setTypeCode(Types.DECIMAL); break; default: break; } } Integer precision = (Integer) values.get("NUM_PREC_RADIX"); if (precision != null) { column.setPrecisionRadix(precision.intValue()); } String size = (String) values.get("COLUMN_SIZE"); if (size == null) { size = (String) _defaultSizes.get(new Integer(column.getTypeCode())); } // we're setting the size after the precision and radix in case // the database prefers to return them in the size value column.setSize(size); int scale = 0; Object dec_digits = values.get("DECIMAL_DIGITS"); if (dec_digits instanceof String) { scale = (dec_digits == null) ? 0 : NumberUtils.toInt(dec_digits.toString()); } else if (dec_digits instanceof Integer) { scale = (dec_digits == null) ? 0 : (Integer) dec_digits; } if (scale != 0) { column.setScale(scale); } column.setRequired("NO".equalsIgnoreCase(((String) values.get("IS_NULLABLE")).trim())); column.setDescription((String) values.get("REMARKS")); return column; } private static Map<String, Object> readColumns(ResultSet resultSet, List<MetaDataColumnDescriptor> columnDescriptors) throws SQLException { Map<String, Object> values = new HashMap<String, Object>(); MetaDataColumnDescriptor descriptor; for (Iterator<MetaDataColumnDescriptor> it = columnDescriptors.iterator(); it.hasNext(); values .put(descriptor.getName(), descriptor.readColumn(resultSet))) { descriptor = (MetaDataColumnDescriptor) it.next(); } return values; } private static Collection<String> readPrimaryKeyNames(DatabaseMetaDataWrapper metaData, String tableName) throws SQLException { ResultSet pkData = null; try { List<String> pks = new ArrayList<String>(); Map<String, Object> values; for (pkData = metaData.getPrimaryKeys(tableName); pkData.next(); pks .add(readPrimaryKeyName(metaData, values))) { values = readColumns(pkData, initColumnsForPK()); } return pks; } finally { JdbcUtils.closeResultSet(pkData); } } private static String readPrimaryKeyName(DatabaseMetaDataWrapper metaData, Map<String, Object> values) throws SQLException { return (String) values.get("COLUMN_NAME"); } }