关于通过JDBC连接MySQL、ORACLE、TIDB等数据库获取数据库表信息及注释信息的问题

慕木兮人可

已于 2023-04-12 16:00:57 修改

阅读量1k

点赞数

分类专栏： Java 文章标签：数据库 mysql oracle tidb JDBC

于 2023-04-12 13:05:28 首次发布

本文链接：https://blog.csdn.net/qq_38616503/article/details/130103424

版权

Java 专栏收录该内容

45 篇文章 3 订阅

订阅专栏

一、一般网上说的办法

1.1 url拼接useInformationSchema=true

connectionURL="jdbc:mysql://127.0.0.1:3306/test?characterEncoding=UTF-8&useInformationSchema=true"

1.2设置连接属性

Properties props =newProperties();
props.put("remarksReporting","true");//Oracle
props.put("useInformationSchema","true")
dbConn = DriverManager.getConnection(url, props);
DatabaseMetaData dbmd = dbConn.getMetaData();

注意：以上两种方法貌似是不行滴，debug追进源码
在这里插入图片描述

new Field(“”, “REMARKS”, this.metadataCollationIndex, this.metadataEncoding, MysqlType.VARCHAR, 0)
上述返回获取表元数据的时候已经将表注释长度设置成 0

    protected ColumnDefinition createTablesFields() {
        Field[] fields = new Field[]{new Field("", "TABLE_CAT", this.metadataCollationIndex, this.metadataEncoding, MysqlType.VARCHAR, 255), new Field("", "TABLE_SCHEM", this.metadataCollationIndex, this.metadataEncoding, MysqlType.VARCHAR, 0), new Field("", "TABLE_NAME", this.metadataCollationIndex, this.metadataEncoding, MysqlType.VARCHAR, 255), new Field("", "TABLE_TYPE", this.metadataCollationIndex, this.metadataEncoding, MysqlType.VARCHAR, 5), new Field("", "REMARKS", this.metadataCollationIndex, this.metadataEncoding, MysqlType.VARCHAR, 0), new Field("", "TYPE_CAT", this.metadataCollationIndex, this.metadataEncoding, MysqlType.VARCHAR, 0), new Field("", "TYPE_SCHEM", this.metadataCollationIndex, this.metadataEncoding, MysqlType.VARCHAR, 0), new Field("", "TYPE_NAME", this.metadataCollationIndex, this.metadataEncoding, MysqlType.VARCHAR, 0), new Field("", "SELF_REFERENCING_COL_NAME", this.metadataCollationIndex, this.metadataEncoding, MysqlType.VARCHAR, 0), new Field("", "REF_GENERATION", this.metadataCollationIndex, this.metadataEncoding, MysqlType.VARCHAR, 0)};
        return new DefaultColumnDefinition(fields);
    }

二、我的解决办法

/**
     * 从Connection构造表信息，用于获取表名
     */
    public List<Map<String, String>> assembleTableMeta(Connection conn) throws SQLException {
        List<Map<String, String>> tableMetaList = Lists.newArrayList();
        ResultSet tables = conn.getMetaData()
                .getTables(conn.getCatalog(), null, null, new String[]{"TABLE", "VIEW", ""});
        while (tables.next()) {
            Map<String, String> mp = Maps.newHashMap();
            mp.put("tableName", tables.getString("TABLE_NAME"));
            mp.put("tableType", tables.getString("TABLE_TYPE"));
            String tableSchema = conn.getCatalog();
            String table = mp.get("tableName");
            String sql = "SELECT TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '"+tableSchema+"' AND TABLE_NAME = '"+table+"'";
            ResultSet rs = conn.createStatement().executeQuery(sql);
            String tableRemark = null;
            while (rs.next()) {
                tableRemark = rs.getString("TABLE_COMMENT");
            }
            if (StrUtil.isNotEmpty(tableRemark)){
                mp.put("tableComment", tableRemark);
            }else {
                mp.put("tableComment", "该表无注释,需手动输入");
            }
            tableMetaList.add(mp);
        }
        return tableMetaList;
    }

附工具类：

package com.juhe.plugin.mysql.database.meta;

import cn.hutool.core.util.StrUtil;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.juhe.plugin.mysql.bo.DatabaseBO;
import com.juhe.plugin.mysql.constant.PontusConstant;
import com.juhe.plugin.mysql.database.connect.DBConnectionManager;
import com.juhe.plugin.mysql.utils.JdbcTypeUtils;
import com.juhe.plugin.mysql.utils.SQLParserUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * 元数据管理
 */
@Component
@Slf4j
public class DBMetaManager {

    @Autowired
    DBConnectionManager dbConnectionManager;

    /**
     * 优先从缓存获取元数据信息，如果未获取到，从对应连接池中拿连接进行重新取值
     */
    public List<Map<String, String>> getTableMeta(DatabaseBO databaseBo) {
        List<Map<String, String>> tableMetas = null;
        Connection conn = null;
        try {
            conn = dbConnectionManager.getConnection(databaseBo);
            tableMetas = assembleTableMeta(conn);
        } catch (Exception e) {
            log.error("构造表元数据时出错，{}", e);
        } finally {
            dbConnectionManager.close(conn);
        }
        return tableMetas;
    }

    /**
     * 优先从缓存获取元数据信息，如果未获取到，从对应连接池中拿连接进行重新取值
     */
    public List<Map<String, Object>> getColumnMeta(DatabaseBO databaseBo, String tableName) {
        List<Map<String, Object>> columnMetas = null;
        Connection conn = null;
        try {
            conn = dbConnectionManager.getConnection(databaseBo);
            columnMetas = assembleColumnMeta(conn, tableName);
        } catch (Exception e) {
            log.error("构造表元数据时出错，{}", e);
        } finally {
            dbConnectionManager.close(conn);
        }
        return columnMetas;
    }

    /**
     * 从Connection构造表信息，用于获取表名
     */
    public List<Map<String, String>> assembleTableMeta(Connection conn) throws SQLException {
        List<Map<String, String>> tableMetaList = Lists.newArrayList();
        ResultSet tables = conn.getMetaData()
                .getTables(conn.getCatalog(), null, null, new String[]{"TABLE", "VIEW", ""});
        while (tables.next()) {
            Map<String, String> mp = Maps.newHashMap();
            mp.put("tableName", tables.getString("TABLE_NAME"));
            mp.put("tableType", tables.getString("TABLE_TYPE"));
            String tableSchema = conn.getCatalog();
            String table = mp.get("tableName");
            String sql = "SELECT TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '"+tableSchema+"' AND TABLE_NAME = '"+table+"'";
            ResultSet rs = conn.createStatement().executeQuery(sql);
            String tableRemark = null;
            while (rs.next()) {
                tableRemark = rs.getString("TABLE_COMMENT");
            }
            if (StrUtil.isNotEmpty(tableRemark)){
                mp.put("tableComment", tableRemark);
            }else {
                mp.put("tableComment", "该表无注释,需手动输入");
            }
            tableMetaList.add(mp);
        }
        return tableMetaList;
    }

    /**
     * 从Connection构造表信息
     */
    public List<Map<String, Object>> assembleColumnMeta(Connection conn, String tableName) throws SQLException {
        List<Map<String, Object>> tableMetaList = Lists.newArrayList();
        DatabaseMetaData nativeMeta = conn.getMetaData();
        //表类型。 以下或特定于提供程序的值之一： "ALIAS"、"表"、"同义词"、"系统表"、"视图"、"全局临时"、"本地临时" 或 "系统视图"。
        ResultSet tables = nativeMeta.getTables(conn.getCatalog(), null, tableName, new String[]{"TABLE", "VIEW"});
        while (tables.next()) {
            String realTableName = tables.getString("TABLE_NAME");
            //获取列名时对表名进行过滤
            if (!tableName.equalsIgnoreCase(realTableName)) {
                continue;
            }

            ResultSet columns = nativeMeta.getColumns(conn.getCatalog(), null, realTableName, null);
            while (columns.next()) {
                if (PontusConstant.list().contains(columns.getString("TYPE_NAME").toUpperCase()) || String.valueOf(columns.getString("TYPE_NAME")).toUpperCase().contains(PontusConstant.BLOB)) {
                    continue;
                }
                Map<String, Object> mp = Maps.newHashMap();
                mp.put("columnName", columns.getString("COLUMN_NAME"));
                mp.put("columnType", columns.getString("TYPE_NAME"));
                mp.put("columnComment", columns.getString("REMARKS"));
                mp.put("dataType", columns.getInt("DATA_TYPE"));
                mp.put("columnLength", columns.getInt("COLUMN_SIZE"));
                mp.put("paramType", SQLParserUtils.paramTypeMapping(columns.getString("TYPE_NAME")));
                // 增加 PlaceHolder
                String placeHolder = "请输入参数值";
                if (JdbcTypeUtils.isDate(columns.getInt("DATA_TYPE"))) {
                    placeHolder = "请输入日期类型参数";
                }
                if (JdbcTypeUtils.isNumber(columns.getInt("DATA_TYPE"))) {
                    placeHolder = "请输入数字类型参数";
                }
                if (JdbcTypeUtils.isString(columns.getInt("DATA_TYPE"))) {
                    placeHolder = "请输入字符类型参数";
                }
                mp.put("placeHolder", placeHolder);

                tableMetaList.add(mp);
            }
        }
        return tableMetaList;
    }

}