数据源管理｜JDBC｜JdbcTemplate｜MybatisPlusGenerator

发表于 2024-05-24 更新于 2024-09-14 阅读次数：本文字数： 1.5k 阅读时长 ≈ 5 分钟

个人语雀：wnhyang

复杂的项目常常会涉及到多数据源的配置，解决方案也是有很多。但这篇文章不是讲这个的，而是纯粹的数据源管理，通过数据源配置，获取数据库信息、表信息、数据等等，更像是超级简单的数据库管理工具（Dbeaver、Navicat等）。

这个有什么应用呢？

1、一些后台类系统常需要查数功能，这个就可以用到。有人问了我们有Dbeaver、Navicat还需要这个？确实，有专业的数据库管理工具，这个就显得非常鸡肋了。但是数据库通常是由专业的DBA管理的，账号和权限都是受管控的，而且数据库账号通常无法和后台类系统用户权限关联，所以这个就显得有点用处了。

2、作为外部数据源，有时候系统本身的数据不够用，或者说是有局限性，希望能够接上外部更加丰富数据，这时也是可以应用的。

3、代码生成器

4、等

数据库信息

因为JDBC，数据源的配置包含jdbcUrl、username、password、driverClassName就可以建立数据库的连接了。

数据库类型

数据库类型从jdbcUrl就能确认，可以参考mybatis-plus-generator的com.baomidou.mybatisplus.generator.config;中的DataSourceConfig#getDbType方法。

private DbType getDbType(@NotNull String str) {
    if (str.contains(":mysql:") || str.contains(":cobar:")) {
        return DbType.MYSQL;
    } else if (str.contains(":oracle:")) {
        return DbType.ORACLE;
    } else if (str.contains(":postgresql:")) {
        return DbType.POSTGRE_SQL;
    } else if (str.contains(":sqlserver:")) {
        return DbType.SQL_SERVER;
    } else if (str.contains(":db2:")) {
        return DbType.DB2;
    } else if (str.contains(":mariadb:")) {
        return DbType.MARIADB;
    } else if (str.contains(":sqlite:")) {
        return DbType.SQLITE;
    } else if (str.contains(":h2:")) {
        return DbType.H2;
    } else if (str.contains(":kingbase:") || str.contains(":kingbase8:")) {
        return DbType.KINGBASE_ES;
    } else if (str.contains(":dm:")) {
        return DbType.DM;
    } else if (str.contains(":zenith:")) {
        return DbType.GAUSS;
    } else if (str.contains(":oscar:")) {
        return DbType.OSCAR;
    } else if (str.contains(":firebird:")) {
        return DbType.FIREBIRD;
    } else if (str.contains(":xugu:")) {
        return DbType.XU_GU;
    } else if (str.contains(":clickhouse:")) {
        return DbType.CLICK_HOUSE;
    } else if (str.contains(":sybase:")) {
        return DbType.SYBASE;
    } else {
        return DbType.OTHER;
    }
}

数据库默认库

数据库本身自带有默认的数据库，常用的MySQL有mysql、information_schema等基础库。数据库的默认库也可以参考DataSourceConfig#getDefaultSchema方法。

数据库s

因为数据库SQL语句有差异，所以并非所有SQL都是MySQL的SHOW DATABASES;。

以下是针对一些常见数据库系统的 SQL 查询所有数据库的示例：

MySQL:

1	SHOW DATABASES;

PostgreSQL:

1	SELECT datname FROM pg_database WHERE datistemplate = false;

SQL Server:

1	SELECT name FROM sys.databases;

Oracle:

1	SELECT username FROM all_users;

SQLite:

1	.databases

DB2:

1	SELECT dbname FROM sysibm.sysdummy1;

这些 SQL 查询语句可以在相应的数据库管理系统中执行，以获取所有数据库的列表或名称。需要注意的是，每个数据库系统的系统表或系统视图可能不同，因此查询方式会有所不同。

更多可以chatGPT一下，重写一下不同实现在使用时会更方便。

表信息

在已有数据源配置和数据库信息后，表信息当然也是很轻易就能获取。

表s

mybatis-plus-generator的com.baomidou.mybatisplus.generator.config;中的IDbQuery#tablesSql接口针对不同库有不同实现，可以使用。如下代码就可以查询出所有数据源中一个库的所有表信息。

HikariDataSource ds = new HikariDataSource();
ds.setJdbcUrl(dbConfig.getFullJdbcUrl());
ds.setUsername(dbConfig.getUsername());
ds.setPassword(dbConfig.getPassword());
ds.setDriverClassName(dbConfig.getDriverClassName());
JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);

DataSourceConfig dataSourceConfig = new DataSourceConfig.Builder(dbConfig.getJdbcUrl(), dbConfig.getUsername(), dbConfig.getPassword()).build();
IDbQuery dbQuery = dataSourceConfig.getDbQuery();
List<Map<String, Object>> results = jdbcTemplate.queryForList(dbQuery.tablesSql());
List<TableInfo> tableInfos = Lists.newArrayList();
for (Map<String, Object> table : results) {
    TableInfo tableInfo = new TableInfo();
    tableInfo.setName((String) table.get(dbQuery.tableName()));
    tableInfo.setComment((String) table.get(dbQuery.tableComment()));
    tableInfos.add(tableInfo);
}

表结构信息

方法1

在 JDBC 中，DatabaseMetaData.getColumns()方法返回的 ResultSet 包含了表的列信息，其中列名可以作为参数传递给 ResultSet.getString() 方法。除了 COLUMN_NAME 字符串外，还有一些其他的字符串可以用来获取不同的列信息。以下是一些常用的列信息对应的字符串：

COLUMN_NAME：列名
TYPE_NAME：列的数据类型名称
COLUMN_SIZE：列的大小（长度）
NULLABLE：列是否允许为空，返回值为 ResultSetMetaData.columnNoNulls、ResultSetMetaData.columnNullable、ResultSetMetaData.columnNullableUnknown之一
REMARKS：列的注释（备注）
IS_AUTOINCREMENT：列是否是自动增长的，返回值为 "YES" 或 "NO"
IS_GENERATEDCOLUMN：列是否是生成的列（自动生成的），返回值为 "YES" 或 "NO"
COLUMN_DEF：列的默认值

这些字符串可以作为 ResultSet 的 getXXX() 方法的参数，用于获取相应的列信息。例如，ResultSet.getString("COLUMN_NAME") 获取列名，ResultSet.getString("TYPE_NAME")获取列的数据类型名称，以此类推。

ResultSet columnsResultSet = metaData.getColumns(null, null, tableName, null);
while (columnsResultSet.next()) {
    String columnName = columnsResultSet.getString("COLUMN_NAME");
    String columnType = columnsResultSet.getString("TYPE_NAME");
    String columnComment = columnsResultSet.getString("REMARKS");

    System.out.println("Column Name: " + columnName);
    System.out.println("Column Type: " + columnType);
    System.out.println("Column Comment: " + columnComment);
    System.out.println();
}

方法2

好吧，又是它mybatis-plus-generator的com.baomidou.mybatisplus.generator.config;中的IDbQuery#tableFieldsSql可以确认数据库表结构，而且有不同实现，用起来更方便。

不确定表结构情况下查询数据

以下仅作参考

public class Main {

    private static final DataSource dataSource;

    private static final ThreadLocal<Connection> threadLocal = new ThreadLocal<>();

    private static final String SQL_QUERY = "SELECT * FROM {}";

    static {
        try {
            HikariConfig hikariConfig = new HikariConfig();
            hikariConfig.setJdbcUrl("jdbc:mysql://ip:3306/carbon");
            hikariConfig.setUsername("root");
            hikariConfig.setPassword("123456");
            hikariConfig.setDriverClassName("com.mysql.cj.jdbc.Driver");
            dataSource = new HikariDataSource(hikariConfig);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }

    public static Connection getConnection() {
        try {
            Connection connection = threadLocal.get();
            if (connection == null) {
                connection = dataSource.getConnection();
                threadLocal.set(connection);
            }
            return connection;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static void close() {
        try {
            Connection connection = threadLocal.get();
            if (connection != null) {
                threadLocal.remove();
                connection.setAutoCommit(true);
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static void main(String[] args) throws SQLException {
        Connection connection = getConnection();

        String totalCreditsQuery = StrUtil.format(SQL_QUERY, "total_credits");

        PreparedStatement preparedStatement = connection.prepareStatement(totalCreditsQuery);
        ResultSet resultSet = preparedStatement.executeQuery();

        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();

        for (int i = 1; i <= columnCount; i++) {
            String columnName = metaData.getColumnName(i);
            System.out.print(columnName + "\t");
        }
        System.out.println();

        while (resultSet.next()) {
            for (int i = 1; i <= columnCount; i++) {
                System.out.print(resultSet.getString(i) + "\t");
            }
            System.out.println();
        }

        close();
    }
}

表数据/索引容量

SELECT
	table_schema AS '数据库',
	table_name AS '表名',
	table_rows AS '记录数',
	TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '数据容量(MB)',
	TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)' 
FROM
	information_schema.TABLES 
ORDER BY
	data_length DESC,
	index_length DESC;