2 * ============LICENSE_START=======================================================
4 * ================================================================================
5 * Copyright (C) 2016 - 2017 AT&T
6 * ================================================================================
7 * Licensed under the Apache License, Version 2.0 (the "License");
8 * you may not use this file except in compliance with the License.
9 * You may obtain a copy of the License at
11 * http://www.apache.org/licenses/LICENSE-2.0
13 * Unless required by applicable law or agreed to in writing, software
14 * distributed under the License is distributed on an "AS IS" BASIS,
15 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
16 * See the License for the specific language governing permissions and
17 * limitations under the License.
18 * ============LICENSE_END=========================================================
21 package org.openecomp.sdnc.sli.resource.dblib;
23 import java.io.Closeable;
24 import java.io.IOException;
25 import java.io.PrintWriter;
26 import java.io.StringWriter;
28 import java.sql.Connection;
30 import java.sql.PreparedStatement;
31 import java.sql.ResultSet;
32 import java.sql.SQLException;
33 import java.sql.Statement;
34 import java.sql.Timestamp;
35 import java.util.ArrayList;
36 import java.util.Arrays;
37 import java.util.Observer;
39 import javax.sql.DataSource;
40 import javax.sql.rowset.CachedRowSet;
41 import javax.sql.rowset.RowSetProvider;
43 import org.slf4j.Logger;
44 import org.slf4j.LoggerFactory;
45 import org.apache.tomcat.jdbc.pool.PoolExhaustedException;
46 import org.openecomp.sdnc.sli.resource.dblib.config.BaseDBConfiguration;
47 import org.openecomp.sdnc.sli.resource.dblib.pm.SQLExecutionMonitor;
48 import org.openecomp.sdnc.sli.resource.dblib.pm.SQLExecutionMonitorObserver;
49 import org.openecomp.sdnc.sli.resource.dblib.pm.SQLExecutionMonitor.TestObject;
50 import com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException;
54 * @version $Revision: 1.13 $
56 * Author Date Comments
57 * ============== ======== ====================================================
61 public abstract class CachedDataSource implements DataSource, SQLExecutionMonitorObserver
63 private static Logger LOGGER = LoggerFactory.getLogger(CachedDataSource.class);
65 protected static final String AS_CONF_ERROR = "AS_CONF_ERROR: ";
67 protected long CONN_REQ_TIMEOUT = 30L;
68 protected long DATA_REQ_TIMEOUT = 100L;
70 private final SQLExecutionMonitor monitor;
71 protected DataSource ds = null;
72 protected String connectionName = null;
73 protected boolean initialized = false;
75 private long interval = 1000;
76 private long initialDelay = 5000;
77 private long expectedCompletionTime = 50L;
78 private boolean canTakeOffLine = true;
79 private long unprocessedFailoverThreshold = 3L;
81 private long nextErrorReportTime = 0L;
83 private String globalHostName = null;
86 public CachedDataSource(BaseDBConfiguration jdbcElem) throws DBConfigException
89 monitor = new SQLExecutionMonitor(this);
92 protected abstract void configure(BaseDBConfiguration jdbcElem) throws DBConfigException;
94 * @see javax.sql.DataSource#getConnection()
96 public Connection getConnection() throws SQLException
98 return ds.getConnection();
101 public CachedRowSet getData(String statement, ArrayList<Object> arguments) throws SQLException, Throwable
103 TestObject testObject = null;
104 testObject = monitor.registerRequest();
106 Connection connection = null;
108 connection = this.getConnection();
109 if(connection == null ) {
110 throw new SQLException("Connection invalid");
112 if(LOGGER.isDebugEnabled())
113 LOGGER.debug("Obtained connection <" + connectionName + ">: "+connection.toString());
114 return executePreparedStatement(connection, statement, arguments, true);
117 if(connection != null && !connection.isClosed()) {
120 } catch(Throwable exc) {
121 // the exception not monitored
126 monitor.deregisterReguest(testObject);
130 public boolean writeData(String statement, ArrayList<Object> arguments) throws SQLException, Throwable
132 TestObject testObject = null;
133 testObject = monitor.registerRequest();
135 Connection connection = null;
137 connection = this.getConnection();
138 if(connection == null ) {
139 throw new SQLException("Connection invalid");
141 if(LOGGER.isDebugEnabled())
142 LOGGER.debug("Obtained connection <" + connectionName + ">: "+connection.toString());
143 return executeUpdatePreparedStatement(connection, statement, arguments, true);
146 if(connection != null && !connection.isClosed()) {
149 } catch(Throwable exc) {
150 // the exception not monitored
155 monitor.deregisterReguest(testObject);
159 CachedRowSet executePreparedStatement(Connection conn, String statement, ArrayList<Object> arguments, boolean close) throws SQLException, Throwable
161 long time = System.currentTimeMillis();
163 CachedRowSet data = null;
164 if(LOGGER.isDebugEnabled()){
165 LOGGER.debug("SQL Statement: "+ statement);
166 if(arguments != null && !arguments.isEmpty()) {
167 LOGGER.debug("Argunments: "+ Arrays.toString(arguments.toArray()));
173 data = RowSetProvider.newFactory().createCachedRowSet();
174 PreparedStatement ps = conn.prepareStatement(statement);
175 if(arguments != null)
177 for(int i = 0, max = arguments.size(); i < max; i++){
178 ps.setObject(i+1, arguments.get(i));
181 rs = ps.executeQuery();
183 // Point the rowset Cursor to the start
184 if(LOGGER.isDebugEnabled()){
185 LOGGER.debug("SQL SUCCESS. rows returned: " + data.size()+ ", time(ms): "+ (System.currentTimeMillis() - time)); }
186 } catch(SQLException exc){
187 if(LOGGER.isDebugEnabled()){
188 LOGGER.debug("SQL FAILURE. time(ms): "+ (System.currentTimeMillis() - time));
190 try { conn.rollback(); } catch(Throwable thr){}
191 if(arguments != null && !arguments.isEmpty()) {
192 LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with arguments: "+arguments.toString(), exc);
194 LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with no arguments. ", exc);
197 } catch(Throwable exc){
198 if(LOGGER.isDebugEnabled()){
199 LOGGER.debug("SQL FAILURE. time(ms): "+ (System.currentTimeMillis() - time));
201 if(arguments != null && !arguments.isEmpty()) {
202 LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with arguments: "+arguments.toString(), exc);
204 LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with no arguments. ", exc);
206 throw exc; // new SQLException(exc);
214 } catch(Exception exc){
218 if(conn != null && close){
222 } catch(Exception exc){
230 boolean executeUpdatePreparedStatement(Connection conn, String statement, ArrayList<Object> arguments, boolean close) throws SQLException, Throwable {
231 long time = System.currentTimeMillis();
233 CachedRowSet data = null;
237 data = RowSetProvider.newFactory().createCachedRowSet();
238 PreparedStatement ps = conn.prepareStatement(statement);
239 if(arguments != null)
241 for(int i = 0, max = arguments.size(); i < max; i++){
242 if(arguments.get(i) instanceof Blob) {
243 ps.setBlob(i+1, (Blob)arguments.get(i));
244 } else if(arguments.get(i) instanceof Timestamp) {
245 ps.setTimestamp(i+1, (Timestamp)arguments.get(i));
246 } else if(arguments.get(i) instanceof Integer) {
247 ps.setInt(i+1, (Integer)arguments.get(i));
248 } else if(arguments.get(i) instanceof Long) {
249 ps.setLong(i+1, (Long)arguments.get(i));
250 } else if(arguments.get(i) instanceof Date) {
251 ps.setDate(i+1, (Date)arguments.get(i));
253 ps.setObject(i+1, arguments.get(i));
257 rs = ps.executeUpdate();
258 // Point the rowset Cursor to the start
259 if(LOGGER.isDebugEnabled()){
260 LOGGER.debug("SQL SUCCESS. rows returned: " + data.size()+ ", time(ms): "+ (System.currentTimeMillis() - time));
262 } catch(SQLException exc){
263 if(LOGGER.isDebugEnabled()){
264 LOGGER.debug("SQL FAILURE. time(ms): "+ (System.currentTimeMillis() - time));
266 try { conn.rollback(); } catch(Throwable thr){}
267 if(arguments != null && !arguments.isEmpty()) {
268 LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with arguments: "+arguments.toString(), exc);
270 LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with no arguments. ", exc);
273 } catch(Throwable exc){
274 if(LOGGER.isDebugEnabled()){
275 LOGGER.debug("SQL FAILURE. time(ms): "+ (System.currentTimeMillis() - time));
277 if(arguments != null && !arguments.isEmpty()) {
278 LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with arguments: "+arguments.toString(), exc);
280 LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with no arguments. ", exc);
282 throw exc; // new SQLException(exc);
285 if(conn != null && close){
289 } catch(Exception exc){
298 * @see javax.sql.DataSource#getConnection(java.lang.String, java.lang.String)
300 public Connection getConnection(String username, String password)
303 return ds.getConnection(username, password);
307 * @see javax.sql.DataSource#getLogWriter()
309 public PrintWriter getLogWriter() throws SQLException
311 return ds.getLogWriter();
315 * @see javax.sql.DataSource#getLoginTimeout()
317 public int getLoginTimeout() throws SQLException
319 return ds.getLoginTimeout();
323 * @see javax.sql.DataSource#setLogWriter(java.io.PrintWriter)
325 public void setLogWriter(PrintWriter out) throws SQLException
327 ds.setLogWriter(out);
331 * @see javax.sql.DataSource#setLoginTimeout(int)
333 public void setLoginTimeout(int seconds) throws SQLException
335 ds.setLoginTimeout(seconds);
339 public final String getDbConnectionName(){
340 return connectionName;
343 protected final void setDbConnectionName(String name) {
344 this.connectionName = name;
347 public void cleanUp(){
348 if(ds != null && ds instanceof Closeable) {
350 ((Closeable)ds).close();
351 } catch (IOException e) {
352 LOGGER.warn(e.getMessage());
356 monitor.deleteObservers();
360 public boolean isInitialized() {
364 protected boolean testConnection(){
365 return testConnection(false);
368 protected boolean testConnection(boolean error_level){
369 Connection conn = null;
371 Statement stmt = null;
374 Boolean readOnly = null;
375 String hostname = null;
376 conn = this.getConnection();
377 stmt = conn.createStatement();
378 rs = stmt.executeQuery("SELECT @@global.read_only, @@global.hostname"); //("SELECT 1 FROM DUAL"); //"select BANNER from SYS.V_$VERSION"
381 readOnly = rs.getBoolean(1);
382 hostname = rs.getString(2);
384 if(LOGGER.isDebugEnabled()){
385 LOGGER.debug("SQL DataSource <"+getDbConnectionName() + "> connected to " + hostname + ", read-only is " + readOnly + ", tested successfully ");
389 } catch (Throwable exc) {
391 LOGGER.error("SQL DataSource <" + this.getDbConnectionName() + "> test failed. Cause : " + exc.getMessage());
393 LOGGER.info("SQL DataSource <" + this.getDbConnectionName() + "> test failed. Cause : " + exc.getMessage());
401 } catch (SQLException e) {
408 } catch (SQLException e) {
415 } catch (SQLException e) {
422 public boolean isWrapperFor(Class<?> iface) throws SQLException {
426 public <T> T unwrap(Class<T> iface) throws SQLException {
430 @SuppressWarnings("deprecation")
431 public void setConnectionCachingEnabled(boolean state)
433 // if(ds != null && ds instanceof OracleDataSource)
435 // ((OracleDataSource)ds).setConnectionCachingEnabled(true);
436 // } catch (SQLException exc) {
437 // LOGGER.warn("", exc);
441 public void addObserver(Observer observer) {
442 monitor.addObserver(observer);
445 public void deleteObserver(Observer observer) {
446 monitor.deleteObserver(observer);
449 public long getInterval() {
453 public long getInitialDelay() {
457 public void setInterval(long value) {
461 public void setInitialDelay(long value) {
462 initialDelay = value;
465 public long getExpectedCompletionTime() {
466 return expectedCompletionTime;
469 public void setExpectedCompletionTime(long value) {
470 expectedCompletionTime = value;
473 public long getUnprocessedFailoverThreshold() {
474 return unprocessedFailoverThreshold;
477 public void setUnprocessedFailoverThreshold(long value) {
478 this.unprocessedFailoverThreshold = value;
481 public boolean canTakeOffLine() {
482 return canTakeOffLine;
485 public void blockImmediateOffLine() {
486 canTakeOffLine = false;
487 final Thread offLineTimer = new Thread()
491 Thread.sleep(30000L);
492 }catch(Throwable exc){
495 canTakeOffLine = true;
499 offLineTimer.setDaemon(true);
500 offLineTimer.start();
504 * @return the monitor
506 final SQLExecutionMonitor getMonitor() {
510 protected boolean isSlave() throws PoolExhaustedException, MySQLNonTransientConnectionException {
511 CachedRowSet rs = null;
512 boolean isSlave = true;
513 String hostname = "UNDETERMINED";
515 boolean localSlave = true;
516 rs = this.getData("SELECT @@global.read_only, @@global.hostname", new ArrayList<Object>());
518 localSlave = rs.getBoolean(1);
519 hostname = rs.getString(2);
521 isSlave = localSlave;
522 } catch(PoolExhaustedException | MySQLNonTransientConnectionException peexc){
524 } catch (SQLException e) {
527 } catch (Throwable e) {
532 LOGGER.debug("SQL SLAVE : "+connectionName + " on server " + hostname);
534 LOGGER.debug("SQL MASTER : "+connectionName + " on server " + hostname);
539 public boolean isFabric() {
543 protected boolean lockTable(Connection conn, String tableName) {
544 boolean retValue = false;
545 Statement lock = null;
547 if(tableName != null) {
548 if(LOGGER.isDebugEnabled()) {
549 LOGGER.debug("Executing 'LOCK TABLES " + tableName + " WRITE' on connection " + conn.toString());
550 if("SVC_LOGIC".equals(tableName)) {
551 Exception e = new Exception();
552 StringWriter sw = new StringWriter();
553 PrintWriter pw = new PrintWriter(sw);
554 e.printStackTrace(pw);
555 LOGGER.debug(sw.toString());
558 lock = conn.createStatement();
559 lock.execute("LOCK TABLES " + tableName + " WRITE");
562 } catch(Exception exc){
563 LOGGER.error("", exc);
568 } catch(Exception exc) {
575 protected boolean unlockTable(Connection conn) {
576 boolean retValue = false;
577 Statement lock = null;
579 if(LOGGER.isDebugEnabled()) {
580 LOGGER.debug("Executing 'UNLOCK TABLES' on connection " + conn.toString());
582 lock = conn.createStatement();
583 retValue = lock.execute("UNLOCK TABLES");
584 } catch(Exception exc){
585 LOGGER.error("", exc);
590 } catch(Exception exc) {
597 public void getPoolInfo(boolean allocation) {
601 public long getNextErrorReportTime() {
602 return nextErrorReportTime;
605 public void setNextErrorReportTime(long nextTime) {
606 this.nextErrorReportTime = nextTime;
609 public void setGlobalHostName(String hostname) {
610 this.globalHostName = hostname;
613 public String getGlobalHostName() {
614 return globalHostName;