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 com.walker.jdbc.util.StringSqlUtils;
|
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.ArrayList;
|
import java.util.HashMap;
|
import java.util.List;
|
import java.util.Map;
|
|
/**
|
* MySQL元数据引擎实现
|
* @author shikeying
|
* @date 2015年12月18日
|
*
|
*/
|
public class MySQLMetaEngine extends AbstractDatabaseMetaEngine {
|
|
public static final String SQL_GET_TBL_COUNT = "SELECT COUNT(*) cnt FROM information_schema.`TABLES`"
|
+ " WHERE TABLE_SCHEMA = ? AND TABLE_TYPE = 'BASE TABLE'";
|
|
public final String QUERY_TABLE_FIELDS = "SELECT COLUMN_NAME from information_schema.COLUMNS "
|
+ "WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?";
|
|
public final String QUERY_COLUMNS_LIST = "select table_name,column_name,data_type,NUMERIC_PRECISION data_precision,NUMERIC_SCALE data_scale, column_key, column_comment"
|
+ " from INFORMATION_SCHEMA.Columns"
|
+ " where table_schema=(SELECT DATABASE()) and table_name like ? order by table_name,ordinal_position";
|
|
public final String SQL_TABLES_ROW = "SELECT TABLE_NAME tname, TABLE_COMMENT summary, TABLE_ROWS trows"
|
+ " FROM information_schema.`TABLES` "
|
+ " WHERE TABLE_SCHEMA = :schema AND TABLE_TYPE = 'BASE TABLE'"
|
+ " AND TABLE_NAME in (:ids)";
|
|
public final String SQL_ONE_TABLE_ROW = "SELECT TABLE_ROWS trows"
|
+ " FROM information_schema.`TABLES` "
|
+ " WHERE TABLE_SCHEMA = ? AND TABLE_TYPE = 'BASE TABLE'"
|
+ " AND TABLE_NAME = ?";
|
public final String SQL_TABLE_LIKE = "SELECT TABLE_NAME "
|
+ " FROM information_schema.`TABLES` "
|
+ " WHERE TABLE_SCHEMA = ? AND TABLE_TYPE = 'BASE TABLE'"
|
+ " AND TABLE_NAME like ?";
|
|
// 模糊分页查询用户表集合
|
public final String SQL_TABLE_LIKE_PAGE = "SELECT TABLE_NAME tname, TABLE_ROWS trows, TABLE_COMMENT summary, "
|
+ "AVG_ROW_LENGTH avg_row_len, DATA_LENGTH blocks, UPDATE_TIME last_analyzed, ENGINE tablespace_name "
|
+ " FROM information_schema.`TABLES` "
|
+ " WHERE TABLE_SCHEMA = ? AND TABLE_TYPE = 'BASE TABLE'"
|
+ " AND TABLE_NAME like ?";
|
public final String SQL_TABLE_PAGE = "SELECT TABLE_NAME tname, TABLE_ROWS trows, TABLE_COMMENT summary, "
|
+ "AVG_ROW_LENGTH avg_row_len, DATA_LENGTH blocks, UPDATE_TIME last_analyzed, ENGINE tablespace_name "
|
+ " FROM information_schema.`TABLES` "
|
+ " WHERE TABLE_SCHEMA = ? AND TABLE_TYPE = 'BASE TABLE'";
|
|
private final TableInfoMapper tableInfoMapper = new TableInfoMapper();
|
private final FieldInfoMapper fieldInfoMapper = new FieldInfoMapper();
|
|
@Override
|
protected DatabaseConnector createDbConnector(Address address) {
|
DatabaseConnector connector = ConnectorUtils.createMySQLConnector(address);
|
if(connector == null){
|
throw new IllegalArgumentException("connector create failed!");
|
}
|
return ConnectorUtils.acquireTransactionProxyConnector(connector);
|
}
|
|
@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[]{StringSqlUtils.getMySQLSchemaName(connector.getServiceName()), tableName});
|
if(!StringUtils.isEmptyList(list)){
|
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 {
|
// StringBuilder tnames = new StringBuilder();
|
// if(tableNameList != null){
|
// int i = 0;
|
// for(String s : tableNameList){
|
// if(i > 0){
|
// tnames.append(",");
|
// }
|
// tnames.append("'");
|
// tnames.append(s).append("'");
|
// }
|
// }
|
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) {
|
// MapSqlParameterSource nameSet = new MapSqlParameterSource();
|
// nameSet.addValue("schema", DatabaseUtils.getMySQLSchemaName(connector.getServiceName()));
|
// nameSet.addValue("tableName", tableName);
|
return connector.queryForLong(SQL_ONE_TABLE_ROW, new Object[]{DatabaseUtils.getMySQLSchemaName(connector.getServiceName()), 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[]{DatabaseUtils.getMySQLSchemaName(conn.getServiceName()), 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 {
|
ResultSetMetaData rsMetaData = rs.getMetaData();
|
TableInfo ti = new TableInfo();
|
ti.setName(rs.getString("tname"));
|
ti.setSummary(rs.getString("summary"));
|
ti.setRows(rs.getInt("trows"));
|
if(this.containColumn("avg_row_len", rsMetaData)){
|
ti.setAvgRowLen(rs.getLong("avg_row_len"));
|
}
|
if(this.containColumn("blocks", rsMetaData)){
|
ti.setBlocks(rs.getLong("blocks"));
|
}
|
if(this.containColumn("last_analyzed", rsMetaData)){
|
ti.setLastAnalyzed(rs.getString("last_analyzed"));
|
}
|
if(this.containColumn("tablespace_name", rsMetaData)){
|
ti.setTableSpace(rs.getString("tablespace_name"));
|
}
|
ti.setStatus(StringUtils.EMPTY_STRING);
|
return ti;
|
}
|
|
private boolean containColumn(String columnName, ResultSetMetaData rsMetaData) throws SQLException{
|
// int numberOfColumns = rsMetaData.getColumnCount();
|
// String column = null;
|
// for (int i = 1; i < numberOfColumns + 1; i++) {
|
// column = rsMetaData.getColumnName(i);
|
// if (column.equals(columnName)) {
|
// return true;
|
// }
|
// }
|
// return false;
|
return DatabaseMetaEngineUtils.isContainColumnName(columnName, rsMetaData);
|
}
|
}
|
|
private class FieldInfoMapper implements RowMapper<FieldInfo>{
|
@Override
|
public FieldInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
|
ResultSetMetaData rsMetaData = rs.getMetaData();
|
FieldInfo fieldInfo = new FieldInfo();
|
fieldInfo.setFieldName(rs.getString("column_name"));
|
fieldInfo.setDataType(rs.getString("data_type"));
|
// fieldInfo.setLength();
|
if(DatabaseMetaEngineUtils.isContainColumnName("column_comment", rsMetaData)){
|
// 字段备注
|
// logger.debug("+++++++ " + rs.getString("column_comment"));
|
fieldInfo.setComments(rs.getString("column_comment"));
|
}
|
if(DatabaseMetaEngineUtils.isContainColumnName("data_precision", rsMetaData)){
|
fieldInfo.setPrecision(rs.getInt("data_precision"));
|
}
|
if(DatabaseMetaEngineUtils.isContainColumnName("data_scale", rsMetaData)){
|
fieldInfo.setScale(rs.getInt("data_scale"));
|
}
|
if(DatabaseMetaEngineUtils.isContainColumnName("column_key", rsMetaData)){
|
fieldInfo.setColumnKey(rs.getString("column_key"));
|
}
|
fieldInfo.setTableName(rs.getString("table_name"));
|
return fieldInfo;
|
}
|
}
|
|
@Override
|
public GenericPager<TableInfo> queryPageTableNamesByLike(Address address, String tableNameLike
|
// , int pageIndex, int pageSize
|
) {
|
DatabaseConnector conn = getConnector(address);
|
// 查询总记录
|
// 查询数据集合
|
String query = null;
|
String pageSql = null;
|
Object[] args = null;
|
// args[0] = address.getServiceName();
|
// args[1] = tableNameLike;
|
|
if(StringUtils.isNotEmpty(tableNameLike)){
|
query = SQL_TABLE_LIKE_PAGE;
|
pageSql = SQL_TABLE_LIKE_PAGE + " limit ? offset ?";
|
args = new Object[]{address.getServiceName(), DatabaseMetaEngineUtils.getLikeConditionArg(tableNameLike)};
|
} else {
|
query = SQL_TABLE_PAGE;
|
pageSql = SQL_TABLE_PAGE + " limit ? offset ?";
|
args = new Object[]{address.getServiceName()};
|
}
|
// Object[] pageArgs = new Object[4];
|
// pageArgs[0] = address.getServiceName();
|
// pageArgs[1] = tableNameLike;
|
// pageArgs[2] = ListPageContext.getCurrentPageSize();
|
// pageArgs[3] = ListPageContext.getCurrentPageIndex();
|
return conn.sqlSimpleQueryPager(query, args, tableInfoMapper
|
// , pageIndex, pageSize
|
, pageSql);
|
}
|
|
@Override
|
protected List<FieldInfo> loadFieldsObject(DatabaseConnector connector, String tableName) {
|
return connector.queryForRowMapper(QUERY_COLUMNS_LIST, new Object[]{"%" + tableName}, this.fieldInfoMapper);
|
}
|
|
@Override
|
protected void doCreateTableAction(Address address
|
, List<FieldInfo> fieldList, String dataVersionField, String tableName) throws DatabaseException {
|
DatabaseConnector conn = getConnector(address);
|
if(conn == null){
|
throw new DatabaseException("数据库连接器不存在，无法执行ddl。table = " + tableName);
|
}
|
|
StringBuilder tableSql = new StringBuilder();
|
tableSql.append("CREATE TABLE ").append(tableName).append("(");
|
|
int i = 0;
|
for(FieldInfo fi : fieldList){
|
tableSql.append(fi.getFieldName()).append(" ");
|
if(fi.getDataType().equals(FieldInfo.TYPE_DOUBLE)){
|
tableSql.append("double(10,2) not null default 0");
|
} else if(fi.getDataType().equals(FieldInfo.TYPE_LONG)){
|
tableSql.append("bigint not null default 0");
|
} else if(fi.getDataType().equals(FieldInfo.TYPE_STRING)){
|
tableSql.append("varchar(255)");
|
} else {
|
throw new UnsupportedOperationException("未实现字段类型：" + fi.getDataType());
|
}
|
i++;
|
if(i == fieldList.size()){
|
// 最后一个字段
|
tableSql.append(");");
|
} else {
|
tableSql.append(StringUtils.DEFAULT_SPLIT_SEPARATOR);
|
}
|
}
|
// 1-创建表
|
conn.exeCreateTable(tableSql.toString());
|
|
// 2-创建数据版本索引（如果存在）
|
if(StringUtils.isNotEmpty(dataVersionField)){
|
StringBuilder dataVersionIndexSql = new StringBuilder("create index inx_");
|
dataVersionIndexSql.append(tableName).append(StringUtils.UNDERLINE).append(StringUtils.generateRandomNumber(3));
|
dataVersionIndexSql.append(" on ").append(tableName);
|
dataVersionIndexSql.append("(").append(dataVersionField).append(")");
|
logger.debug("添加索引：" + dataVersionIndexSql);
|
conn.execute(dataVersionIndexSql.toString());
|
}
|
}
|
|
public static void main(String[] args){
|
Address address = new Address();
|
// address.setUrl("127.0.0.1");
|
address.setUrl("116.198.40.76");
|
address.setPort(3306);
|
// address.setService("enterprise-platform");
|
address.setService("iplatform");
|
address.setAuthentication("root");
|
// address.setCertification("root");
|
address.setCertification("Bjjmy_2020");
|
|
MySQLMetaEngine metaEngine = new MySQLMetaEngine();
|
|
// 测试查询
|
// ListPageContext.setCurrentPageIndex(1);
|
// ListPageContext.setCurrentPageSize(10);
|
//
|
// GenericPager<TableInfo> pager = metaEngine.queryPageTableNamesByLike(address, "b2");
|
// System.out.println(pager.getDatas());
|
|
// 测试创建表
|
// List<Map<String, Object>> datas = metaEngine.loadTableDatas(address
|
// , "s_user_core", "select id, create_time, name, type, status, org_name, mobile, find_pass_time from s_user_core limit 20");
|
// List<Map<String, Object>> datas = metaEngine.loadTableDatas(address
|
// , "", "select u.*, d.name dept from s_user_core u, s_department d where u.DEPARTMENT_ID = d.ID limit 30");
|
try {
|
// metaEngine.createTableDynamic(address, datas, "id", "test_user_dept");
|
List<FieldInfo> list = metaEngine.getFieldsObject(address, "s_user_core");
|
if(list != null){
|
for(FieldInfo fieldInfo : list){
|
System.out.println(fieldInfo);
|
}
|
}
|
} catch (Exception e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
