Java面向对象系列[v1.0.0][分析数据库信息]

待我称王封你为后i 2022-12-11 13:44 111阅读 0赞

## 使用DatabaseMetaData分析数据库元数据 ##

在某些时候程序需要动态地获取数据的相关信息，例如数据库里的数据表信息、列信息，此外如果希望在程序中动态地利用底层数据库所提供的特殊功能，则都需要动态分析数据库相关信息

*  JDBC提供了DatabaseMetaData来封装数据库连接对应数据库的信息，从过connection提供的getMetaData()方法就可以获取数据库对应的DatabaseMetaData对象
 *  DatabaseMetaData接口通常有驱动程序提供实现，该接口的目的时发现如何处理底层数据库，尤其是对于需要处理多个数据库的应用程序，在多个数据库之间切换则必须利用该接口来找出底层数据库的功能，例如调用supportsCorrelatedSubqueries()方法查看是否可以使用关联子查询，调用supportsBatchUpdates()方法查看是否可以使用批量更新
 *  许多DatabaseMetaData方法以ResultSet对象的形式返回查询信息，然后使用ResultSet的常规方法例如getString()和getInt()即可从这些ResultSet对象中获取数据；如果查询信息不可用，则将返回一个空ResultSet对象
 *  DatabaseMetaData的很多方法都需要传入一个xxxPattern模式字符串，也就是百分号代表任意多个字符，下划线代表一个字符，如果把该模式字符串的参数设置为null，则表明该参数不作为过滤条件

import java.sql.*;
    import java.util.*;
    import java.io.*;
    
    public class DatabaseMetaDataTest
    { 
    	private String driver;
    	private String url;
    	private String user;
    	private String pass;
    	public void initParam(String paramFile) throws Exception
    	{ 
    		// 使用Properties类来加载属性文件
    		var props = new Properties();
    		props.load(new FileInputStream(paramFile));
    		driver = props.getProperty("driver");
    		url = props.getProperty("url");
    		user = props.getProperty("user");
    		pass = props.getProperty("pass");
    	}
    	public void info() throws Exception
    	{ 
    		// 加载驱动
    		Class.forName(driver);
    		try (
    			// 获取数据库连接
    			Connection conn = DriverManager.getConnection(url, user, pass))
    		{ 
    			// 获取的DatabaseMetaData对象
    			DatabaseMetaData dbmd = conn.getMetaData();
    			// 获取MySQL支持的所有表类型
    			ResultSet rs = dbmd.getTableTypes();
    			System.out.println("--MySQL支持的表类型信息--");
    			printResultSet(rs);
    			// 获取当前数据库的全部数据表
    			rs = dbmd.getTables("select_test", null, "%", new String[]{ "TABLE"});
    			System.out.println("--当前数据库里的数据表信息--");
    			printResultSet(rs);
    			// 获取student_table表的主键
    			rs = dbmd.getPrimaryKeys("select_test", null, "student_table");
    			System.out.println("--student_table表的主键信息--");
    			printResultSet(rs);
    			// 获取当前数据库的全部存储过程
    			rs = dbmd.getProcedures("select_test", null, "%");
    			System.out.println("--当前数据库里的存储过程信息--");
    			printResultSet(rs);
    			// 获取teacher_table表和student_table之间的外键约束
    			rs = dbmd.getCrossReference("select_test", null, "teacher_table",
    				null, null, "student_table");
    			System.out.println("--teacher_table表和student_table之间"
    				+ "的外键约束--");
    			printResultSet(rs);
    			// 获取student_table表的全部数据列
    			rs = dbmd.getColumns("select_test", null, "student_table", "%");
    			System.out.println("--student_table表的全部数据列--");
    			printResultSet(rs);
    		}
    	}
    	public void printResultSet(ResultSet rs) throws SQLException
    	{ 
    		ResultSetMetaData rsmd = rs.getMetaData();
    		// 打印ResultSet的所有列标题
    		for (var i = 0; i < rsmd.getColumnCount(); i++)
    		{ 
    			System.out.print(rsmd.getColumnName(i + 1) + "\t");
    		}
    		System.out.print("\n");
    		// 打印ResultSet里的全部数据
    		while (rs.next())
    		{ 
    			for (var i = 0; i < rsmd.getColumnCount(); i++)
    			{ 
    				System.out.print(rs.getString(i + 1) + "\t");
    			}
    			System.out.print("\n");
    		}
    		rs.close();
    	}
    	public static void main(String[] args) throws Exception
    	{ 
    		var dt = new DatabaseMetaDataTest();
    		dt.initParam("mysql.ini");
    		dt.info();
    	}
    }

这段代码通过DatabaseMetaData分析了当前Connection链接对应数据库的一些基本信息，包括当前数据库包含多少数据表，存储过程，student\_table表的数据列、主键、外键等信息

--------------------

set CLASSPATH=%CLASSPATH%;../mysql-connector-java-8.0.13.jar
    java DatabaseMetaDataTest
    cmd

## 使用系统表分析数据库信息 ##

除了使用DatabaseMetaData来分析底层数据库信息之外，如果已经确定应用程序所使用的数据库系统，则可以通过数据库的系统表来分析数据库信息  
系统表又称为数据字典，数据字典的数据通常由数据库系统负责维护，用户通常只能查询数据字典，而不能修改数据字典内容

> 几乎所有的数据库都会提供系统表供用户查询，用户可以通过查询系统表来获得数据库的相关信息，对于MySQL和SQL Server这样的数据库，他们还提供了一个系统数据库来存储这些系统表

MySQL数据库使用information\_schema数据库来保存系统表，常用系统表：

*  tables：存放数据库里所有数据表的信息
 *  schemata：存放数据库里所有数据库的实例信息
 *  views：存放数据库里所有视图的信息
 *  columns：存放数据库里所有列的信息
 *  triggers：存放数据库里所有触发器的信息
 *  routines：存放数据库里所有存储过程和函数的信息
 *  key\_column\_usage：存放数据库里所有具有约束的键信息
 *  table\_constraints：存放数据库里全部约束的表信息
 *  statistics：存放数据库里全部索引的信息

mysql> select * from tables where table_schema = 'mysql';
    +---------------+--------------+---------------------------+------------+--------+---------+------------+------------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+--------------------+-----------------------------------------+
    | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME                | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH    | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME         | UPDATE_TIME         | CHECK_TIME | TABLE_COLLATION   | CHECKSUM | CREATE_OPTIONS     | TABLE_COMMENT                           |
    +---------------+--------------+---------------------------+------------+--------+---------+------------+------------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+--------------------+-----------------------------------------+
    | def           | mysql        | columns_priv              | BASE TABLE | MyISAM |      10 | Fixed      |          0 |              0 |           0 | 241505530017742847 |         4096 |         0 |           NULL | 2020-09-22 23:19:42 | 2020-09-22 23:19:42 | NULL       | utf8_bin          |     NULL |                    | Column privileges                       |
    | def           | mysql        | db                        | BASE TABLE | MyISAM |      10 | Fixed      |          2 |            488 |         976 | 137359788634800127 |         5120 |         0 |           NULL | 2020-09-22 23:19:41 | 2020-09-22 23:19:52 | NULL       | utf8_bin          |     NULL |                    | Database privileges                     |
    | def           | mysql        | engine_cost               | BASE TABLE | InnoDB |      10 | Dynamic    |          2 |           8192 |       16384 |                  0 |            0 |         0 |           NULL | 2020-09-22 23:19:45 | NULL                | NULL       | utf8_general_ci   |     NULL | stats_persistent=0 |                                         |
    | def           | mysql        | event                     | BASE TABLE | MyISAM |      10 | Dynamic    |          0 |              0 |           0 |    281474976710655 |         2048 |         0 |           NULL | 2020-09-22 23:19:44 | 2020-09-22 23:19:44 | NULL       | utf8_general_ci   |     NULL |                    | Events                                  |
    | def           | mysql        | func                      | BASE TABLE | MyISAM |      10 | Fixed      |          0 |              0 |           0 | 162974011515469823 |         1024 |         0 |           NULL | 2020-09-22 23:19:41 | 2020-09-22 23:19:41 | NULL       | utf8_bin          |     NULL |                    | User defined functions                  |
    | def           | mysql        | general_log               | BASE TABLE | CSV    |      10 | Dynamic    |          2 |              0 |           0 |                  0 |            0 |         0 |           NULL | NULL                | NULL                | NULL       | utf8_general_ci   |     NULL |                    | General log                             |
    | def           | mysql        | gtid_executed             | BASE TABLE | InnoDB |      10 | Dynamic    |          0 |              0 |       16384 |                  0 |            0 |         0 |           NULL | 2020-09-22 23:19:45 | NULL                | NULL       | latin1_swedish_ci |     NULL |                    |                                         |
    | def           | mysql        | help_category             | BASE TABLE | InnoDB |      10 | Dynamic    |         43 |            381 |       16384 |                  0 |        16384 |         0 |           NULL | 2020-09-22 23:19:42 | NULL                | NULL       | utf8_general_ci   |     NULL | stats_persistent=0 | help categories                         |
    | def           | mysql        | help_keyword              | BASE TABLE | InnoDB |      10 | Dynamic    |       1045 |            109 |      114688 |                  0 |       114688 |         0 |           NULL | 2020-09-22 23:19:42 | NULL                | NULL       | utf8_general_ci   |     NULL | stats_persistent=0 | help keywords                           |
    | def           | mysql        | help_relation             | BASE TABLE | InnoDB |      10 | Dynamic    |       1678 |             58 |       98304 |                  0 |            0 |         0 |           NULL | 2020-09-22 23:19:42 | NULL                | NULL       | utf8_general_ci   |     NULL | stats_persistent=0 | keyword-topic relation                  |
    | def           | mysql        | help_topic                | BASE TABLE | InnoDB |      10 | Dynamic    |        833 |           1907 |     1589248 |                  0 |        98304 |   4194304 |           NULL | 2020-09-22 23:19:42 | NULL                | NULL       | utf8_general_ci   |     NULL | stats_persistent=0 | help topics                             |
    | def           | mysql        | innodb_index_stats        | BASE TABLE | InnoDB |      10 | Dynamic    |        153 |            321 |       49152 |                  0 |            0 |         0 |           NULL | 2020-09-22 23:19:44 | NULL                | NULL       | utf8_bin          |     NULL | stats_persistent=0 |                                         |
    | def           | mysql        | innodb_table_stats        | BASE TABLE | InnoDB |      10 | Dynamic    |         21 |            780 |       16384 |                  0 |            0 |         0 |           NULL | 2020-09-22 23:19:44 | NULL                | NULL       | utf8_bin          |     NULL | stats_persistent=0 |                                         |
    | def           | mysql        | ndb_binlog_index          | BASE TABLE | MyISAM |      10 | Dynamic    |          0 |              0 |           0 |    281474976710655 |         1024 |         0 |           NULL | 2020-09-22 23:19:44 | 2020-09-22 23:19:44 | NULL       | latin1_swedish_ci |     NULL |                    |                                         |
    | def           | mysql        | plugin                    | BASE TABLE | InnoDB |      10 | Dynamic    |          0 |              0 |       16384 |                  0 |            0 |         0 |           NULL | 2020-09-22 23:19:41 | NULL                | NULL       | utf8_general_ci   |     NULL | stats_persistent=0 | MySQL plugins                           |
    | def           | mysql        | proc                      | BASE TABLE | MyISAM |      10 | Dynamic    |         54 |           5770 |      311588 |    281474976710655 |         4096 |         0 |           NULL | 2020-09-22 23:19:43 | 2020-09-22 23:33:49 | NULL       | utf8_general_ci   |     NULL |                    | Stored Procedures                       |
    | def           | mysql        | procs_priv                | BASE TABLE | MyISAM |      10 | Fixed      |          0 |              0 |           0 | 266275327968280575 |         4096 |         0 |           NULL | 2020-09-22 23:19:43 | 2020-09-22 23:19:43 | NULL       | utf8_bin          |     NULL |                    | Procedure privileges                    |
    | def           | mysql        | proxies_priv              | BASE TABLE | MyISAM |      10 | Fixed      |          1 |            837 |         837 | 235594555506819071 |         9216 |         0 |           NULL | 2020-09-22 23:19:49 | 2020-09-22 23:19:56 | NULL       | utf8_bin          |     NULL |                    | User proxy privileges                   |
    | def           | mysql        | server_cost               | BASE TABLE | InnoDB |      10 | Dynamic    |          6 |           2730 |       16384 |                  0 |            0 |         0 |           NULL | 2020-09-22 23:19:45 | NULL                | NULL       | utf8_general_ci   |     NULL | stats_persistent=0 |                                         |
    | def           | mysql        | servers                   | BASE TABLE | InnoDB |      10 | Dynamic    |          0 |              0 |       16384 |                  0 |            0 |         0 |           NULL | 2020-09-22 23:19:42 | NULL                | NULL       | utf8_general_ci   |     NULL | stats_persistent=0 | MySQL Foreign Servers table             |
    | def           | mysql        | slave_master_info         | BASE TABLE | InnoDB |      10 | Dynamic    |          0 |              0 |       16384 |                  0 |            0 |         0 |           NULL | 2020-09-22 23:19:44 | NULL                | NULL       | utf8_general_ci   |     NULL | stats_persistent=0 | Master Information                      |
    | def           | mysql        | slave_relay_log_info      | BASE TABLE | InnoDB |      10 | Dynamic    |          0 |              0 |       16384 |                  0 |            0 |         0 |           NULL | 2020-09-22 23:19:44 | NULL                | NULL       | utf8_general_ci   |     NULL | stats_persistent=0 | Relay Log Information                   |
    | def           | mysql        | slave_worker_info         | BASE TABLE | InnoDB |      10 | Dynamic    |          0 |              0 |       16384 |                  0 |            0 |         0 |           NULL | 2020-09-22 23:19:44 | NULL                | NULL       | utf8_general_ci   |     NULL | stats_persistent=0 | Worker Information                      |
    | def           | mysql        | slow_log                  | BASE TABLE | CSV    |      10 | Dynamic    |          2 |              0 |           0 |                  0 |            0 |         0 |           NULL | NULL                | NULL                | NULL       | utf8_general_ci   |     NULL |                    | Slow log                                |
    | def           | mysql        | tables_priv               | BASE TABLE | MyISAM |      10 | Fixed      |          2 |            947 |        1894 | 266556802944991231 |         9216 |         0 |           NULL | 2020-09-22 23:19:42 | 2020-09-22 23:19:56 | NULL       | utf8_bin          |     NULL |                    | Table privileges                        |
    | def           | mysql        | time_zone                 | BASE TABLE | InnoDB |      10 | Dynamic    |          0 |              0 |       16384 |                  0 |            0 |         0 |              1 | 2020-09-22 23:19:43 | NULL                | NULL       | utf8_general_ci   |     NULL | stats_persistent=0 | Time zones                              |
    | def           | mysql        | time_zone_leap_second     | BASE TABLE | InnoDB |      10 | Dynamic    |          0 |              0 |       16384 |                  0 |            0 |         0 |           NULL | 2020-09-22 23:19:43 | NULL                | NULL       | utf8_general_ci   |     NULL | stats_persistent=0 | Leap seconds information for time zones |
    | def           | mysql        | time_zone_name            | BASE TABLE | InnoDB |      10 | Dynamic    |          0 |              0 |       16384 |                  0 |            0 |         0 |           NULL | 2020-09-22 23:19:43 | NULL                | NULL       | utf8_general_ci   |     NULL | stats_persistent=0 | Time zone names                         |
    | def           | mysql        | time_zone_transition      | BASE TABLE | InnoDB |      10 | Dynamic    |          0 |              0 |       16384 |                  0 |            0 |         0 |           NULL | 2020-09-22 23:19:43 | NULL                | NULL       | utf8_general_ci   |     NULL | stats_persistent=0 | Time zone transitions                   |
    | def           | mysql        | time_zone_transition_type | BASE TABLE | InnoDB |      10 | Dynamic    |          0 |              0 |       16384 |                  0 |            0 |         0 |           NULL | 2020-09-22 23:19:43 | NULL                | NULL       | utf8_general_ci   |     NULL | stats_persistent=0 | Time zone transition types              |
    | def           | mysql        | user                      | BASE TABLE | MyISAM |      10 | Dynamic    |          3 |            132 |         396 |    281474976710655 |         4096 |         0 |           NULL | 2020-09-22 23:19:41 | 2020-09-25 01:11:31 | NULL       | utf8_bin          |     NULL |                    | Users and global privileges             |
    +---------------+--------------+---------------------------+------------+--------+---------+------------+------------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+--------------------+-----------------------------------------+
    31 rows in set (0.88 sec)

## 选择合适的分析方式 ##

通常情况下，如果使用DatabaseMetaData来分析数据库信息，具有更好的跨数据库特性，应用程序可以做到数据库无关，但可能无法准确的获得数据库的更多细节  
使用数据库系统表来分析数据库系统信息会更加准确，但这种方式与底层数据库耦合严重，只能运行在特定数据库上

--------------------

一般情况下，如果需要获得数据库信息，包括数据库驱动提供了哪些功能，则应该使用DatabaseMetaData来了解该数据库支持哪些功能，因为可能底层数据库支持的功能数据库驱动没有提供该功能，如果需要纯粹的分析数据库的静态对象，则利用数据库系统表更合适

--------------------

为了更好的通用性，程序可以通过DatabaseMetaData的getDatabaseProductName()、getDatabaseProductVersion()方法来获取底层数据库的产品名产品版本号，还可以通过DatabaseMetaData的getDriverName()和getDriverVersion()方法获取驱动程序名和驱动程序版本号