mysql 获取数据库表元数据_获取数据库元数据信息

package read.mysql.demo;

import java.sql.*;

import java.util.ArrayList;

import java.util.List;

/**

* 获取数据库表结构，字段，字段类型，字段注释

*/

public class DBUtil {

private static final String URL = "jdbc:mysql://localhost:3306/guan";

private static final String USER = "root";

private static final String Password = "admin";

static {

try {

Class.forName("com.mysql.jdbc.Driver");

} catch (ClassNotFoundException e) {

e.printStackTrace();

}

}

public static Connection getConnection() {

Connection connect = null;

try {

connect = DriverManager.getConnection(URL, USER, Password);

} catch (Exception e) {

e.printStackTrace();

}

return connect;

}

/**

* 关闭连接

*

* @param connect

*/

public static void closeConnection(Connection connect) {

if (connect != null) {

try {

connect.close();

} catch (SQLException e) {

System.out.println("数据库连接错误");

e.printStackTrace();

}

}

}

/**

* 获取数据库下的所有表名

*

* @return

*/

public static List getTableNames() {

List tableNames = new ArrayList<>();

Connection conn = getConnection();

ResultSet rs = null;

try {

// 获取数据库元数据信息

DatabaseMetaData metaData = conn.getMetaData();

System.out.println(metaData);

// 从元数据中获取所有的表名

rs = metaData.getTables(null, null, null

, new String[]{"TABLE"});

while (rs.next()) {

tableNames.add(rs.getString(3));

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

try {

rs.close();

closeConnection(conn);

} catch (SQLException e) {

e.printStackTrace();

}

}

return tableNames;

}

/**

* 获取表中所有字段名称

*

* @param tableName 表名

* @throws Exception

*/

public static List getColumnNames(String tableName) {

ArrayList columnNames = new ArrayList<>();

PreparedStatement pst = null;

String tableSql = "SELECT * FROM " + tableName;

Connection connection = getConnection();

try {

pst = connection.prepareStatement(tableSql);

ResultSetMetaData rsmd = pst.getMetaData();

int size = rsmd.getColumnCount();

for (int i = 0; i < size; i++) {

columnNames.add(rsmd.getColumnName(i + 1));

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

if (pst != null) {

try {

pst.close();

} catch (SQLException e) {

e.printStackTrace();

}

closeConnection(connection);

}

}

return columnNames;

}

/**

* 获取表中所有字段类型

*

* @param tableName

* @return

*/

public static List getColumnTypes(String tableName) {

ArrayList columnTyps = new ArrayList<>();

// 与数据库的连接

Connection connection = getConnection();

PreparedStatement pst = null;

String sqlStr = "SELECT * FROM " + tableName;

try {

pst = connection.prepareStatement(sqlStr);

// 结果集数据

ResultSetMetaData metaData = pst.getMetaData();

// 表列表

int size = metaData.getColumnCount();

for (int i = 0; i < size; i++) {

columnTyps.add(metaData.getColumnTypeName(i + 1));

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

if (pst != null) {

try {

pst.close();

} catch (SQLException e) {

e.printStackTrace();

}

closeConnection(connection);

}

}

return columnTyps;

}

public static List getColumnComments(String tableName) {

ArrayList columnComments = new ArrayList<>();

Connection connection = getConnection();

PreparedStatement pst = null;

ResultSet rs =null;

String sqlStr = "SELECT * FROM " + tableName;

try {

pst = connection.prepareStatement(sqlStr);

// 结果集数据

rs = pst.executeQuery("show FULL COLUMNS FROM " + tableName);

while (rs.next()) {

columnComments.add(rs.getString("Comment"));

}

} catch (SQLException e) {

e.printStackTrace();

} finally {

if (pst != null) {

try {

rs.close();

pst.close();

} catch (SQLException e) {

e.printStackTrace();

}

closeConnection(connection);

}

}

return columnComments;

}

public static void main(String[] args) {

List tableNames = getTableNames();

System.out.println("tableNames = " + tableNames);

for (String tableName : tableNames) {

System.out.println("ColumnNames :" + getColumnNames(tableName));

System.out.println("ColumnTypes :" + getColumnTypes(tableName));

System.out.println("ColumnComments :" + getColumnComments(tableName));

}

}

}