check if a database foo exist in MySQL

Problem: I want to see if a database named “foo” exist in MySQL server


This should not be a tough problem at first sight. However, I googled the topic and did not find an official answer except several quick&dirty solutions. Please let me know if there are better and clean ways to do it right.


Setup: MySQL v4.1.9 + mysql-connector-java v3.1.6 + J2SE v1.4.2_06


Solution 1: Catch the exception for “unknown database %s” (I used this one finally).


If you create a new Connection to the URL that points to a non-existent database in MySQL, an SQLException is thrown with the message “unknown database %s” where %s is the database name. The exception has error code of 1049 (int) and SQL state of 42000 (String). Check MySQL error code list for the full list of possible errors for MySQL.


So, I just check the SQLException error code and/or SQL state to make sure this exception happens so that I know a database with the given name exist or not.


        boolean isExistRepos = true;
       
        try{
            connection = DriverManager.getConnection(reposURL, “root”, “rootpassword”);
        }catch (SQLException se){
            while(se != null && isExistRepos){
                String logMessage = “\n\n An SQL Error Occured: “
                                  + se.getMessage() + “\n\t”
                                  + “Error Code: ” + se.getErrorCode()
                                  + “\n\t” + “SQLState: “
                                  + se.getSQLState() + “\n”;
                System.err.println(logMessage);
               
                //repos does not exist and the connection cannot set up
                //MySQL error list (http://dev.mysql.com/doc/mysql/en/error-handling.html)
                //#Error: 1049 SQLSTATE: 42000 (ER_BAD_DB_ERROR)
                //Message: Unknown database ‘%s’
                if((se.getErrorCode() == 1049) && (se.getSQLState().equalsIgnoreCase(”42000″)))
                    isExistRepos = false;
                se = se.getNextException();
            }
        }


Solution 2: Call mysql command line client using Runtime class.


I did not try it, but here is the general idea. MySQL comes with a command line client program called “mysql” where users can interact with the database server. You can type in “show databases” and a list of existing databases will be presented.


In Java, we can use Runtime.getRuntime().exec(“mysql -u root -p rootpassword”) to get a handle on a Process object, then we can use OutputStream and InputStream to input “show databases” and parse the output to see if the database foo exist in the list of databases.


Solution 3: Check if the directory corresponding to the database exists in MySQL data directory.


I just found that for each database, there exist a directory with the same name in MySQL data directory, e.g. C:\Program Files\MySQL\MySQL Server 4.1\data for my case. So, you can juse check if the directory foo exist in that directory to tell if the corresponding database exist or not. I am not sure about MySQL internals, so this solution is not stable and portable.