Sql query blocker issue fixed.

Rdundant code and complextity of methods reduced.
@override annotaiton added.
Exception handling and logging impoved.

Issue-ID:CCSDK-87

Change-Id: I3907be73a2f6ccdfadeaed79c87705ae7afdb4ab
Signed-off-by: Bharat saraswal <bharat.saraswal@huawei.com>

2 files changed, 627 insertions, 729 deletions

diff --git a/dblib/provider/src/main/java/org/onap/ccsdk/sli/core/dblib/CachedDataSource.java b/dblib/provider/src/main/java/org/onap/ccsdk/sli/core/dblib/CachedDataSource.java
index 072a6f47..356f5ee8 100755
--- a/dblib/provider/src/main/java/org/onap/ccsdk/sli/core/dblib/CachedDataSource.java
+++ b/dblib/provider/src/main/java/org/onap/ccsdk/sli/core/dblib/CachedDataSource.java
@@ -20,17 +20,6 @@
 
 package org.onap.ccsdk.sli.core.dblib;
 
-import org.apache.tomcat.jdbc.pool.PoolExhaustedException;
-import org.onap.ccsdk.sli.core.dblib.config.BaseDBConfiguration;
-import org.onap.ccsdk.sli.core.dblib.pm.SQLExecutionMonitor;
-import org.onap.ccsdk.sli.core.dblib.pm.SQLExecutionMonitor.TestObject;
-import org.onap.ccsdk.sli.core.dblib.pm.SQLExecutionMonitorObserver;
-import org.slf4j.Logger;
-import org.slf4j.LoggerFactory;
-
-import javax.sql.DataSource;
-import javax.sql.rowset.CachedRowSet;
-import javax.sql.rowset.RowSetProvider;
 import java.io.Closeable;
 import java.io.IOException;
 import java.io.PrintWriter;
@@ -45,8 +34,18 @@ import java.sql.Statement;
 import java.sql.Timestamp;
 import java.util.ArrayList;
 import java.util.Arrays;
+import java.util.List;
 import java.util.Observer;
-
+import javax.sql.DataSource;
+import javax.sql.rowset.CachedRowSet;
+import javax.sql.rowset.RowSetProvider;
+import org.apache.tomcat.jdbc.pool.PoolExhaustedException;
+import org.onap.ccsdk.sli.core.dblib.config.BaseDBConfiguration;
+import org.onap.ccsdk.sli.core.dblib.pm.SQLExecutionMonitor;
+import org.onap.ccsdk.sli.core.dblib.pm.SQLExecutionMonitor.TestObject;
+import org.onap.ccsdk.sli.core.dblib.pm.SQLExecutionMonitorObserver;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
 
 /**
  * @version $Revision: 1.13 $
@@ -56,582 +55,502 @@ import java.util.Observer;
  * Rich Tabedzki
  */
 
-public abstract class CachedDataSource implements DataSource, SQLExecutionMonitorObserver
-{
-	private static Logger LOGGER = LoggerFactory.getLogger(CachedDataSource.class);
-
-	protected static final String AS_CONF_ERROR = "AS_CONF_ERROR: ";
-
-	protected long CONN_REQ_TIMEOUT = 30L;
-	protected long DATA_REQ_TIMEOUT = 100L;
-
-	private final SQLExecutionMonitor monitor;
-	protected DataSource ds = null;
-	protected String connectionName = null;
-	protected boolean initialized = false;
-
-	private long interval = 1000;
-	private long initialDelay = 5000;
-	private long expectedCompletionTime = 50L;
-	private boolean canTakeOffLine = true;
-	private long unprocessedFailoverThreshold = 3L;
-
-	private long nextErrorReportTime = 0L;
-
-	private String globalHostName = null;
-
-
-	public CachedDataSource(BaseDBConfiguration jdbcElem) throws DBConfigException
-	{
-		configure(jdbcElem);
-		monitor = new SQLExecutionMonitor(this);
-	}
-
-	protected abstract void configure(BaseDBConfiguration jdbcElem) throws DBConfigException;
-	/* (non-Javadoc)
-	 * @see javax.sql.DataSource#getConnection()
-	 */
-	@Override
-	public Connection getConnection() throws SQLException
-	{
-		return ds.getConnection();
-	}
-
-	public CachedRowSet getData(String statement, ArrayList<Object> arguments)
-			throws SQLException, Throwable
-	{
-		TestObject testObject = null;
-		testObject = monitor.registerRequest();
-
-		Connection connection = null;
-		try {
-			connection = this.getConnection();
-			if(connection ==  null ) {
-				throw new SQLException("Connection invalid");
-			}
-			if(LOGGER.isDebugEnabled())
-				LOGGER.debug("Obtained connection <" + connectionName + ">: "+connection.toString());
-			return executePreparedStatement(connection, statement, arguments, true);
-		} finally {
-			try {
-				if(connection != null && !connection.isClosed()) {
-					connection.close();
-				}
-			} catch(Throwable exc) {
-				// the exception not monitored
-			} finally {
-				connection = null;
-			}
-
-			monitor.deregisterRequest(testObject);
-		}
-	}
-
-	public boolean writeData(String statement, ArrayList<Object> arguments)
-			throws SQLException, Throwable
-	{
-		TestObject testObject = null;
-		testObject = monitor.registerRequest();
-
-		Connection connection = null;
-		try {
-			connection = this.getConnection();
-			if(connection ==  null ) {
-				throw new SQLException("Connection invalid");
-			}
-			if(LOGGER.isDebugEnabled())
-				LOGGER.debug("Obtained connection <" + connectionName + ">: "+connection.toString());
-			return executeUpdatePreparedStatement(connection, statement, arguments, true);
-		} finally {
-			try {
-				if(connection != null && !connection.isClosed()) {
-					connection.close();
-				}
-			} catch(Throwable exc) {
-				// the exception not monitored
-			} finally {
-				connection = null;
-			}
-
-			monitor.deregisterRequest(testObject);
-		}
-	}
-
-	CachedRowSet executePreparedStatement(Connection conn, String statement,
-			ArrayList<Object> arguments, boolean close) throws SQLException, Throwable
-	{
-		long time = System.currentTimeMillis();
-
-		CachedRowSet data = null;
-		if(LOGGER.isDebugEnabled()){
-			LOGGER.debug("SQL Statement: "+ statement);
-			if(arguments != null && !arguments.isEmpty()) {
-				LOGGER.debug("Argunments: "+ Arrays.toString(arguments.toArray()));
-			}
-		}
-
-		ResultSet rs = null;
-		PreparedStatement ps = null;
-		try {
-			data = RowSetProvider.newFactory().createCachedRowSet();
-			ps = conn.prepareStatement(statement);
-			if(arguments != null)
-			{
-				for(int i = 0, max = arguments.size(); i < max; i++){
-					ps.setObject(i+1, arguments.get(i));
-				}
-			}
-			rs = ps.executeQuery();
-			data.populate(rs);
-		    // Point the rowset Cursor to the start
-			if(LOGGER.isDebugEnabled()){
-				LOGGER.debug("SQL SUCCESS. rows returned: " + data.size()+ ", time(ms): "+ (System.currentTimeMillis() - time));			}
-		} catch(SQLException exc){
-			if(LOGGER.isDebugEnabled()){
-				LOGGER.debug("SQL FAILURE. time(ms): "+ (System.currentTimeMillis() - time));
-			}
-			try {	conn.rollback(); } catch(Throwable thr){}
-			if(arguments != null && !arguments.isEmpty()) {
-				LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with arguments: "+arguments.toString(), exc);
-			} else {
-				LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with no arguments. ", exc);
-			}
-			throw exc;
-		} catch(Throwable exc){
-			if(LOGGER.isDebugEnabled()){
-				LOGGER.debug("SQL FAILURE. time(ms): "+ (System.currentTimeMillis() - time));
-			}
-			if(arguments != null && !arguments.isEmpty()) {
-				LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with arguments: "+arguments.toString(), exc);
-			} else {
-				LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with no arguments. ", exc);
-			}
-			throw exc; // new SQLException(exc);
-		} finally {
-
-			try {
-				if(rs != null){
-					rs.close();
-					rs = null;
-				}
-			} catch(Exception exc){
-
-			}
-			try {
-				if(conn != null && close){
-					conn.close();
-					conn = null;
-				}
-			} catch(Exception exc){
-
-			}
-			try {
-				if (ps != null){
-					ps.close();
-				}
-			} catch (Exception exc){
-
-			}
-		}
-
-		return data;
-	}
-
-	boolean executeUpdatePreparedStatement(Connection conn, String statement, ArrayList<Object> arguments, boolean close) throws SQLException, Throwable {
-		long time = System.currentTimeMillis();
-
-		CachedRowSet data = null;
-
-		int rs = -1;
-		try {
-			data = RowSetProvider.newFactory().createCachedRowSet();
-			PreparedStatement ps = conn.prepareStatement(statement);
-			if(arguments != null)
-			{
-				for(int i = 0, max = arguments.size(); i < max; i++){
-					if(arguments.get(i) instanceof Blob) {
-						ps.setBlob(i+1, (Blob)arguments.get(i));
-					} else	if(arguments.get(i) instanceof Timestamp) {
-						ps.setTimestamp(i+1, (Timestamp)arguments.get(i));
-					} else	if(arguments.get(i) instanceof Integer) {
-						ps.setInt(i+1, (Integer)arguments.get(i));
-					} else	if(arguments.get(i) instanceof Long) {
-						ps.setLong(i+1, (Long)arguments.get(i));
-					} else	if(arguments.get(i) instanceof Date) {
-						ps.setDate(i+1, (Date)arguments.get(i));
-					} else {
-					ps.setObject(i+1, arguments.get(i));
-				}
-			}
-			}
-			rs = ps.executeUpdate();
-		    // Point the rowset Cursor to the start
-			if(LOGGER.isDebugEnabled()){
-				LOGGER.debug("SQL SUCCESS. rows returned: " + data.size()+ ", time(ms): "+ (System.currentTimeMillis() - time));
-			}
-		} catch(SQLException exc){
-			if(LOGGER.isDebugEnabled()){
-				LOGGER.debug("SQL FAILURE. time(ms): "+ (System.currentTimeMillis() - time));
-			}
-			try {	conn.rollback(); } catch(Throwable thr){}
-			if(arguments != null && !arguments.isEmpty()) {
-				LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with arguments: "+arguments.toString(), exc);
-			} else {
-				LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with no arguments. ", exc);
-			}
-			throw exc;
-		} catch(Throwable exc){
-			if(LOGGER.isDebugEnabled()){
-				LOGGER.debug("SQL FAILURE. time(ms): "+ (System.currentTimeMillis() - time));
-			}
-			if(arguments != null && !arguments.isEmpty()) {
-				LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with arguments: "+arguments.toString(), exc);
-			} else {
-				LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with no arguments. ", exc);
-			}
-			throw exc; // new SQLException(exc);
-		} finally {
-			try {
-				if(conn != null && close){
-					conn.close();
-					conn = null;
-				}
-			} catch(Exception exc){
-
-			}
-		}
-
-		return true;
-	}
-
-	/* (non-Javadoc)
-	 * @see javax.sql.DataSource#getConnection(java.lang.String, java.lang.String)
-	 */
-	@Override
-	public Connection getConnection(String username, String password)
-			throws SQLException
-	{
-		return ds.getConnection(username, password);
-	}
-
-	/* (non-Javadoc)
-	 * @see javax.sql.DataSource#getLogWriter()
-	 */
-	@Override
-	public PrintWriter getLogWriter() throws SQLException
-	{
-		return ds.getLogWriter();
-	}
-
-	/* (non-Javadoc)
-	 * @see javax.sql.DataSource#getLoginTimeout()
-	 */
-	@Override
-	public int getLoginTimeout() throws SQLException
-	{
-		return ds.getLoginTimeout();
-	}
-
-	/* (non-Javadoc)
-	 * @see javax.sql.DataSource#setLogWriter(java.io.PrintWriter)
-	 */
-	@Override
-	public void setLogWriter(PrintWriter out) throws SQLException
-	{
-		ds.setLogWriter(out);
-	}
-
-	/* (non-Javadoc)
-	 * @see javax.sql.DataSource#setLoginTimeout(int)
-	 */
-	@Override
-	public void setLoginTimeout(int seconds) throws SQLException
-	{
-		ds.setLoginTimeout(seconds);
-	}
-
-
-	@Override
-	public final String getDbConnectionName(){
-		return connectionName;
-	}
-
-	protected final void setDbConnectionName(String name) {
-		this.connectionName = name;
-	}
-
-	public void cleanUp(){
-		if(ds != null && ds instanceof Closeable) {
-			try {
-				((Closeable)ds).close();
-			} catch (IOException e) {
-				LOGGER.warn(e.getMessage());
-			}
-		}
-		ds = null;
-		monitor.deleteObservers();
-		monitor.cleanup();
-	}
-
-	public boolean isInitialized() {
-		return initialized;
-	}
-
-	protected boolean testConnection(){
-		return testConnection(false);
-	}
-
-	protected boolean testConnection(boolean error_level){
-		Connection conn = null;
-		ResultSet rs = null;
-		Statement stmt = null;
-		try
-		{
-			Boolean readOnly = null;
-			String hostname = null;
-			conn = this.getConnection();
-			stmt = conn.createStatement();
-			rs = stmt.executeQuery("SELECT @@global.read_only, @@global.hostname");   //("SELECT 1 FROM DUAL"); //"select BANNER from SYS.V_$VERSION"
-			while(rs.next())
-			{
-				readOnly = rs.getBoolean(1);
-				hostname = rs.getString(2);
-
-					if(LOGGER.isDebugEnabled()){
-						LOGGER.debug("SQL DataSource <"+getDbConnectionName() + "> connected to " + hostname + ", read-only is " + readOnly + ", tested successfully ");
-					}
-			}
-
-		} catch (Throwable exc) {
-			if(error_level) {
-				LOGGER.error("SQL DataSource <" + this.getDbConnectionName() +	"> test failed. Cause : " + exc.getMessage());
-			} else {
-				LOGGER.info("SQL DataSource <" + this.getDbConnectionName() +	"> test failed. Cause : " + exc.getMessage());
-			}
-			return false;
-		} finally {
-			if(rs != null) {
-				try {
-					rs.close();
-					rs = null;
-				} catch (SQLException e) {
-				}
-			}
-			if(stmt != null) {
-				try {
-					stmt.close();
-					stmt = null;
-				} catch (SQLException e) {
-				}
-			}
-			if(conn !=null){
-				try {
-					conn.close();
-					conn = null;
-				} catch (SQLException e) {
-				}
-			}
-		}
-		return true;
-	}
-
-	@Override
-	public boolean isWrapperFor(Class<?> iface) throws SQLException {
-		return false;
-	}
-
-	@Override
-	public <T> T unwrap(Class<T> iface) throws SQLException {
-		return null;
-	}
-
-	@SuppressWarnings("deprecation")
-	public void setConnectionCachingEnabled(boolean state)
-	{
+public abstract class CachedDataSource implements DataSource, SQLExecutionMonitorObserver {
+
+    private static final Logger LOGGER = LoggerFactory.getLogger(CachedDataSource.class);
+
+    private static final String SQL_FAILURE = "SQL FAILURE. time(ms): ";
+    private static final String FAILED_TO_EXECUTE = "> Failed to execute: ";
+    private static final String WITH_ARGUMENTS = " with arguments: ";
+    private static final String WITH_NO_ARGUMENTS = " with no arguments. ";
+    private static final String SQL_DATA_SOURCE = "SQL DataSource <";
+
+
+    protected long connReqTimeout = 30L;
+    protected long dataReqTimeout = 100L;
+
+    private final SQLExecutionMonitor monitor;
+    protected DataSource ds = null;
+    protected String connectionName = null;
+    protected boolean initialized = false;
+
+    private long interval = 1000;
+    private long initialDelay = 5000;
+    private long expectedCompletionTime = 50L;
+    private boolean canTakeOffLine = true;
+    private long unprocessedFailoverThreshold = 3L;
+
+    private long nextErrorReportTime = 0L;
+
+    private String globalHostName = null;
+
+
+    public CachedDataSource(BaseDBConfiguration jdbcElem) throws DBConfigException {
+        configure(jdbcElem);
+        monitor = new SQLExecutionMonitor(this);
+    }
+
+    protected abstract void configure(BaseDBConfiguration jdbcElem) throws DBConfigException;
+
+    /* (non-Javadoc)
+     * @see javax.sql.DataSource#getConnection()
+     */
+    @Override
+    public Connection getConnection() throws SQLException {
+        return ds.getConnection();
+    }
+
+    public CachedRowSet getData(String statement, List<Object> arguments)
+        throws SQLException {
+        TestObject testObject = monitor.registerRequest();
+
+        try (Connection connection = this.getConnection()) {
+            if (connection == null) {
+                throw new SQLException("Connection invalid");
+            }
+            if (LOGGER.isDebugEnabled()) {
+                LOGGER.debug("Obtained connection <" + connectionName + ">: " + connection.toString());
+            }
+            return executePreparedStatement(connection, statement, arguments, true);
+        } finally {
+            monitor.deregisterRequest(testObject);
+        }
+    }
+
+    public boolean writeData(String statement, List<Object> arguments)
+        throws SQLException {
+        TestObject testObject = monitor.registerRequest();
+
+        try (Connection connection = this.getConnection()) {
+            if (connection == null) {
+                throw new SQLException("Connection invalid");
+            }
+            if (LOGGER.isDebugEnabled()) {
+                LOGGER.debug("Obtained connection <" + connectionName + ">: " + connection.toString());
+            }
+            return executeUpdatePreparedStatement(connection, statement, arguments, true);
+        } finally {
+            monitor.deregisterRequest(testObject);
+        }
+    }
+
+    CachedRowSet executePreparedStatement(Connection conn, String statement,
+        List<Object> arguments, boolean close) throws SQLException {
+        long time = System.currentTimeMillis();
+
+        CachedRowSet data = null;
+        if (LOGGER.isDebugEnabled()) {
+            LOGGER.debug("SQL Statement: " + statement);
+            if (arguments != null && !arguments.isEmpty()) {
+                LOGGER.debug("Argunments: " + Arrays.toString(arguments.toArray()));
+            }
+        }
+
+        ResultSet rs = null;
+        try (PreparedStatement ps = conn.prepareStatement(statement)) {
+            data = RowSetProvider.newFactory().createCachedRowSet();
+            if (arguments != null) {
+                for (int i = 0, max = arguments.size(); i < max; i++) {
+                    ps.setObject(i + 1, arguments.get(i));
+                }
+            }
+            rs = ps.executeQuery();
+            data.populate(rs);
+            // Point the rowset Cursor to the start
+            if (LOGGER.isDebugEnabled()) {
+                LOGGER.debug("SQL SUCCESS. rows returned: " + data.size() + ", time(ms): " + (System.currentTimeMillis()
+                    - time));
+            }
+        } catch (SQLException exc) {
+            handleSqlExceptionForExecuteStatement(conn, statement, arguments, exc, time);
+        } finally {
+            handleFinallyBlockForExecutePreparedStatement(rs, conn, close);
+        }
+
+        return data;
+    }
+
+    private void handleSqlExceptionForExecuteStatement(Connection conn, String statement,
+        List<Object> arguments, SQLException exc, long time) throws SQLException {
+        if (LOGGER.isDebugEnabled()) {
+            LOGGER.debug(SQL_FAILURE + (System.currentTimeMillis() - time));
+        }
+        try {
+            conn.rollback();
+        } catch (Exception thr) {
+            LOGGER.error(thr.getLocalizedMessage(), thr);
+        }
+        if (arguments != null && !arguments.isEmpty()) {
+            LOGGER.error(String.format("<%s%s%s%s%s", connectionName, FAILED_TO_EXECUTE, statement, WITH_ARGUMENTS,
+                arguments.toString()), exc);
+        } else {
+            LOGGER.error(String.format("<%s%s%s%s", connectionName, FAILED_TO_EXECUTE, statement, WITH_NO_ARGUMENTS),
+                exc);
+        }
+        throw exc;
+    }
+
+    private void handleFinallyBlockForExecutePreparedStatement(ResultSet rs, Connection conn, boolean close) {
+        try {
+            if (rs != null) {
+                rs.close();
+            }
+        } catch (Exception exc) {
+            LOGGER.error(exc.getLocalizedMessage(), exc);
+        }
+        try {
+            if (conn != null && close) {
+                conn.close();
+            }
+        } catch (Exception exc) {
+            LOGGER.error(exc.getLocalizedMessage(), exc);
+        }
+    }
+
+    boolean executeUpdatePreparedStatement(Connection conn, String statement, List<Object> arguments,
+        boolean close) throws SQLException {
+        long time = System.currentTimeMillis();
+
+        CachedRowSet data;
+
+        try (PreparedStatement ps = conn.prepareStatement(statement)) {
+            data = RowSetProvider.newFactory().createCachedRowSet();
+            if (arguments != null) {
+                prepareStatementForExecuteUpdate(arguments, ps);
+            }
+            ps.executeUpdate();
+            // Point the rowset Cursor to the start
+            if (LOGGER.isDebugEnabled()) {
+                LOGGER.debug("SQL SUCCESS. rows returned: " + data.size() + ", time(ms): " + (System.currentTimeMillis()
+                    - time));
+            }
+            ps.close();
+        } catch (SQLException exc) {
+            handleSqlExceptionForExecuteStatement(conn, statement, arguments, exc, time);
+        } finally {
+            try {
+                if (close) {
+                    conn.close();
+                }
+            } catch (Exception exc) {
+                LOGGER.error(exc.getLocalizedMessage(), exc);
+            }
+        }
+
+        return true;
+    }
+
+    private void prepareStatementForExecuteUpdate(List<Object> arguments, PreparedStatement ps)
+        throws SQLException {
+        for (int i = 0, max = arguments.size(); i < max; i++) {
+            Object value = arguments.get(i);
+            if (value instanceof Blob) {
+                ps.setBlob(i + 1, (Blob) value);
+            } else if (value instanceof Timestamp) {
+                ps.setTimestamp(i + 1, (Timestamp) value);
+            } else if (value instanceof Integer) {
+                ps.setInt(i + 1, (Integer) value);
+            } else if (value instanceof Long) {
+                ps.setLong(i + 1, (Long) value);
+            } else if (value instanceof Date) {
+                ps.setDate(i + 1, (Date) value);
+            } else {
+                ps.setObject(i + 1, value);
+            }
+        }
+    }
+
+    /* (non-Javadoc)
+     * @see javax.sql.DataSource#getConnection(java.lang.String, java.lang.String)
+     */
+    @Override
+    public Connection getConnection(String username, String password)
+        throws SQLException {
+        return ds.getConnection(username, password);
+    }
+
+    /* (non-Javadoc)
+     * @see javax.sql.DataSource#getLogWriter()
+     */
+    @Override
+    public PrintWriter getLogWriter() throws SQLException {
+        return ds.getLogWriter();
+    }
+
+    /* (non-Javadoc)
+     * @see javax.sql.DataSource#getLoginTimeout()
+     */
+    @Override
+    public int getLoginTimeout() throws SQLException {
+        return ds.getLoginTimeout();
+    }
+
+    /* (non-Javadoc)
+     * @see javax.sql.DataSource#setLogWriter(java.io.PrintWriter)
+     */
+    @Override
+    public void setLogWriter(PrintWriter out) throws SQLException {
+        ds.setLogWriter(out);
+    }
+
+    /* (non-Javadoc)
+     * @see javax.sql.DataSource#setLoginTimeout(int)
+     */
+    @Override
+    public void setLoginTimeout(int seconds) throws SQLException {
+        ds.setLoginTimeout(seconds);
+    }
+
+
+    @Override
+    public final String getDbConnectionName() {
+        return connectionName;
+    }
+
+    protected final void setDbConnectionName(String name) {
+        this.connectionName = name;
+    }
+
+    public void cleanUp() {
+        if (ds != null && ds instanceof Closeable) {
+            try {
+                ((Closeable) ds).close();
+            } catch (IOException e) {
+                LOGGER.warn(e.getMessage());
+            }
+        }
+        ds = null;
+        monitor.deleteObservers();
+        monitor.cleanup();
+    }
+
+    public boolean isInitialized() {
+        return initialized;
+    }
+
+    protected boolean testConnection() {
+        return testConnection(false);
+    }
+
+    protected boolean testConnection(boolean errorLevel) {
+        ResultSet rs = null;
+        try (Connection conn = this.getConnection(); Statement stmt = conn.createStatement()) {
+            Boolean readOnly;
+            String hostname;
+            rs = stmt.executeQuery(
+                "SELECT @@global.read_only, @@global.hostname");   //("SELECT 1 FROM DUAL"); //"select BANNER from SYS.V_$VERSION"
+            while (rs.next()) {
+                readOnly = rs.getBoolean(1);
+                hostname = rs.getString(2);
+
+                if (LOGGER.isDebugEnabled()) {
+                    LOGGER.debug(
+                        SQL_DATA_SOURCE + getDbConnectionName() + "> connected to " + hostname + ", read-only is "
+                            + readOnly + ", tested successfully ");
+                }
+            }
+        } catch (Exception exc) {
+            if (errorLevel) {
+                LOGGER.error(
+                    SQL_DATA_SOURCE + this.getDbConnectionName() + "> test failed. Cause : " + exc.getMessage());
+            } else {
+                LOGGER.info(
+                    SQL_DATA_SOURCE + this.getDbConnectionName() + "> test failed. Cause : " + exc.getMessage());
+            }
+            return false;
+        } finally {
+            if (rs != null) {
+                try {
+                    rs.close();
+                } catch (SQLException e) {
+                    LOGGER.error(e.getLocalizedMessage(), e);
+                }
+            }
+        }
+        return true;
+    }
+
+    @Override
+    public boolean isWrapperFor(Class<?> iface) throws SQLException {
+        return false;
+    }
+
+    @Override
+    public <T> T unwrap(Class<T> iface) throws SQLException {
+        return null;
+    }
+
+    @SuppressWarnings("deprecation")
+    public void setConnectionCachingEnabled(boolean state) {
 //		if(ds != null && ds instanceof OracleDataSource)
 //			try {
 //				((OracleDataSource)ds).setConnectionCachingEnabled(true);
 //			} catch (SQLException exc) {
 //				LOGGER.warn("", exc);
 //			}
-	}
-
-	public void addObserver(Observer observer) {
-		monitor.addObserver(observer);
-	}
-
-	public void deleteObserver(Observer observer) {
-		monitor.deleteObserver(observer);
-	}
-
-	@Override
-	public long getInterval() {
-		return interval;
-	}
-
-	@Override
-	public long getInitialDelay() {
-		return initialDelay;
-	}
-
-	@Override
-	public void setInterval(long value) {
-		interval = value;
-	}
-
-	@Override
-	public void setInitialDelay(long value) {
-		initialDelay = value;
-	}
-
-	@Override
-	public long getExpectedCompletionTime() {
-		return expectedCompletionTime;
-	}
-
-	@Override
-	public void setExpectedCompletionTime(long value) {
-		expectedCompletionTime = value;
-	}
-
-	@Override
-	public long getUnprocessedFailoverThreshold() {
-		return unprocessedFailoverThreshold;
-	}
-
-	@Override
-	public void setUnprocessedFailoverThreshold(long value) {
-		this.unprocessedFailoverThreshold = value;
-	}
-
-	public boolean canTakeOffLine() {
-		return canTakeOffLine;
-	}
-
-	public void blockImmediateOffLine() {
-		canTakeOffLine = false;
-		final Thread offLineTimer = new Thread()
-		{
-			@Override
-			public void run(){
-				try {
-					Thread.sleep(30000L);
-				}catch(Throwable exc){
-
-				}finally{
-					canTakeOffLine = true;
-				}
-			}
-		};
-		offLineTimer.setDaemon(true);
-		offLineTimer.start();
-	}
-
-	/**
-	 * @return the monitor
-	 */
-	final SQLExecutionMonitor getMonitor() {
-		return monitor;
-	}
-
-	protected boolean isSlave() throws PoolExhaustedException {
-		CachedRowSet rs = null;
-		boolean isSlave = true;
-		String hostname = "UNDETERMINED";
-		try {
-			boolean localSlave = true;
-			rs = this.getData("SELECT @@global.read_only, @@global.hostname", new ArrayList<Object>());
-			while(rs.next()) {
-				localSlave = rs.getBoolean(1);
-				hostname = rs.getString(2);
-			}
-			isSlave = localSlave;
-		} catch(PoolExhaustedException peexc){
-			throw peexc;
-		} catch (SQLException e) {
-			LOGGER.error("", e);
-			isSlave = true;
-		} catch (Throwable e) {
-			LOGGER.error("", e);
-			isSlave = true;
-		}
-		if(isSlave){
-			LOGGER.debug("SQL SLAVE : "+connectionName + " on server " + hostname);
-		} else {
-			LOGGER.debug("SQL MASTER : "+connectionName + " on server " + hostname);
-		}
-		return isSlave;
-	}
-
-	public boolean isFabric() {
-		return false;
-	}
-
-	protected boolean lockTable(Connection conn, String tableName) {
-		boolean retValue = false;
-		Statement lock = null;
-		try {
-			if(tableName != null) {
-				if(LOGGER.isDebugEnabled()) {
-					LOGGER.debug("Executing 'LOCK TABLES " + tableName + " WRITE' on connection " + conn.toString());
-					if("SVC_LOGIC".equals(tableName)) {
-						Exception e = new Exception();
-						StringWriter sw = new StringWriter();
-						PrintWriter pw = new PrintWriter(sw);
-						e.printStackTrace(pw);
-						LOGGER.debug(sw.toString());
-					}
-				}
-				lock = conn.createStatement();
-				lock.execute("LOCK TABLES " + tableName + " WRITE");
-				retValue = true;
-			}
-		} catch(Exception exc){
-			LOGGER.error("", exc);
-			retValue =  false;
-		} finally {
-			try {
-                            if (lock != null) {
-                                lock.close();
-                            }
-			} catch(Exception exc) {
-
-			}
-		}
-		return retValue;
-	}
-
-	protected boolean unlockTable(Connection conn) {
-		boolean retValue = false;
-		try (Statement lock = conn.createStatement()){
-			if(LOGGER.isDebugEnabled()) {
-				LOGGER.debug("Executing 'UNLOCK TABLES' on connection " + conn.toString());
-			}
-			retValue = lock.execute("UNLOCK TABLES");
-		} catch(Exception exc){
-			LOGGER.error("", exc);
-			retValue =  false;
-		}
-		return retValue;
-	}
-
-	public void getPoolInfo(boolean allocation) {
-
-	}
-
-	public long getNextErrorReportTime() {
-		return nextErrorReportTime;
-	}
-
-	public void setNextErrorReportTime(long nextTime) {
-		this.nextErrorReportTime = nextTime;
-	}
-
-	public void setGlobalHostName(String hostname) {
-		this.globalHostName  = hostname;
-	}
-
-	public String getGlobalHostName() {
-		return globalHostName;
-	}
+    }
+
+    public void addObserver(Observer observer) {
+        monitor.addObserver(observer);
+    }
+
+    public void deleteObserver(Observer observer) {
+        monitor.deleteObserver(observer);
+    }
+
+    @Override
+    public long getInterval() {
+        return interval;
+    }
+
+    @Override
+    public long getInitialDelay() {
+        return initialDelay;
+    }
+
+    @Override
+    public void setInterval(long value) {
+        interval = value;
+    }
+
+    @Override
+    public void setInitialDelay(long value) {
+        initialDelay = value;
+    }
+
+    @Override
+    public long getExpectedCompletionTime() {
+        return expectedCompletionTime;
+    }
+
+    @Override
+    public void setExpectedCompletionTime(long value) {
+        expectedCompletionTime = value;
+    }
+
+    @Override
+    public long getUnprocessedFailoverThreshold() {
+        return unprocessedFailoverThreshold;
+    }
+
+    @Override
+    public void setUnprocessedFailoverThreshold(long value) {
+        this.unprocessedFailoverThreshold = value;
+    }
+
+    public boolean canTakeOffLine() {
+        return canTakeOffLine;
+    }
+
+    public void blockImmediateOffLine() {
+        canTakeOffLine = false;
+        final Thread offLineTimer = new Thread(() -> {
+            try {
+                Thread.sleep(30000L);
+            } catch (Exception exc) {
+                LOGGER.error(exc.getLocalizedMessage(), exc);
+            } finally {
+                canTakeOffLine = true;
+            }
+        });
+        offLineTimer.setDaemon(true);
+        offLineTimer.start();
+    }
+
+    /**
+     * @return the monitor
+     */
+    final SQLExecutionMonitor getMonitor() {
+        return monitor;
+    }
+
+    protected boolean isSlave() throws PoolExhaustedException {
+        CachedRowSet rs = null;
+        boolean isSlave = true;
+        String hostname = "UNDETERMINED";
+        try {
+            boolean localSlave = true;
+            rs = this.getData("SELECT @@global.read_only, @@global.hostname", new ArrayList<Object>());
+            while (rs.next()) {
+                localSlave = rs.getBoolean(1);
+                hostname = rs.getString(2);
+            }
+            isSlave = localSlave;
+        } catch (PoolExhaustedException peexc) {
+            throw peexc;
+        } catch (Exception e) {
+            LOGGER.error("", e);
+            isSlave = true;
+        }
+        if (isSlave) {
+            LOGGER.debug(String.format("SQL SLAVE : %s on server %s", connectionName, hostname));
+        } else {
+            LOGGER.debug(String.format("SQL MASTER : %s on server %s", connectionName, hostname));
+        }
+        return isSlave;
+    }
+
+    public boolean isFabric() {
+        return false;
+    }
+
+    protected boolean lockTable(Connection conn, String tableName) {
+        boolean retValue = false;
+        String query = "LOCK TABLES " + tableName + " WRITE";
+        try (Statement preStmt = conn.createStatement(); Statement lock = conn.prepareStatement(query);
+            ResultSet rs = preStmt.executeQuery("GETDATE()")) {
+            if (tableName != null) {
+                if (LOGGER.isDebugEnabled()) {
+                    LOGGER.debug("Executing 'LOCK TABLES " + tableName + " WRITE' on connection " + conn.toString());
+                    if ("SVC_LOGIC".equals(tableName)) {
+                        Exception e = new Exception();
+                        StringWriter sw = new StringWriter();
+                        PrintWriter pw = new PrintWriter(sw);
+                        e.printStackTrace(pw);
+                        LOGGER.debug(sw.toString());
+                    }
+                }
+                lock.execute(query);
+                retValue = true;
+            }
+        } catch (Exception exc) {
+            LOGGER.error("", exc);
+            retValue = false;
+        }
+        return retValue;
+    }
+
+    protected boolean unlockTable(Connection conn) {
+        boolean retValue;
+        try (Statement lock = conn.createStatement()) {
+            if (LOGGER.isDebugEnabled()) {
+                LOGGER.debug("Executing 'UNLOCK TABLES' on connection " + conn.toString());
+            }
+            retValue = lock.execute("UNLOCK TABLES");
+        } catch (Exception exc) {
+            LOGGER.error("", exc);
+            retValue = false;
+        }
+        return retValue;
+    }
+
+    public void getPoolInfo(boolean allocation) {
+
+    }
+
+    public long getNextErrorReportTime() {
+        return nextErrorReportTime;
+    }
+
+    public void setNextErrorReportTime(long nextTime) {
+        this.nextErrorReportTime = nextTime;
+    }
+
+    public void setGlobalHostName(String hostname) {
+        this.globalHostName = hostname;
+    }
+
+    public String getGlobalHostName() {
+        return globalHostName;
+    }
 }
diff --git a/dblib/provider/src/main/java/org/onap/ccsdk/sli/core/dblib/jdbc/JdbcDBCachedDataSource.java b/dblib/provider/src/main/java/org/onap/ccsdk/sli/core/dblib/jdbc/JdbcDBCachedDataSource.java
index 3ec4f2ed..ca16834c 100755
--- a/dblib/provider/src/main/java/org/onap/ccsdk/sli/core/dblib/jdbc/JdbcDBCachedDataSource.java
+++ b/dblib/provider/src/main/java/org/onap/ccsdk/sli/core/dblib/jdbc/JdbcDBCachedDataSource.java
@@ -20,11 +20,7 @@
 
 package org.onap.ccsdk.sli.core.dblib.jdbc;
 
-import java.sql.Connection;
-import java.sql.PreparedStatement;
-import java.sql.ResultSet;
 import java.sql.SQLFeatureNotSupportedException;
-
 import org.apache.tomcat.jdbc.pool.DataSource;
 import org.apache.tomcat.jdbc.pool.PoolProperties;
 import org.onap.ccsdk.sli.core.dblib.CachedDataSource;
@@ -34,66 +30,61 @@ import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;
 
 
-public class JdbcDBCachedDataSource extends CachedDataSource
-{
-	private String dbDriver;
-	private String dbUserId;
-	private String dbPasswd;
-	private String dbUrl;
-
-	private int minLimit;
-	private int maxLimit;
-	private int initialLimit;
-
-	private static final String AS_CONF_ERROR = "AS_CONF_ERROR: ";
-
-	private static Logger LOGGER = LoggerFactory.getLogger(JdbcDBCachedDataSource.class);
-
-	/**
-	 * @param jdbcElem
-	 */
-	public JdbcDBCachedDataSource(BaseDBConfiguration jdbcElem)
-	{
-			super(jdbcElem);
-	}
-
-	@Override
-	protected void configure(BaseDBConfiguration xmlElem) throws DBConfigException
-	{
-		BaseDBConfiguration jdbcConfig = (BaseDBConfiguration)xmlElem;
-		if(jdbcConfig.getConnTimeout() > 0){
-			this.CONN_REQ_TIMEOUT = jdbcConfig.getConnTimeout();
-		}
-		if(jdbcConfig.getRequestTimeout() > 0){
-				this.DATA_REQ_TIMEOUT = jdbcConfig.getRequestTimeout();
-		}
-
-    	// set connection pool name
-		String dbConnectionName = jdbcConfig.getDbConnectionName();
-    	super.setDbConnectionName(dbConnectionName);
-    	// Configure the JDBC connection
-    	dbUserId = jdbcConfig.getDbUserId();
-        if (dbUserId == null)
-        {
-        	String errorMsg =  "Invalid XML contents: JDBCConnection missing dbUserId attribute";
-        	LOGGER.error(AS_CONF_ERROR + errorMsg);
+public class JdbcDBCachedDataSource extends CachedDataSource {
+
+    private String dbDriver;
+    private String dbUserId;
+    private String dbPasswd;
+    private String dbUrl;
+
+    private int minLimit;
+    private int maxLimit;
+    private int initialLimit;
+
+    private static final String AS_CONF_ERROR = "AS_CONF_ERROR: ";
+
+    private static final Logger LOGGER = LoggerFactory.getLogger(JdbcDBCachedDataSource.class);
+
+    /**
+     * @param jdbcElem
+     */
+    public JdbcDBCachedDataSource(BaseDBConfiguration jdbcElem) {
+        super(jdbcElem);
+    }
+
+    @Override
+    protected void configure(BaseDBConfiguration xmlElem) throws DBConfigException {
+        BaseDBConfiguration jdbcConfig = xmlElem;
+        if (jdbcConfig.getConnTimeout() > 0) {
+            this.connReqTimeout = jdbcConfig.getConnTimeout();
+        }
+        if (jdbcConfig.getRequestTimeout() > 0) {
+            this.dataReqTimeout = jdbcConfig.getRequestTimeout();
+        }
+
+        // set connection pool name
+        String dbConnectionName = jdbcConfig.getDbConnectionName();
+        super.setDbConnectionName(dbConnectionName);
+        // Configure the JDBC connection
+        dbUserId = jdbcConfig.getDbUserId();
+        if (dbUserId == null) {
+            String errorMsg = "Invalid XML contents: JDBCConnection missing dbUserId attribute";
+            LOGGER.error(AS_CONF_ERROR + errorMsg);
             throw new DBConfigException(errorMsg);
         }
 
         dbPasswd = jdbcConfig.getDbPasswd();
-        if (dbPasswd == null)
-        {
-        	String errorMsg =  "Invalid XML contents: JDBCConnection missing dbPasswd attribute";
-        	LOGGER.error(AS_CONF_ERROR + errorMsg);
+        if (dbPasswd == null) {
+            String errorMsg = "Invalid XML contents: JDBCConnection missing dbPasswd attribute";
+            LOGGER.error(AS_CONF_ERROR + errorMsg);
             throw new DBConfigException(errorMsg);
         }
 
         dbDriver = jdbcConfig.getDriverName();
-        if (dbDriver == null)
-        {
-        	String errorMsg =  "Invalid XML contents: JDBCConnection missing dbDriver attribute";
-        	LOGGER.error(AS_CONF_ERROR + errorMsg);
-        	throw new DBConfigException(errorMsg);
+        if (dbDriver == null) {
+            String errorMsg = "Invalid XML contents: JDBCConnection missing dbDriver attribute";
+            LOGGER.error(AS_CONF_ERROR + errorMsg);
+            throw new DBConfigException(errorMsg);
         }
 
         minLimit = jdbcConfig.getDbMinLimit();
@@ -103,14 +94,14 @@ public class JdbcDBCachedDataSource extends CachedDataSource
 //        	LOGGER.error(AS_CONF_ERROR + errorMsg);
 //        	throw new DBConfigException(errorMsg);
 //        }
-        maxLimit =  jdbcConfig.getDbMaxLimit();
+        maxLimit = jdbcConfig.getDbMaxLimit();
 //        if (maxLimit == null)
 //        {
 //        	String errorMsg =  "Invalid XML contents: JDBC Connection missing maxLimit attribute";
 //        	LOGGER.error(AS_CONF_ERROR + errorMsg);
 //        	throw new DBConfigException(errorMsg);
 //        }
-        initialLimit =  jdbcConfig.getDbInitialLimit();
+        initialLimit = jdbcConfig.getDbInitialLimit();
 //        if (initialLimit == null)
 //        {
 //        	String errorMsg =  "Invalid XML contents: JDBC Connection missing initialLimit attribute";
@@ -119,98 +110,86 @@ public class JdbcDBCachedDataSource extends CachedDataSource
 //        }
 
         dbUrl = jdbcConfig.getDbUrl();
-        if(dbUrl == null){
-        	String errorMsg =  "Invalid XML contents: JDBCConnection missing dbUrl attribute";
-        	LOGGER.error(AS_CONF_ERROR + errorMsg);
+        if (dbUrl == null) {
+            String errorMsg = "Invalid XML contents: JDBCConnection missing dbUrl attribute";
+            LOGGER.error(AS_CONF_ERROR + errorMsg);
             throw new DBConfigException(errorMsg);
         }
 
-		try {
-			Class clazz = Class.forName(dbDriver) ;
-
-			PoolProperties p = new PoolProperties();
-			p.setDriverClassName(dbDriver);
-			p.setUrl(dbUrl);
-			p.setUsername(dbUserId);
-			p.setPassword(dbPasswd);
-			p.setJmxEnabled(true);
-			p.setTestWhileIdle(false);
-			p.setTestOnBorrow(true);
-			p.setValidationQuery("SELECT 1");
-			p.setTestOnReturn(false);
-			p.setValidationInterval(30000);
-			p.setTimeBetweenEvictionRunsMillis(30000);
-			p.setInitialSize(initialLimit);
-			p.setMaxActive(maxLimit);
-			p.setMaxIdle(maxLimit);
-			p.setMaxWait(10000);
-			p.setRemoveAbandonedTimeout(60);
-			p.setMinEvictableIdleTimeMillis(30000);
-			p.setMinIdle(minLimit);
-			p.setLogAbandoned(true);
-			p.setRemoveAbandoned(true);
-			p.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"
-					+ "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
-
-			DataSource dataSource = new DataSource(p);
-
-			synchronized(this)
-			{
-				this.ds = dataSource;
-				Connection con = null;
-				PreparedStatement st = null;
-				ResultSet rs = null;
-
-				initialized = true;
-				LOGGER.info("JdbcDBCachedDataSource <"+dbConnectionName+"> configured successfully. Using URL: "+dbUrl);
-			}
-		} catch (Exception exc) {
-    		initialized = false;
-			StringBuffer sb = new StringBuffer();
-			sb.append("Failed to initialize MySQLCachedDataSource <");
-			sb.append(dbConnectionName).append(">. Reason: ");
-			sb.append(exc.getMessage());
-			LOGGER.error("AS_CONF_ERROR: " + sb.toString());
+        try {
+
+            PoolProperties p = new PoolProperties();
+            p.setDriverClassName(dbDriver);
+            p.setUrl(dbUrl);
+            p.setUsername(dbUserId);
+            p.setPassword(dbPasswd);
+            p.setJmxEnabled(true);
+            p.setTestWhileIdle(false);
+            p.setTestOnBorrow(true);
+            p.setValidationQuery("SELECT 1");
+            p.setTestOnReturn(false);
+            p.setValidationInterval(30000);
+            p.setTimeBetweenEvictionRunsMillis(30000);
+            p.setInitialSize(initialLimit);
+            p.setMaxActive(maxLimit);
+            p.setMaxIdle(maxLimit);
+            p.setMaxWait(10000);
+            p.setRemoveAbandonedTimeout(60);
+            p.setMinEvictableIdleTimeMillis(30000);
+            p.setMinIdle(minLimit);
+            p.setLogAbandoned(true);
+            p.setRemoveAbandoned(true);
+            p.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"
+                + "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
+
+            DataSource dataSource = new DataSource(p);
+
+            synchronized (this) {
+                this.ds = dataSource;
+
+                initialized = true;
+                LOGGER.info(String.format("JdbcDBCachedDataSource <%s> configured successfully. Using URL: %s",
+                    dbConnectionName, dbUrl));
+            }
+        } catch (Exception exc) {
+            initialized = false;
+            LOGGER.error(String.format("AS_CONF_ERROR: Failed to initialize MySQLCachedDataSource <%s>. Reason: %s",
+                dbConnectionName, exc.getMessage()));
 //    		throw new DBConfigException(e.getMessage());
-    	}
+        }
     }
 
-	public final String getDbUrl()
-	{
-		return dbUrl;
-	}
-
-	public final String getDbUserId()
-	{
-		return dbUserId;
-	}
-
-	public final String getDbPasswd()
-	{
-		return dbPasswd;
-	}
-
-	public static JdbcDBCachedDataSource createInstance(BaseDBConfiguration config) /*throws Exception*/ {
-		return new JdbcDBCachedDataSource(config);
-	}
-
-	public String toString(){
-		return getDbConnectionName();
-	}
-
-	public java.util.logging.Logger getParentLogger()
-			throws SQLFeatureNotSupportedException {
-		// TODO Auto-generated method stub
-		return null;
-	}
-
-	public void cleanUp(){
-		DataSource dataSource = (DataSource)ds;
-		dataSource.getPool().purge();
-		int active = dataSource.getActive();
-		int size = dataSource.getSize();
-		dataSource.close(true);
-		super.cleanUp();
-	}
+    public final String getDbUrl() {
+        return dbUrl;
+    }
+
+    public final String getDbUserId() {
+        return dbUserId;
+    }
+
+    public final String getDbPasswd() {
+        return dbPasswd;
+    }
+
+    public static JdbcDBCachedDataSource createInstance(BaseDBConfiguration config) /*throws Exception*/ {
+        return new JdbcDBCachedDataSource(config);
+    }
+
+    public String toString() {
+        return getDbConnectionName();
+    }
 
+    public java.util.logging.Logger getParentLogger()
+        throws SQLFeatureNotSupportedException {
+        // TODO Auto-generated method stub
+        return null;
+    }
+
+    @Override
+    public void cleanUp() {
+        DataSource dataSource = (DataSource) ds;
+        dataSource.getPool().purge();
+        dataSource.close(true);
+        super.cleanUp();
+    }
 }