Static finally block in Java

Question

I have a class that uses a static initialization block to set up a connection to a database. The class has many public static methods that query the db. I would like to properly close this connection in a static block that executes just before the program terminates, kind of like a finally block in a try/catch. I am almost certain that something like this does not exist in Java. Is my best option to open and close the connection with each query?

Answer 1

Have a look at this : Running a method when closing the program?

You could try writing the code to close connection in this method.

public static void main(String[] args) {
    Runtime.getRuntime().addShutdownHook(new Thread(new Runnable() {
        public void run() {
            //code to close connection
        }
    }, "Shutdown-thread"));
}

Answer 2

Opening and closing the connection for every query will cause an additional overhead on system making the application slow.

You could surround the final query of your DB program with try catch blocks instead and release the connection in the finally clause (for the last query of your program).

NOTE: If the JVM terminates before the main thread finishes execution, i.e. System.exit() executes, the subsequent code and the finally block won't be executed.

Answer 3

public class JdbcDBManager {
private Connection connection = null;
private PreparedStatement preparedStatement = null;
private ResultSet resultSet = null;

public JdbcDBManager(String url, String user, String pass) throws ClassNotFoundException, SQLException {
    Class.forName("org.gjt.mm.mysql.Driver");
    this.connection = DriverManager.getConnection(url, user, pass);
}

public void close() {
    try {if (this.preparedStatement != null)this.preparedStatement.close();} catch (Exception e) {e.printStackTrace();}
    try {if (this.resultSet != null)this.resultSet.close();} catch (Exception e) {e.printStackTrace();}
    try {if (this.connection != null)this.connection.close();} catch (Exception e) {e.printStackTrace();}
}
public void customerInsert(Customer customer) {
    try {
        String query = "INSERT INTO customer(email,product) VALUES(?,?,?,?,?)";
        this.preparedStatement = this.connection.prepareStatement(query);
        this.preparedStatement.setString(1, customer.getEmail());
        this.preparedStatement.setString(3, customer.getProduct());
    } catch (Exception e) { e.printStackTrace();}
}}

You can create an object for each Database and when you are finally done processing close it.

public class test {

public static void process() throws ClassNotFoundException, SQLException {
    JdbcDBManager customerDB = new JdbcDBManager(JdbcURL.URL, JdbcURL.USER, JdbcURL.PASS);
    try {
        customerDB.insertCustomer(Customer customer);
        doSomething(customerDB); // Pass db object as a parameter
    } finally { customerDB.close();} // close it when you are finally done
}
doSomething(JdbcDBManager customerDB){
     ---------------------------
     --process info in db-------
} }

This way you open connection for one time and close when process are finally finished

Answer 4

Is my best option to open and close the connection with each query? Ans : NO

I would suggest you to follow:

Singleton Class for opening the connection, something like this :

public class connectDB {
static Connection conn = null;

public static Connection getConnection(){
    if (conn != null) return conn;
    String connString = "DATABASE ACCESS URL HERE";
    return getConnection(connString);
}

private static Connection getConnection(String conString){
    try{
        Class.forName("LOAD DRIVER HERE");
        String uname = "DB USERNAME";
        String pass = "DB PASSWORD";
        conn = DriverManager.getConnection(conString, uname, pass);  
    }
    catch(Exception e){
        //Handle Exceptions
        e.printStackTrace(); //<--Retrieves the error/Exception for you
    }
    return conn;
}
}

And close the connection with something like :

public static void closeConnection(Connection conn) {
try {
    conn.close();
}
catch (SQLException e) {
    //Handle Exception Here
}
}

Just call conn = connectDB.getConnection() for connection and the other one for closing, preferable in a finally