JDBC: Connection returning NULL, what to do?

Question

This is the output of the program given below:

Connection made!
Schema Name:null

Successfully connected to null
Releasing all open resources ...

Inside establishConnection(), conn is initialized as null. Then the first statement inside the try block is supposed to establish a connection with the database, and the third statement is then printing the name of the current schema of conn.

According to the API, getSchema() returns the current schema name or null if there is none.

This means that there is no schema (I think the schema name is same as the database name) associated with conn? Can anyone suggest if I am correct in my anticipation, and also suggest why is that there is no schema or null associated with conn?

public class ConnectDB {

    private Connection establishConnection() throws SQLException {
        Connection conn = null;
        try {
            conn = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/test_final1", "root", "password");
            System.out.println("Connection made!");
            System.out.println("Schema Name:"+conn.getSchema()+"\n");
        } catch (SQLException sqle) {
            System.err.println("SQL Exception thrown while making connection");
            printSQLException(sqle);
        }
        return conn;
    }

    public static void main(String[] args) {
        ConnectDB cdb= new ConnectDB();
        Connection myconn=null;
        try{
            myconn=cdb.establishConnection();
            if(myconn!=null) System.out.println("Successfully connected to " + myconn.getSchema());
        }catch (SQLException e) {
              ConnectDB.printSQLException(e);
        } catch (Exception e) {
          e.printStackTrace(System.err);
        } finally {
          ConnectDB.closeConnection(myconn);
        }

    }

Answer 1

MySQL doesn't support the concept of schema. For MySQL, the schema is in fact the database. From MySQL Glossary:

schema

Conceptually, a schema is a set of interrelated database objects, such as tables, table columns, data types of the columns, indexes, foreign keys, and so on. (...)

Based on this answer from MySQL forums, you can't get the current database (or databases) through Connection#getSchema method (which was added since Java 7 with JDBC 4) but using Connection#getCatalog unless you use the latest JDBC jar driver:

The JDBC driver (because of legacy, mysql didn't call them "schemas" until 5.0, and JDBC didn't have a way to select schemas until JDBC4), calls databases "catalogs", so thus you have to call getCatalogs() to get the list of databases

I made a dirty quick to prove the italic sentence above (unless you use the latest JDBC jar driver). Using getCatalog() worked using Java 6:

public class DirtyQuickTest {
    private static final String url = "jdbc:mysql://localhost:7841/test";
    private static final String user = "lmendozaj";
    private static final String password = "s3cr3t"; //I won't show you my password
    public static void main(String[] args) throws Exception {
        Connection con = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection(url, user, password);
            //commented since doesn't exists in Java 6
            //System.out.println(con.getSchema());
            System.out.println(con.getCatalog());
        } finally {
            con.close();
        }
    }
}

Output:

test

Then I executed the same code in Java 8 and uncommented the statement containing getSchema using mysql-connector-java-5.1.31-bin.jar (currently the latest Java Connector Driver for MySQL). This was the output:

null
test

Which means they still don't support getSchema method.

Related:

• Difference Between Schema / Database in MySQL