What is the proper way to shutdown a specific db and close the connection?

Question

I created the following three methods but am confused about what shutDownDB() is really doing. Here are my questions:

1. Why do I need an active connection to shutdown a specific database?
2. Doesn't it make more sense to close the connection and then shutdown the DB
3. Why do I need to close a connection if the specific database is shutdown?
4. How come I'm able to reestablish a connection after the specific db has been shut down?
5. What is the actual difference between shutting down a specific db and closing the connection?

NOTE: using derby in embedded mode

    public static Connection openDB(String dbFolderString) {
    Connection conn = null;
    try{ 
        File dbFolder = new File(dbFolderString);
        String URL = "jdbc:derby:" + dbFolderString + ";create=true"; 
        if(print)System.out.println("\n" + "db exists " + dbFolder.exists());

        conn = DriverManager.getConnection(URL);            
        if(print)System.out.println("Succesfully connected to " + dbFolderString);            
    }catch(SQLException e){
        System.out.println("FATAL ERROR: from getDB " + e);
        System.exit(0);            
    } 
    return conn;
    }   

    public static boolean shutDownDB(Connection conn) { 
    //shutsdown a specific database but DOES NOT SHUTDOWN DERBY
    try{
        String[] tokens;
        String url = conn.getMetaData().getURL(); 
        tokens = url.split(":");
        DriverManager.getConnection("jdbc:derby:" + tokens[2] +";shutdown=true");                      
    }catch(SQLException e1){
        if(e1.getSQLState().equals("08006") && e1.getErrorCode() == 45000){
            if(false)System.out.println(e1.getSQLState() + "  " + e1.getErrorCode());
            if(print)System.out.println("\n" + "Database shutdown successful"); 
        }else{
            System.out.println(e1.getSQLState() + "  " + e1.getErrorCode());
            System.out.println("FATAL ERROR: Database not shutdown  " + e1);
            System.exit(0);
        }
    }
    return true;
    }    

    public static void closeConnection(Connection conn){
    try{ 
        conn.close();
        if(print)System.out.println("Connection closed");
    }catch(SQLException e){            
        System.out.println("connection NOT closed " + e);
        System.exit(0);
    }        
    }

Answer 1

Lets start with what the Derby documentation says:

Shutting down Derby or an individual database

Applications in an embedded environment shut down the Derby system by specifying the shutdown=true attribute in the connection URL. To shut down the system, you do not specify a database name, and you do not ordinarily specify any other attribute.

jdbc:derby:;shutdown=true

A successful shutdown always results in an SQLException to indicate that Derby has shut down and that there is no other exception.

If you have enabled user authentication at the system level, you will need to specify credentials (that is, username and password) in order to shut down a Derby system, and the supplied username and password must also be defined at the system level.

and so on.

---

Your questions:

1) Why do I need an active connection to shutdown a specific database?

• Because the documentation says so.

• Because this is how they have implemented it.

• Because shutting down the database (in general) requires user authentication and connection establishment is the place where user authentication happens.

2) Doesn't it make more sense to close the connection and then shutdown the DB?

Given the last, no.

3) Why do I need to close a connection if the specific database is shutdown?

You don't need to if your application can cope with potential resource leakage; e.g. sockets that may not have been closed on the client side.

But it is certainly advisable if your application is going to want to continue after shutting down the database.

4) How come I'm able to reestablish a connection after the specific db has been shut down?

Presumably, because it is designed to allow that.

5) What is the actual difference between shutting down a specific db and closing the connection?

(I'm not entirely sure about this ....)

Shutting down a database will invalidate all connections for the specific database. It doesn't close them on the client side, so the sockets are liable to remain open .... until the part of your application that uses the respective collections tries to use them, discovers they are "dead" and closes them.

By contrast, closing a connection closes just that connection, both on the server and client side. Any sockets should be closed immediately.

---

NOTE: using derby in embedded mode

The documentation doesn't make a distinction for embedded and non-embedded mode.