数据源管理｜JDBC｜JdbcTemplate｜MybatisPlusGenerator

个人博客：无奈何杨（wnhyang）

个人语雀：wnhyang

共享语雀：在线知识共享

Github：wnhyang - Overview

复杂的项目常常会涉及到多数据源的配置，解决方案也是有很多。但这篇文章不是讲这个的，而是纯粹的数据源管理，通过数据源配置，获取数据库信息、表信息、数据等等，更像是超级简单的数据库管理工具（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 查询所有数据库的示例：

  1. MySQL:
SHOW DATABASES;
  1. PostgreSQL:
SELECT datname FROM pg_database WHERE datistemplate = false;
  1. SQL Server:
SELECT name FROM sys.databases;
  1. Oracle:
SELECT username FROM all_users;
  1. SQLite:
.databases
  1. DB2:
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 字符串外，还有一些其他的字符串可以用来获取不同的列信息。以下是一些常用的列信息对应的字符串：

  1. COLUMN_NAME：列名
  2. TYPE_NAME：列的数据类型名称
  3. COLUMN_SIZE：列的大小（长度）
  4. NULLABLE：列是否允许为空，返回值为 ResultSetMetaData.columnNoNulls、ResultSetMetaData.columnNullable、ResultSetMetaData.columnNullableUnknown 之一
  5. REMARKS：列的注释（备注）
  6. IS_AUTOINCREMENT：列是否是自动增长的，返回值为 "YES" 或 "NO"
  7. IS_GENERATEDCOLUMN：列是否是生成的列（自动生成的），返回值为 "YES" 或 "NO"
  8. 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://158.1.18.149:3306/carbon");
            hikariConfig.setUsername("root");
            hikariConfig.setPassword("hzbank");
            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;

直到我看到这条sql，并去看information_schema.TABLES ，才知道数据库自带的库是有多么重要！

写在最后

拙作艰辛，字句心血，望诸君垂青，多予支持，不胜感激。

个人博客：无奈何杨（wnhyang）

个人语雀：wnhyang

共享语雀：在线知识共享

Github：wnhyang - Overview

posted @ 2024-05-24 11:19  wnhyang  阅读(1)  评论(0)  编辑  收藏  举报