如何查询MySQL数据库的基本元信息？

本文共计577个文字，预计阅读时间需要3分钟。

javapackage com.wp.tool.sql;

import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;

获取MySQL元信息包括数据库，表，字段，类型等(代码怎么格式显示不正常)

package com.wp.tool.sql; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import javax.sql.DataSource; public class MySQLTableMeta { private DataSource dataSource; public static final String SQL_DATABASES = "SELECT t.`SCHEMA_NAME` FROM `information_schema`.`SCHEMATA` t"; public static final String SQL_TABLES = "SELECT " + " b.`TABLE_NAME`, " + " b.`TABLE_TYPE` " + "FROM " + " `information_schema`.`SCHEMATA` a " + " LEFT JOIN `information_schema`.`TABLES` b " + " ON a.`SCHEMA_NAME` = b.`TABLE_SCHEMA` " + "WHERE a.`SCHEMA_NAME` = ? "; public static final String SQL_COLUMNS = "SELECT " + " c.`TABLE_NAME`, " + " c.`COLUMN_NAME`, " + " c.`COLUMN_TYPE`, " + " c.`DATA_TYPE` " + "FROM " + " `information_schema`.`SCHEMATA` a " + " LEFT JOIN `information_schema`.`TABLES` b " + " ON a.`SCHEMA_NAME` = b.`TABLE_SCHEMA` " + " LEFT JOIN `information_schema`.`COLUMNS` c " + " ON b.`TABLE_NAME` = c.`TABLE_NAME` " + "WHERE a.`SCHEMA_NAME` = ? " + " AND b.`TABLE_NAME` = ? "; public static final String COLUMN_SCHEMA_NAME = "SCHEMA_NAME"; public static final String COLUMN_TABLE_NAME = "TABLE_NAME"; public static final String COLUMN_TABLE_TYPE = "TABLE_TYPE"; public static final String COLUMN_COLUMN_NAME = "COLUMN_NAME"; public static final String COLUMN_COLUMN_TYPE = "COLUMN_TYPE"; public static final String COLUMN_DATA_TYPE = "DATA_TYPE"; /** *

* getMeta info： * SQL_DATABASES:SCHEMA_NAME * SQL_TABLES:TABLE_NAME,TABLE_TYPE PARAMS:SCHEMA_NAME * SQL_COLUMNS:TABLE_NAME,COLUMN_NAME,COLUMN_TYPE,DATA_TYPE PAEAMS:SCHEMA_NAME,TABLE_NAME * * * @param sql * @param params * @return */ public List > getMeta(String sql, String... params) { Connection connection = null; PreparedStatement pstmt = null; ResultSet rs = null; List > arr = null; try { connection = dataSource.getConnection(); pstmt = connection.prepareStatement(sql); for (int i = 1; i <= params.length; i++) { pstmt.setObject(i, params[i - 1]); } rs = pstmt.executeQuery(); while (rs.next()) { if (arr == null) arr = new ArrayList >(); Map row = new HashMap (); ResultSetMetaData metaData = rs.getMetaData(); int columnCount = metaData.getColumnCount(); for (int i = 1; i <= columnCount; i++) { String columnName = metaData.getColumnName(i); row.put(columnName, rs.getString(i)); } arr.add(row); } } catch (SQLException e) { throw new RuntimeException(e); } finally { try { rs.close(); pstmt.close(); connection.close(); } catch (Exception e2) { throw new RuntimeException(e2); } } return arr; } public static void main(String[] args) { //将类放到spring中通过xml注入MySQL的dataSource就可以使用 MySQLTableMeta meta= null; //获取数据库列表 List > list1 = meta.getMeta(MySQLTableMeta.SQL_DATABASES); for (Map map : list1) { System.out.println(map.get(MySQLTableMeta.COLUMN_SCHEMA_NAME));//数据库名 } //获取"test"库的表或视图等 List > list2 = meta.getMeta(MySQLTableMeta.SQL_TABLES,"test"); for (Map map : list1) { System.out.println(map.get(MySQLTableMeta.COLUMN_TABLE_NAME));//表名 System.out.println(map.get(MySQLTableMeta.COLUMN_TABLE_TYPE));//表的类型 } //获取"test"库中"teacher"表的字段 List > list3 = meta.getMeta(MySQLTableMeta.SQL_COLUMNS,"test","teacher"); for (Map map : list1) { System.out.println(map.get(MySQLTableMeta.COLUMN_TABLE_NAME));//表名 System.out.println(map.get(MySQLTableMeta.COLUMN_COLUMN_NAME));//列名 System.out.println(map.get(MySQLTableMeta.COLUMN_COLUMN_TYPE));//字段类型 如：varchar(20) System.out.println(map.get(MySQLTableMeta.COLUMN_DATA_TYPE));//字段类型 如：varchar } } /** * via spring xml DI to this Object * @param dataSource */ public void setDataSource(DataSource dataSource) { this.dataSource = dataSource; } }