2 * ============LICENSE_START=======================================================
4 * ================================================================================
5 * Copyright (C) 2016 - 2017 ONAP
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.onap.ccsdk.sli.core.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.onap.ccsdk.sli.core.dblib.config.BaseDBConfiguration;
47 import org.onap.ccsdk.sli.core.dblib.pm.SQLExecutionMonitor;
48 import org.onap.ccsdk.sli.core.dblib.pm.SQLExecutionMonitorObserver;
49 import org.onap.ccsdk.sli.core.dblib.pm.SQLExecutionMonitor.TestObject;
51 import com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException;
55 * @version $Revision: 1.13 $
57 * Author Date Comments
58 * ============== ======== ====================================================
62 public abstract class CachedDataSource implements DataSource, SQLExecutionMonitorObserver
64 private static Logger LOGGER = LoggerFactory.getLogger(CachedDataSource.class);
66 protected static final String AS_CONF_ERROR = "AS_CONF_ERROR: ";
68 protected long CONN_REQ_TIMEOUT = 30L;
69 protected long DATA_REQ_TIMEOUT = 100L;
71 private final SQLExecutionMonitor monitor;
72 protected DataSource ds = null;
73 protected String connectionName = null;
74 protected boolean initialized = false;
76 private long interval = 1000;
77 private long initialDelay = 5000;
78 private long expectedCompletionTime = 50L;
79 private boolean canTakeOffLine = true;
80 private long unprocessedFailoverThreshold = 3L;
82 private long nextErrorReportTime = 0L;
84 private String globalHostName = null;
87 public CachedDataSource(BaseDBConfiguration jdbcElem) throws DBConfigException
90 monitor = new SQLExecutionMonitor(this);
93 protected abstract void configure(BaseDBConfiguration jdbcElem) throws DBConfigException;
95 * @see javax.sql.DataSource#getConnection()
97 public Connection getConnection() throws SQLException
99 return ds.getConnection();
102 public CachedRowSet getData(String statement, ArrayList<Object> arguments) throws SQLException, Throwable
104 TestObject testObject = null;
105 testObject = monitor.registerRequest();
107 Connection connection = null;
109 connection = this.getConnection();
110 if(connection == null ) {
111 throw new SQLException("Connection invalid");
113 if(LOGGER.isDebugEnabled())
114 LOGGER.debug("Obtained connection <" + connectionName + ">: "+connection.toString());
115 return executePreparedStatement(connection, statement, arguments, true);
118 if(connection != null && !connection.isClosed()) {
121 } catch(Throwable exc) {
122 // the exception not monitored
127 monitor.deregisterReguest(testObject);
131 public boolean writeData(String statement, ArrayList<Object> arguments) throws SQLException, Throwable
133 TestObject testObject = null;
134 testObject = monitor.registerRequest();
136 Connection connection = null;
138 connection = this.getConnection();
139 if(connection == null ) {
140 throw new SQLException("Connection invalid");
142 if(LOGGER.isDebugEnabled())
143 LOGGER.debug("Obtained connection <" + connectionName + ">: "+connection.toString());
144 return executeUpdatePreparedStatement(connection, statement, arguments, true);
147 if(connection != null && !connection.isClosed()) {
150 } catch(Throwable exc) {
151 // the exception not monitored
156 monitor.deregisterReguest(testObject);
160 CachedRowSet executePreparedStatement(Connection conn, String statement, ArrayList<Object> arguments, boolean close) throws SQLException, Throwable
162 long time = System.currentTimeMillis();
164 CachedRowSet data = null;
165 if(LOGGER.isDebugEnabled()){
166 LOGGER.debug("SQL Statement: "+ statement);
167 if(arguments != null && !arguments.isEmpty()) {
168 LOGGER.debug("Argunments: "+ Arrays.toString(arguments.toArray()));
174 data = RowSetProvider.newFactory().createCachedRowSet();
175 PreparedStatement ps = conn.prepareStatement(statement);
176 if(arguments != null)
178 for(int i = 0, max = arguments.size(); i < max; i++){
179 ps.setObject(i+1, arguments.get(i));
182 rs = ps.executeQuery();
184 // Point the rowset Cursor to the start
185 if(LOGGER.isDebugEnabled()){
186 LOGGER.debug("SQL SUCCESS. rows returned: " + data.size()+ ", time(ms): "+ (System.currentTimeMillis() - time)); }
187 } catch(SQLException exc){
188 if(LOGGER.isDebugEnabled()){
189 LOGGER.debug("SQL FAILURE. time(ms): "+ (System.currentTimeMillis() - time));
191 try { conn.rollback(); } catch(Throwable thr){}
192 if(arguments != null && !arguments.isEmpty()) {
193 LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with arguments: "+arguments.toString(), exc);
195 LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with no arguments. ", exc);
198 } catch(Throwable exc){
199 if(LOGGER.isDebugEnabled()){
200 LOGGER.debug("SQL FAILURE. time(ms): "+ (System.currentTimeMillis() - time));
202 if(arguments != null && !arguments.isEmpty()) {
203 LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with arguments: "+arguments.toString(), exc);
205 LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with no arguments. ", exc);
207 throw exc; // new SQLException(exc);
215 } catch(Exception exc){
219 if(conn != null && close){
223 } catch(Exception exc){
231 boolean executeUpdatePreparedStatement(Connection conn, String statement, ArrayList<Object> arguments, boolean close) throws SQLException, Throwable {
232 long time = System.currentTimeMillis();
234 CachedRowSet data = null;
238 data = RowSetProvider.newFactory().createCachedRowSet();
239 PreparedStatement ps = conn.prepareStatement(statement);
240 if(arguments != null)
242 for(int i = 0, max = arguments.size(); i < max; i++){
243 if(arguments.get(i) instanceof Blob) {
244 ps.setBlob(i+1, (Blob)arguments.get(i));
245 } else if(arguments.get(i) instanceof Timestamp) {
246 ps.setTimestamp(i+1, (Timestamp)arguments.get(i));
247 } else if(arguments.get(i) instanceof Integer) {
248 ps.setInt(i+1, (Integer)arguments.get(i));
249 } else if(arguments.get(i) instanceof Long) {
250 ps.setLong(i+1, (Long)arguments.get(i));
251 } else if(arguments.get(i) instanceof Date) {
252 ps.setDate(i+1, (Date)arguments.get(i));
254 ps.setObject(i+1, arguments.get(i));
258 rs = ps.executeUpdate();
259 // Point the rowset Cursor to the start
260 if(LOGGER.isDebugEnabled()){
261 LOGGER.debug("SQL SUCCESS. rows returned: " + data.size()+ ", time(ms): "+ (System.currentTimeMillis() - time));
263 } catch(SQLException exc){
264 if(LOGGER.isDebugEnabled()){
265 LOGGER.debug("SQL FAILURE. time(ms): "+ (System.currentTimeMillis() - time));
267 try { conn.rollback(); } catch(Throwable thr){}
268 if(arguments != null && !arguments.isEmpty()) {
269 LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with arguments: "+arguments.toString(), exc);
271 LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with no arguments. ", exc);
274 } catch(Throwable exc){
275 if(LOGGER.isDebugEnabled()){
276 LOGGER.debug("SQL FAILURE. time(ms): "+ (System.currentTimeMillis() - time));
278 if(arguments != null && !arguments.isEmpty()) {
279 LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with arguments: "+arguments.toString(), exc);
281 LOGGER.error("<"+connectionName+"> Failed to execute: "+ statement + " with no arguments. ", exc);
283 throw exc; // new SQLException(exc);
286 if(conn != null && close){
290 } catch(Exception exc){
299 * @see javax.sql.DataSource#getConnection(java.lang.String, java.lang.String)
301 public Connection getConnection(String username, String password)
304 return ds.getConnection(username, password);
308 * @see javax.sql.DataSource#getLogWriter()
310 public PrintWriter getLogWriter() throws SQLException
312 return ds.getLogWriter();
316 * @see javax.sql.DataSource#getLoginTimeout()
318 public int getLoginTimeout() throws SQLException
320 return ds.getLoginTimeout();
324 * @see javax.sql.DataSource#setLogWriter(java.io.PrintWriter)
326 public void setLogWriter(PrintWriter out) throws SQLException
328 ds.setLogWriter(out);
332 * @see javax.sql.DataSource#setLoginTimeout(int)
334 public void setLoginTimeout(int seconds) throws SQLException
336 ds.setLoginTimeout(seconds);
340 public final String getDbConnectionName(){
341 return connectionName;
344 protected final void setDbConnectionName(String name) {
345 this.connectionName = name;
348 public void cleanUp(){
349 if(ds != null && ds instanceof Closeable) {
351 ((Closeable)ds).close();
352 } catch (IOException e) {
353 LOGGER.warn(e.getMessage());
357 monitor.deleteObservers();
361 public boolean isInitialized() {
365 protected boolean testConnection(){
366 return testConnection(false);
369 protected boolean testConnection(boolean error_level){
370 Connection conn = null;
372 Statement stmt = null;
375 Boolean readOnly = null;
376 String hostname = null;
377 conn = this.getConnection();
378 stmt = conn.createStatement();
379 rs = stmt.executeQuery("SELECT @@global.read_only, @@global.hostname"); //("SELECT 1 FROM DUAL"); //"select BANNER from SYS.V_$VERSION"
382 readOnly = rs.getBoolean(1);
383 hostname = rs.getString(2);
385 if(LOGGER.isDebugEnabled()){
386 LOGGER.debug("SQL DataSource <"+getDbConnectionName() + "> connected to " + hostname + ", read-only is " + readOnly + ", tested successfully ");
390 } catch (Throwable exc) {
392 LOGGER.error("SQL DataSource <" + this.getDbConnectionName() + "> test failed. Cause : " + exc.getMessage());
394 LOGGER.info("SQL DataSource <" + this.getDbConnectionName() + "> test failed. Cause : " + exc.getMessage());
402 } catch (SQLException e) {
409 } catch (SQLException e) {
416 } catch (SQLException e) {
423 public boolean isWrapperFor(Class<?> iface) throws SQLException {
427 public <T> T unwrap(Class<T> iface) throws SQLException {
431 @SuppressWarnings("deprecation")
432 public void setConnectionCachingEnabled(boolean state)
434 // if(ds != null && ds instanceof OracleDataSource)
436 // ((OracleDataSource)ds).setConnectionCachingEnabled(true);
437 // } catch (SQLException exc) {
438 // LOGGER.warn("", exc);
442 public void addObserver(Observer observer) {
443 monitor.addObserver(observer);
446 public void deleteObserver(Observer observer) {
447 monitor.deleteObserver(observer);
450 public long getInterval() {
454 public long getInitialDelay() {
458 public void setInterval(long value) {
462 public void setInitialDelay(long value) {
463 initialDelay = value;
466 public long getExpectedCompletionTime() {
467 return expectedCompletionTime;
470 public void setExpectedCompletionTime(long value) {
471 expectedCompletionTime = value;
474 public long getUnprocessedFailoverThreshold() {
475 return unprocessedFailoverThreshold;
478 public void setUnprocessedFailoverThreshold(long value) {
479 this.unprocessedFailoverThreshold = value;
482 public boolean canTakeOffLine() {
483 return canTakeOffLine;
486 public void blockImmediateOffLine() {
487 canTakeOffLine = false;
488 final Thread offLineTimer = new Thread()
492 Thread.sleep(30000L);
493 }catch(Throwable exc){
496 canTakeOffLine = true;
500 offLineTimer.setDaemon(true);
501 offLineTimer.start();
505 * @return the monitor
507 final SQLExecutionMonitor getMonitor() {
511 protected boolean isSlave() throws PoolExhaustedException, MySQLNonTransientConnectionException {
512 CachedRowSet rs = null;
513 boolean isSlave = true;
514 String hostname = "UNDETERMINED";
516 boolean localSlave = true;
517 rs = this.getData("SELECT @@global.read_only, @@global.hostname", new ArrayList<Object>());
519 localSlave = rs.getBoolean(1);
520 hostname = rs.getString(2);
522 isSlave = localSlave;
523 } catch(PoolExhaustedException | MySQLNonTransientConnectionException peexc){
525 } catch (SQLException e) {
528 } catch (Throwable e) {
533 LOGGER.debug("SQL SLAVE : "+connectionName + " on server " + hostname);
535 LOGGER.debug("SQL MASTER : "+connectionName + " on server " + hostname);
540 public boolean isFabric() {
544 protected boolean lockTable(Connection conn, String tableName) {
545 boolean retValue = false;
546 Statement lock = null;
548 if(tableName != null) {
549 if(LOGGER.isDebugEnabled()) {
550 LOGGER.debug("Executing 'LOCK TABLES " + tableName + " WRITE' on connection " + conn.toString());
551 if("SVC_LOGIC".equals(tableName)) {
552 Exception e = new Exception();
553 StringWriter sw = new StringWriter();
554 PrintWriter pw = new PrintWriter(sw);
555 e.printStackTrace(pw);
556 LOGGER.debug(sw.toString());
559 lock = conn.createStatement();
560 lock.execute("LOCK TABLES " + tableName + " WRITE");
563 } catch(Exception exc){
564 LOGGER.error("", exc);
569 } catch(Exception exc) {
576 protected boolean unlockTable(Connection conn) {
577 boolean retValue = false;
578 Statement lock = null;
580 if(LOGGER.isDebugEnabled()) {
581 LOGGER.debug("Executing 'UNLOCK TABLES' on connection " + conn.toString());
583 lock = conn.createStatement();
584 retValue = lock.execute("UNLOCK TABLES");
585 } catch(Exception exc){
586 LOGGER.error("", exc);
591 } catch(Exception exc) {
598 public void getPoolInfo(boolean allocation) {
602 public long getNextErrorReportTime() {
603 return nextErrorReportTime;
606 public void setNextErrorReportTime(long nextTime) {
607 this.nextErrorReportTime = nextTime;
610 public void setGlobalHostName(String hostname) {
611 this.globalHostName = hostname;
614 public String getGlobalHostName() {
615 return globalHostName;