如何通过表名获取所有字段信息

1. 前言

通常情况下, 如果想写一些比较通用的应用程序, 我们需要通过表名去了解整个表的情况, 才能编写一些比较通用的数据库工具.
通过表名获取字段名有多种方式:

• 第一种 我们可以查询系统表或者数据字典来获取表的信息, 其优点是效率比较高, 缺点是所写的工具往往只适用与某种特定的数据库.

• 第二种 通过一些标准的数据库连接库获取数据库元数据, 从而获取到相应表的结构信息, 这样做的优点是由于程序是面向标准库的API,
  无论底层是何种数据库都能够兼容. 在Java领域可以通过Jdbc库提供的接口获取表信息, 在.net领域可以通过odbc, PHP领域可以使用PDO,
  SQLAPI ++ 之于C++, database/sql之于golang, sqlx之于Rust等等

本文主要介绍使用jdbc获取数据库字段信息.

2. 概念讲解

• 数据库驱动
  首先我们要连接数据库, 需要通过数据库驱动来完成.
  什么是数据库驱动? 我们可以设想一下如果没有驱动的情况下, 我们明明知道远程有一台数据库服务器监听在3306或1521或者5432端口上. 如果我们不是数据库专家, 不了解网络连接的细节, 我们往往不得其门而入. 而数据库驱动帮助屏蔽了连接方面的复杂性以及细节, 最终暴露给用户一下简单的接口. 简单来说数据库驱动就是客户端一个库程序帮助开发人员方便的连接到特定的数据库.

  数据库驱动由谁提供?
  一般来说数据库驱动由厂商针对特定的开发语言, 提供特定的驱动程序, 比较数据库厂商才是最了解自家产品的. 但是开发语言有千千万, 厂商不可能面面俱到, 对于一些小众的开发语言, 或者新兴的开发语言, 往往由社区或某些该领域的大牛来提供驱动程序, 以提高该语言在数据库开发领域的占有率. 比如golang领域, 针对oracle的驱动godror就不是Oracle提供的.

• 标准库
  为什么需要数据库标准库?
  相信读到这里答案应该很明显了, 如果没有标准库, 我们需要针对特定的数据库驱动来编程, 这样的结果就是程序的可移植性会非常差, 另外对工具开发者或者第三方库开发者成本会非常高.
  为什么标准库能够得到驱动开发者的支持?
  因为如果驱动开发者不支持标准库, 他们的驱动就得不到更大范围的采用, 导致其数据库产品的流行度不高, 相信这不是数据库厂商也不是驱动开发者想要看到的结果.
  标准库有没有什么缺点?
  前面光讲了标准库的好处, 标准库的缺点也是显而易见的. 由于在驱动和应用程序直接增加了一层抽象, 常常会带来一些性能上的损失. 对于那些极度追求性能的应用程序来说, 有时是无法接受的. 所以往往会看到一些app在经过长期稳定的运行后, 为了调优方面的考虑将标准库这一层部分地去掉的, 因为经过长期的运行, 他们的数据库类型已经非常确定了. 另外一点, 由于标准库的变化或版本升级往往是非常缓慢的, 这会导致数据库厂商在数据库的创新上不能立即被纳入到标准, 而得不到大规模使用. 这会影响数据库厂商创新的积极性以及投资回报. 所以有些应用为了获得创新方面的优势而绕过标准库与驱动耦合在一起. 比如jdbc, odbc都很好地得到了各数据库驱动的支持.

• 数据库连接
  数据库连接通常都是长连接, 如果是短连接的话, 用户每执行一次SQL, 都需要认证鉴权等等操作, 效率会非常地下. 但是一台机能开启的长连接是有限的, 此时就需要用到复用技术, 那就是数据库连接池. 这样程序就不用频繁地创建连接, 节省在正在执行SQL前后的开销.

3. 实现思路

最后回到本文的重点, 如何通过表名获取所有字段信息.

• 首先加载驱动
• 然后通过Jdbc与数据库建立连接
• 通过连接获取到数据库的元数据
• 通过元数据获取表信息

4. 代码示例

在正式写代码之前, 我们需要创建一个gradle项目, 使用gradle来管理依赖包.

gradle init

核心代码如下

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;

public class App {

    public static void main(String[] args) throws Exception {

        try (Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test", "root",
                "password")) {
            // create a Statement
            DatabaseMetaData dbMetaData = conn.getMetaData();
            
            ResultSet tabs = dbMetaData.getTables(null, null, "Test", new String[]{"TABLE"});

            while (tabs.next()) {
                ResultSet resultSet = dbMetaData.getColumns(null, tabs.getString("TABLE_SCHEM"), 
                tabs.getString("TABLE_NAME"), null);
                while (resultSet.next()) {
                    String colName = resultSet.getString("COLUMN_NAME");
                    System.out.println(colName);
                }
            }
        }
    }
}

直接将其封装成一个函数.

我们的输入参数, 是connection和table name, 输出使用由column name数组.

private String[] getColumnsByTableName(Connection conn, String tableNamePattern) throws SQLException {

        DatabaseMetaData dbMetaData = conn.getMetaData();

        ResultSet tabs = dbMetaData.getTables(null, null, tableNamePattern, new String[] { "TABLE" });

        List<String> columnList = new ArrayList<>();
        while (tabs.next()) {
            ResultSet resultSet = dbMetaData.getColumns(null, tabs.getString("TABLE_SCHEM"),
                    tabs.getString("TABLE_NAME"), null);
            while (resultSet.next()) {
                columnList.add(resultSet.getString("COLUMN_NAME"));
            }
        }

        return columnList.toArray(new String[columnList.size()]);
    }

5. 参考文档

DatabaseMetaData类