1. 全栈程序员必看首页

mysql DatabaseMetaData使用说明[通俗易懂]

全栈程序员-用户IM • • 未分类

mysql DatabaseMetaData使用说明[通俗易懂]DatabaseMetaData数据库元数据，这一名词经常在软件平台中出现，特别是支持多种数据库的平台，主要用于获取数据库信息，便于系统对数据库更好的适配。

大家好，又见面了，我是你们的朋友全栈君。

DatabaseMetaData 数据库元数据，这一名词经常在软件平台中出现，特别是支持多种数据库的平台，主要用于获取数据库信息，便于系统对数据库更好的适配。

mysql数据库,在8.0之前 nullCatalogMeansCurrent默认是true，在8.0后默认是false。如果值为false，会获取数据库连接地址中所有数据信息，而不是指定数据库的信息。具体示例如下：

jdbc:mysql://127.0.0.1:3306/local-xwh?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai

如果在本地除了local-xwh还有其他数据库：zpxt、test，，则会获取到local-xwh、zpxt、test所有的数据库信息。

information_schema库  tables表记录了当前服务器上所有数据库的表、视图等信息

select * from tables where TABLE_SCHEMA =’cfoa23-97′

如果需要获取指定数据库信息，在连接后加如下代码：

&nullCatalogMeansCurrent=true

完整的数据库连接如下：

jdbc:mysql://127.0.0.1:3306/local-xwh?characterEncoding=UTF-8&useUnicode=true&useSSL=false&tinyInt1isBit=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai&nullCatalogMeansCurrent=true

 /**
     * 建立数据库连接
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        return ((DataSource) SpringContextUtils.getApplicationContext().getBean(DataSource.class)).getConnection();
    }

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

public static void getDataBaseInfo()
    {
        Connection connection = null;
        ResultSet tables = null;
        Boolean localBoolean;
        try {
            String[] array = {"TABLE"};
            connection = getConnection();
            DatabaseMetaData metaData = connection.getMetaData();
            System.out.println("数据库已知的用户: "+ metaData.getUserName());
            System.out.println("数据库的系统函数的逗号分隔列表: "+ metaData.getSystemFunctions());
            System.out.println("数据库的时间和日期函数的逗号分隔列表: "+ metaData.getTimeDateFunctions());
            System.out.println("数据库的字符串函数的逗号分隔列表: "+ metaData.getStringFunctions());
            System.out.println("数据库供应商用于 'schema' 的首选术语: "+ metaData.getSchemaTerm());
            System.out.println("数据库URL: " + metaData.getURL());
            System.out.println("是否允许只读:" + metaData.isReadOnly());
            System.out.println("数据库的产品名称:" + metaData.getDatabaseProductName());
            System.out.println("数据库的版本:" + metaData.getDatabaseProductVersion());
            System.out.println("驱动程序的名称:" + metaData.getDriverName());
            System.out.println("驱动程序的版本:" + metaData.getDriverVersion());
            metaData.getTables(null,null,null,null);
            tables = metaData.getTables(null, null, null,array);
            while (tables.next()) {
             /*   获取返回的字段名称
             System.out.println(tables.getMetaData().getColumnCount());
                System.out.println(tables.getMetaData().getColumnName(10));
                System.out.println(tables.getMetaData().getColumnName(1));
                System.out.println(tables.getMetaData().getColumnName(2));
                System.out.println(tables.getMetaData().getColumnName(3));
                System.out.println(tables.getMetaData().getColumnName(4));
                System.out.println(tables.getMetaData().getColumnName(5));
                System.out.println(tables.getMetaData().getColumnName(6));
                System.out.println(tables.getMetaData().getColumnName(7));
                System.out.println(tables.getMetaData().getColumnName(8));
                System.out.println(tables.getMetaData().getColumnName(9));*/


                System.out.println(tables.getString("TABLE_CAT")+" "+tables.getString("TABLE_SCHEM")+" "+tables.getString("TABLE_NAME")+" "+tables.getString("TABLE_TYPE")+" "+tables.getString("REMARKS")
                        +" "+tables.getString("TYPE_CAT")+" "+tables.getString("TYPE_SCHEM")+" "+tables.getString("TYPE_NAME")+" "+tables.getString("SELF_REFERENCING_COL_NAME")+" "+tables.getString("REF_GENERATION"));
            }
        }
        catch (SQLException e2) {
            e2.printStackTrace();
            throw new RuntimeException();
        }
        finally {
            try {
                if (tables != null) {
                    tables.close();
                }
                if (connection != null)
                    connection.close();
            }
            catch (SQLException ex)
            {
                ex.printStackTrace();
                log.error(ex.getMessage(), ex);
            }
        }
    }
/**
     * 验证给定的表是否存在
     * @param tableName
     * @return
     */
    public static Boolean checkTableName(String tableName)
    {
        Connection connection = null;
        ResultSet tables = null;
        Boolean localBoolean;
        try {
            String[] array = {"TABLE"};
            connection = getConnection();
            DatabaseMetaData metaData = connection.getMetaData();
            System.out.println("数据库已知的用户: "+ metaData.getUserName());
            System.out.println("数据库的系统函数的逗号分隔列表: "+ metaData.getSystemFunctions());
            System.out.println("数据库的时间和日期函数的逗号分隔列表: "+ metaData.getTimeDateFunctions());
            System.out.println("数据库的字符串函数的逗号分隔列表: "+ metaData.getStringFunctions());
            System.out.println("数据库供应商用于 'schema' 的首选术语: "+ metaData.getSchemaTerm());
            System.out.println("数据库URL: " + metaData.getURL());
            System.out.println("是否允许只读:" + metaData.isReadOnly());
            System.out.println("数据库的产品名称:" + metaData.getDatabaseProductName());
            System.out.println("数据库的版本:" + metaData.getDatabaseProductVersion());
            System.out.println("驱动程序的名称:" + metaData.getDriverName());
            System.out.println("驱动程序的版本:" + metaData.getDriverVersion());
            metaData.getTables(null,null,null,null);
            //tables = metaData.getTables("xw-wex", null, null,array);
            tables = metaData.getTables(null, null, tableName,array);
            if (tables.next()) {
                System.out.println(tables.getString("TABLE_CAT")+" "+tables.getString("TABLE_SCHEM")+" "+tables.getString("TABLE_NAME")+" "+tables.getString("TABLE_TYPE")+" "+tables.getString("REMARKS")
                        +" "+tables.getString("TYPE_CAT")+" "+tables.getString("TYPE_SCHEM")+" "+tables.getString("TYPE_NAME")+" "+tables.getString("SELF_REFERENCING_COL_NAME")+" "+tables.getString("REF_GENERATION"));
                log.info("数据库表：" + tableName + " 已存在");
                localBoolean = Boolean.valueOf(true);
            }
             localBoolean = Boolean.valueOf(false);
            return localBoolean;
        }
        catch (SQLException ex2) {
            ex2.printStackTrace();
            throw new RuntimeException();
        }
        finally {
            try {
                if (tables != null) {
                    tables.close();
                }
                if (connection != null)
                    connection.close();
            }
            catch (SQLException ex)
            {
                ex.printStackTrace();
                log.error(ex.getMessage(), ex);
            }
        }
    }

附完整代码：

package org.xt.modules.pushMessage.util;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import lombok.extern.slf4j.Slf4j;
import org.xt.common.util.SpringContextUtils;
import org.xt.common.util.oConvertUtils;
import org.xt.modules.online.config.exception.DBException;
import org.springframework.stereotype.Service;

import java.sql.SQLException;


/**
 * TODO
 *
 * @author sky pbsoft
 * @date 2022-02-16 11:56
 */
@Service
@Slf4j
public class TestDataBaseMateBaseHibernateCore {
    public static String connectionStr = "";

    /**
     * 建立数据库连接
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        return ((DataSource) SpringContextUtils.getApplicationContext().getBean(DataSource.class)).getConnection();
    }

    public static String getDatabaseType() throws SQLException, DBException {
        if (oConvertUtils.isNotEmpty(connectionStr)) {
            return connectionStr;
        }
        return getDataBaseType((DataSource)SpringContextUtils.getApplicationContext().getBean(DataSource.class));
    }
    public static String getDataBaseType(DataSource dataSource) throws SQLException, DBException {
        if ("".equals(connectionStr)) {
            Connection connection = dataSource.getConnection();
            try {
                String lowerCase = connection.getMetaData().getDatabaseProductName().toLowerCase();
                if (lowerCase.indexOf("mysql") >= 0) {
                    connectionStr = "MYSQL";
                }
                else if (lowerCase.indexOf("oracle") >= 0) {
                    connectionStr = "ORACLE";
                }
                else if ((lowerCase.indexOf("sqlserver") >= 0) || (lowerCase.indexOf("sql server") >= 0)) {
                    connectionStr = "SQLSERVER";
                }
                else {
                    if (lowerCase.indexOf("postgresql") < 0) {
                        throw new DBException("数据库类型:[" + lowerCase + "]不识别!");
                    }
                    connectionStr = "POSTGRESQL";
                }
            }
            catch (Exception ex) {
                log.error(ex.getMessage(), ex);
            }
            finally {
                connection.close();
            }
        }
        return connectionStr;
    }
    public static String getConnection(Connection connection) throws SQLException, DBException {
        if ("".equals(connectionStr)) {
            String lowerCase = connection.getMetaData().getDatabaseProductName().toLowerCase();
            if (lowerCase.indexOf("mysql") >= 0) {
                connectionStr = "MYSQL";
            }
            else if (lowerCase.indexOf("oracle") >= 0) {
                connectionStr = "ORACLE";
            }
            else if ((lowerCase.indexOf("sqlserver") >= 0) || (lowerCase.indexOf("sql server") >= 0)) {
                connectionStr = "SQLSERVER";
            }
            else {
                if (lowerCase.indexOf("postgresql") < 0) {
                    throw new DBException("数据库类型:[" + lowerCase + "]不识别!");
                }
                connectionStr = "POSTGRESQL";
            }
        }
        return connectionStr;
    }
    public void getDataType() throws DBException, SQLException {
        String databaseType = getDatabaseType();
        System.out.println(databaseType);
        checkTableName("a");
    }

    /**
     * 验证给定的表是否存在
     * @param tableName
     * @return
     */
    public static Boolean checkTableName(String tableName)
    {
        Connection connection = null;
        ResultSet tables = null;
        Boolean localBoolean;
        try {
            String[] array = {"TABLE"};
            connection = getConnection();
            DatabaseMetaData metaData = connection.getMetaData();
            System.out.println("数据库已知的用户: "+ metaData.getUserName());
            System.out.println("数据库的系统函数的逗号分隔列表: "+ metaData.getSystemFunctions());
            System.out.println("数据库的时间和日期函数的逗号分隔列表: "+ metaData.getTimeDateFunctions());
            System.out.println("数据库的字符串函数的逗号分隔列表: "+ metaData.getStringFunctions());
            System.out.println("数据库供应商用于 'schema' 的首选术语: "+ metaData.getSchemaTerm());
            System.out.println("数据库URL: " + metaData.getURL());
            System.out.println("是否允许只读:" + metaData.isReadOnly());
            System.out.println("数据库的产品名称:" + metaData.getDatabaseProductName());
            System.out.println("数据库的版本:" + metaData.getDatabaseProductVersion());
            System.out.println("驱动程序的名称:" + metaData.getDriverName());
            System.out.println("驱动程序的版本:" + metaData.getDriverVersion());
            metaData.getTables(null,null,null,null);
            //tables = metaData.getTables("xw-wex", null, null,array);
            tables = metaData.getTables(null, null, tableName,array);
            if (tables.next()) {
                System.out.println(tables.getString("TABLE_CAT")+" "+tables.getString("TABLE_SCHEM")+" "+tables.getString("TABLE_NAME")+" "+tables.getString("TABLE_TYPE")+" "+tables.getString("REMARKS")
                        +" "+tables.getString("TYPE_CAT")+" "+tables.getString("TYPE_SCHEM")+" "+tables.getString("TYPE_NAME")+" "+tables.getString("SELF_REFERENCING_COL_NAME")+" "+tables.getString("REF_GENERATION"));
                log.info("数据库表：" + tableName + " 已存在");
                localBoolean = Boolean.valueOf(true);
            }
             localBoolean = Boolean.valueOf(false);
            return localBoolean;
        }
        catch (SQLException ex2) {
            ex2.printStackTrace();
            throw new RuntimeException();
        }
        finally {
            try {
                if (tables != null) {
                    tables.close();
                }
                if (connection != null)
                    connection.close();
            }
            catch (SQLException ex)
            {
                ex.printStackTrace();
                log.error(ex.getMessage(), ex);
            }
        }
    }

    /**
     * 获取数据库信息
     */
    public static void getDataBaseInfo()
    {
        Connection connection = null;
        ResultSet tables = null;
        Boolean localBoolean;
        try {
            String[] array = {"TABLE"};
            connection = getConnection();
            DatabaseMetaData metaData = connection.getMetaData();
            System.out.println("数据库已知的用户: "+ metaData.getUserName());
            System.out.println("数据库的系统函数的逗号分隔列表: "+ metaData.getSystemFunctions());
            System.out.println("数据库的时间和日期函数的逗号分隔列表: "+ metaData.getTimeDateFunctions());
            System.out.println("数据库的字符串函数的逗号分隔列表: "+ metaData.getStringFunctions());
            System.out.println("数据库供应商用于 'schema' 的首选术语: "+ metaData.getSchemaTerm());
            System.out.println("数据库URL: " + metaData.getURL());
            System.out.println("是否允许只读:" + metaData.isReadOnly());
            System.out.println("数据库的产品名称:" + metaData.getDatabaseProductName());
            System.out.println("数据库的版本:" + metaData.getDatabaseProductVersion());
            System.out.println("驱动程序的名称:" + metaData.getDriverName());
            System.out.println("驱动程序的版本:" + metaData.getDriverVersion());
            metaData.getTables(null,null,null,null);
            tables = metaData.getTables(null, null, null,array);
            while (tables.next()) {
             /*   获取返回的字段名称
             System.out.println(tables.getMetaData().getColumnCount());
                System.out.println(tables.getMetaData().getColumnName(10));
                System.out.println(tables.getMetaData().getColumnName(1));
                System.out.println(tables.getMetaData().getColumnName(2));
                System.out.println(tables.getMetaData().getColumnName(3));
                System.out.println(tables.getMetaData().getColumnName(4));
                System.out.println(tables.getMetaData().getColumnName(5));
                System.out.println(tables.getMetaData().getColumnName(6));
                System.out.println(tables.getMetaData().getColumnName(7));
                System.out.println(tables.getMetaData().getColumnName(8));
                System.out.println(tables.getMetaData().getColumnName(9));*/


                System.out.println(tables.getString("TABLE_CAT")+" "+tables.getString("TABLE_SCHEM")+" "+tables.getString("TABLE_NAME")+" "+tables.getString("TABLE_TYPE")+" "+tables.getString("REMARKS")
                        +" "+tables.getString("TYPE_CAT")+" "+tables.getString("TYPE_SCHEM")+" "+tables.getString("TYPE_NAME")+" "+tables.getString("SELF_REFERENCING_COL_NAME")+" "+tables.getString("REF_GENERATION"));
            }
        }
        catch (SQLException e2) {
            e2.printStackTrace();
            throw new RuntimeException();
        }
        finally {
            try {
                if (tables != null) {
                    tables.close();
                }
                if (connection != null)
                    connection.close();
            }
            catch (SQLException ex)
            {
                ex.printStackTrace();
                log.error(ex.getMessage(), ex);
            }
        }
    }
}

测试代码：

package org.xt.modules.pushMessage.util;
import org.xt.modules.config.exception.DBException;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.sql.SQLException;

/**
 * TODO
 *
 * @author sky pobasoft
 * @date 2021-11-15 17:15
 */
@RunWith(SpringRunner.class)
@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
public class TestDataBaseMateBaseHibernateCoreTest {
    @Autowired
    TestDataBaseMateBaseHibernateCore dataBaseMateBaseHibernateCore;
    @Test
    public void getDataType() {
        try {
            dataBaseMateBaseHibernateCore.getDataType();
        } catch (DBException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

发布者：全栈程序员-用户IM，转载请注明出处：https://javaforall.cn/150980.html原文链接：https://javaforall.cn

【正版授权，激活自己账号】： Jetbrains全家桶Ide使用，1年售后保障，每天仅需1毛

【官方授权 正版激活】： 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

赞 (0)
全栈程序员-用户IM全栈程序员-用户IM
0 0


相关推荐

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号