背景

   今天早上，领导给了我一个任务：在老的系统运行中，DBA反馈说获取database TableMeta操作有点慢，让我分析下基于oracle driver驱动是否可以做下优化。由此引出了本文，仅仅做一个记录。

内容

在补充几点背景知识：

1.  老系统介绍

• 老系统主要负责的业务是做跨机房之间的数据库记录同步，需要获取数据库的table meta信息，进行构造对应的sql。将源数据的columns变化，通过sql方式更新到目标库上。
• table meta信息分析时，需要获取table的字段，主键，需要支持视图，同义词等表查询

2.  table meta操作原理

1.jdbcTemplate.execute(new ConnectionCallback() {  
2.  
3.            public Object doInConnection(Connection c) throws SQLException, DataAccessException {  
4.                DatabaseMetaData meta = c.getMetaData();  
5.                meta.getTables(catalog, schemaPattern, tableNamePattern, types);  
6.                meta.getColumns(catalog, schemaPattern, tableNamePattern, columnNamePattern);  
7.                meta.getPrimaryKeys(catalog, schema, table);  
8.                return null;  
9.            }  
10.        });  

简单一点说，就是利用java.sql.DatabaseMetaData接口中定义的meta信息获取接口进行处理。

mysql/oracle实现

oracle实现(oracle.jdbc.driver.OracleDatabaseMetaData)：

1. getTables

主要是通过构造对应的SQL进行查询，主要是关联了all_objects 和 all_tab_comments, all_synonyms

1.SELECT NULL AS table_cat,  
2.o.owner AS table_schem,  
3.o.object_name AS table_name,  
4.o.object_type AS table_type,  
5.c.comments AS remarks  
6.FROM all_objects o, all_tab_comments c  
7.WHERE o.owner LIKE #schema# ESCAPE '/'  
8.AND o.object_name LIKE #table# ESCAPE '/'  
9.AND o.object_type IN ('TABLE', 'SYNONYM', 'VIEW')  
10.AND o.owner = c.owner (+)  
11.AND o.object_name = c.table_name (+)  
12.UNION  
13.SELECT NULL AS table_cat,  
14.s.owner AS table_schem,  
15.s.synonym_name AS table_name,  
16.'SYNONYM' AS table_table_type,  
17.c.comments AS remarks  
18.FROM all_synonyms s, all_objects o, all_tab_comments c  
19.WHERE s.owner LIKE #schema# ESCAPE '/'  
20.AND s.synonym_name LIKE #table# ESCAPE '/'  
21.AND s.table_owner = o.owner  
22.AND s.table_name = o.object_name  
23.AND o.object_type IN ('TABLE', 'VIEW')  
24.AND o.owner = c.owner (+)  
25.AND o.object_name = c.table_name (+)  
26.ORDER BY table_type, table_schem, table_name  

注意一下#schema# , #table#的替换，可以使用%进行模糊匹配

2. getColumns

主要是通过构造对应的SQL进行查询，主要关联了all_tab_comments, all_synonyms, all_col_comments.

1.SELECT NULL AS table_cat,  
2.DECODE(s.table_owner, NULL, t.owner, s.table_owner) AS table_schem,  
3.DECODE(s.synonym_name, NULL, t.table_name, s.synonym_name) AS table_name,  
4.t.column_name AS column_name,  
5.DECODE (t.data_type, 'CHAR', 1, 'VARCHAR2', 12, 'NUMBER', 3,  
6.'LONG', -1, 'DATE', 93 , 'RAW', -3, 'LONG RAW', -4, 'BLOB', 2004, 'CLOB', 2005, 'BFILE', -13, 'FLOAT', 6, 'TIMESTAMP(6)',  
7.93, 'TIMESTAMP(6) WITH TIME ZONE', -101, 'TIMESTAMP(6) WITH LOCAL TIME ZONE', -102, 'INTERVAL YEAR(2) TO MONTH', -103,  
8.'INTERVAL DAY(2) TO SECOND(6)', -104, 'BINARY_FLOAT', 100, 'BINARY_DOUBLE', 101, 1111) AS data_type,  
9.t.data_type AS type_name,  
10.DECODE (t.data_precision, null, t.data_length, t.data_precision) AS column_size,  
11.0 AS buffer_length,  
12.t.data_scale AS decimal_digits,  
13.10 AS num_prec_radix,  
14.DECODE (t.nullable, 'N', 0, 1) AS nullable,  
15.c.comments AS remarks,  
16.t.data_default AS column_def,  
17.0 AS sql_data_type,  
18.0 AS sql_datetime_sub,  
19.t.data_length AS char_octet_length,  
20.t.column_id AS ordinal_position,  
21.DECODE (t.nullable, 'N', 'NO', 'YES') AS is_nullable  
22.FROM all_tab_columns t , all_col_comments c , all_synonyms s  
23.WHERE (t.owner LIKE #schema# ESCAPE '/' OR  
24.       (s.owner LIKE #schema# ESCAPE '/' AND t.owner = s.table_owner))  
25.        AND (t.table_name LIKE #table# ESCAPE '/' OR  
26.        s.synonym_name LIKE #table# ESCAPE '/')  
27.        AND t.column_name LIKE #column# ESCAPE '/'  
28.        AND t.owner = c.owner (+)  AND t.table_name = c.table_name (+)  AND t.column_name = c.column_name (+)  
29.        AND s.table_name (+) = t.table_name  AND ((DECODE(s.owner, t.owner, 'OK','PUBLIC', 'OK',NULL, 'OK','NOT OK') = 'OK') OR (s.owner LIKE 'SRF' AND t.owner = s.table_owner))  
30.        ORDER BY table_schem, table_name, ordinal_position  

注意一下#schema# , #table# , #column#的替换，可以使用%进行模糊匹配

3. getPrimaryKeys

主要是通过构造对应的SQL进行查询，主要关联了 all_cons_columns, all_constraints

1.SELECT NULL AS table_cat,  
2.c.owner AS table_schem,  
3.c.table_name,  
4.c.column_name,  
5.c.position AS key_seq,  
6.c.constraint_name AS pk_name  
7.FROM all_cons_columns c, all_constraints k  
8.WHERE k.constraint_type = 'P'  
9.  AND k.table_name = #table#  AND k.owner like #schema# escape '/'  
10.  AND k.constraint_name = c.constraint_name  
11.  AND k.table_name = c.table_name  
12.  AND k.owner = c.owner  
13.  ORDER BY column_name  

注意一下#schema# , #table# 的替换，可以使用%进行模糊匹配

mysql实现：

1.SELECT TABLE_SCHEMA AS TABLE_CAT,   
2.NULL AS TABLE_SCHEM, TABLE_NAME,   
3.CASE WHEN TABLE_TYPE='BASE TABLE' THEN 'TABLE' WHEN TABLE_TYPE='TEMPORARY' THEN 'LOCAL_TEMPORARY' ELSE TABLE_TYPE END AS TABLE_TYPE,   
4.TABLE_COMMENT AS REMARKS   
5.FROM INFORMATION_SCHEMA.TABLES WHERE   
6.TABLE_SCHEMA LIKE #schema# AND TABLE_NAME LIKE #table# AND TABLE_TYPE IN ('BASE TABLE','VIEW','TEMPORARY')   
7.ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME  

1.SHOW TABLES from retl like 'columns';  
2.show full columns from columns from retl like 'text%' ;  

1.Connection connection = dataSource.getConnection();  
2.DatabaseMetaData databaseMetaData = connection.getMetaData();  
3.String nameSpace = dataMedia.getNamespace();  
4.String name = dataMedia.getName();  
5.if (databaseMetaData.storesUpperCaseIdentifiers()) {// 识别大小写  
6.    nameSpace = nameSpace.toUpperCase();  
7.    name = name.toUpperCase();  
8.}  
9.if (databaseMetaData.storesLowerCaseIdentifiers()) {  
10.    nameSpace = nameSpace.toLowerCase();  
11.    name = name.toLowerCase();  
12.}  
13.  
14.final SchemaCrawlerOptions options = new SchemaCrawlerOptions();  
15.options.setSchemaInfoLevel(SchemaInfoLevel.standard());  
16.options.setSchemaInclusionRule(new InclusionRule(nameSpace, InclusionRule.NONE));  
17.options.setTableInclusionRule(new InclusionRule(nameSpace + "." + name, InclusionRule.NONE));  
18.Database database = SchemaCrawlerUtility.getDatabase(connection, options);  
19.  
20.Schema[] schemas = database.getSchemas();  
21.  
22.for(Schema schema : schemas) {  
23.    for (Table table: Schema.getTables()) {  
24.        Column[] columns = table.getColumns();  
25.    }  
26.  
27.  
28.  
29.  
30.  
31.}  

1. MetadataResultSet results = null;  
2. results = new MetadataResultSet(getMetaData()  
3..getTables(unquotedName(catalogName),  
4.           unquotedName(schemaName),  
5.           tableNamePattern,  
6.           TableType.toStrings(tableTypes)));// 调用getTables方法  
7.  
8. while (results.next())  
9. {  
10.// "TABLE_CAT", "TABLE_SCHEM"  
11.final String tableName = quotedName(results.getString("TABLE_NAME"));  
12.final TableType tableType = results.getEnum("TABLE_TYPE",TableType.unknown);  
13.final String remarks = results.getString("REMARKS");  
14.  
15.final MutableSchema schema = lookupSchema(catalogName, schemaName);  
16......  
17.  
18.if (tableInclusionRule.include(table.getFullName()))  
19.{  
20.  table.setType(tableType);  
21.  table.setRemarks(remarks);  
22.  
23.  schema.addTable(table);  
24.}  
25. }