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.dbmeta.AbstractDatabaseMetaEngine;
|
import com.walker.dbmeta.FieldInfo;
|
import com.walker.dbmeta.util.DatabaseMetaEngineUtils;
|
import com.walker.dbmeta.util.DatabaseUtils;
|
import com.walker.infrastructure.utils.StringUtils;
|
import org.springframework.jdbc.core.RowMapper;
|
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
|
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.ArrayList;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
/**
|
* PostgreSQL数据库对元数据的操作引擎实现，注意与Mysql的区别
|
* @author shikeying
|
* @date 2016年4月22日
|
*
|
*/
|
public class PostgresMetaEngine extends AbstractDatabaseMetaEngine {
|
|
public static final String SQL_GET_TBL_COUNT = "SELECT COUNT(*) cnt FROM information_schema.TABLES"
|
+ " WHERE table_catalog = ? and table_schema = 'public' and TABLE_TYPE = 'BASE TABLE'";
|
|
public final String QUERY_TABLE_FIELDS = "SELECT COLUMN_NAME from information_schema.COLUMNS "
|
+ "WHERE table_catalog = ? and table_schema = 'public' AND TABLE_NAME = ?";
|
|
public final String SQL_TABLES_ROW = "SELECT c.relname tname, '' summary, c.reltuples trows"
|
+ " from pg_class c, pg_namespace n "
|
+ " where c.relkind = 'r' and n.nspname = 'public' and c.relnamespace = n.oid"
|
+ " and c.relname in (:ids)";
|
|
public final String SQL_ONE_TABLE_ROW = "SELECT c.reltuples trows"
|
+ " from pg_class c, pg_namespace n "
|
+ " where c.relkind = 'r' and n.nspname = 'public' and c.relnamespace = n.oid"
|
+ " and c.relname = ?";
|
public final String SQL_TABLE_LIKE = "SELECT c.relname table_name"
|
+ " from pg_class c, pg_namespace n "
|
+ " where c.relkind = 'r' and n.nspname = 'public' and c.relnamespace = n.oid"
|
+ " and c.relname like ?";
|
|
public final String SQL_TABLE_LIKE_PAGE = "SELECT c.relname tname, '' summary, c.reltuples trows"
|
+ " from pg_class c, pg_namespace n "
|
+ " where c.relkind = 'r' and n.nspname = 'public' and c.relnamespace = n.oid"
|
+ " and c.relname like ?";
|
|
private final TableInfoMapper tableInfoMapper = new TableInfoMapper();
|
|
@Override
|
protected DatabaseConnector createDbConnector(Address address) {
|
return ConnectorUtils.createPostgresConnector(address);
|
}
|
|
@SuppressWarnings("unchecked")
|
protected List<Map<String, Object>> loadDatas(DatabaseConnector connector
|
, String tableName, String sql) throws Exception{
|
List<Map<String, Object>> list = (List<Map<String, Object>>)connector.invoke(sql, (Object[])null);
|
if(list == null || list.size() == 0){
|
return null;
|
}
|
return list;
|
}
|
|
@Override
|
protected List<String> loadFields(DatabaseConnector connector, String tableName) {
|
List<Map<String, Object>> list = connector.queryForList(QUERY_TABLE_FIELDS, new Object[]{DatabaseUtils.getMySQLSchemaName(connector.getServiceName()), tableName});
|
if(list != null && list.size() > 0){
|
List<String> result = new ArrayList<String>(list.size());
|
for(Map<String, Object> map : list){
|
result.add(map.get("COLUMN_NAME").toString().toLowerCase());
|
}
|
return result;
|
}
|
return null;
|
}
|
|
@Override
|
protected int loadSchemaTableSize(DatabaseConnector connector) {
|
return connector.queryForInt(SQL_GET_TBL_COUNT, new Object[]{connector.getServiceName()});
|
}
|
|
@Override
|
protected Map<String, TableInfo> loadTablesRow(Address address, DatabaseConnector connector, List<String> tableNameList) {
|
try {
|
MapSqlParameterSource nameSet = new MapSqlParameterSource();
|
// nameSet.addValue("schema", DatabaseUtils.getMySQLSchemaName(connector.getServiceName()));
|
nameSet.addValue("ids", tableNameList);
|
|
List<TableInfo> list = (List<TableInfo>)connector.queryForRowMapper(SQL_TABLES_ROW
|
, 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;
|
} catch (Exception e) {
|
e.printStackTrace();
|
return null;
|
}
|
}
|
|
@Override
|
protected long loadTableRow(DatabaseConnector connector, String tableName) {
|
return connector.queryForLong(SQL_ONE_TABLE_ROW, new Object[]{tableName});
|
}
|
|
/**
|
* 查找给定库中的表名集合
|
* @param address
|
* @param tableNameLike 要模糊查找的表名，如: base_custdept_
|
* @return
|
*/
|
@Override
|
public List<String> getTableNamesByLike(Address address, String tableNameLike){
|
DatabaseConnector conn = getConnector(address);
|
List<Map<String, Object>> list = conn.queryForList(SQL_TABLE_LIKE
|
, new Object[]{DatabaseMetaEngineUtils.getLikeConditionArg(tableNameLike)});
|
List<String> result = new ArrayList<>();
|
if(list != null){
|
for(Map<String, Object> m : list){
|
result.add(m.get("table_name").toString());
|
}
|
}
|
return result;
|
}
|
|
private class TableInfoMapper implements RowMapper<TableInfo>{
|
@Override
|
public TableInfo mapRow(ResultSet rs, int arg1) throws SQLException {
|
TableInfo ti = new TableInfo();
|
ti.setName(rs.getString("tname"));
|
ti.setSummary(rs.getString("summary"));
|
ti.setRows(rs.getInt("trows"));
|
return ti;
|
}
|
|
}
|
|
@Override
|
public GenericPager<TableInfo> queryPageTableNamesByLike(Address address, String tableNameLike) {
|
// throw new UnsupportedOperationException("未支持的方法");
|
DatabaseConnector conn = getConnector(address);
|
// 查询总记录
|
// 查询数据集合
|
String pageSql = SQL_TABLE_LIKE_PAGE + " limit ? offset ?";
|
Object[] args = new Object[1];
|
// args[0] = address.getServiceName();
|
args[0] = tableNameLike;
|
|
return conn.sqlSimpleQueryPager(SQL_TABLE_LIKE_PAGE, args, tableInfoMapper, pageSql);
|
}
|
|
@Override
|
protected List<FieldInfo> loadFieldsObject(DatabaseConnector connector, String tableName) {
|
// TODO Auto-generated method stub
|
return null;
|
}
|
|
@Override
|
protected void doCreateTableAction(Address address, List<FieldInfo> fieldList, String dataVersionField, String tableName)
|
throws DatabaseException {
|
// TODO Auto-generated method stub
|
|
}
|
|
}
|
