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: stackoverflow.com/questions/838978/… , I doubt there is an API method for this, do the same as in the linked post. — zeller
– zeller, Commented Sep 13, 2012 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
– dimas, Commented Sep 13, 2012 at 20:56
You can call the sql query in the accepted answer in a prepared statement... — zeller
– zeller, Commented Sep 13, 2012 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
– mguymon, Commented Sep 13, 2012 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
– sp00m, Commented Sep 14, 2012 at 19:10

Peter Mortensen · Accepted Answer · 2023-01-24 16:46:21Z

21

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

edited Jan 24, 2023 at 16:46

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Sep 13, 2012 at 21:04

mguymon

9,0252 gold badges41 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alain Collins · Accepted Answer · 2012-09-13 20:54:24Z

8

show databases like 'students'

If you get a row back, it exists.

answered Sep 13, 2012 at 20:54

Alain Collins

16.4k2 gold badges34 silver badges56 bronze badges

2 Comments

dimas Over a year ago

hi there, but show databases is an sql command if am not mistaken. Is there an equivalent for this in java?

Alain Collins Over a year ago

If you were planning to connect to the database, I thought you wouldn't mind using the database. Seems like a wash...

Adam · Accepted Answer · 2012-09-13 21:02:33Z

3

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;

edited Sep 13, 2012 at 21:02

answered Sep 13, 2012 at 20:56

Adam

45.1k17 gold badges107 silver badges145 bronze badges

Comments

user207421 · Accepted Answer · 2012-09-13 22:07:24Z

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, 2012 at 22:07

user207421

312k45 gold badges324 silver badges493 bronze badges

2 Comments

dimas Over a year ago

hi EJP thank you for answering, i'll try your suggestion and if I can get it to work i'll post it.

dimas Over a year ago

yes back then... but i have other solutions now based on the comments here

Peter Mortensen · Accepted Answer · 2023-01-24 16:17:57Z

0

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

edited Jan 24, 2023 at 16:17

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Jan 23, 2023 at 15:11

Muinde J Kivai

11 bronze badge

Collectives™ on Stack Overflow

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

5 Answers 5

Comments

2 Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related