使用jdbc驱动的元数据metaData获取指定数据库的表信息和表字段信息。
测试请求:http://localhost:30001/api/tableInfoQuery/queryTableInfos
测试数据:{“dataSourceUrl”: “jdbc:mysql://192.168.51.1:3306/yusp_plus_real_xs?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true”,“userName”: “root”,“passWord”: “123456”}
测试时请更改数据库链接配置信息。
下面代码不全,需要补充下contrller入口调用类、TableInfoQueyVO实体对象。这里只是写了实现方法
package cn.com.yusys.yusp.base.data.service.impl;
import cn.com.yusys.yusp.base.data.domain.vo.TableInfoQueyVO;
import cn.com.yusys.yusp.base.data.service.TableInfoQueryService;
import cn.com.yusys.yusp.commons.util.StringUtils;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.*;
@Slf4j
@Service("tableInfoQueryService")
public class TableInfoQueryServiceImpl implements TableInfoQueryService {
@Override
public List<TableInfoQueyVO> getTableFieldInfo(String dataInfo) throws Exception {
Map<String,String> dataSourceInfos = (Map<String, String>) JSON.parse(dataInfo);
String dataSourceUrl = dataSourceInfos.get("dataSourceUrl");
if (StringUtils.isBlank(dataSourceUrl)){
throw new Exception("数据源信息为空!");
}
String userName = dataSourceInfos.get("userName");
if (StringUtils.isBlank(userName)){
throw new Exception("该用户信息为空!");
}
String passWord = dataSourceInfos.get("passWord");
if (StringUtils.isBlank(passWord)){
throw new Exception("该用户密码为空!");
}
String tableName = dataSourceInfos.get("tableName");
if (StringUtils.isBlank(tableName)){
throw new Exception("查询表名不能为空!");
}
Connection conn = getDataSourceConn(dataSourceUrl,userName,passWord);
log.info("-------数据源初始化结束------");
DatabaseMetaData metaData = conn.getMetaData();
List<TableInfoQueyVO> tableFields = this.getTableFields(metaData, tableName,dataSourceUrl,userName);
return tableFields;
}
@Override
public List<Map<String, String>> getTableListInfo(String dataSourceInfo) throws Exception {
Map<String,String> dataSourceInfos = (Map<String, String>) JSON.parse(dataSourceInfo);
String dataSourceUrl = dataSourceInfos.get("dataSourceUrl");
if (StringUtils.isBlank(dataSourceUrl)){
throw new Exception("数据源信息为空!");
}
String userName = dataSourceInfos.get("userName");
if (StringUtils.isBlank(userName)){
throw new Exception("该用户信息为空!");
}
String passWord = dataSourceInfos.get("passWord");
if (StringUtils.isBlank(passWord)){
throw new Exception("该用户密码为空!");
}
log.info("开始查询数据源{}-{}-{},信息",dataSourceUrl,userName,passWord);
Connection conn = getDataSourceConn(dataSourceUrl,userName,passWord);
log.info("-------数据源初始化结束------");
DatabaseMetaData metaData = conn.getMetaData();
List<Map<String, String>> tableList = this.getTableInfoList(metaData,dataSourceUrl,userName);
closeConnection(conn);
return tableList;
}
@Override
public List<Map<String, Object>> getTableInfos(String dataSourceInfo) throws Exception{
Map<String,String> dataSourceInfos = (Map<String, String>) JSON.parse(dataSourceInfo);
String dataSourceUrl = dataSourceInfos.get("dataSourceUrl");
if (StringUtils.isBlank(dataSourceUrl)){
throw new Exception("数据源信息为空!");
}
String userName = dataSourceInfos.get("userName");
if (StringUtils.isBlank(userName)){
throw new Exception("该用户信息为空!");
}
String passWord = dataSourceInfos.get("passWord");
if (StringUtils.isBlank(passWord)){
throw new Exception("该用户密码为空!");
}
log.info("开始查询数据源{},信息",dataSourceUrl,userName,passWord);
List<Map<String,Object>> listInfos = new ArrayList<>();
Connection conn = getDataSourceConn(dataSourceUrl,userName,passWord);
log.info("-------数据源初始化结束------");
DatabaseMetaData metaData = conn.getMetaData();
List<Map<String, String>> tableList = this.getTableList(metaData,dataSourceUrl,userName);
for (Map<String, String> stringMap : tableList) {
Map<String,Object> tableInfoMap = new HashMap<>();
for (String ss : stringMap.keySet()) {
tableInfoMap.put("tableName",ss);
List<TableInfoQueyVO> tableFields = this.getTableFields(metaData, ss,dataSourceUrl,userName);
tableInfoMap.put("tableFields",tableFields);
}
listInfos.add(tableInfoMap);
}
closeConnection(conn);
log.info("查询数据源信息处理结束!");
return listInfos;
}
public Connection getDataSourceConn(String dataSourceUrl,String userName,String passWord) throws Exception{
log.info("-------数据源初始化开始------");
Properties pros = new Properties();
pros.setProperty("user",userName);
pros.setProperty("password",passWord);
pros.setProperty("remarks","true");
if (dataSourceUrl.contains("mysql")){
pros.setProperty("useInformationSchema","true");
Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
}else if (dataSourceUrl.contains("oracle")){
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
}else {
throw new Exception("不支持的数据类型");
}
return DriverManager.getConnection(dataSourceUrl,pros);
}
public List<Map<String,String>> getTableList(DatabaseMetaData metaData,String dataSourceUrl,String userName) throws Exception{
log.info("-------获取数据库表 列表开始---------");
List<Map<String,String>> tableInfoList = new ArrayList<>();
String dataBaseName = null;
if (dataSourceUrl.contains("mysql")){
dataBaseName =dataSourceUrl.substring(dataSourceUrl.lastIndexOf("/")+1,dataSourceUrl.indexOf("?"));
userName = null;
}
ResultSet tables = metaData.getTables(dataBaseName, userName, "%", new String[]{"TABLE"});
while (tables.next()){
Map<String,String> tableInfoMap = new HashMap<>();
tableInfoMap.put(tables.getString("TABLE_NAME"),tables.getString("REMARKS"));
tableInfoList.add(tableInfoMap);
}
log.info("-------获取数据库表 列表结束---------");
return tableInfoList;
}
public List<TableInfoQueyVO> getTableFields(DatabaseMetaData metaData,String tableName,String dataSourceUrl,String userName) throws Exception{
List<TableInfoQueyVO> tableFieldList = new ArrayList<>();
String dataBaseName = null;
if (dataSourceUrl.contains("mysql")){
dataBaseName =dataSourceUrl.substring(dataSourceUrl.lastIndexOf("/")+1,dataSourceUrl.indexOf("?"));
userName = null;
}
ResultSet dataColumns = metaData.getColumns(dataBaseName, userName, tableName, "%");
ResultSet keys = metaData.getPrimaryKeys(dataBaseName, null, tableName);
Object entityInstance = this.getEntityInstance(getClassNameList(tableName));
Map<String,String> breakMap = new HashMap<>();
while(null!=dataColumns&&dataColumns.next()){
TableInfoQueyVO tiqv = new TableInfoQueyVO();
if (!StringUtils.isEmpty(breakMap.get(dataColumns.getString("COLUMN_NAME")))){
break;
}
breakMap.put(dataColumns.getString("COLUMN_NAME"),dataColumns.getString("TYPE_NAME"));
if (this.compareFieldType(dataColumns.getString("COLUMN_NAME"),
dataColumns.getString("TYPE_NAME"),entityInstance)){
tiqv.setFieldType(dataColumns.getString("TYPE_NAME"));
}
tiqv.setFieldCode(dataColumns.getString("COLUMN_NAME"));
tiqv.setFieldName(dataColumns.getString("REMARKS"));
String primaryKey = null;
if (null!=keys&&keys.next()){
primaryKey = keys.getString("COLUMN_NAME");
String primaryKeyType = keys.getString("PK_NAME");
if ("PRIMARY".equals(primaryKeyType)&&primaryKey.equals(tiqv.getFieldCode())){
tiqv.setIsKey(primaryKey);
}
}
tableFieldList.add(tiqv);
}
breakMap.clear();
return tableFieldList;
}
public void closeConnection(Connection conn) throws Exception{
if (conn.isClosed()){
log.info("数据源链接已关闭!");
return;
}else {
conn.close();
log.info("数据源链接关闭结束");
}
}
public Object getEntityInstance(List<String> className) throws Exception{
Class<?> aClass;
for (String s : className) {
try {
aClass = Class.forName(s);
} catch (ClassNotFoundException e) {
continue;
}
if (null != aClass){
return aClass.newInstance();
}
}
return null;
}
public List<String> getClassNameList(String tableName){
List<String> classPathList = new ArrayList<>();
classPathList.add("cn.com.yusys.yusp.base.data.domain.entity.");
classPathList.add("cn.com.yusys.yusp.base.engine.domain.entity.");
classPathList.add("cn.com.yusys.yusp.data.domain.entity.");
classPathList.add("cn.com.yusys.yusp.message.entity.");
classPathList.add("cn.com.yusys.yusp.notice.entity.");
classPathList.add("cn.com.yusys.yusp.oca.domain.entity.");
classPathList.add("cn.com.yusys.yusp.strategy.data.domain.entity.");
classPathList.add("cn.com.yusys.yusp.strategy.engine.domain.entity.");
classPathList.add("cn.com.yusys.yusp.strategy.manager.domain.entity.");
classPathList.add("cn.com.yusys.yusp.stream.engine.domain.entity.");
classPathList.add("cn.com.yusys.yusp.stream.manager.domain.entity.");
classPathList.add("cn.com.yusys.yusp.untiy.domain.entity.");
classPathList.add("cn.com.yusys.yusp.warning.data.domain.entity.");
classPathList.add("cn.com.yusys.yusp.warning.engine.domain.entity.");
List<String> classPathContent = new ArrayList<>();
String[] split = tableName.split("_");
String tableNameTmpA = "";
for (String s : split) {
tableNameTmpA = tableNameTmpA+org.apache.commons.lang.StringUtils.capitalize(s);
}
String tableNameTmpB = "";
for (int i = 1; i < split.length; i++) {
tableNameTmpB = tableNameTmpB+org.apache.commons.lang.StringUtils.capitalize(split[i]);
}
for (String s : classPathList) {
classPathContent.add(s+tableNameTmpA+"Entity");
classPathContent.add(s+tableNameTmpA+"Entity");
}
return classPathContent;
}
public boolean compareFieldType(String fieldName,String fieldType,Object entityInstance){
if (org.springframework.util.StringUtils.isEmpty(entityInstance)){
return true;
}
Field[] declaredFields = entityInstance.getClass().getDeclaredFields();
if (org.springframework.util.StringUtils.isEmpty(declaredFields)){
return false;
}
String[] split = fieldName.split("_");
String fieldNameTmp = split[0].toLowerCase();
for (int i = 1; i < split.length; i++) {
fieldNameTmp = fieldNameTmp+org.apache.commons.lang.StringUtils.capitalize(split[i].toLowerCase());
}
for (Field declaredField : declaredFields) {
if (org.springframework.util.StringUtils.isEmpty(declaredField)){
continue;
}
if (StringUtils.isBlank(FieldTypeRef.getRefTypeCode(fieldType))){
return false;
}
if (fieldNameTmp.equals(declaredField.getName())
&&declaredField.getType().getName().contains(FieldTypeRef.getRefTypeCode(fieldType))){
return true;
}
}
return false;
}
public List<Map<String,String>> getTableInfoList(DatabaseMetaData metaData,String dataSourceUrl,String userName) throws Exception{
log.info("-------获取数据库表 列表开始---------");
List<Map<String,String>> tableInfoList = new ArrayList<>();
String dataBaseName = null;
if (dataSourceUrl.contains("mysql")){
dataBaseName =dataSourceUrl.substring(dataSourceUrl.lastIndexOf("/")+1,dataSourceUrl.indexOf("?"));
userName = null;
}
ResultSet tables = metaData.getTables(dataBaseName, userName, "%", new String[]{"TABLE"});
while (tables.next()){
Map<String,String> tableInfoMap = new HashMap<>();
tableInfoMap.put("tableCode",tables.getString("TABLE_NAME"));
tableInfoMap.put("tableName",tables.getString("REMARKS"));
tableInfoList.add(tableInfoMap);
}
log.info("-------获取数据库表 列表结束---------");
return tableInfoList;
}
}
enum FieldTypeRef{
VAR("VARCHAR","String"),
TEXT("TEXT","String"),
INT("INT","Integer"),
DATETIME("DATETIME","Date"),
CHAR("CHAR","String"),
DECIMAL("DECIMAL","BigDecimal"),
FLOAT("FLOAT","float")
;
private String typeCode;
private String refTypeCoe;
public String getTypeCode() {
return typeCode;
}
public void setTypeCode(String typeCode) {
this.typeCode = typeCode;
}
public String getRefTypeCoe() {
return refTypeCoe;
}
public void setRefTypeCoe(String refTypeCoe) {
this.refTypeCoe = refTypeCoe;
}
FieldTypeRef(String typeCode, String refTypeCoe) {
this.typeCode = typeCode;
this.refTypeCoe = refTypeCoe;
}
public static String getRefTypeCode(String key){
for (FieldTypeRef value : FieldTypeRef.values()) {
if (key.equals(value.typeCode)){
return value.getRefTypeCoe();
}
}
return null;
}
}
getTables
检索给定目录中可用表的描述。仅返回与目录、模式、表名称和类型条件匹配的表描述。它们按 TABLE_TYPE、TABLE_CAT、TABLE_SCHEM 和 TABLE_NAME 排序。
参数:
? String catalog: mysql下就是数据库名称,oracle下就是instance名;可以为null,可以为“”。解释参见@param catalog
? String schemaPattern:mysql下就是数据库名称,oracle中就是用户名.解释参见@param schemaPattern
? String tableNamePattern: 数据表名称
? String[] types: 查询的表类型,参考注解中的解释
getColumns
参数
? String catalog: mysql下就是数据库名称,oracle下就是instance名;可以为null,可以为“”。解释参见@param catalog
? String schemaPattern:mysql下就是数据库名称,oracle中就是用户名.解释参见@param schemaPattern
? String tableNamePattern: 数据表名称
? String columnNamePattern: 列名模式,参考注解中的解释
getPrimaryKeys
getIndexInfo
oracle 和mysql 的 catalog,schema
oracle
Oracle:
server instance == database == catalog: all data managed by same execution engine
schema: namespace within database, identical to user account
user == schema owner == named account: identical to schema, who can connect to database, who owns the schema and use objects possibly in other schemas
to identify any object in running server, you need (schema name + object name)
mysql
MySQL:
server instance == not identified with catalog, just a set of databases
database == schema == catalog: a namespace within the server.
user == named account: who can connect to server and use (but can not own - no concept of ownership) objects in one or more databases
to identify any object in running server, you need (database name)
|