How to check if a particular database in MySQL already exists using Java

Question

I am new to JDBC, and I wanted to find out if there is a way to check if a particular database already exists in 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, I wanted to create a Boolean method to check if students database already exists. If it exists then the Boolean method would return false, otherwise if it’s true, then I can create the 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?

I followed mguymons suggestion and this is what I came up:

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;
}

Possible duplicate: http://stackoverflow.com/questions/838978/how-to-check-if-mysql-database-exists , I doubt there is an API method for this, do the same as in the linked post. — zeller, Sep 13 '12 at 20:54
I don't know much about mysql but I have seen this answer before its just that its not implemented in java its in php i think... but based on kirtan's answer can I placed that SQL statement in a string and then pass it as an argument of Statement? Btw I don't think this is also a duplicate because the link you showed me is asking in mysql. I am looking for a java implementation if there is any. — dimas, Sep 13 '12 at 20:56
You can call the sql query in the accepted answer in a prepared statement... — zeller, Sep 13 '12 at 20:58
Surprise, there is a JDBC API for this! I think the problem is the confusing name of getCatalogs() which returns the database names. — mguymon, Sep 13 '12 at 21:18
In the solution you added in your question, remove the `boolean exists = false;` line, replace the `exists = true;` line by `return true;` and the `return exists;` line by `return false;`. It's a more common way to do ;) — sp00m, Sep 14 '12 at 19:10
thanks for the suggestion spoon i've made the changes and i'll start using your tip from now on. — dimas, Sep 14 '12 at 19:24
When the MySQL server is running on Windows, the databaseName from conn.getMetaData().getCatalogs() is always lowercased. So, if you want to look for a database whose name contains at least one uppercase letter, you won't find it this way - unless you change the equals() call to equalsIgnoreCase(). But then, the code will be wrong in the case that the MySQL server is on Linux, because then it will pretend to find a database 'LOWERCASE' when in fact 'lowercase' exists and 'LOWERCASE' does not. — slowhand, Dec 15 '13 at 00:04

score 22 · Accepted Answer · edited Jan 24 '23 at 16:46

You can get that information from a JDBC Connection using getCatalogs. Here is an example of getting the Catalogs, aka Database names

// 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();

score 8 · Answer 2 · answered Sep 13 '12 at 20:54

8

show databases like 'students'

If you get a row back, it exists.

answered Sep 13 '12 at 20:54

Alain Collins

16,268
2
32
55

hi there, but show databases is an sql command if am not mistaken. Is there an equivalent for this in java? – dimas Sep 13 '12 at 21:00
If you were planning to connect to the database, I thought you wouldn't mind using the database. Seems like a wash... – Alain Collins Sep 13 '12 at 22:02

Adam · Answer 3 · 2012-09-13T21:02:33.643

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

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

If the result is 1 it exists.

In Java JDBC that would look something like this:

// 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;

score 3 · Answer 4 · answered Sep 13 '12 at 22:07

3

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.

answered Sep 13 '12 at 22:07

user207421

305,947
44
307
483

hi EJP thank you for answering, i'll try your suggestion and if I can get it to work i'll post it. – dimas Sep 14 '12 at 19:06
yes back then... but i have other solutions now based on the comments here – dimas Aug 17 '16 at 05:01

score 0 · Answer 5 · edited Jan 24 '23 at 16:17

You should break out of the loop once the target database is found. Otherwise, it's only sensible if your target search is the last in the result set.

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;
                break;
            }
        }
        resultSet.close();
    }
    catch(Exception e) {
        e.printStackTrace();
    }
    return false;
}

How to check if a particular database in MySQL already exists using Java

5 Answers5

Linked