summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorDan Timoney <dtimoney@att.com>2017-09-22 12:07:22 +0000
committerGerrit Code Review <gerrit@onap.org>2017-09-22 12:07:22 +0000
commitda6eb42cae52b25d47fc9ff0c144dd5a12cd456a (patch)
treeb4fb51500f1ac3e965604110fd8c383f257c0c18
parent37d6468cc670045ab4ff1ee23315739a429aa5e3 (diff)
parent6d585c2900751d0269772de6ca21476c51fcc942 (diff)
Merge "Sql query blocker issue fixed."
-rwxr-xr-xdblib/provider/src/main/java/org/onap/ccsdk/sli/core/dblib/CachedDataSource.java1087
-rwxr-xr-xdblib/provider/src/main/java/org/onap/ccsdk/sli/core/dblib/jdbc/JdbcDBCachedDataSource.java269
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 072a6f470..356f5ee8b 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 3ec4f2edf..ca16834c4 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();
+ }
}