Include impacted changes for APPC-346,APPC-348

Issue-ID: APPC-347
Change-Id: I399bc2a1e0dfd481e103032a373bb80fce5baf41
Signed-off-by: Anand <ac204h@att.com>

13 files changed, 761 insertions, 42 deletions

diff --git a/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/AppcDatabaseConnectionPool.java b/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/AppcDatabaseConnectionPool.java
new file mode 100644
index 000000000..219739755
--- /dev/null
+++ b/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/AppcDatabaseConnectionPool.java
@@ -0,0 +1,161 @@
+/*-
+ * ============LICENSE_START=======================================================
+ * ONAP : APPC
+ * ================================================================================
+ * Copyright (C) 2017 AT&T Intellectual Property. All rights reserved.
+ * ================================================================================
+ * Copyright (C) 2017 Amdocs
+ * =============================================================================
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ * 
+ *      http://www.apache.org/licenses/LICENSE-2.0
+ * 
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ * 
+ * ECOMP is a trademark and service mark of AT&T Intellectual Property.
+ * ============LICENSE_END=========================================================
+ */
+
+package org.onap.appc.dao.util;
+
+import com.att.eelf.configuration.EELFLogger;
+import com.att.eelf.configuration.EELFManager;
+import org.onap.appc.configuration.Configuration;
+import org.onap.appc.configuration.ConfigurationFactory;
+import org.onap.appc.dao.util.api.DBConnectionPoolService;
+import org.onap.appc.dao.util.dbcp.DBConnectionPool;
+import org.onap.appc.dao.util.exception.DBConnectionPoolException;
+
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.util.Map;
+
+/**
+ * This class implements
+ *
+ * @see org.onap.appc.dao.util.dbcp.DBConnectionPool
+ * that provides concrete implemenation of accessing appc database which basic setup
+ * data would be got from global configuration.
+ * @see org.onap.appc.configuration.Configuration
+ * <p>
+ * The singleton instance of this class has been instantiated by blueprint.
+ * An example is shown in the {@link DBConnectionPoolService}
+ */
+public class AppcDatabaseConnectionPool implements DBConnectionPoolService {
+    enum PropertyPattern {
+        DBURL("org.onap.appc.db.url.%s"),
+        USERNAME("org.onap.appc.db.user.%s"),
+        PASSWORD("org.onap.appc.db.pass.%s"),
+        DRIVER("org.onap.appc.db.jdbc.driver");
+
+        private String pattern;
+
+        PropertyPattern(String pattern) {
+            this.pattern = pattern;
+        }
+
+        public String getPattern() {
+            return pattern;
+        }
+    }
+
+    private final EELFLogger logger = EELFManager.getInstance().getLogger(AppcDatabaseConnectionPool.class);
+
+    private DBConnectionPool dbConnectionPool;
+    private String dbName;
+
+    public AppcDatabaseConnectionPool() {
+        // do nothing
+    }
+
+    public AppcDatabaseConnectionPool(String dbUrl, String userName, String password, String jdbcDriver) {
+        dbConnectionPool = new DBConnectionPool(dbUrl, userName, password, jdbcDriver);
+    }
+
+    /**
+     * Injected by blueprint
+     *
+     * @param dbName
+     */
+    public void setDbName(String dbName) {
+        this.dbName = dbName;
+    }
+
+    /**
+     * Bean init method used by blueprint
+     */
+    public void init() {
+        Configuration configuration = ConfigurationFactory.getConfiguration();
+        String dbUrl = getConnectionProperty(configuration, PropertyPattern.DBURL);
+        String userName = getConnectionProperty(configuration, PropertyPattern.USERNAME);
+        String password = getConnectionProperty(configuration, PropertyPattern.PASSWORD);
+        String jdbcDriver = getJDBCDriver(configuration);
+
+        dbConnectionPool = new DBConnectionPool(dbUrl, userName, password, jdbcDriver);
+
+        // a simple health check
+        Connection connection = null;
+        try {
+            connection = dbConnectionPool.getConnection();
+        } catch (DBConnectionPoolException e) {
+            logger.error("DB connection pool is created failed." +
+                "Please make sure the provided information is correct.");
+        }
+
+        if (connection != null) {
+            try {
+                connection.close();
+            } catch (SQLException e) {
+                logger.error("DB connection cannot be closed:", e.getMessage());
+            }
+        }
+    }
+
+    /**
+     * Bean destroy method used by blueprint
+     */
+    public void destroy() {
+        if (dbConnectionPool != null) {
+            dbConnectionPool.shutdown();
+        }
+    }
+
+    /**
+     * Get the connection from connection pool.
+     *
+     * @return Connection. If the provided db information is not correct,
+     * the return value might be null.
+     */
+    @Override
+    public Connection getConnection() throws DBConnectionPoolException {
+        return dbConnectionPool.getConnection();
+    }
+
+    /**
+     * Get dbcp status like active_status.
+     * <p>
+     * More details about status of DBConnectionPool,
+     * go check {@link org.onap.appc.dao.util.dbcp.DBConnectionPool#getDataSourceStatus()}
+     *
+     * @return a map contains some dbcp information.
+     */
+    @Override
+    public Map<String, Integer> getDataSourceStatus() {
+        return dbConnectionPool.getDataSourceStatus();
+    }
+
+    private String getConnectionProperty(Configuration configuration, PropertyPattern propertyPattern) {
+        String property = configuration.getProperty(String.format(propertyPattern.getPattern(), dbName), "");
+        return property;
+    }
+
+    private String getJDBCDriver(Configuration configuration) {
+        return configuration.getProperty(PropertyPattern.DRIVER.getPattern(), "");
+    }
+}
diff --git a/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/AppcJdbcConnectionFactory.java b/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/AppcJdbcConnectionFactory.java
index e62f03da3..c4af7ceee 100644
--- a/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/AppcJdbcConnectionFactory.java
+++ b/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/AppcJdbcConnectionFactory.java
@@ -24,9 +24,20 @@
 
 package org.onap.appc.dao.util;
 
+import org.onap.appc.dao.util.api.JdbcConnectionFactory;
+import org.onap.appc.dao.util.exception.JdbcRuntimeException;
+import org.onap.appc.dao.util.message.Messages;
+
 import java.sql.Connection;
 import java.sql.SQLException;
 
+/**
+ * @deprecated As of release 1802, replaced by {@link #(AppcDatabaseConnectionPool)}
+ * <p>
+ * This class provides the ability to create dbconnection by using DBUtils which
+ * has been depreacted.
+ */
+@Deprecated
 public class AppcJdbcConnectionFactory implements JdbcConnectionFactory {
 
     private String schema;
@@ -38,7 +49,7 @@ public class AppcJdbcConnectionFactory implements JdbcConnectionFactory {
     public Connection openDbConnection() {
         try {
             return DBUtils.getConnection(schema);
-        } catch(SQLException e) {
+        } catch (SQLException e) {
             throw new JdbcRuntimeException(Messages.EXP_APPC_JDBC_CONNECT.format(schema), e);
         }
     }
@@ -46,7 +57,7 @@ public class AppcJdbcConnectionFactory implements JdbcConnectionFactory {
     public void closeDbConnection(Connection connection) {
         try {
             connection.close();
-        } catch(SQLException e) {
+        } catch (SQLException e) {
             throw new JdbcRuntimeException(Messages.EXP_APPC_JDBC_DISCONNECT.format(schema), e);
         }
     }
diff --git a/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/DBUtils.java b/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/DBUtils.java
index 447dce8fc..95942a3b5 100644
--- a/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/DBUtils.java
+++ b/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/DBUtils.java
@@ -24,46 +24,59 @@
 
 package org.onap.appc.dao.util;
 
-import java.sql.*;
-
 import org.onap.appc.configuration.Configuration;
 import org.onap.appc.configuration.ConfigurationFactory;
+import java.sql.*;
 
+/**
+ * @deprecated As of release 1802, replaced by {@link #(org.onap.appc.dao.util.dbcp.DBConnectionPool)}
+ * <p>
+ * This class provides the ability to access mysql database which has been @Deprecated because
+ * {@link #getConnection(String)} for each database request is not a good practice especially
+ * on appc performance.
+ * <p>
+ * If you would like to use appcctl (mysql database), bundle:appc-data-access-lib has created
+ * a database connection pool bean and exported as a service by using blueprint.
+ * If you would like to create a new database connection pool, refer to the way mentioned above.
+ * {@link org.onap.appc.dao.util.api.DBConnectionPoolService} has an example of how to use
+ * the connection pool.
+ */
 @Deprecated
 public class DBUtils {
-	private static final String JDBC_DRIVER = "org.mariadb.jdbc.Driver";
-	private static final Configuration configuration = ConfigurationFactory.getConfiguration();
-	static {
-		try {
-			String driver = JDBC_DRIVER;
-			Class.forName(driver);
-		} catch (ClassNotFoundException e) {
-			e.printStackTrace();
-		}
-	}
+    private static final String JDBC_DRIVER = "org.mariadb.jdbc.Driver";
+    private static final Configuration configuration = ConfigurationFactory.getConfiguration();
+
+    static {
+        try {
+            String driver = JDBC_DRIVER;
+            Class.forName(driver);
+        } catch (ClassNotFoundException e) {
+            e.printStackTrace();
+        }
+    }
 
-	public static Connection getConnection(String schema) throws SQLException {
-		DriverManager.registerDriver(new org.mariadb.jdbc.Driver());
-		String dbURL = configuration.getProperty(String.format("org.onap.appc.db.url.%s", schema), "");
-		String userName = configuration.getProperty(String.format("org.onap.appc.db.user.%s", schema), "");
-		String password = configuration.getProperty(String.format("org.onap.appc.db.pass.%s", schema), "");
-		return DriverManager.getConnection(dbURL, userName, password);
-	}
+    public static Connection getConnection(String schema) throws SQLException {
+        DriverManager.registerDriver(new org.mariadb.jdbc.Driver());
+        String dbURL = configuration.getProperty(String.format("org.onap.appc.db.url.%s", schema), "");
+        String userName = configuration.getProperty(String.format("org.onap.appc.db.user.%s", schema), "");
+        String password = configuration.getProperty(String.format("org.onap.appc.db.pass.%s", schema), "");
+        return DriverManager.getConnection(dbURL, userName, password);
+    }
 
-	public static boolean clearResources(ResultSet resultSet, PreparedStatement ptmt, Connection connection) {
-		boolean clearFlag = false;
-		try {
-			if (resultSet != null)
-				resultSet.close();
-			if (ptmt != null)
-				ptmt.close();
-			if (connection != null)
-				connection.close();
-			clearFlag = true;
-		} catch (SQLException e) {
+    public static boolean clearResources(ResultSet resultSet, PreparedStatement ptmt, Connection connection) {
+        boolean clearFlag = false;
+        try {
+            if (resultSet != null)
+                resultSet.close();
+            if (ptmt != null)
+                ptmt.close();
+            if (connection != null)
+                connection.close();
+            clearFlag = true;
+        } catch (SQLException e) {
 
-		}
-		return clearFlag;
+        }
+        return clearFlag;
 
-	}
+    }
 }
diff --git a/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/DefaultJdbcConnectionFactory.java b/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/DefaultJdbcConnectionFactory.java
index 88599ac72..f877bd220 100644
--- a/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/DefaultJdbcConnectionFactory.java
+++ b/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/DefaultJdbcConnectionFactory.java
@@ -24,10 +24,28 @@
 
 package org.onap.appc.dao.util;
 
+import org.onap.appc.dao.util.api.JdbcConnectionFactory;
+import org.onap.appc.dao.util.exception.JdbcRuntimeException;
+import org.onap.appc.dao.util.message.Messages;
+
 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.SQLException;
 
+/**
+ * @deprecated As of release 1802, replaced by {@link #(org.onap.appc.dao.util.dbcp.DBConnectionPool)}
+ * <p>
+ * This class provides the ability to access mysql database which has been deprecated because
+ * {@link #openDbConnection()} for each database request is not a good practice especially
+ * on appc performance.
+ * <p>
+ * If you would like to use appcctl (mysql database), bundle:appc-data-access-lib has created
+ * a database connection pool bean and exported as a service by using blueprint.
+ * If you would like to create a new database connection pool, refer to the way mentioned above.
+ * {@link org.onap.appc.dao.util.api.DBConnectionPoolService} has an example of how to use
+ * the connection pool.
+ */
+@Deprecated
 public abstract class DefaultJdbcConnectionFactory implements JdbcConnectionFactory {
 
     private static boolean driverRegistered = false;
@@ -54,12 +72,12 @@ public abstract class DefaultJdbcConnectionFactory implements JdbcConnectionFact
     @Override
     public Connection openDbConnection() {
         try {
-            if(!driverRegistered) {
+            if (!driverRegistered) {
                 registedDriver();
                 driverRegistered = true;
             }
             return DriverManager.getConnection(jdbcURL, jdbcUserName, jdbcPassword);
-        } catch(SQLException e) {
+        } catch (SQLException e) {
             throw new JdbcRuntimeException(Messages.EXP_JDBC_CONNECT.format(jdbcURL), e);
         }
     }
@@ -68,7 +86,7 @@ public abstract class DefaultJdbcConnectionFactory implements JdbcConnectionFact
     public void closeDbConnection(Connection connection) {
         try {
             connection.close();
-        } catch(SQLException e) {
+        } catch (SQLException e) {
             throw new JdbcRuntimeException(Messages.EXP_JDBC_DISCONNECT.format(jdbcURL), e);
         }
     }
diff --git a/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/api/DBConnectionPoolService.java b/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/api/DBConnectionPoolService.java
new file mode 100644
index 000000000..2f51666dc
--- /dev/null
+++ b/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/api/DBConnectionPoolService.java
@@ -0,0 +1,100 @@
+/*-
+ * ============LICENSE_START=======================================================
+ * ONAP : APPC
+ * ================================================================================
+ * Copyright (C) 2017 AT&T Intellectual Property. All rights reserved.
+ * ================================================================================
+ * Copyright (C) 2017 Amdocs
+ * =============================================================================
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ * 
+ *      http://www.apache.org/licenses/LICENSE-2.0
+ * 
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ * 
+ * ECOMP is a trademark and service mark of AT&T Intellectual Property.
+ * ============LICENSE_END=========================================================
+ */
+
+package org.onap.appc.dao.util.api;
+
+import org.onap.appc.dao.util.exception.DBConnectionPoolException;
+import org.onap.appc.dao.util.exception.DataAccessException;
+
+import java.sql.Connection;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.Map;
+
+/**
+ * This class is the interface of DBConnectionPool.
+ * <p>
+ * Below is an example of how to query an entry from database.
+ * Inject AppcDatabaseConnectionPool bean by the blueprint first
+ * for example,
+ * {@code
+ * <reference id="AppcMysqlDBConnectionPoolService" availability="mandatory"
+ * activation="eager" interface="org.onap.appc.dao.util.api.DBConnectionPoolService" />
+ * }
+ * <p>
+ * Then, query the data and close ResultSet, Statement, Connection.
+ * <blockquote><pre>
+ * {@code
+ * private AppcDatabaseConnectionPool pool;
+ * public void setAppcDatabaseConnectionPool(AppcDatabaseConnectionPool pool){
+ *     this.pool = pool;
+ * }
+ * public queryAppcDatabase(AppcDatabaseConnectionPool pool){
+ *      Connection connection = null;
+ *      try {
+ *          connection = pool.getConnection();
+ *      } catch (DBConnectionPoolException e) {
+ *          e.printStackTrace();
+ *      }
+ *      Connection conn = null;
+ *      Statement stmt = null;
+ *      ResultSet rs = null;
+ *      try {
+ *            stmt = connection.createStatement();
+ *            rs = stmt.executeQuery("select * from appcctl.transactions");
+ *            System.out.println("# of entries in db:");
+ *            int numcols = rs.getMetaData().getColumnCount();
+ *            System.out.println(pool.getDataSourceStatus());
+ *      }catch (SQLException e) {
+ *          e.printStackTrace();
+ *      } finally {
+ *          try {
+ *               pool.close(rs, stmt, conn);
+ *              } catch (DataAccessException e) {
+ *                  e.printStackTrace();
+ *              }
+ *     }
+ * }
+ * }
+ * <p>
+ * </pre></blockquote>
+ */
+public interface DBConnectionPoolService {
+    /**
+     * Get a jdbc connection
+     *
+     * @return connection {@link Connection}
+     * @throws DBConnectionPoolException - if a {@link Connection} cannot be return.
+     */
+    Connection getConnection() throws DBConnectionPoolException;
+
+    /**
+     * Get Data source status
+     *
+     * @return map
+     */
+    Map<String, Integer> getDataSourceStatus();
+
+}
diff --git a/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/JdbcConnectionFactory.java b/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/api/JdbcConnectionFactory.java
index 1a47db3f7..5c7342508 100644
--- a/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/JdbcConnectionFactory.java
+++ b/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/api/JdbcConnectionFactory.java
@@ -22,12 +22,30 @@
  * ============LICENSE_END=========================================================
  */
 
-package org.onap.appc.dao.util;
+package org.onap.appc.dao.util.api;
 
 import java.sql.Connection;
 
+/**
+ * @deprecated As of release 1802, replaced by {@link #(org.onap.appc.dao.util.api.DBConnectionPoolService)}
+ * <p>
+ * This interface has been deprecated due to a connection pool has
+ * been introduced into this bundle.
+ * refer to {@link DBConnectionPoolService}
+ */
+@Deprecated
 public interface JdbcConnectionFactory {
-
+    /**
+     * Open a jdbc connection
+     *
+     * @return {@link Connection}
+     */
     Connection openDbConnection();
+
+    /**
+     * Close a jdbc connection
+     *
+     * @param {@link Connection}
+     */
     void closeDbConnection(Connection connection);
 }
diff --git a/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/dbcp/DBConnectionPool.java b/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/dbcp/DBConnectionPool.java
new file mode 100644
index 000000000..7f4b59baa
--- /dev/null
+++ b/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/dbcp/DBConnectionPool.java
@@ -0,0 +1,154 @@
+/*-
+ * ============LICENSE_START=======================================================
+ * ONAP : APPC
+ * ================================================================================
+ * Copyright (C) 2017 AT&T Intellectual Property. All rights reserved.
+ * ================================================================================
+ * Copyright (C) 2017 Amdocs
+ * =============================================================================
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ * 
+ *      http://www.apache.org/licenses/LICENSE-2.0
+ * 
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ * 
+ * ECOMP is a trademark and service mark of AT&T Intellectual Property.
+ * ============LICENSE_END=========================================================
+ */
+
+package org.onap.appc.dao.util.dbcp;
+
+import com.att.eelf.configuration.EELFLogger;
+import com.att.eelf.configuration.EELFManager;
+import org.apache.commons.dbcp2.BasicDataSource;
+import org.onap.appc.dao.util.api.DBConnectionPoolService;
+import org.onap.appc.dao.util.exception.DBConnectionPoolException;
+
+import java.sql.Connection;
+import java.sql.SQLException;
+import java.util.HashMap;
+import java.util.Map;
+
+/**
+ * This class provides ability to create database connection pool.
+ */
+public class DBConnectionPool implements DBConnectionPoolService {
+
+    private final EELFLogger logger = EELFManager.getInstance().getLogger(DBConnectionPool.class);
+    private BasicDataSource dataSource;
+
+    public enum DataSourceStatus {
+        ACTIVE_NUMBER("active_number"),
+        IDLE_NUMBER("idle_number");
+
+        private String attribute;
+
+        DataSourceStatus(String attribute) {
+            this.attribute = attribute;
+        }
+
+        public String getAttribute() {
+            return attribute;
+        }
+    }
+
+    public DBConnectionPool(String connectURI, String username, String password, String driverClass) {
+        this(connectURI, username, password, driverClass, null, null, null, null, null);
+    }
+
+    public DBConnectionPool(String connectURI, String username, String password,
+                            String driverClass, Integer initialSize, Integer maxActive,
+                            Integer maxIdle, Integer maxWait, Integer minIdle) {
+        this.dataSource = getBasicDataSource(connectURI, username, password, driverClass,
+            initialSize, maxActive, maxIdle, maxWait, minIdle);
+    }
+
+    /**
+     * Get a connection from datasource which is thread safe.
+     * {@inheritDoc}
+     */
+    @Override
+    public Connection getConnection() throws DBConnectionPoolException {
+        if (dataSource == null) {
+            throw new DBConnectionPoolException();
+        }
+
+        Connection connection = null;
+        try {
+            connection = dataSource.getConnection();
+        } catch (SQLException e) {
+            logger.error("Get connection failure", e);
+            throw new DBConnectionPoolException(e);
+        }
+
+        if(connection == null){
+            //
+            throw new DBConnectionPoolException("Connection was not created");
+        }
+
+        return connection;
+    }
+
+    /**
+     * Closes and releases all idle connections that are currently stored in the connection pool associated with this
+     * data source.
+     */
+    public void shutdown() {
+        if (dataSource != null) {
+            try {
+                dataSource.close();
+            } catch (SQLException e) {
+                logger.error("Datasource cannot be closed normally.", e.getMessage());
+            }
+        }
+
+        dataSource = null;
+    }
+
+    /**
+     * Get datasource status
+     *
+     * @return
+     */
+    public Map<String, Integer> getDataSourceStatus() {
+        Map<String, Integer> map = new HashMap<>(2);
+        map.put(DataSourceStatus.ACTIVE_NUMBER.getAttribute(), dataSource.getNumActive());
+        map.put(DataSourceStatus.IDLE_NUMBER.getAttribute(), dataSource.getNumIdle());
+
+        return map;
+    }
+
+    private BasicDataSource getBasicDataSource(String connectURI, String username, String password,
+                                               String driverClass, Integer initialSize, Integer maxtotal,
+                                               Integer maxIdle, Integer maxWaitMillis, Integer minIdle) {
+        BasicDataSource dataSource = new BasicDataSource();
+        dataSource.setDriverClassName(driverClass);
+        dataSource.setUsername(username);
+        dataSource.setPassword(password);
+        dataSource.setUrl(connectURI);
+
+        if (initialSize != null) {
+            dataSource.setInitialSize(initialSize);
+        }
+        if (maxtotal != null) {
+            dataSource.setMaxTotal(maxtotal);
+        }
+        if (maxIdle != null) {
+            dataSource.setMaxIdle(maxIdle);
+        }
+        if (maxWaitMillis != null) {
+            dataSource.setMaxWaitMillis(maxWaitMillis);
+        }
+        if (minIdle != null) {
+            dataSource.setMinIdle(minIdle);
+        }
+
+        return dataSource;
+    }
+}
diff --git a/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/exception/DBConnectionPoolException.java b/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/exception/DBConnectionPoolException.java
new file mode 100644
index 000000000..fff269b36
--- /dev/null
+++ b/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/exception/DBConnectionPoolException.java
@@ -0,0 +1,48 @@
+/*-
+ * ============LICENSE_START=======================================================
+ * ONAP : APPC
+ * ================================================================================
+ * Copyright (C) 2017 AT&T Intellectual Property. All rights reserved.
+ * ================================================================================
+ * Copyright (C) 2017 Amdocs
+ * =============================================================================
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ * 
+ *      http://www.apache.org/licenses/LICENSE-2.0
+ * 
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ * 
+ * ECOMP is a trademark and service mark of AT&T Intellectual Property.
+ * ============LICENSE_END=========================================================
+ */
+
+package org.onap.appc.dao.util.exception;
+
+import java.sql.SQLException;
+
+/**
+ * This class is exception class for DBConnectionPool{@link org.onap.appc.dao.util.dbcp.DBConnectionPool}
+ */
+public class DBConnectionPoolException extends SQLException {
+    public DBConnectionPoolException() {
+        super();
+    }
+
+    public DBConnectionPoolException(String message) {
+        super(message);
+    }
+
+    public DBConnectionPoolException(String message, Throwable cause) {
+        super(message, cause);
+    }
+
+    public DBConnectionPoolException(Throwable cause) {
+        super(cause);
+    }
+}
diff --git a/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/exception/DataAccessException.java b/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/exception/DataAccessException.java
new file mode 100644
index 000000000..1fae9c979
--- /dev/null
+++ b/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/exception/DataAccessException.java
@@ -0,0 +1,48 @@
+/*-
+ * ============LICENSE_START=======================================================
+ * ONAP : APPC
+ * ================================================================================
+ * Copyright (C) 2017 AT&T Intellectual Property. All rights reserved.
+ * ================================================================================
+ * Copyright (C) 2017 Amdocs
+ * =============================================================================
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ * 
+ *      http://www.apache.org/licenses/LICENSE-2.0
+ * 
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ * 
+ * ECOMP is a trademark and service mark of AT&T Intellectual Property.
+ * ============LICENSE_END=========================================================
+ */
+
+package org.onap.appc.dao.util.exception;
+
+import java.sql.SQLException;
+
+/**
+ * This class is exception class for DataAccessException
+ */
+public class DataAccessException extends SQLException {
+    public DataAccessException() {
+        super();
+    }
+
+    public DataAccessException(String message) {
+        super(message);
+    }
+
+    public DataAccessException(String message, Throwable cause) {
+        super(message, cause);
+    }
+
+    public DataAccessException(Throwable cause) {
+        super(cause);
+    }
+}
diff --git a/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/JdbcRuntimeException.java b/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/exception/JdbcRuntimeException.java
index 8e606fe94..3ac4f654e 100644
--- a/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/JdbcRuntimeException.java
+++ b/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/exception/JdbcRuntimeException.java
@@ -22,7 +22,7 @@
  * ============LICENSE_END=========================================================
  */
 
-package org.onap.appc.dao.util;
+package org.onap.appc.dao.util.exception;
 
 public class JdbcRuntimeException extends RuntimeException {
 
diff --git a/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/helper/DBHelper.java b/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/helper/DBHelper.java
new file mode 100644
index 000000000..20e119a12
--- /dev/null
+++ b/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/helper/DBHelper.java
@@ -0,0 +1,110 @@
+/*-
+ * ============LICENSE_START=======================================================
+ * ONAP : APPC
+ * ================================================================================
+ * Copyright (C) 2017 AT&T Intellectual Property. All rights reserved.
+ * ================================================================================
+ * Copyright (C) 2017 Amdocs
+ * =============================================================================
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ * 
+ *      http://www.apache.org/licenses/LICENSE-2.0
+ * 
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ * 
+ * ECOMP is a trademark and service mark of AT&T Intellectual Property.
+ * ============LICENSE_END=========================================================
+ */
+
+package org.onap.appc.dao.util.helper;
+
+import java.sql.Connection;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+
+/**
+ * This class provides the basic utility methods of database connection
+ * <p>
+ * Since currently this class only contains stateless methods, the package
+ * is exported by maven-bundle-plugin. If you have to add some stateful methods
+ * in this class, one suggested solution is that use blueprint to create a singleton
+ * which is exported as service
+ */
+public class DBHelper {
+    /**
+     * Closes a database resultSet,statement and connection in that order. Data access objects should call this method
+     * in a finally block.
+     *
+     * @param resultSet  or null
+     * @param statement  or null
+     * @param connection or null
+     */
+    public static void close(ResultSet resultSet, Statement statement, Connection connection) {
+        try {
+            closeResultSet(resultSet);
+        } finally {
+            try {
+                closeStatement(statement);
+            } finally {
+                closeConnection(connection);
+            }
+        }
+    }
+
+    /**
+     * Closes a database result set. Data access objects should call this method
+     * when a result set is no longer needed.
+     *
+     * @param rs A ResultSet Object
+     */
+    public static void closeResultSet(ResultSet rs) {
+        try {
+            if (rs != null) {
+                rs.close();
+            }
+        } catch (SQLException se) {
+            // Ignore this exception and allow execution to continue.
+            // so that connection can try to be close.
+        }
+    }
+
+    /**
+     * Closes a database query statement. Data access objects should call this
+     * method when a statement is no longer needed.
+     *
+     * @param stmt A Statement Object
+     */
+    public static void closeStatement(Statement stmt) {
+        try {
+            if (stmt != null) {
+                stmt.close();
+            }
+        } catch (SQLException se) {
+            // Ignore this exception and allow execution to continue.
+            // so that connection can try to be close.
+        }
+    }
+
+    /**
+     * Closes a database connection. Data access objects should call this method
+     * when a database connection is no longer needed.
+     *
+     * @param connection A Connection Object     *
+     */
+    public static void closeConnection(Connection connection) {
+        try {
+            if (connection != null && !connection.isClosed()) {
+                connection.close();
+            }
+        } catch (SQLException se) {
+            // Ignore this exception and allow execution to continue.
+        }
+    }
+}
diff --git a/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/Messages.java b/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/message/Messages.java
index 6330793b7..33696938f 100644
--- a/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/Messages.java
+++ b/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/java/org/onap/appc/dao/util/message/Messages.java
@@ -22,7 +22,7 @@
  * ============LICENSE_END=========================================================
  */
 
-package org.onap.appc.dao.util;
+package org.onap.appc.dao.util.message;
 
 public enum Messages {
     EXP_JDBC_CONNECT("Error connecting to JDBC URL [%s]."),
diff --git a/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/resources/OSGI-INF/blueprint/blueprint.xml b/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/resources/OSGI-INF/blueprint/blueprint.xml
new file mode 100644
index 000000000..6dc95ec5a
--- /dev/null
+++ b/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/src/main/resources/OSGI-INF/blueprint/blueprint.xml
@@ -0,0 +1,38 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+  ============LICENSE_START=======================================================
+  ONAP : APPC
+  ================================================================================
+  Copyright (C) 2017 AT&T Intellectual Property. All rights reserved.
+  ================================================================================
+  Copyright (C) 2017 Amdocs
+  =============================================================================
+  Licensed under the Apache License, Version 2.0 (the "License");
+  you may not use this file except in compliance with the License.
+  You may obtain a copy of the License at
+  
+       http://www.apache.org/licenses/LICENSE-2.0
+  
+  Unless required by applicable law or agreed to in writing, software
+  distributed under the License is distributed on an "AS IS" BASIS,
+  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+  See the License for the specific language governing permissions and
+  limitations under the License.
+  
+  ECOMP is a trademark and service mark of AT&T Intellectual Property.
+  ============LICENSE_END=========================================================
+  -->
+
+
+<blueprint xmlns="http://www.osgi.org/xmlns/blueprint/v1.0.0"
+           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
+           xsi:schemaLocation="http://www.osgi.org/xmlns/blueprint/v1.0.0 http://www.osgi.org/xmlns/blueprint/v1.0.0/blueprint.xsd">
+
+    <bean id="AppcMysqlDBConnectionPoolBean" class="org.onap.appc.dao.util.AppcDatabaseConnectionPool"
+          scope="singleton" init-method="init" destroy-method="destroy">
+        <property name="dbName" value="sdnctl"/>
+    </bean>
+
+    <service id="AppcMysqlDBConnectionPoolService" interface="org.onap.appc.dao.util.api.DBConnectionPoolService"
+             ref="AppcMysqlDBConnectionPoolBean"/>
+</blueprint>