package com.walker.dbmeta.support;
|
|
import com.walker.connector.Address;
|
import com.walker.connector.support.DatabaseConnector;
|
import com.walker.connector.util.ConnectorUtils;
|
import com.walker.db.DatabaseException;
|
import com.walker.db.TableInfo;
|
import com.walker.db.page.GenericPager;
|
import com.walker.db.page.ListPageContext;
|
import com.walker.dbmeta.AbstractDatabaseMetaEngine;
|
import com.walker.dbmeta.FieldInfo;
|
import com.walker.dbmeta.util.DatabaseMetaEngineUtils;
|
import com.walker.infrastructure.utils.StringUtils;
|
import com.walker.jdbc.util.SqlUtils;
|
import org.springframework.jdbc.core.RowMapper;
|
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
|
|
import java.sql.ResultSet;
|
import java.sql.ResultSetMetaData;
|
import java.sql.SQLException;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
/**
|
* 描述：oracle元数据管理引擎具体实现
|
* @author 时克英
|
* @date 2017年11月14日 下午10:40:26
|
*/
|
|
public class OracleMetaEngine extends AbstractDatabaseMetaEngine {
|
|
// private final Oracle9iDialect oracleDialect = new Oracle9iDialect();
|
private final TableInfoMapper tableInfoMapper = new TableInfoMapper();
|
private final FieldInfoMapper fieldInfoMapper = new FieldInfoMapper();
|
|
/* oracle数据库只读用户后缀，因为连接的很可能是我们创建的只读用户，如：zzdxzcgl_read,
|
* 因此需要用原始的用户才能访问到实际的表信息 */
|
private final static String READ_SUFFIX = "_READ";
|
|
private final String SQL_ALL_TABLES = "Select t.table_name, t.tablespace_name, t.status, t.num_rows, t.blocks, t.avg_row_len, t.last_analyzed, c.comments"
|
+ " from all_tables t, all_tab_comments c"
|
+ " where t.owner=? And t.owner = c.owner And c.table_type = 'TABLE' And c.table_name = t.table_name"
|
+ " order by num_rows Desc";
|
|
private final String SQL_ALL_TABLES_LIKE = "Select t.table_name, t.tablespace_name, t.status, t.num_rows, t.blocks, t.avg_row_len, t.last_analyzed, c.comments"
|
+ " from all_tables t, all_tab_comments c"
|
+ " where t.owner=? And t.owner = c.owner And c.table_type = 'TABLE' And c.table_name = t.table_name And t.table_name Like ?"
|
+ " order by num_rows Desc";
|
private final String SQL_ALL_TABLES_WHERE_IN = "Select t.table_name, t.tablespace_name, t.status, t.num_rows, t.blocks, t.avg_row_len, t.last_analyzed, c.comments"
|
+ " from all_tables t, all_tab_comments c"
|
+ " where t.owner=:owner And t.owner = c.owner And c.table_type = 'TABLE' And c.table_name = t.table_name And t.table_name in (:ids)"
|
+ " order by num_rows Desc";
|
|
/*private final String SQL_FIELDS = "select c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, c.DATA_LENGTH, c.NULLABLE, c.COLUMN_ID, s.COMMENTS "
|
+ "from user_tab_columns c, user_col_comments s where c.Table_Name=? "
|
+ "and c.TABLE_NAME = s.TABLE_NAME and c.COLUMN_NAME = s.COLUMN_NAME order by c.COLUMN_ID";*/
|
private final String SQL_FIELDS = "t1.*, ucc.constraint_name, uc.constraint_type from ("
|
+ "select c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, c.DATA_LENGTH, c.DATA_PRECISION, C.DATA_SCALE, c.NULLABLE, c.COLUMN_ID, s.COMMENTS "
|
+ "from user_tab_columns c, user_col_comments s "
|
+ "where c.Table_Name=? and c.TABLE_NAME = s.TABLE_NAME and c.COLUMN_NAME = s.COLUMN_NAME order by c.COLUMN_ID"
|
+ ") t1 "
|
+ "left join user_cons_columns ucc on ucc.table_name=t1.Table_Name and ucc.column_name=t1.COLUMN_NAME "
|
+ "left join user_constraints uc on uc.constraint_name=ucc.constraint_name";
|
|
@Override
|
public GenericPager<TableInfo> queryPageTableNamesByLike(Address address, String tableNameLike) {
|
DatabaseConnector conn = getConnector(address);
|
|
String query = null;
|
String pageSql = null;
|
Object[] args = null;
|
if(StringUtils.isEmpty(tableNameLike)){
|
query = SQL_ALL_TABLES;
|
// pageSql = oracleDialect.getLimitString(SQL_ALL_TABLES, true);
|
pageSql = SqlUtils.getOraclePageSql(query);
|
args = new Object[1];
|
args[0] = this.getDatabaseUser(address.getAuthentication());
|
} else {
|
query = SQL_ALL_TABLES_LIKE;
|
// pageSql = oracleDialect.getLimitString(SQL_ALL_TABLES_LIKE, true);
|
pageSql = SqlUtils.getOraclePageSql(query);
|
args = new Object[2];
|
args[0] = this.getDatabaseUser(address.getAuthentication());
|
args[1] = DatabaseMetaEngineUtils.getLikeConditionArg(tableNameLike.toUpperCase());
|
}
|
|
return conn.sqlSimpleQueryPager(query, args, tableInfoMapper
|
// , ListPageContext.getCurrentPageIndex(), ListPageContext.getCurrentPageSize()
|
, pageSql);
|
}
|
|
/**
|
* 对于只读库，我们会规范oracle用户命名为：***_READ，因此在查询用户库元数据时，
|
* 需要判断是否是原始用户，最终必须使用原始用户才能查询到。
|
* @param oracleUserName
|
* @return
|
*/
|
private String getDatabaseUser(String oracleUserName){
|
String oracleUser = oracleUserName.toUpperCase();
|
int index = oracleUser.indexOf(READ_SUFFIX);
|
if(index > 0){
|
oracleUser = oracleUser.substring(0, index);
|
}
|
return oracleUser;
|
}
|
|
private class TableInfoMapper implements RowMapper<TableInfo>{
|
@Override
|
public TableInfo mapRow(ResultSet rs, int arg1) throws SQLException {
|
TableInfo ti = new TableInfo();
|
ti.setName(rs.getString("table_name"));
|
ti.setSummary(rs.getString("comments"));
|
ti.setRows(rs.getInt("num_rows"));
|
ti.setAvgRowLen(rs.getLong("avg_row_len"));
|
ti.setBlocks(rs.getLong("blocks"));
|
ti.setLastAnalyzed(rs.getString("last_analyzed"));
|
ti.setStatus(rs.getString("status"));
|
ti.setTableSpace(rs.getString("tablespace_name"));
|
return ti;
|
}
|
}
|
|
private class FieldInfoMapper implements RowMapper<FieldInfo> {
|
@Override
|
public FieldInfo mapRow(ResultSet rs, int arg1) throws SQLException {
|
ResultSetMetaData rsMetaData = rs.getMetaData();
|
FieldInfo f = new FieldInfo();
|
f.setColumnId(rs.getInt("COLUMN_ID"));
|
f.setComments(rs.getString("COMMENTS"));
|
f.setDataType(rs.getString("DATA_TYPE"));
|
f.setFieldName(rs.getString("COLUMN_NAME"));
|
f.setLength(rs.getLong("DATA_LENGTH"));
|
f.setNullable(rs.getString("NULLABLE"));
|
f.setTableName(rs.getString("TABLE_NAME"));
|
String constraintType = rs.getString("constraint_type");
|
if(StringUtils.isNotEmpty(constraintType) && constraintType.equals("P")){
|
f.setColumnKey(FieldInfo.NAME_PRIMARY_KEY);
|
}
|
if(rs.getObject("DATA_PRECISION") != null){
|
f.setPrecision(rs.getInt("DATA_PRECISION"));
|
}
|
// if(DatabaseMetaEngineUtils.isContainColumnName("data_scale", rsMetaData)){
|
if(rs.getObject("DATA_SCALE") != null){
|
f.setScale(rs.getInt("DATA_SCALE"));
|
}
|
return f;
|
}
|
}
|
|
@Override
|
protected DatabaseConnector createDbConnector(Address address) {
|
DatabaseConnector connector = ConnectorUtils.createOracleConnector(address);
|
if(connector == null){
|
throw new IllegalArgumentException("connector create failed!");
|
}
|
return ConnectorUtils.acquireTransactionProxyConnector(connector);
|
}
|
|
@Override
|
protected int loadSchemaTableSize(DatabaseConnector connnector) {
|
return 0;
|
}
|
|
@Override
|
protected List<String> loadFields(DatabaseConnector connector, String tableName) {
|
return null;
|
}
|
|
@Override
|
protected Map<String, TableInfo> loadTablesRow(Address address, DatabaseConnector connector, List<String> tableNameList) {
|
MapSqlParameterSource nameSet = new MapSqlParameterSource();
|
// nameSet.addValue("schema", DatabaseUtils.getMySQLSchemaName(connector.getServiceName()));
|
nameSet.addValue("owner", address.getAuthentication());
|
nameSet.addValue("ids", tableNameList);
|
logger.debug(".............. address.getAuthentication()={}", address.getAuthentication());
|
|
List<TableInfo> list = (List<TableInfo>)connector.queryForRowMapper(SQL_ALL_TABLES_WHERE_IN, tableInfoMapper, nameSet);
|
if(!StringUtils.isEmptyList(list)){
|
Map<String, TableInfo> result = new HashMap<String, TableInfo>();
|
for(TableInfo ti : list){
|
result.put(ti.getName(), ti);
|
}
|
return result;
|
}
|
return null;
|
}
|
|
@Override
|
protected long loadTableRow(DatabaseConnector connector, String tableName) {
|
return 0;
|
}
|
|
@Override
|
protected List<Map<String, Object>> loadDatas(DatabaseConnector connector, String tableName, String sql)
|
throws Exception {
|
return null;
|
}
|
|
@Override
|
protected List<FieldInfo> loadFieldsObject(DatabaseConnector connector, String tableName) {
|
return connector.queryForRowMapper(SQL_FIELDS, new Object[]{tableName.toUpperCase()}, fieldInfoMapper);
|
}
|
|
@Override
|
protected void doCreateTableAction(Address address, List<FieldInfo> fieldList, String dataVersionField, String tableName)
|
throws DatabaseException {
|
// TODO Auto-generated method stub
|
|
}
|
|
public static void main(String[] args){
|
Address address = new Address();
|
address.setUrl("127.0.0.1");
|
address.setPort(1521);
|
address.setService("ORCL2");
|
address.setAuthentication("YGZW");
|
address.setCertification("YGZW");
|
|
OracleMetaEngine metaEngine = new OracleMetaEngine();
|
|
ListPageContext.setCurrentPageIndex(1);
|
ListPageContext.setCurrentPageSize(10);
|
|
GenericPager<TableInfo> pager = metaEngine.queryPageTableNamesByLike(address, "biz");
|
System.out.println(pager.getDatas());
|
}
|
}
|
