当前位置： 首页 > article >正文

Mysql,SqlServer,Oracle获取库名 表名 列名

article 2024/11/16 15:33:13

先看下需求背景：

获取某个数据源连接下所有库名，库下所有表名，表中所有字段

1.MySql

先说MySql吧，最简单

1.1获得所有数据库库名

这是一个mysql和sqlserver公用的方法，这里url不用担心数据库问题，他其实用的是ip,端口号，用户名，密码 这些信息就行，但是因为url上没有数据库名字的话无法建立连接，所以url上就不得不带着。

实际上这个方法可以获取到该连接下的所有数据库

jdbc:sqlserver://127.0.0.1:3306;databaseName=master;encrypt=true;trustServerCertificate=true

jdbc:mysql://127.0.0.1:3306/test

 private static final String TABLE_CAT = "TABLE_CAT";

    /**
     * 获取连接下所有的数据库名称  mysql和sqlserver可用
     *
     * @param url
     * @param user
     * @param password
     * @return
     */
    public static List<String> getDBNames(String url, String user, String password) {
        Connection conn = null;
        List<String> list = new ArrayList<>();
        try {
            conn = DriverManager.getConnection(url, user, password);
            DatabaseMetaData metadata = conn.getMetaData();
            ResultSet resultSet = metadata.getCatalogs();
            while (resultSet.next()) {
                String databaseName = resultSet.getString(TABLE_CAT);
                list.add(databaseName);
                System.out.println(databaseName);
            }
            return list;
        } catch (SQLException e) {
            log.error(e.getMessage());
        } finally {
            //finally中关闭连接
            if (Objects.nonNull(conn)) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
        }
        return list;
    }

1.2 获取某个库下所有表名

这个方法也不用替换url中的数据库名，但是需要把数据库名作为参数上传。

因为查询sql中用到了数据库名

SELECT table_name FROM information_schema.tables WHERE table_schema = 
 private static final String TABLE_NAME = "TABLE_NAME";   
 /**
     * 获取数据库下所有表名  mysql sqlserver使用
     *
     * @param url
     * @param user
     * @param password
     * @param dbName
     * @return
     */
    public static List<String> getMysqlTableNames(String url, String user, String password, String dbName) {
        Connection conn = null;
        Statement statement = null;
        ResultSet resultSet = null;
        List<String> tableNames = new ArrayList<>();
        StringBuilder stringBuilder = new StringBuilder();
        String query = stringBuilder.append("SELECT table_name FROM information_schema.tables WHERE table_schema = ").append("'").append(dbName).append("'").toString();
        try {
            conn = DriverManager.getConnection(url, user, password);
            statement = conn.createStatement();
            resultSet = statement.executeQuery(query);
            while (resultSet.next()) {
                String tableName = resultSet.getString(TABLE_NAME);
                tableNames.add(tableName);
            }
        } catch (SQLException e) {
            log.error(e.getMessage());
        } finally {
            //finally中关闭连接
            try {
                if (Objects.nonNull(conn)) {
                    conn.close();
                }
                if (Objects.nonNull(statement)) {
                    statement.close();
                }
                if (Objects.nonNull(resultSet)) {
                    resultSet.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return tableNames;
    }

1.3 获取表中的所有列名

这个很简单，就执行个select * from 就可以

但是注意 url需要替换 现在新选择的库名是什么，那就把url中的databaseName替换为新选择的

这也是一个mysql和sqlserver可以公用的

但是sqlserver有一点需要注意，就是最后一个参数 tableName 需要上传为 模式名.表名

因为对于sql来说正常执行查询sql的时候 也要 模式名.表名 查询

select * from dbo.spt_fallback_db
    /**
     * 获取表下所有字段  其中url记得把前端新传来库的替换
     *
     * @param url
     * @param user
     * @param password
     * @param tableName
     * @return
     * @throws SQLException
     */
    public static List<String> getColumns(String url, String user, String password, String tableName) {
        //用新的dbName对连接上的dbName进行替换
        List<String> columns = new ArrayList<>();
        Connection conn = null;
        Statement statement = null;
        ResultSet resultSet = null;
        StringBuilder stringBuilder = new StringBuilder();
        String query = stringBuilder.append("select * from ").append(tableName).append(" where 1!=1").toString();
        try {
            conn = DriverManager.getConnection(url, user, password);
            statement = conn.createStatement();
            resultSet = statement.executeQuery(query);
            ResultSetMetaData metaData = resultSet.getMetaData();
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                String columnName = metaData.getColumnName(i);
                columns.add(columnName);
            }
        } catch (SQLException e) {
            log.error(e.getMessage());
        } finally {
            //finally中关闭连接
            try {
                if (Objects.nonNull(conn)) {
                    conn.close();
                }
                if (Objects.nonNull(statement)) {
                    statement.close();
                }
                if (Objects.nonNull(resultSet)) {
                    resultSet.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return columns;
    }

2.SqlServer

2.1获得所有数据库名

直接看1.1就行 跟mysql公用一个

2.2获得某个库下所有表名

这个url中的数据库名字需要替换为新选择的

而且返回方式，是 模式名.表名 的方式返回的，以便之后查询使用

select * from new_schema.table_name

获得所有表名的sql 

SELECT s.name AS schema_name,t.name AS table_name 
FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    private static final String SCHEMA_NAME = "SCHEMA_NAME";

    private static final String COLUMN_NAME = "column_name";

    /**
     * 获取数据库下所有表名 sqlserver使用 url需要替换
     *
     * @param url
     * @param user
     * @param password
     * @return
     */
    public static List<String> getSqlServerTableNames(String url, String user, String password) {
        Connection conn = null;
        Statement statement = null;
        ResultSet resultSet = null;
        List<String> tableNames = new ArrayList<>();
        String query = "SELECT s.name AS schema_name,t.name AS table_name FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id";
        try {
            conn = DriverManager.getConnection(url, user, password);
            statement = conn.createStatement();
            resultSet = statement.executeQuery(query);
            while (resultSet.next()) {
                String schemaName = resultSet.getString(SCHEMA_NAME);
                String tableName = resultSet.getString(TABLE_NAME);
                //sqlserver查询需要用模式名.表名 如select * from new_schema.table_name 所以拼接返回 存储时也是拼接好的存起来new_schema.table_name
                tableNames.add(schemaName.concat(Constant.POINT).concat(tableName));
            }
        } catch (SQLException e) {
            log.error(e.getMessage());
        } finally {
            //finally中关闭连接
            try {
                if (Objects.nonNull(conn)) {
                    conn.close();
                }
                if (Objects.nonNull(statement)) {
                    statement.close();
                }
                if (Objects.nonNull(resultSet)) {
                    resultSet.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        return tableNames;
    }

2.3获得表中所有列名

直接看1.3就行，跟mysql公用一个

注意点跟Mysql也一样，url中数据库名字要替换为新选择的

还有就是 sqlserver 要 模式名.表名

3.Oracle

oracle真的是一个特别的存在

oracle连接url中的长得像库名的东西，其实不是库名，是SID，他俩不是一个含义

ORACLE_SID、实例名和数据库名的区别及查看方法_oracle数据库名称和sid的关系-CSDN博客

jdbc:oracle:thin:@127.0.0.1:3306:helowin

 

还有就是对于oracle来说你用dbeaver这个连接工具操作更好一点，别用datagrip了

对于oracle来说新建数据库其实就是新建用户，建好了是要授权，才能生效

授权 

Oracle 创建用户详解（create user）-CSDN博客

3.1获得所有库名

了解好了前边的那些知识点，再来进行接下来的操作

    /**
     * 获得oracle下所有数据库名称
     *
     * @param url
     * @param user
     * @param password
     * @return
     */
    public static List<String> getOracleDBNames(String url, String user, String password) throws Exception {
        Connection conn = null;
        List<String> list = new ArrayList<>();
        try {
            conn = DriverManager.getConnection(url, user, password);
            ResultSet schemas = conn.getMetaData().getSchemas();
            while (schemas.next()) {//如果对象中有数据，就会循环打印出来
                String dbName = schemas.getString("TABLE_SCHEM");
                log.info(dbName);
                list.add(dbName);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new Exception("oracle数据库连接信息有误，请检查数据源");
        } finally {
            //finally中关闭连接
            if (Objects.nonNull(conn)) {
                conn.close();
            }
        }
    }

这个我找了好久，附上源码截图

把源码里边的sql粘出来执行，附上执行结果截图

所以其实你用

resultSet.getString(TABLE_SHEM);

也是可以取到库名的

3.2获得某个库下所有表名

 前边说了，对于oracle来说，新建库其实就是新建用户（上边有DBeaver截图为证）

所以想要查库下所有表，就要用owner

select TABLE_NAME from all_tables WHERE owner=

而且url中库名也不用替换为新的，因为sql中会用新的库名 

 private static final String TABLE_NAME = "TABLE_NAME";

    /**
     * 获取oracle指定库下的所有表名
     *
     * @param url
     * @param user
     * @param password
     * @return
     */
    public static List<String> getOracleTableNames(String url, String user, String password, String dbName) {
        Connection conn = null;
        List<String> list = new ArrayList<>();
        Statement stmt = null;
        ResultSet resultSet = null;
        try {
            conn = DriverManager.getConnection(url, user, password);
            stmt = conn.createStatement();
            //dba权限才能执行Sselect TABLE_NAME from all_tables WHERE owner="+dbName  在没有那么高权限，且数据库连接建立成功的情况下，只返回数据源那里配置的数据库
            StringBuilder stringBuilder = new StringBuilder();
            String string = stringBuilder.append("select TABLE_NAME from all_tables WHERE owner=").append("'").append(dbName).append("'").toString();
            resultSet = stmt.executeQuery(string);
            //有dba权限情况下才会执行这部
            while (resultSet.next()) {//如果对象中有数据，就会循环打印出来
                String tableName = resultSet.getString(TABLE_NAME);
                log.info(tableName);
                list.add(tableName);
            }
            return list;
        } catch (SQLException e) {
            //select TABLE_NAME from all_tables WHERE owner="+dbName 这个语句可能没有权限执行
            log.error(e.getMessage());
            return list;
        } finally {
            //finally中关闭连接
            try {
                if (Objects.nonNull(conn)) {
                    conn.close();
                }
                if (Objects.nonNull(stmt)) {
                    stmt.close();
                }
                if (Objects.nonNull(resultSet)) {
                    resultSet.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

3.3获取表中所有列名

这里url也不用替换库名，但是方法查询要上传新的库名和表名

SELECT COLUMN_NAME  FROM ALL_TAB_COLUMNS WHERE owner= 
AND table_name=

    /**
     * 获取oracle指定库表下的所有列名
     *
     * @param url
     * @param user
     * @param password
     * @return
     */
    public static List<String> getOracleColumNames(String url, String user, String password, String dbName, String tableName) {
        Connection conn = null;
        List<String> list = new ArrayList<>();
        Statement stmt = null;
        ResultSet resultSet = null;
        try {
            conn = DriverManager.getConnection(url, user, password);
            stmt = conn.createStatement();
            StringBuilder stringBuilder = new StringBuilder();
            //通过库名表名获取到列名
            String string = stringBuilder.append("SELECT COLUMN_NAME  FROM ALL_TAB_COLUMNS WHERE owner=").append("'").append(dbName).append("'")
                    .append(" AND table_name=").append("'").append(tableName).append("'").toString();
            resultSet = stmt.executeQuery(string);
            //有dba权限情况下才会执行这部
            while (resultSet.next()) {//如果对象中有数据，就会循环打印出来
                String columName = resultSet.getString(COLUMN_NAME);
                log.info(columName);
                list.add(columName);
            }
            return list.stream().distinct().collect(Collectors.toList());
        } catch (SQLException e) {
            //select TABLE_NAME from all_tables WHERE owner="+dbName 这个语句可能没有权限执行
            log.error(e.getMessage());
            return list;
        } finally {
            //finally中关闭连接
            try {
                if (Objects.nonNull(conn)) {
                    conn.close();
                }
                if (Objects.nonNull(stmt)) {
                    stmt.close();
                }
                if (Objects.nonNull(resultSet)) {
                    resultSet.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }


http://www.kler.cn/a/106707.html

相关文章：

  • 图像处理之cornerdetection(角点检测)综述
  • 《动手学深度学习》中d2l库的安装以及问题解决
  • FreeRTOS的列表与列表项
  • Queuing 表（buffer表）的优化实践 | OceanBase 性能优化实践
  • AI风向标｜算力与通信的完美融合，SRM6690解锁端侧AI的智能密码
  • 入侵排查之Linux
  • 配置VUE环境过程中 npm报错的处理方案以及VUE环境搭建过程
  • 线扫相机DALSA--采集卡Base模式设置
  • 工控安全与网络安全有什么不同？
  • 在Win11上部署ChatGLM2-6B详细步骤--（上）准备工作
  • 算法通关村第三关-白银挑战双指针思想
  • 操作系统缓冲区管理（单缓冲，双缓冲，循环缓冲，缓冲池）
  • SpringMVC(四）域对象共享数据
  • Django 全局配置 settings 详解
  • 730. 机器人跳跃问题--二分
  • 【报错】java.sql.SQLException: Invalid utf8mb3 character string: ‘ACED00‘
  • Fourier分析导论——第1章——Fourier分析的起源(E.M. Stein R. Shakarchi)
  • vue 使用openlayers导出渲染的地图
  • 在ffmpeg中，网络视频流h264为什么默认的转为YUV而不是其他格式
  • 工业4.0的安全挑战与解决方案
  • IDEA配置HTML和Thymeleaf热部署开发
  • yo！这里是进程间通信
  • Redis 的优势
  • mysql冷拷贝大表
  • （PyTorch）PyTorch中的常见运算（*、@、Mul、Matmul）
  • 如何使用SHC对Shell脚本进行封装和源码隐藏