如何使用java检查mysql中的特定数据库是否已存在

时间:2021-11-16 04:27:21

Hi everyone I am new in jdbc and I wanted to find out if there is a way to check if a particular database already exists in mysql.

大家好我是jdbc的新手,我想知道是否有办法检查mysql中是否已经存在某个特定的数据库。

Let's say I wanted to create a database named students, if the students database is already created in mysql an error message in eclipse would state that this students database already exists. However what I wanted to do is to create a boolean method to check if students database already exists. If it exists then boolean method would return false otherwise its true then I can create students database. How do I do these in Java? Are there any methods in jdbc that does this or do I need to code it from scratch? Thanks for your help.

假设我想创建一个名为students的数据库,如果学生数据库已经在mysql中创建,eclipse中的错误消息会说明这个学生数据库已经存在。但是我想要做的是创建一个布尔方法来检查学生数据库是否已经存在。如果它存在则boolean方法将返回false,否则为true,然后我可以创建学生数据库。我如何用Java做这些?在jdbc中是否有任何方法可以执行此操作,还是需要从头开始编写代码?谢谢你的帮助。

EDIT 2

I followed mguymons suggestion and this is what I came up

我遵循了mguymons的建议,这就是我的想法

public boolean checkDBExists(String dbName){

    try{
        Class.forName(JDBCDriver); //Register JDBC Driver

        System.out.println("Creating a connection...");
        conn = DriverManager.getConnection(DBURL, USER, PASS); //Open a connection

        ResultSet resultSet = conn.getMetaData().getCatalogs();

        while (resultSet.next()) {

          String databaseName = resultSet.getString(1);
            if(databaseName.equals(dbName)){
                return true;
            }
        }
        resultSet.close();

    }
    catch(Exception e){
        e.printStackTrace();
    }

    return false;
}

4 个解决方案

#1


18  

You can get that info from a JDBC Connection using DatabaseMetaData#getCatalogs, here is an example of getting the Catalogs, aka Database names

您可以使用DatabaseMetaData#getCatalogs从JDBC连接获取该信息,以下是获取目录(即数据库名称)的示例

// Connection connection = <your java.sql.Connection>
ResultSet resultSet = connection.getMetaData().getCatalogs();

//iterate each catalog in the ResultSet
while (resultSet.next()) {
  // Get the database name, which is at position 1
  String databaseName = resultSet.getString(1);
}
resultSet.close();

#2


6  

show databases like 'students'

If you get a row back, it exists.

如果你回来一行,它就存在了。

#3


3  

In newer versions of MySQL (5 and above) run this query:

在较新版本的MySQL(5及以上版本)中运行此查询:

SELECT COUNT(*)
FROM information_schema.tables 
WHERE table_schema = '[database name]' 
AND table_name = '[table name]';

If the result is 1 it exists.

如果结果为1则存在。

In Java JDBC that would look something like this:

在Java JDBC中,它看起来像这样:

// Create connection and statement
String query = "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema'[database name]' AND table_name = '[table name]'";
ResultSet rs = stmt.executeQuery(query);                  
rs.next();
boolean exists = rs.getInt("COUNT(*)") > 0;
// Close connection, statement, and result set.
return exists;   

#4


2  

You're doing it back to front. The correct technique is to try to create it and catch the exception. The way you want to do it is subject to timing-window problems: it wasn't there when you tested, but it was there when you tried to create it. And you still have to catch the exception anyway.

你正在回到前面。正确的方法是尝试创建它并捕获异常。你想要这样做的方式受时间窗问题的影响:当你测试时它不存在,但当你试图创建它时它就在那里。而且你仍然必须抓住异常。

#1


18  

You can get that info from a JDBC Connection using DatabaseMetaData#getCatalogs, here is an example of getting the Catalogs, aka Database names

您可以使用DatabaseMetaData#getCatalogs从JDBC连接获取该信息,以下是获取目录(即数据库名称)的示例

// Connection connection = <your java.sql.Connection>
ResultSet resultSet = connection.getMetaData().getCatalogs();

//iterate each catalog in the ResultSet
while (resultSet.next()) {
  // Get the database name, which is at position 1
  String databaseName = resultSet.getString(1);
}
resultSet.close();

#2


6  

show databases like 'students'

If you get a row back, it exists.

如果你回来一行,它就存在了。

#3


3  

In newer versions of MySQL (5 and above) run this query:

在较新版本的MySQL(5及以上版本)中运行此查询:

SELECT COUNT(*)
FROM information_schema.tables 
WHERE table_schema = '[database name]' 
AND table_name = '[table name]';

If the result is 1 it exists.

如果结果为1则存在。

In Java JDBC that would look something like this:

在Java JDBC中,它看起来像这样:

// Create connection and statement
String query = "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema'[database name]' AND table_name = '[table name]'";
ResultSet rs = stmt.executeQuery(query);                  
rs.next();
boolean exists = rs.getInt("COUNT(*)") > 0;
// Close connection, statement, and result set.
return exists;   

#4


2  

You're doing it back to front. The correct technique is to try to create it and catch the exception. The way you want to do it is subject to timing-window problems: it wasn't there when you tested, but it was there when you tried to create it. And you still have to catch the exception anyway.

你正在回到前面。正确的方法是尝试创建它并捕获异常。你想要这样做的方式受时间窗问题的影响:当你测试时它不存在,但当你试图创建它时它就在那里。而且你仍然必须抓住异常。