Java中使用JDBC获取MySQL数据库的名称、表名和字段名及相关信息

吴佳杰杰

已于 2024-05-20 14:20:24 修改

阅读量1.4k

点赞数 30

文章标签：数据库 java mysql

于 2024-05-15 22:00:00 首次发布

本文链接：https://blog.csdn.net/wxq_csdn/article/details/138907993

版权

MySQL相关操作

前置条件：1.项目中需添加MySQL依赖；2. 文件中导入SQL所需要的包；

1. 加载JDBC驱动

// 驱动名称
private static final String DRIVER = "com.mysql.cj.jdbc.Driver";

static {
    try {
    	// 加载驱动
        Class.forName(DRIVER);
    } catch (ClassNotFoundException e) {
        System.out.println("加载驱动失败：" + e);
    }
}

2. 获取连接

/**
* 获取MySQL连接
* 
* @param url 需连接的MySql对应的url, 如 "jdbc:mysql://127.0.0.1:3306"
* @param userName 用户名
* @param password 密码
* @return Connection 
*/
public static Connection getConnection(String url, String userName, String password) {
    Connection connection = null;
    try {
    	// 创建连接
        connection = DriverManager.getConnection(url, userName, password);
    } catch (SQLException e) {
         System.out.println("获取连接失败：" + e);
    }
    return connection;
}

3. 获取数据库列表名称

url 不用拼接具体数据库

/**
* 获取MySQL中数据库名称
* 
* @param url 需连接的MySql对应的url, 如 "jdbc:mysql://127.0.0.1:3306"
* @param userName 用户名
* @param password 密码
*/
public static void getDataBase(String url, String userName, String password) {
    DatabaseMetaData databaseMetaData;
    ResultSet catalogResultSet = null;
    try(Connection connection = getConnection(url, userName, password)) {
        databaseMetaData = connection.getMetaData();
        catalogResultSet = databaseMetaData.getCatalogs();
        while (catalogResultSet.next()) {
            String databaseName = catalogResultSet.getString(1);
            // 去除系统表
            if ("information_schema".equals(databaseName)
                    || "mysql".equals(databaseName)
                    || "performance_schema".equals(databaseName)
                    || "sys".equals(databaseName)) {
                continue;
            }
            System.out.println("DataBase : " + databaseName);
        }
    } catch (SQLException e) {
        System.out.println("获取数据库名称失败：" + e);
    } finally {
        close(catalogResultSet);
    } 
}

输出结果如下：
DataBase : hive
DataBase : nacos
DataBase : test

4. 获取数据库表名称

url需要拼接具体数据库名称，否则获取表名失败

/**
* 获取MySQL中数据库表名称
* 
* @param url 需连接的MySql对应的url, 如 "jdbc:mysql://127.0.0.1:3306/test"
* @param userName 用户名
* @param password 密码
*/
public static void getTables(String url, String userName, String password) {
    // 1、连接数据库， 控制台打印出这个数据库里面所有的表
    ResultSet resultSet = null;
    try (Connection connection = getConnection(url, userName, password)) {
        DatabaseMetaData databaseMetaData = connection.getMetaData();
        // 可用可不用
        String[] types = {"TABLE"}; 
        resultSet = databaseMetaData.getTables(connection.getCatalog(), null, "%", types);
        while (resultSet.next()) {
            //获取表名
            System.out.println("database : " + resultSet.getString(1) + " ==== table : " + resultSet.getString(3));
        }
    } catch (SQLException e) {
        System.out.println("获取数据库表名失败：" + e);
    } finally {
        close(resultSet);
    }
}

输出结果如下：
database : test ==== table : menu
database : test ==== table : role
database : test ==== table : user

5. 获取表的字段及类型

url需要拼接具体数据库名称，否则获取字段失败

/**
* 获取MySQL中数据库表字段名称及类型
* 
* @param tableName 表名
* @param url 需连接的MySql对应的url, 如 "jdbc:mysql://127.0.0.1:3306/test"
* @param userName 用户名
* @param password 密码
*/
public static void getColumns(String tableName, String url, String userName, String password) {
    String query = "SELECT * FROM " + tableName;
    try (Connection connection = getConnection(url, userName, password);
         PreparedStatement statement = connection.prepareStatement(query)) {
        ResultSetMetaData resultSetMetaData = statement.getMetaData();
        System.out.println("table : " + tableName);
        for (int i = 0; i < resultSetMetaData.getColumnCount(); i++) {
            System.out.println("Column : " + resultSetMetaData.getColumnName(i + 1) + " ==== ColumnType : " + resultSetMetaData.getColumnTypeName(i + 1));
        }
    } catch (SQLException e) {
        System.out.println("获取数据库表字段和类型失败：" + e);
    }
}

输出结果如下：
table : user
Column : id ==== ColumnType : INT
Column : name ==== ColumnType : VARCHAR
Column : username ==== ColumnType : VARCHAR
Column : tele ==== ColumnType : VARCHAR
Column : email ==== ColumnType : VARCHAR
Column : password ==== ColumnType : VARCHAR
Column : roleid ==== ColumnType : INT
Column : status ==== ColumnType : CHAR

6. 获取表字段对应的内容

url需要拼接具体数据库名称，否则获取内容失败

/**
* 获取MySQL中数据库表字段对应内容
* 
* @param tableName 表名
* @param url 需连接的MySql对应的url, 如 "jdbc:mysql://127.0.0.1:3306/test"
* @param userName 用户名
* @param password 密码
*/
public static void getRows(String tableName, String url, String userName, String password) {
		ResultSet resultSet = null;
        String query = "SELECT * FROM " + tableName + " LIMIT 0, 10";
        try (Connection connection = getConnection(url, userName, password);
             PreparedStatement statement = connection.prepareStatement(query)) {
            ResultSetMetaData resultSetMetaData = statement.getMetaData();
            resultSet = statement.executeQuery();
            System.out.println("table : " + tableName);
            while (resultSet.next()) {
                for (int i = 0; i < resultSetMetaData.getColumnCount(); i++) {
                     System.out.println(resultSetMetaData.getColumnName(i+1) + ": " + resultSet.getString(i + 1));
                }
            }
        } catch (SQLException e) {
            System.out.println("获取数据库表字段对应内容失败：" + e);
        } finally {
            close(resultSet);
        }
    }

输出结果如下：
table : user
id: 1
name: 管理员
username: admin
tele: 18612345588
email: hahaha@qq.com
password: 482c811da5d5b4bc6d497ffa98491e38
roleid: 0
status: 1
……

7. 获取数据库相关信息

url 不用拼接具体数据库

/**
* 获取MySQL中数据库相关信息
* 
* @param url 需连接的MySql对应的url, 如 "jdbc:mysql://127.0.0.1:3306"
* @param userName 用户名
* @param password 密码
*/
public static void getDataBaseInfo(String url, String userName, String password) {
		 ResultSet resultSet = null;
         try (Connection connection = getConnection(url, userName, password)) {
            DatabaseMetaData dbmd = connection.getMetaData();
            System.out.println("数据库已知的用户: " + dbmd.getUserName());
            System.out.println("数据库的系统函数: " + dbmd.getSystemFunctions());
            System.out.println("数据库的时间和日期函数: " + dbmd.getTimeDateFunctions());
            System.out.println("数据库的字符串函数: " + dbmd.getStringFunctions());
            System.out.println("数据库供应商用于 'schema' 的首选术语: " + dbmd.getSchemaTerm());
            System.out.println("数据库URL: " + dbmd.getURL());
            System.out.println("是否允许只读:" + dbmd.isReadOnly());
            System.out.println("数据库的产品名称:" + dbmd.getDatabaseProductName());
            System.out.println("数据库的版本:" + dbmd.getDatabaseProductVersion());
            System.out.println("驱动程序的名称:" + dbmd.getDriverName());
            System.out.println("驱动程序的版本:" + dbmd.getDriverVersion());
            System.out.println("数据库中使用的表类型:");
            resultSet= dbmd.getTableTypes();
            while (resultSet.next()) {
                System.out.println(resultSet.getString("TABLE_TYPE"));
            }
        } catch (SQLException e){
            System.out.println("获取数据库相关信息失败：" + e);
        } finally {
            close(resultSet);
        }
    }

输出结果如下：
数据库已知的用户: xxx@localhost
数据库的系统函数: DATABASE,USER,SYSTEM_USER,SESSION_USER,PASSWORD,ENCRYPT,LAST_INSERT_ID,VERSION
数据库的时间和日期函数: DAYOFWEEK,WEEKDAY,DAYOFMONTH,DAYOFYEAR,MONTH,DAYNAME,MONTHNAME,QUARTER,WEEK,YEAR,HOUR,MINUTE,SECOND,PERIOD_ADD,PERIOD_DIFF,TO_DAYS,FROM_DAYS,DATE_FORMAT,TIME_FORMAT,CURDATE,CURRENT_DATE,CURTIME,CURRENT_TIME,NOW,SYSDATE,CURRENT_TIMESTAMP,UNIX_TIMESTAMP,FROM_UNIXTIME,SEC_TO_TIME,TIME_TO_SEC
数据库的字符串函数: ASCII,BIN,BIT_LENGTH,CHAR,CHARACTER_LENGTH,CHAR_LENGTH,CONCAT,CONCAT_WS,CONV,ELT,EXPORT_SET,FIELD,FIND_IN_SET,HEX,INSERT,INSTR,LCASE,LEFT,LENGTH,LOAD_FILE,LOCATE,LOCATE,LOWER,LPAD,LTRIM,MAKE_SET,MATCH,MID,OCT,OCTET_LENGTH,ORD,POSITION,QUOTE,REPEAT,REPLACE,REVERSE,RIGHT,RPAD,RTRIM,SOUNDEX,SPACE,STRCMP,SUBSTRING,SUBSTRING,SUBSTRING,SUBSTRING,SUBSTRING_INDEX,TRIM,UCASE,UPPER
数据库供应商用于 ‘schema’ 的首选术语:
数据库URL: jdbc:mysql://127.0.0.1:3306
是否允许只读:false
数据库的产品名称:MySQL
数据库的版本:5.7.30
驱动程序的名称:MySQL Connector/J
驱动程序的版本:mysql-connector-java-8.0.28 (Revision: 7ff2161da3899f379fb3171b6538b191b1c5c7e2)
数据库中使用的表类型:
LOCAL TEMPORARY
SYSTEM TABLE
SYSTEM VIEW
TABLE
VIEW

8. 关闭连接

public static void close(Object o) {
    if (Objects.isNull(o)) {
        return ;
    }
    try {
        if (o instanceof ResultSet) {
            ((ResultSet) o).close();
        } else if (o instanceof Statement) {
            ((Statement) o).close();
        } else if (o instanceof Connection) {
            Connection con = (Connection) o;
            if (!con.isClosed()) {
                con.close();
            }
        }
    } catch (SQLException e){
        System.out.println("关闭连接失败：" + e);
    }
}

总体代码

import java.sql.*;
import java.util.Objects;

public class MysqlDemo {

	// 1.加载JDBC驱动
    private static final String DRIVER = "com.mysql.cj.jdbc.Driver";

    static {
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            System.out.println("加载驱动失败：" + e);
        }
    }

    // 2.获取连接
    public static Connection getConnection(String url, String userName, String password) {
        Connection connection = null;
        try {
            // 创建连接
            connection = DriverManager.getConnection(url, userName, password);
        } catch (SQLException e) {
            System.out.println("获取连接失败：" + e);
        }
        return connection;
    }

    // 3. 获取数据库列表名称
    public static void getDataBase(String url, String userName, String password) {
        DatabaseMetaData databaseMetaData;
        ResultSet catalogResultSet = null;
        try(Connection connection = getConnection(url, userName, password)) {
            databaseMetaData = connection.getMetaData();
            catalogResultSet = databaseMetaData.getCatalogs();
            while (catalogResultSet.next()) {
                String databaseName = catalogResultSet.getString(1);
                // 去除系统表
                if ("information_schema".equals(databaseName)
                        || "mysql".equals(databaseName)
                        || "performance_schema".equals(databaseName)
                        || "sys".equals(databaseName)) {
                    continue;
                }
                System.out.println("DataBase : " + databaseName);
            }
        } catch (SQLException e) {
            System.out.println("获取数据库名称失败：" + e);
        } finally {
            close(catalogResultSet);
        }
    }

    // 4.获取数据库表名称
    public static void getTables(String url, String userName, String password) {
        // 1、连接数据库， 控制台打印出这个数据库里面所有的表
        ResultSet resultSet = null;
        try (Connection connection = getConnection(url, userName, password)) {
            DatabaseMetaData databaseMetaData = connection.getMetaData();
            // 可用可不用
            String[] types = {"TABLE"};
            resultSet = databaseMetaData.getTables(connection.getCatalog(), null, "%", types);
            while (resultSet.next()) {
                System.out.println("database : " + resultSet.getString(1) + " ==== table : " + resultSet.getString(3));
            }
        } catch (SQLException e) {
            System.out.println("获取数据库所有表名失败：" + e);
        } finally {
            close(resultSet);
        }
    }

    // 5. 获取表的字段及类型
    public static void getColumns(String tableName, String url, String userName, String password) {
        String query = "SELECT * FROM " + tableName;
        try (Connection connection = getConnection(url, userName, password);
             PreparedStatement statement = connection.prepareStatement(query)) {
            ResultSetMetaData resultSetMetaData = statement.getMetaData();
            System.out.println("table : " + tableName);
            for (int i = 0; i < resultSetMetaData.getColumnCount(); i++) {
                System.out.println("Column : " + resultSetMetaData.getColumnName(i + 1) + " ==== ColumnType : "
                        + resultSetMetaData.getColumnTypeName(i + 1));
            }
        } catch (SQLException e) {
            System.out.println("获取数据库表字段和类型失败：" + e);
        }
    }

    // 6.获取表字段对应的内容
    public static void getRows(String tableName, String url, String userName, String password) {
        String query = "SELECT * FROM " + tableName + " LIMIT 0, 10";
        ResultSet resultSet = null;
        try (Connection connection = getConnection(url, userName, password);
             PreparedStatement statement = connection.prepareStatement(query)) {
            ResultSetMetaData resultSetMetaData = statement.getMetaData();
            resultSet = statement.executeQuery();
            System.out.println("table : " + tableName);
            while (resultSet.next()) {
                for (int i = 0; i < resultSetMetaData.getColumnCount(); i++) {
                    System.out.println(resultSetMetaData.getColumnName(i + 1) + ": " + resultSet.getString(i + 1));
                }
            }
        } catch (SQLException e) {
            System.out.println("获取数据库表字段对应内容失败：" + e);
        } finally {
            close(resultSet);
        }
    }

    // 7. 获取数据库相关信息
    public static void getDataBaseInfo(String url, String userName, String password) {
        ResultSet resultSet = null;
        try (Connection connection = getConnection(url, userName, password)) {
            DatabaseMetaData dbmd = connection.getMetaData();
            System.out.println("数据库已知的用户: " + dbmd.getUserName());
            System.out.println("数据库的系统函数: " + dbmd.getSystemFunctions());
            System.out.println("数据库的时间和日期函数: " + dbmd.getTimeDateFunctions());
            System.out.println("数据库的字符串函数: " + dbmd.getStringFunctions());
            System.out.println("数据库供应商用于 'schema' 的首选术语: " + dbmd.getSchemaTerm());
            System.out.println("数据库URL: " + dbmd.getURL());
            System.out.println("是否允许只读:" + dbmd.isReadOnly());
            System.out.println("数据库的产品名称:" + dbmd.getDatabaseProductName());
            System.out.println("数据库的版本:" + dbmd.getDatabaseProductVersion());
            System.out.println("驱动程序的名称:" + dbmd.getDriverName());
            System.out.println("驱动程序的版本:" + dbmd.getDriverVersion());
            System.out.println("数据库中使用的表类型:");
            resultSet = dbmd.getTableTypes();
            while (resultSet.next()) {
                System.out.println(resultSet.getString("TABLE_TYPE"));
            }
        }catch (SQLException e){
            System.out.println("获取数据库相关信息失败：" + e);
        } finally {
            close(resultSet);
        }
    }

    // 8.关闭连接
    public static void close(Object o) {
        if (Objects.isNull(o)) {
            return ;
        }
        try {
            if (o instanceof ResultSet) {
                ((ResultSet) o).close();
            } else if (o instanceof Statement) {
                ((Statement) o).close();
            } else if (o instanceof Connection) {
                Connection con = (Connection) o;
                if (!con.isClosed()) {
                    con.close();
                }
            }
        } catch (SQLException e){
            System.out.println("关闭连接失败：" + e);
        }
    }
	
	// 测试
    public static void main(String[] args) throws SQLException {
    	// 对应url、userName、password需换成自己配置的内容
        getDataBase("jdbc:mysql://127.0.0.1:3306", "xxx", "xxx");
        getTables("jdbc:mysql://127.0.0.1:3306/test", "xxx", "xxx");
        getColumns("user", "jdbc:mysql://127.0.0.1:3306/test", "xxx", "xxx");
        getRows("user", "jdbc:mysql://127.0.0.1:3306/test", "xxx", "xxx");
        getDataBaseInfo("jdbc:mysql://127.0.0.1:3306", "xxx", "xxx");
    }
}