Database connection open for entire life of object shared between 2 application

Question

Application:

1. Web application, which will act as a standalone application. [Servlet3.0 technologies]
2. Windows application, will be delivered as jar file.

Database:

1. MySQL

Database Helper class

We have database helper class which shared across both the above applications.

The database helper class has all static methods for e.g. below user validation method.

    public static boolean validate(String name, String pass) {
    Connection dbConnection = null;
    ResultSet resultSet = null;
    PreparedStatement preparedStatement = null;
    //checkDbConnection();

    try {
        dbConnection = getDBConnection();

        String query = "SELECT * FROM " + TABLE_USERS + " WHERE " + USERS_USERNAME + " = ? AND " + USERS_PASSWORD
                + " = ?";
        preparedStatement = ZMCdbConnection.prepareStatement(query);
        preparedStatement.setString(1, name);
        preparedStatement.setString(2, pass);
        resultSet = preparedStatement.executeQuery();
        return resultSet.next();

    } catch (Exception e) {
        System.out.println(e);
        return false;
    } finally {
        closeResources(dbConnection, preparedStatement, resultSet);
    }
}

Query

Here for each method, at the end we are closing all database resources like PreparedStatement, ResultSet, dbConnection.

and then we get below comments from senior code reviewer,

Wow, closing the database connection after every query? That is very inefficient. It is better to keep it open for the life of this object. (Note this may mean that queries need to be resilient to occasional disconnects, i.e. upon error try a simple query (like "select 1") and if it fails re-establish the connection and re-attempt the original query.

As per our understanding database connection should be always closed once the activity is finished. however to support reviewer's comment we have thought of 2 solution which we feel little less convincing.

Solution 1

Now, to change the logic where database connection to be kept open for entire life of object we have developed below method which will be called at top of the above method.

    private static void checkDbConnection() {
    if (ZMCdbConnection == null) {
        ZMCdbConnection = getDBConnection();
    }

    boolean querySuccess = false;
    try {
        String query = "select 1";
        PreparedStatement ps = ZMCdbConnection.prepareStatement(query);
        querySuccess = ps.execute();            
    } catch (Exception e) {
    }
    if (!querySuccess) {
        ZMCdbConnection = getDBConnection();
    }
}

Pros:

we assure that we get always dbconnection object which is open.

cons:

Unnecessary additional call at every method.

Solution 2

1. Open dbconnection once at very first time in database helper class in static block.
2. call database helper methods from Servlets and Execute the query.
3. If query fails, try "select 1" query to make sure dbconnection closed.
4. if that fails, send an "dbconnection close" exception to Servlet from where database helper method is been called.
5. Call the method again, where database connection will get open at step 1.

Can anybody suggest some other solution for the problem?

Answer 1

Let me answer my own question.

I know similar line of questions have been answered in the past but still let me try for my case, in case somebody needs.

Implementation: Connection pooling mechanism

Library used: Commons DBCP2

Implementation:

public class DatabaseHelper {
    public static final BasicDataSource dataSource = new BasicDataSource();

    static {
       setDataSourceParameter();
    }

    public static void setDataSourceParameter() {
       try {
        dataSource.setDriverClassName(H2_DB_DRIVER);
        dataSource.setUrl(H2_DB_CONNECTION);
        dataSource.setUsername(H2_DB_USER);
        dataSource.setPassword(H2_DB_PASSWORD);
        dataSource.setValidationQuery("SELECT 1");
        LOGGER.log(Level.DEBUG, "setDataSourceParameter()");
    } catch (Exception e) {
        LOGGER.log(Level.ERROR, e.getMessage(), e.fillInStackTrace());
     }
 } 

 /**
  * This method returns a database connection used for database query
  *
  * @return Connection
  */
 public static Connection getDBConnection() throws SQLException {
     LOGGER.log(Level.DEBUG, "getDBConnection() : getNumActive : " + dataSource.getNumActive());
     LOGGER.log(Level.DEBUG, "getDBConnection() : getNumIdle : " +       dataSource.getNumIdle());
     return dataSource.getConnection();
 }

 /**
  * This method closes database related resources
  *
  * @param connection
  * @param statement
  * @param resultSet
  */
  public static void closeResources(Connection connection, PreparedStatement statement, ResultSet resultSet) {
     try {
         if (statement != null) {
             statement.close();
         }
         if (resultSet != null) {
             resultSet.close();
         }
         if (connection != null) {
             connection.close();
          }
      }  catch (SQLException e) {
         LOGGER.log(Level.ERROR, e.getMessage(), e.fillInStackTrace());
     }
  }
}

public static boolean validate(String username, String password) {
    Connection dbConnection = null;
    ResultSet resultSet = null;
    PreparedStatement preparedStatement = null;

    try {
        dbConnection = getDBConnection();
        if (dbConnection == null) {
            return false;
        }

        String query = "SELECT * FROM " + TABLE_USERS + " WHERE " + USERS_USERNAME + " = ? AND " + USERS_PASSWORD
                + " = ?";
        LOGGER.log(Level.TRACE, "validate() : query " + query);
        preparedStatement = dbConnection.prepareStatement(query);
        preparedStatement.setString(1, username);
        preparedStatement.setString(2, password);
        resultSet = preparedStatement.executeQuery();

        return resultSet.next();
    } catch (Exception e) {
        LOGGER.log(Level.ERROR, e.getMessage(), e.fillInStackTrace());
        return false;
    } finally {
        closeResources(dbConnection, preparedStatement, resultSet);
    }
}

Creating database connection pool

call setDataSourceParameter() method at start of your application.

Using connection

call getDBConnection() method from the method where you will run sql query [refer validate() method].

Returning connection to pool

call closeResources() method when your query execution is finished [refer validate() method].