14

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.

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?

EDIT 2

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;
}
Community
  • 1
  • 1
dimas
  • 2,358
  • 6
  • 36
  • 63
  • 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
  • 1
    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

4 Answers4

21

You can get that info from a JDBC Connection using DatabaseMetaData#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();
mguymon
  • 8,826
  • 2
  • 38
  • 61
8
show databases like 'students'

If you get a row back, it exists.

Alain Collins
  • 16,035
  • 2
  • 31
  • 54
  • 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
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;
Adam
  • 41,954
  • 16
  • 104
  • 142
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.

user207421
  • 298,294
  • 41
  • 291
  • 462