Include impacted changes for APPC-346,APPC-348

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

7 files changed, 988 insertions, 195 deletions

diff --git a/appc-dispatcher/appc-dispatcher-common/transaction-recorder/pom.xml b/appc-dispatcher/appc-dispatcher-common/transaction-recorder/pom.xml
index 3e9a24bfa..24148cb81 100644
--- a/appc-dispatcher/appc-dispatcher-common/transaction-recorder/pom.xml
+++ b/appc-dispatcher/appc-dispatcher-common/transaction-recorder/pom.xml
@@ -1,5 +1,29 @@
 <?xml version="1.0" encoding="UTF-8"?>
-<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
+<!--
+  ============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=========================================================
+  -->
+<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
+         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
     <parent>
         <artifactId>appc-dispatcher-common</artifactId>
         <groupId>org.onap.appc</groupId>
@@ -9,8 +33,12 @@
 
     <artifactId>transaction-recorder</artifactId>
     <packaging>bundle</packaging>
+    <name>APPC Transaction Recorder</name>
+
+    <properties>
+        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
+    </properties>
 
-    <name>transaction-recorder</name>
     <dependencies>
         <dependency>
             <groupId>org.onap.appc</groupId>
@@ -22,12 +50,21 @@
             <artifactId>appc-data-access-lib</artifactId>
             <version>${project.version}</version>
         </dependency>
-    </dependencies>
+        <dependency>
+            <groupId>org.onap.appc</groupId>
+            <artifactId>domain-model-lib</artifactId>
+            <version>${project.version}</version>
+        </dependency>
+        <dependency>
+            <groupId>org.onap.appc</groupId>
+            <artifactId>appc-test-dependencies</artifactId>
+            <version>${project.version}</version>
+            <scope>test</scope>
+            <type>pom</type>
+        </dependency>
 
+    </dependencies>
 
-    <properties>
-        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
-    </properties>
     <build>
         <plugins>
             <plugin>
@@ -37,16 +74,18 @@
                     <instructions>
                         <Bundle-SymbolicName>${project.artifactId}</Bundle-SymbolicName>
                         <Bundle-Version>${project.version}</Bundle-Version>
-                        <Embed-Dependency>appc-common,eelf-core,logback-core,logback-classic,appc-data-access-lib;scope=compile|runtime;inline=false</Embed-Dependency>
                         <Embed-Transitive>true</Embed-Transitive>
                         <Export-Service>org.onap.appc.transactionrecorder.TransactionRecorder</Export-Service>
-                        <Import-Package>!javax.*,!groovy.lang,!org.apache.log,!org.apache.log4j.*,!org.codehaus.jackson.*,!org.codehaus.jackson.map.*,!org.codehaus.commons.compiler,!org.codehaus.groovy.*,!org.apache.commons.lang3,!org.codehaus.janino,!org.jasypt.*,!com.ibm.icu.*,!com.sun.faces.*,*;resolution:=optional</Import-Package>
-                        <Export-Package>org.onap.appc.transactionrecorder,org.onap.appc.transactionrecorder.objects</Export-Package>
+                        <Import-Package>
+                            org.onap.appc.domainmodel.lcm,org.onap.ccsdk.sli.resource.dblib,org.onap.appc.dao.util,*;resolution:=optional
+                        </Import-Package>
+                        <Export-Package>
+                            org.onap.appc.transactionrecorder,org.onap.appc.transactionrecorder.objects
+                        </Export-Package>
                     </instructions>
                 </configuration>
             </plugin>
         </plugins>
     </build>
 
-
-</project>
\ No newline at end of file
+</project>
diff --git a/appc-dispatcher/appc-dispatcher-common/transaction-recorder/src/main/java/org/onap/appc/transactionrecorder/TransactionRecorder.java b/appc-dispatcher/appc-dispatcher-common/transaction-recorder/src/main/java/org/onap/appc/transactionrecorder/TransactionRecorder.java
index 8f3c51129..8969b5842 100644
--- a/appc-dispatcher/appc-dispatcher-common/transaction-recorder/src/main/java/org/onap/appc/transactionrecorder/TransactionRecorder.java
+++ b/appc-dispatcher/appc-dispatcher-common/transaction-recorder/src/main/java/org/onap/appc/transactionrecorder/TransactionRecorder.java
@@ -25,13 +25,77 @@
 package org.onap.appc.transactionrecorder;
 
 
-import org.onap.appc.transactionrecorder.objects.TransactionRecord;
+import org.onap.appc.domainmodel.lcm.RequestStatus;
+import org.onap.appc.exceptions.APPCException;
+import org.onap.appc.domainmodel.lcm.TransactionRecord;
+import org.onap.appc.transactionrecorder.objects.TransactionConstants;
 
+import java.util.List;
+import java.util.Map;
 
+/**
+ * Interface to persist and query LCM requests
+ */
 public interface TransactionRecorder {
     /**
      * Stores transaction record to appc database by calling APPC Dao layer.
      * @param record Transaction record data.
      */
-    void store(TransactionRecord record);
+    void store(TransactionRecord record) throws APPCException;
+
+    /**
+     * This method is called when a particular row in transactions needs to be updated
+     * @param key This is TransactionId which uniquely identifies the record.
+     * @param updateColumns Map containing names of updated columns and their values.
+     * @throws APPCException
+     */
+    void update(String key, Map<TransactionConstants.TRANSACTION_ATTRIBUTES, String> updateColumns) throws APPCException;
+
+    /**
+     * Marks all records in Transactions table in non-terminal state as ABORTED. This method is to be called during
+     * APPC startup.
+     *
+     * @param appcInstanceId
+     */
+    void markTransactionsAborted(String appcInstanceId);
+
+    /**
+     * Fetch list of Transactions which are in non-terminal state i.e. ACCEPTED or RECEIVED for particular TargetId.
+     * @param record Transactions object from which TargetId and StartTime is extracted to fetch list of in progress
+     *               requests which APPC received before the current request.
+     * @return List of Transactions in non terminal state.
+     * @throws APPCException
+     */
+    List<TransactionRecord> getInProgressRequests(TransactionRecord record) throws APPCException;
+
+    /**
+     * Checks whether the incoming request is duplicate.
+     * @param record Transaction object from which RequestId, SubRequestId, OriginatorId is extracted to check duplicate request.
+     * @return
+     * @throws APPCException
+     */
+    Boolean isTransactionDuplicate(TransactionRecord record) throws APPCException;
+
+    /**
+     * Retrieves {@link RequestStatus} from transaction table based on the passed parameters.
+     * @param requestId: RequestId of the request to search (Required)
+     * @param subrequestId: Sub-requestId (Optional)
+     * @param originatorId: Originator Id who sent the request(Optional)
+     * @param vnfId: VNFId to search (Required)
+     * @return list of RequestStatus'es
+     */
+    List<RequestStatus> getRecords(String requestId, String subrequestId, String originatorId, String vnfId)
+            throws APPCException;
+
+    /**
+     * Count of all requests which are currently in non-terminal state.
+     * @return Count of all request in state RECEIVED and ACCEPTED.
+     */
+    Integer getInProgressRequestsCount() throws APPCException;
+
+    /**
+     *
+     * @param appcInstanceId
+     */
+    void setAppcInstanceId(String appcInstanceId);
 }
diff --git a/appc-dispatcher/appc-dispatcher-common/transaction-recorder/src/main/java/org/onap/appc/transactionrecorder/impl/TransactionRecorderImpl.java b/appc-dispatcher/appc-dispatcher-common/transaction-recorder/src/main/java/org/onap/appc/transactionrecorder/impl/TransactionRecorderImpl.java
index 89e0d9281..ac3e9d819 100644
--- a/appc-dispatcher/appc-dispatcher-common/transaction-recorder/src/main/java/org/onap/appc/transactionrecorder/impl/TransactionRecorderImpl.java
+++ b/appc-dispatcher/appc-dispatcher-common/transaction-recorder/src/main/java/org/onap/appc/transactionrecorder/impl/TransactionRecorderImpl.java
@@ -24,58 +24,357 @@
 
 package org.onap.appc.transactionrecorder.impl;
 
-import org.onap.appc.dao.util.DBUtils;
+import org.apache.commons.lang.StringUtils;
+import org.onap.appc.domainmodel.lcm.Flags;
+import org.onap.appc.domainmodel.lcm.RequestStatus;
+import org.onap.appc.domainmodel.lcm.VNFOperation;
+import org.onap.appc.exceptions.APPCException;
 import org.onap.appc.transactionrecorder.TransactionRecorder;
-import org.onap.appc.transactionrecorder.objects.TransactionRecord;
+import org.onap.appc.domainmodel.lcm.TransactionRecord;
+import org.onap.appc.transactionrecorder.objects.TransactionConstants;
+import org.onap.ccsdk.sli.core.dblib.DbLibService;
 import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;
 
-import java.sql.Connection;
-import java.sql.PreparedStatement;
+import javax.sql.rowset.CachedRowSet;
 import java.sql.SQLException;
+import java.text.ParseException;
+import java.text.SimpleDateFormat;
+import java.time.Instant;
+import java.time.ZoneOffset;
+import java.time.format.DateTimeFormatter;
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Map;
 
+import static org.onap.appc.transactionrecorder.objects.TransactionConstants.TRANSACTION_ATTRIBUTES.*;
+import static org.onap.appc.transactionrecorder.objects.TransactionConstants.*;
 
 
 public class TransactionRecorderImpl implements TransactionRecorder {
 
-    private static String APPCCTL_SCHEMA = "appcctl";
+    private final String SCHEMA = "sdnctl";
+
+    private String appcInstanceId;
+
+    private DbLibService dbLibService;
+
+    public void setDbLibService(DbLibService dbLibService) {
+        this.dbLibService = dbLibService;
+    }
 
     private static final Logger logger = LoggerFactory.getLogger(TransactionRecorderImpl.class);
 
     /**
      * Stores transaction record to appc database by calling APPC Dao layer.
+     *
      * @param record Transaction record data.
      */
     @Override
-    public void store(TransactionRecord record) {
-        Connection connection = null;
-        PreparedStatement stmt = null;
-        String queryString = "INSERT INTO transactions VALUES (?,?,?,?,?,?,?,?,?,?)";
+    public void store(TransactionRecord record) throws APPCException {
+        if (logger.isTraceEnabled()) {
+            logger.trace("Transaction data insertion into DB");
+        }
+        final String STORE_DATE_QUERY = TransactionConstants.INSERT_INTO + TransactionConstants.TRANSACTIONS +
+            "(" + TRANSACTION_ID.getColumnName() + TransactionConstants.COMMA +
+            ORIGIN_TIMESTAMP.getColumnName() + TransactionConstants.COMMA +
+            REQUEST_ID.getColumnName() + TransactionConstants.COMMA +
+            SUBREQUEST_ID.getColumnName() + TransactionConstants.COMMA +
+            ORIGINATOR_ID.getColumnName() + TransactionConstants.COMMA +
+            START_TIME.getColumnName() + TransactionConstants.COMMA +
+            END_TIME.getColumnName() + TransactionConstants.COMMA +
+            TARGET_ID.getColumnName() + TransactionConstants.COMMA +
+            TARGET_TYPE.getColumnName() + TransactionConstants.COMMA +
+            OPERATION.getColumnName() + TransactionConstants.COMMA +
+            RESULT_CODE.getColumnName() + TransactionConstants.COMMA +
+            DESCRIPTION.getColumnName() + TransactionConstants.COMMA +
+            STATE.getColumnName() + TransactionConstants.COMMA +
+            SERVICE_INSTANCE_ID + TransactionConstants.COMMA +
+            VNFC_NAME + TransactionConstants.COMMA +
+            VSERVER_ID + TransactionConstants.COMMA +
+            VF_MODULE_ID + TransactionConstants.COMMA +
+            MODE + ") " +
+            "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
+        try {
+            dbLibService.writeData(STORE_DATE_QUERY, prepareArguments(record), SCHEMA);
+        } catch (SQLException e) {
+            logger.error("Error on storing record " + record.toString(), e);
+            throw new APPCException(ERROR_ACCESSING_DATABASE, e);
+        }
+        if (logger.isTraceEnabled()) {
+            logger.trace("Transaction Data Inserted Successfully into DB");
+        }
+    }
+
+    @Override
+    public void update(String key, Map<TransactionConstants.TRANSACTION_ATTRIBUTES, String> updateColumns) throws
+        APPCException {
+        ArrayList<String> values = new ArrayList<>();
+
+        StringBuilder queryBuilder = new StringBuilder("UPDATE TRANSACTIONS SET ");
+        for (Map.Entry<TransactionConstants.TRANSACTION_ATTRIBUTES, String> entry : updateColumns.entrySet()) {
+            queryBuilder.append(entry.getKey().getColumnName() + " = ? ,");
+            values.add(entry.getValue());
+        }
+        queryBuilder.deleteCharAt(queryBuilder.lastIndexOf(","));
+        queryBuilder.append(WHERE + TRANSACTION_ID.getColumnName() + " = ?");
+        values.add(appcInstanceId + "~" + key);
+
+        String query = queryBuilder.toString();
+        try {
+            dbLibService.writeData(query, values, SCHEMA);
+        } catch (SQLException e) {
+            logger.error("Error in updating records " + e);
+            throw new APPCException(ERROR_ACCESSING_DATABASE, e);
+        }
+        if (logger.isTraceEnabled()) {
+            logger.trace("Transaction data updated successfully");
+        }
+
+    }
+
+    @Override
+    public void markTransactionsAborted(String appcInstanceId) {
+        if (logger.isTraceEnabled()) {
+            logger.trace("marking in progress transactions to aborted");
+        }
+        final String updateQuery =
+            "UPDATE " + TransactionConstants.TRANSACTIONS +
+                " SET " + STATE.getColumnName() + " = '" + RequestStatus.ABORTED.name() + "',"
+                        + END_TIME.getColumnName() + " = ? " +
+                WHERE + TRANSACTION_ID.getColumnName() + " LIKE '" + appcInstanceId + "%'  AND "
+                + STATE.getColumnName() + " in (?,?)";
+
+        if (logger.isDebugEnabled()) {
+            logger.debug("Update query " + updateQuery + " appc-instance-id " + appcInstanceId);
+        }
+
+        ArrayList<String> arguments = new ArrayList<>();
+        arguments.add(dateToStringConverterMillis(Instant.now()));
+        arguments.add(RequestStatus.ACCEPTED.name());
+        arguments.add(RequestStatus.RECEIVED.name());
         try {
-            if (logger.isDebugEnabled()) {
-                logger.debug("Transaction Data started Inserting Successfully into DB");
+            dbLibService.writeData(updateQuery, arguments, SCHEMA);
+        } catch (SQLException e) {
+            String message = "In progress transactions couldn't be marked aborted on server start up";
+            logger.error(message);
+            throw new RuntimeException(message);
+        }
+        if (logger.isTraceEnabled()) {
+            logger.trace("In progress transactions marked aborted");
+        }
+    }
+
+    @Override
+    public List<TransactionRecord> getInProgressRequests(TransactionRecord record) throws APPCException {
+
+        final String IN_PROGRESS_REQUESTS_QUERY = "SELECT * FROM " +
+            TransactionConstants.TRANSACTIONS + WHERE +
+            TARGET_ID + " = ? AND " +
+            STATE.getColumnName() + " IN (?,?) AND " +
+            START_TIME.getColumnName() + " < ?";
+
+        ArrayList<String> inProgressQueryParams = new ArrayList<>();
+        inProgressQueryParams.add(record.getTargetId());
+        inProgressQueryParams.add(RequestStatus.RECEIVED.name());
+        inProgressQueryParams.add(RequestStatus.ACCEPTED.name());
+        inProgressQueryParams.add(dateToStringConverterMillis(record.getStartTime()));
+
+        try (CachedRowSet rowSet = dbLibService.getData(IN_PROGRESS_REQUESTS_QUERY, inProgressQueryParams, SCHEMA)) {
+            List<TransactionRecord> inProgressRecords = new ArrayList<>();
+            TransactionRecord transaction;
+            while (rowSet.next()) {
+                transaction = new TransactionRecord();
+                transaction.setTransactionId(rowSet.getString(TRANSACTION_ID.getColumnName()));
+                transaction.setRequestId(rowSet.getString(REQUEST_ID.getColumnName()));
+                transaction.setSubRequestId(rowSet.getString(SUBREQUEST_ID.getColumnName()));
+                transaction.setOriginatorId(rowSet.getString(ORIGINATOR_ID.getColumnName()));
+                transaction.setStartTime(stringToDateConverterMillis(rowSet.getString(START_TIME.getColumnName())));
+                transaction.setTargetId(rowSet.getString(TARGET_ID.getColumnName()));
+                transaction.setTargetType(rowSet.getString(TARGET_TYPE.getColumnName()));
+                transaction.setOperation(VNFOperation.valueOf(rowSet.getString(OPERATION.getColumnName())));
+                transaction.setRequestState(RequestStatus.valueOf(rowSet.getString(STATE.getColumnName())));
+                transaction.setVnfcName(rowSet.getString(VNFC_NAME.getColumnName()));
+                transaction.setVserverId(rowSet.getString(VSERVER_ID.getColumnName()));
+                transaction.setVfModuleId(rowSet.getString(VF_MODULE_ID.getColumnName()));
+                transaction.setServiceInstanceId(rowSet.getString(SERVICE_INSTANCE_ID.getColumnName()));
+                transaction.setMode(Flags.Mode.valueOf(rowSet.getString(MODE.getColumnName())));
+                inProgressRecords.add(transaction);
+            }
+            if (logger.isTraceEnabled()) {
+                logger.trace("In progress transaction records fetched from database successfully.");
+            }
+            return inProgressRecords;
+        } catch (ParseException e) {
+            logger.error("Error parsing start date during fetching in progress records ", e);
+            throw new APPCException(ERROR_ACCESSING_DATABASE, e);
+        } catch (SQLException e) {
+            logger.error("Error fetching in progress records for Transaction ID = " + appcInstanceId + "~" + record
+                .getTransactionId(), e);
+            throw new APPCException(ERROR_ACCESSING_DATABASE, e);
+        }
+    }
+
+    @Override
+    public Boolean isTransactionDuplicate(TransactionRecord record) throws APPCException {
+
+        StringBuilder duplicateRequestCheckQuery = new StringBuilder("SELECT " +
+            TRANSACTION_ID.getColumnName() + " FROM " +
+            TransactionConstants.TRANSACTIONS + WHERE +
+            TRANSACTION_ID.getColumnName() + " <> ? AND " +
+            REQUEST_ID.getColumnName() + " = ? AND " +
+            STATE.getColumnName() + " IN(?,?) ");
+
+        ArrayList<String> duplicateCheckParams = new ArrayList<>();
+        duplicateCheckParams.add(appcInstanceId + "~" + record.getTransactionId());
+        duplicateCheckParams.add(record.getRequestId());
+        duplicateCheckParams.add(RequestStatus.RECEIVED.name());
+        duplicateCheckParams.add(RequestStatus.ACCEPTED.name());
+
+        if (!StringUtils.isBlank(record.getSubRequestId())) {
+            duplicateRequestCheckQuery.append(AND + SUBREQUEST_ID.getColumnName() + " = ? ");
+            duplicateCheckParams.add(record.getSubRequestId());
+        } else {
+            duplicateRequestCheckQuery.append(AND + SUBREQUEST_ID.getColumnName() + IS_NULL);
+        }
+        if (!StringUtils.isBlank(record.getOriginatorId())) {
+            duplicateRequestCheckQuery.append(AND + ORIGINATOR_ID.getColumnName() + " = ? ");
+            duplicateCheckParams.add(record.getOriginatorId());
+        } else {
+            duplicateRequestCheckQuery.append(AND + ORIGINATOR_ID.getColumnName() + IS_NULL);
+        }
+        if (logger.isDebugEnabled()) {
+            logger.debug(duplicateRequestCheckQuery.toString());
+        }
+        try (CachedRowSet rowSet = dbLibService.getData(duplicateRequestCheckQuery.toString(), duplicateCheckParams,
+            SCHEMA)) {
+            if (rowSet.first()) {
+                String transactionId = rowSet.getString(TRANSACTION_ID.getColumnName());
+                if (logger.isErrorEnabled()) {
+                    logger.error("Duplicate request found. Transaction ID " + transactionId + " is currently in " +
+                        "progress.");
+                }
+                return true;
+            }
+            return false;
+        } catch (SQLException e) {
+            logger.error("Error checking duplicate records for Transaction ID = " + appcInstanceId + "~" + record
+                .getTransactionId(), e);
+            throw new APPCException(ERROR_ACCESSING_DATABASE, e);
+        }
+    }
+
+    @Override
+    public Integer getInProgressRequestsCount() throws APPCException {
+        final String inProgressRequestCountQuery = "SELECT COUNT(*) as VALUE FROM "
+            + TransactionConstants.TRANSACTIONS
+            + WHERE + STATE.getColumnName() + " IN (?,?) ";
+
+        ArrayList<String> checkInProgressParams = new ArrayList<>();
+        checkInProgressParams.add(RequestStatus.RECEIVED.name());
+        checkInProgressParams.add(RequestStatus.ACCEPTED.name());
+        try(CachedRowSet rowSet=dbLibService.getData(inProgressRequestCountQuery,checkInProgressParams,SCHEMA)){
+            if (rowSet.first()) {
+                int count = rowSet.getInt("VALUE");
+                logger.info("In progress request count fetched from database successfully.");
+                return count;
             }
-            connection = DBUtils.getConnection(APPCCTL_SCHEMA);
-            stmt = connection.prepareStatement(queryString);
-            stmt.setTimestamp(1, new java.sql.Timestamp(record.getTimeStamp().toEpochMilli()));
-            stmt.setString(2, record.getRequestID());
-            stmt.setTimestamp(3, new java.sql.Timestamp(record.getStartTime().toEpochMilli()));
-            stmt.setTimestamp(4, new java.sql.Timestamp(record.getEndTime().toEpochMilli()));
-            stmt.setString(5, record.getTargetID());
-            stmt.setString(6, record.getTargetType());
-            stmt.setString(7, record.getSubComponent());
-            stmt.setString(8, record.getOperation());
-            stmt.setString(9, record.getResultCode());
-            stmt.setString(10, record.getDescription());
-            stmt.execute();
-            if (logger.isDebugEnabled()) {
-                logger.debug("Transaction Data Inserted Successfully into DB");
+        }
+        catch (SQLException e) {
+            logger.error("Error checking in progress request count in the transaction table", e);
+            throw new APPCException(ERROR_ACCESSING_DATABASE, e);
+        }
+        logger.error("Error checking in progress request count in the transaction table");
+        throw new APPCException(ERROR_ACCESSING_DATABASE);
+    }
+
+    @Override
+    public void setAppcInstanceId(String appcInstanceId) {
+        this.appcInstanceId = appcInstanceId;
+    }
+
+
+    @Override
+    public List<RequestStatus> getRecords(String requestId, String subrequestId, String originatorId, String vnfId)
+        throws APPCException {
+        StringBuilder queryString = (new StringBuilder(1024))
+            .append("SELECT " + TRANSACTION_ATTRIBUTES.STATE.getColumnName())
+            .append(" FROM " + TRANSACTIONS)
+            .append(" WHERE " + TRANSACTION_ATTRIBUTES.REQUEST_ID.getColumnName() + "  = ? AND " +
+                TRANSACTION_ATTRIBUTES.TARGET_ID.getColumnName() + " = ?");
+
+        ArrayList<String> argList = new ArrayList<>();
+        argList.add(requestId);
+        argList.add(vnfId);
+
+        if (subrequestId != null) {
+            queryString.append(" AND " + TRANSACTION_ATTRIBUTES.SUBREQUEST_ID.getColumnName() + " = ?");
+            argList.add(subrequestId);
+        }
+        if (originatorId != null) {
+            queryString.append(" AND " + TRANSACTION_ATTRIBUTES.ORIGINATOR_ID.getColumnName() + " = ?");
+            argList.add(originatorId);
+        }
+
+        List<RequestStatus> requestStatusList = new ArrayList<>();
+        try {
+            CachedRowSet resultSet = dbLibService.getData(queryString.toString(), argList, SCHEMA);
+            while (resultSet.next()) {
+                String name = resultSet.getString(TRANSACTION_ATTRIBUTES.STATE.getColumnName());
+                RequestStatus requestStatus = null;
+                try {
+                    requestStatus = RequestStatus.valueOf(name);
+                } catch (IllegalArgumentException e) {
+                    logger.error(String.format("Invalid request status (%s) using (%s) :", name, RequestStatus
+                        .UNKNOWN), e);
+                    requestStatus = RequestStatus.UNKNOWN;
+                }
+                requestStatusList.add(requestStatus);
+                logger.debug(String.format("Request Status obtained (%s).", requestStatus));
             }
         } catch (SQLException e) {
-            logger.error("Error Accessing Database " + e);
-            throw new RuntimeException(e);
-        } finally {
-            DBUtils.clearResources(null, stmt, connection);
+            logger.error("Error Accessing Database ", e);
+            throw new APPCException(String.format("Error retrieving record for requestID %s and vnfId %s " +
+                "from the transactions table", requestId, vnfId), e);
+        }
+
+        return requestStatusList;
+    }
+
+    private ArrayList<String> prepareArguments(TransactionRecord input) {
+        ArrayList<String> arguments = new ArrayList<>();
+        arguments.add(appcInstanceId + "~" + input.getTransactionId());
+        arguments.add(dateToStringConverterMillis(input.getOriginTimestamp()));
+        arguments.add(input.getRequestId());
+        arguments.add(input.getSubRequestId());
+        arguments.add(input.getOriginatorId());
+        arguments.add(dateToStringConverterMillis(input.getStartTime()));
+        arguments.add(dateToStringConverterMillis(input.getEndTime()));
+        arguments.add(input.getTargetId());
+        arguments.add(input.getTargetType());
+        arguments.add(input.getOperation().name());
+        arguments.add(String.valueOf(input.getResultCode()));
+        arguments.add(input.getDescription());
+        arguments.add(input.getRequestState());
+        arguments.add(input.getServiceInstanceId());
+        arguments.add(input.getVnfcName());
+        arguments.add(input.getVserverId());
+        arguments.add(input.getVfModuleId());
+        arguments.add(input.getMode());
+
+        return arguments;
+    }
+
+    private static String dateToStringConverterMillis(Instant date) {
+        if (date == null) {
+            return null;
         }
+        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS").withZone(ZoneOffset.UTC);
+        return formatter.format(date);
+    }
+
+    private static Instant stringToDateConverterMillis(String dateString) throws ParseException {
+        SimpleDateFormat customDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
+        return customDate.parse(dateString).toInstant();
     }
 }
diff --git a/appc-dispatcher/appc-dispatcher-common/transaction-recorder/src/main/java/org/onap/appc/transactionrecorder/objects/TransactionConstants.java b/appc-dispatcher/appc-dispatcher-common/transaction-recorder/src/main/java/org/onap/appc/transactionrecorder/objects/TransactionConstants.java
new file mode 100644
index 000000000..ada9f0751
--- /dev/null
+++ b/appc-dispatcher/appc-dispatcher-common/transaction-recorder/src/main/java/org/onap/appc/transactionrecorder/objects/TransactionConstants.java
@@ -0,0 +1,68 @@
+/*-
+ * ============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.transactionrecorder.objects;
+
+public class TransactionConstants {
+
+    public static final String INSERT_INTO = "INSERT INTO ";
+    public static final String TRANSACTIONS = "TRANSACTIONS";
+    public static final String COMMA= " , ";
+    public static final String WHERE = " WHERE ";
+    public static final String IS_NULL= " IS NULL ";
+    public static final String AND=" AND ";
+    public static final String ERROR_ACCESSING_DATABASE = "Error Accessing Database ";
+
+    public enum TRANSACTION_ATTRIBUTES {
+
+        TRANSACTION_ID("TRANSACTION_ID"),
+        ORIGIN_TIMESTAMP("ORIGIN_TIMESTAMP"),
+        REQUEST_ID("REQUEST_ID"),
+        SUBREQUEST_ID("SUBREQUEST_ID"),
+        ORIGINATOR_ID("ORIGINATOR_ID"),
+        START_TIME("START_TIME"),
+        END_TIME("END_TIME"),
+        TARGET_ID("TARGET_ID"),
+        TARGET_TYPE("TARGET_TYPE"),
+        OPERATION("OPERATION"),
+        RESULT_CODE("RESULT_CODE"),
+        DESCRIPTION("DESCRIPTION"),
+        STATE("STATE"),
+        SERVICE_INSTANCE_ID("SERVICE_INSTANCE_ID"),
+        VNFC_NAME("VNFC_NAME"),
+        VSERVER_ID("VSERVER_ID"),
+        VF_MODULE_ID("VF_MODULE_ID"),
+        MODE("MODE");
+
+        private String columnName;
+        TRANSACTION_ATTRIBUTES(String columnName){
+            this.columnName=columnName;
+        }
+
+        public String getColumnName(){
+            return columnName;
+        }
+    }
+
+}
diff --git a/appc-dispatcher/appc-dispatcher-common/transaction-recorder/src/main/java/org/onap/appc/transactionrecorder/objects/TransactionRecord.java b/appc-dispatcher/appc-dispatcher-common/transaction-recorder/src/main/java/org/onap/appc/transactionrecorder/objects/TransactionRecord.java
deleted file mode 100644
index ba3af9985..000000000
--- a/appc-dispatcher/appc-dispatcher-common/transaction-recorder/src/main/java/org/onap/appc/transactionrecorder/objects/TransactionRecord.java
+++ /dev/null
@@ -1,150 +0,0 @@
-/*-
- * ============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.transactionrecorder.objects;
-
-import java.time.Instant;
-
-
-public class TransactionRecord {
-
-   /*
-- Timestamp = RequestHandlerInput.RequestHeader.timeStamp
-- Request ID = RequestHandlerInput.RequestHeader.requestID
-- Start time = from flow
-- End time = from flow
-- VF_ID = RequestHandlerInput.targetID
-- VF_type = genericVnf.getVnfType()
-- Sub-component (optional) e.g. VFC_ID/VM UUID  - ???? empty
-- Operation e.g. Start, Configure etc.  = CommandContext.Command
-- Result - Success/Error code + description,as published to the initiator RequestHandlerResponse.ACCEPTED/RequestHandlerResponse.REJECTED + String (description)
-    */
-
-    private Instant timeStamp;
-    private String requestID;
-    private Instant startTime;
-    private Instant endTime;
-    private String targetID;
-    private String targetType;
-    private String subComponent;
-    private String operation;
-    private String resultCode;
-    private String description;
-
-    public Instant getTimeStamp() {
-        return timeStamp;
-    }
-
-    public void setTimeStamp(Instant timeStamp) {
-        this.timeStamp = timeStamp;
-    }
-
-    public String getRequestID() {
-        return requestID;
-    }
-
-    public void setRequestID(String requestID) {
-        this.requestID = requestID;
-    }
-
-    public Instant getStartTime() {
-        return startTime;
-    }
-
-    public void setStartTime(Instant startTime) {
-        this.startTime = startTime;
-    }
-
-    public Instant getEndTime() {
-        return endTime;
-    }
-
-    public void setEndTime(Instant endTime) {
-        this.endTime = endTime;
-    }
-
-    public String getTargetID() {
-        return targetID;
-    }
-
-    public void setTargetID(String targetID) {
-        this.targetID = targetID;
-    }
-
-    public String getTargetType() {
-        return targetType;
-    }
-
-    public void setTargetType(String targetType) {
-        this.targetType = targetType;
-    }
-
-    public String getSubComponent() {
-        return subComponent;
-    }
-
-    public void setSubComponent(String subComponent) {
-        this.subComponent = subComponent;
-    }
-
-    public String getOperation() {
-        return operation;
-    }
-
-    public void setOperation(String operation) {
-        this.operation = operation;
-    }
-
-    public String getResultCode() {
-        return resultCode;
-    }
-
-    public void setResultCode(String resultCode) {
-        this.resultCode = resultCode;
-    }
-
-    public String getDescription() {
-        return description;
-    }
-
-    public void setDescription(String description) {
-        this.description = description;
-    }
-
-    @Override
-    public String toString() {
-        return "TransactionRecord{" +
-                "timeStamp=" + timeStamp +
-                ", requestID='" + requestID + '\'' +
-                ", startTime=" + startTime +
-                ", endTime=" + endTime +
-                ", targetID='" + targetID + '\'' +
-                ", targetType='" + targetType + '\'' +
-                ", subComponent='" + subComponent + '\'' +
-                ", operation='" + operation + '\'' +
-                ", resultCode='" + resultCode + '\'' +
-                ", description='" + description + '\'' +
-                '}';
-    }
-}
diff --git a/appc-dispatcher/appc-dispatcher-common/transaction-recorder/src/main/resources/OSGI-INF/blueprint/blueprint.xml b/appc-dispatcher/appc-dispatcher-common/transaction-recorder/src/main/resources/OSGI-INF/blueprint/blueprint.xml
index ba8d9f8a0..9240b0efe 100644
--- a/appc-dispatcher/appc-dispatcher-common/transaction-recorder/src/main/resources/OSGI-INF/blueprint/blueprint.xml
+++ b/appc-dispatcher/appc-dispatcher-common/transaction-recorder/src/main/resources/OSGI-INF/blueprint/blueprint.xml
@@ -29,6 +29,13 @@
 <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="transactionRecorderBean" class="org.onap.appc.transactionrecorder.impl.TransactionRecorderImpl" scope="singleton" ></bean>
-    <service id="transactionRecorderService" interface="org.onap.appc.transactionrecorder.TransactionRecorder" ref="transactionRecorderBean"/>
+    <reference id="dbLibServiceRef" availability="mandatory" activation="eager"
+               interface="org.onap.ccsdk.sli.core.dblib.DbLibService"/>
+    <bean id="transactionRecorderBean" class="org.onap.appc.transactionrecorder.impl.TransactionRecorderImpl"
+          scope="singleton">
+        <property name="dbLibService" ref="dbLibServiceRef"/>
+    </bean>
+
+    <service id="transactionRecorderService" interface="org.onap.appc.transactionrecorder.TransactionRecorder"
+             ref="transactionRecorderBean"/>
 </blueprint>
diff --git a/appc-dispatcher/appc-dispatcher-common/transaction-recorder/src/test/java/org/onap/appc/transactionrecorder/impl/TransactionRecorderImplTest.java b/appc-dispatcher/appc-dispatcher-common/transaction-recorder/src/test/java/org/onap/appc/transactionrecorder/impl/TransactionRecorderImplTest.java
new file mode 100644
index 000000000..738a8a638
--- /dev/null
+++ b/appc-dispatcher/appc-dispatcher-common/transaction-recorder/src/test/java/org/onap/appc/transactionrecorder/impl/TransactionRecorderImplTest.java
@@ -0,0 +1,466 @@
+/*-
+ * ============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.transactionrecorder.impl;
+
+import com.sun.rowset.CachedRowSetImpl;
+import org.junit.After;
+import org.junit.Assert;
+import org.junit.Before;
+import org.junit.Test;
+import org.mockito.Mockito;
+
+import org.onap.appc.dao.util.dbcp.DBConnectionPool;
+import org.onap.appc.dao.util.helper.DBHelper;
+import org.onap.appc.domainmodel.lcm.Flags;
+import org.onap.appc.domainmodel.lcm.RequestStatus;
+import org.onap.appc.domainmodel.lcm.TransactionRecord;
+import org.onap.appc.domainmodel.lcm.VNFOperation;
+import org.onap.appc.exceptions.APPCException;
+import org.onap.appc.transactionrecorder.objects.TransactionConstants;
+import org.onap.ccsdk.sli.core.dblib.DbLibService;
+
+import javax.sql.rowset.CachedRowSet;
+import java.sql.*;
+import java.time.Instant;
+import java.time.ZoneOffset;
+import java.time.format.DateTimeFormatter;
+import java.util.ArrayList;
+import java.util.HashMap;
+import java.util.Map;
+import java.util.UUID;
+
+import static org.mockito.Matchers.*;
+
+/**
+ * Test class for TransactionRecorder
+ */
+public class TransactionRecorderImplTest {
+
+    private String dbUrl = "jdbc:h2:mem:test;MODE=MYSQL;DB_CLOSE_DELAY=-1";
+    private String username = "sa";
+    private String password = "sa";
+    private String driver = "org.h2.Driver";
+
+    private TransactionRecorderImpl transactionRecorderImpl;
+    private DbLibService dbLibService;
+
+    private DBConnectionPool dbConnectionPool;
+
+
+    /**
+     * Ideally JUnit should grab the SQL to create the transaction table from the same source used in deployments;
+     * however, at the time of writing this that was not possible.  Should it become possible in the future please
+     * update this JUnit test to use the deployment source.
+     * <p>
+     * Please ensure this table create script is identical to the source script used in a deployment.
+     */
+    private String TRANSACTION_CREATE_TABLE = "CREATE TABLE TRANSACTIONS (" +
+            "  TRANSACTION_ID VARCHAR(75) NOT NULL PRIMARY KEY," +
+            "  ORIGIN_TIMESTAMP DATETIME(3) NOT NULL," +
+            "  REQUEST_ID VARCHAR(256) NOT NULL," +
+            "  SUBREQUEST_ID VARCHAR(256) DEFAULT NULL," +
+            "  ORIGINATOR_ID VARCHAR(256) DEFAULT NULL," +
+            "  START_TIME DATETIME(3) NOT NULL," +
+            "  END_TIME DATETIME(3) DEFAULT NULL," +
+            "  TARGET_ID VARCHAR(256) NOT NULL," +
+            "  TARGET_TYPE VARCHAR(256) DEFAULT NULL," +
+            "  OPERATION VARCHAR(256) NOT NULL," +
+            "  RESULT_CODE INT(11) DEFAULT NULL," +
+            "  DESCRIPTION TEXT," +
+            "  STATE VARCHAR(50) NOT NULL," +
+            "  SERVICE_INSTANCE_ID VARCHAR(256) DEFAULT NULL," +
+            "  VNFC_NAME VARCHAR(256) DEFAULT NULL," +
+            "  VSERVER_ID VARCHAR(256) DEFAULT NULL," +
+            "  VF_MODULE_ID VARCHAR(256) DEFAULT NULL," +
+            "  MODE VARCHAR(50) NOT NULL," +
+            ")";
+    private String TRANSACTION_DROP_TABLE = "DROP TABLE IF EXISTS TRANSACTIONS";
+
+    @Before
+    public void setUp() throws Exception {
+        transactionRecorderImpl = new TransactionRecorderImpl();
+        transactionRecorderImpl.setAppcInstanceId("123");
+        dbLibService = Mockito.mock(DbLibService.class);
+        transactionRecorderImpl.setDbLibService(dbLibService);
+        dbConnectionPool = new DBConnectionPool(dbUrl, username, password, driver);
+        executeUpdate(TRANSACTION_CREATE_TABLE);
+
+    }
+
+
+    @After
+    public void shutdown() {
+        if (dbConnectionPool != null) {
+            executeUpdate(TRANSACTION_DROP_TABLE);
+            dbConnectionPool.shutdown();
+        }
+    }
+
+    private void executeUpdate(String updateSQL) {
+        Connection connection = null;
+        Statement stmt = null;
+        try {
+            connection = dbConnectionPool.getConnection();
+            stmt = connection.createStatement();
+            stmt.executeUpdate(updateSQL);
+        } catch (SQLException e) {
+            throw new RuntimeException(e);
+        } finally {
+            DBHelper.close(null, stmt, connection);
+        }
+    }
+
+    /**
+     * Verify the transactionRecorderImpl.sore() store the TransactionRecord correctly in the database.
+     */
+    @Test
+    public void testStore() throws Exception {
+
+        TransactionRecord input = prepareTransactionsInput();
+        Mockito.when(dbLibService.writeData(anyString(), anyObject(), anyString())).thenAnswer(invocation ->
+                testStoreInMemory(invocation.getArguments()));
+        transactionRecorderImpl.store(input);
+
+    }
+
+    @Test
+    public void testGetInProgressRequests() throws SQLException, APPCException {
+        TransactionRecord record1 = prepareTransactionsInput();
+        insertRecord(record1);
+        TransactionRecord input = prepareTransactionsInput();
+        input.setStartTime(Instant.now());
+        Mockito.when(dbLibService.getData(anyString(), anyObject(), anyString())).thenAnswer(invocation ->
+                inMemoryExecutionWithResultSet(invocation.getArguments()));
+        Assert.assertEquals(1, transactionRecorderImpl.getInProgressRequests(input).size());
+
+    }
+
+    @Test
+    public void testIsTransactionDuplicate() throws SQLException, APPCException {
+        TransactionRecord input = prepareTransactionsInput();
+        Mockito.when(dbLibService.getData(anyString(), anyObject(), anyString())).thenAnswer(invocation ->
+                inMemoryExecutionWithResultSet(invocation.getArguments()));
+        Assert.assertFalse(transactionRecorderImpl.isTransactionDuplicate(input));
+
+    }
+
+    @Test
+    public void testGetInProgressRequestsCount() throws SQLException, APPCException {
+        TransactionRecord input = prepareTransactionsInput();
+        Mockito.when(dbLibService.getData(anyString(), anyObject(), anyString())).thenAnswer(invocation ->
+                inMemoryExecutionWithResultSet(invocation.getArguments()));
+        Assert.assertEquals(0, transactionRecorderImpl.getInProgressRequestsCount().intValue());
+    }
+
+    @Test
+    public void testUpdate() throws APPCException, SQLException {
+        TransactionRecord input = prepareTransactionsInput();
+        insertRecord(input);
+        Map<TransactionConstants.TRANSACTION_ATTRIBUTES, String> updateColumns = new HashMap<>();
+        updateColumns.put(TransactionConstants.TRANSACTION_ATTRIBUTES.TARGET_TYPE, "Firewall");
+        Mockito.when(dbLibService.writeData(anyString(), anyObject(), anyString())).thenAnswer(invocation ->
+                testUpdateInMemory(invocation.getArguments()));
+        transactionRecorderImpl.update(input.getTransactionId(), updateColumns);
+    }
+
+    @Test
+    public void testMarkTransactionsAborted() throws SQLException {
+        TransactionRecord input = prepareTransactionsInput();
+        insertRecord(input);
+        Mockito.when(dbLibService.writeData(anyString(), anyObject(), anyString())).thenAnswer(invocation ->
+                testMarkAbortedInMemory(invocation.getArguments()));
+        transactionRecorderImpl.markTransactionsAborted("123~");
+    }
+
+    private ResultSet inMemoryExecutionWithResultSet(Object[] obj) throws Exception {
+        String query = (String) obj[0];
+        ArrayList<String> args = (ArrayList<String>) obj[1];
+        Connection con = dbConnectionPool.getConnection();
+        PreparedStatement ps = con.prepareStatement(query);
+        for (int i = 1; i <= args.size(); i++) {
+            ps.setString(i, args.get(i - 1));
+        }
+        CachedRowSet rowSet = new CachedRowSetImpl();
+        rowSet.populate(ps.executeQuery());
+        return rowSet;
+    }
+
+    private boolean testMarkAbortedInMemory(Object[] obj) throws Exception {
+        String query = (String) obj[0];
+        ArrayList<String> args = (ArrayList<String>) obj[1];
+        Connection con = dbConnectionPool.getConnection();
+        PreparedStatement ps = con.prepareStatement(query);
+        for (int i = 1; i <= args.size(); i++) {
+            ps.setString(i, args.get(i - 1));
+        }
+        ps.execute();
+        return isTransactionAborted();
+    }
+
+    private boolean isTransactionAborted() throws Exception {
+        String query = "SELECT COUNT(*) FROM  TRANSACTIONS WHERE STATE = ?";
+        Connection con = dbConnectionPool.getConnection();
+        PreparedStatement ps = con.prepareStatement(query);
+        ps.setString(1, RequestStatus.ABORTED.toString());
+        ResultSet rs = ps.executeQuery();
+        while (rs.next()) {
+            int value = rs.getInt(1);
+            if (value == 1) {
+                System.out.println("Non terminal Transactions are aborted");
+                return true;
+            }
+        }
+        throw new Exception("Transactions are not aborted");
+    }
+
+    private boolean testUpdateInMemory(Object[] obj) throws Exception {
+        String query = (String) obj[0];
+        ArrayList<String> args = (ArrayList<String>) obj[1];
+        Connection con = dbConnectionPool.getConnection();
+        PreparedStatement ps = con.prepareStatement(query);
+        for (int i = 1; i <= args.size(); i++) {
+            ps.setString(i, args.get(i - 1));
+        }
+        ps.execute();
+        String updatedValue = checkIfValueIsUpdated(args.get(1));
+        System.out.println("updated Value is " + updatedValue);
+        if (updatedValue.equals("Firewall")) {
+            return true;
+        }
+        throw new Exception("Not Updated");
+    }
+
+    private boolean testStoreInMemory(Object[] obj) throws Exception {
+        String query = (String) obj[0];
+        ArrayList<String> args = (ArrayList<String>) obj[1];
+        Connection con = dbConnectionPool.getConnection();
+        PreparedStatement ps = con.prepareStatement(query);
+        for (int i = 1; i <= args.size(); i++) {
+            ps.setString(i, args.get(i - 1));
+        }
+        ps.execute();
+        if (checkIfRowIsPresent(args.get(0))) {
+            return true;
+        }
+        throw new Exception("Failed to update");
+    }
+
+    private TransactionRecord prepareTransactionsInput() {
+        TransactionRecord input = new TransactionRecord();
+        input.setTransactionId(UUID.randomUUID().toString());
+        input.setOriginTimestamp(Instant.parse("2017-09-11T00:00:01.00Z"));
+        input.setRequestId("REQUEST_ID");
+        input.setSubRequestId("SUB_REQUEST_ID");
+        input.setOriginatorId("ORIGINATOR_ID");
+        input.setStartTime(Instant.parse("2017-09-11T00:00:02.00Z"));
+        input.setTargetId("TARGET_ID");
+        input.setTargetType("TARGET_TYPE");
+        input.setServiceInstanceId("SERVICE_INSTANCE_ID");
+        input.setOperation(VNFOperation.ActionStatus);
+        input.setResultCode(200);
+        input.setRequestState(RequestStatus.ACCEPTED);
+        input.setDescription("DESCRIPTION");
+        input.setMode(Flags.Mode.EXCLUSIVE);
+        return input;
+    }
+
+    private void insertRecord(TransactionRecord input) throws SQLException {
+        final String STORE_DATE_QUERY = TransactionConstants.INSERT_INTO + TransactionConstants.TRANSACTIONS +
+                " values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
+        Connection con = dbConnectionPool.getConnection();
+        PreparedStatement ps = con.prepareStatement(STORE_DATE_QUERY);
+        ArrayList<String> args = prepareArguments(input);
+        args.remove(0);
+        args.add(0, "123~" + input.getTransactionId());
+        for (int i = 1; i <= 18; i++) {
+            ps.setString(i, args.get(i - 1));
+        }
+        ps.execute();
+        if (checkIfRowIsPresent(args.get(0))) {
+            System.out.println("RECORD INSERTED " + args.get(0));
+        }
+
+    }
+
+    private ArrayList<String> prepareArguments(TransactionRecord input) {
+        ArrayList<String> arguments = new ArrayList<>();
+        arguments.add(input.getTransactionId());
+        arguments.add(dateToStringConverterMillis(input.getOriginTimestamp()));
+        arguments.add(input.getRequestId());
+        arguments.add(input.getSubRequestId());
+        arguments.add(input.getOriginatorId());
+        arguments.add(dateToStringConverterMillis(input.getStartTime()));
+        arguments.add(dateToStringConverterMillis(input.getEndTime()));
+        arguments.add(input.getTargetId());
+        arguments.add(input.getTargetType());
+        arguments.add(input.getOperation().name());
+        arguments.add(String.valueOf(input.getResultCode()));
+        arguments.add(input.getDescription());
+        arguments.add(input.getRequestState());
+        arguments.add(input.getServiceInstanceId());
+        arguments.add(input.getVnfcName());
+        arguments.add(input.getVserverId());
+        arguments.add(input.getVfModuleId());
+        arguments.add(input.getMode());
+
+        return arguments;
+    }
+
+    private static String dateToStringConverterMillis(Instant date) {
+        if (date == null) {
+            return null;
+        }
+        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS").withZone(ZoneOffset.UTC);
+        return formatter.format(date);
+    }
+
+    private boolean checkIfRowIsPresent(String key) {
+        Connection con = null;
+        ResultSet rs = null;
+        PreparedStatement ps = null;
+        try {
+            con = dbConnectionPool.getConnection();
+            ps = con.prepareStatement("SELECT COUNT(*) FROM  TRANSACTIONS WHERE TRANSACTION_ID = ?");
+            ps.setString(1, key);
+            rs = ps.executeQuery();
+            while (rs.next()) {
+                int value = rs.getInt(1);
+                System.out.println("KEY checked is " + key + " COUNT RETURNED IS " + value);
+                if (value == 1) {
+                    return true;
+                }
+            }
+        } catch (SQLException e) {
+            e.printStackTrace();
+        } finally {
+            DBHelper.close(rs, ps, con);
+        }
+        return false;
+    }
+
+    private String checkIfValueIsUpdated(String key) throws Exception {
+        Connection con = dbConnectionPool.getConnection();
+        PreparedStatement ps = con.prepareStatement("SELECT TARGET_TYPE FROM  TRANSACTIONS WHERE TRANSACTION_ID = ?");
+        ps.setString(1, key);
+        ResultSet rs = ps.executeQuery();
+        while (rs.next()) {
+            String value = rs.getString("TARGET_TYPE");
+            return value;
+        }
+        throw new Exception("Value not found");
+    }
+
+
+    /**
+     * Verify the transactionRecorderImpl. getRecords () can be fetch with each of the parameter combinations
+     * @throws Exception
+     *//*
+    @Test
+    public void test_api_getRecords() throws Exception {
+
+
+        final int requestId = 0;
+        final int subrequestId = 1;
+        final int originatorId = 2;
+        final int vnfId = 3;
+        final int requestStatus = 4;
+
+
+        String[][] trCreateMatrix = {
+            {"request1", "subrequestId1", "originatorId1", "vnfId1", RequestStatus.UNKNOWN.name()},
+            {"request1", "subrequestId2", "originatorId1", "vnfId1", RequestStatus.RECEIVED.name()},
+            {"request2", "subrequestId1", "originatorId1", "vnfId1", RequestStatus.ACCEPTED.name()},
+            {"request2", "subrequestId2", "originatorId1", "vnfId1", RequestStatus.REJECTED.name()},
+            {"request1", "subrequestId1", "originatorId1", "vnfId2", RequestStatus.SUCCESSFUL.name()},
+            {"request1", "subrequestId2", "originatorId1", "vnfId2", RequestStatus.FAILED.name()},
+            {"request2", "subrequestId1", "originatorId1", "vnfId2", RequestStatus.TIMEOUT.name()},
+            {"request2", "subrequestId2", "originatorId1", "vnfId2", RequestStatus.ABORTED.name()},
+            {"request1", "subrequestId1", "originatorId2", "vnfId1", RequestStatus.UNKNOWN.name()},
+            {"request1", "subrequestId2", "originatorId2", "vnfId1", RequestStatus.RECEIVED.name()},
+            {"request2", "subrequestId1", "originatorId2", "vnfId1", RequestStatus.ACCEPTED.name()},
+            {"request2", "subrequestId2", "originatorId2", "vnfId1", RequestStatus.REJECTED.name()},
+            {"request1", "subrequestId1", "originatorId2", "vnfId2", RequestStatus.SUCCESSFUL.name()},
+            {"request1", "subrequestId2", "originatorId2", "vnfId2", RequestStatus.FAILED.name()},
+            {"request2", "subrequestId1", "originatorId2", "vnfId2", RequestStatus.TIMEOUT.name()},
+            {"request2", "subrequestId2", "originatorId2", "vnfId2", RequestStatus.ABORTED.name()},
+        };
+
+
+        TransactionRecord tr = new TransactionRecord();
+        tr.setTimeStamp(Instant.parse("2017-09-11T00:00:01.00Z"));
+        tr.setStartTime(Instant.parse("2017-09-11T00:00:02.00Z"));
+        tr.setEndTime(Instant.parse("2017-09-11T00:00:03.00Z"));
+        tr.setTargetType("TARGET_TYPE");
+        tr.setSubComponent("SUB_COMPONENT");
+        tr.setOperation(VNFOperation.ActionStatus);
+        tr.setResultCode("RESULT_CODE");
+        tr.setDescription("DESCRIPTION");
+
+        for (int row = 0; row < trCreateMatrix.length; row++) {
+            tr.setRequestID(trCreateMatrix[row][requestId]);
+            tr.setSubRequestID(trCreateMatrix[row][subrequestId]);
+            tr.setOriginatorId(trCreateMatrix[row][originatorId]);
+            tr.setTargetID(trCreateMatrix[row][vnfId]);
+            tr.setRequestStatus(RequestStatus.valueOf(trCreateMatrix[row][requestStatus]));
+            transactionRecorderImpl.store(tr);
+        }
+
+
+        String[][] trSearchMatrix = {
+            {"request1", null, null, "vnfId1"},
+            {"request2", "subrequestId1", null, "vnfId1"},
+            {"request1", null, "originatorId1", "vnfId1"},
+            {"request2", "subrequestId2", "originatorId1", "vnfId1"},
+        };
+
+
+        for (int i = 0; i < trSearchMatrix.length; i++) {
+            final int row = i;
+            List<RequestStatus> actualList = transactionRecorderImpl
+                .getRecords(trSearchMatrix[row][requestId], trSearchMatrix[row][subrequestId],
+                    trSearchMatrix[row][originatorId], trSearchMatrix[row][vnfId])
+                .stream()
+                .sorted()
+                .collect(Collectors.toList());
+
+            List<RequestStatus> expectedList = Arrays.stream(trCreateMatrix)
+                .filter(entry -> entry[requestId].equals(trSearchMatrix[row][requestId]))
+                .filter(entry -> trSearchMatrix[row][subrequestId] == null || entry[subrequestId].equals
+                    (trSearchMatrix[row][subrequestId]))
+                .filter(entry -> trSearchMatrix[row][originatorId] == null || entry[originatorId].equals
+                    (trSearchMatrix[row][originatorId]))
+                .filter(entry -> entry[vnfId].equals(trSearchMatrix[row][vnfId]))
+                .map(entry -> RequestStatus.valueOf(entry[requestStatus]))
+                .sorted()
+                .collect(Collectors.toList());
+            System.out.println(expectedList);
+            System.out.println(actualList);
+            Assert.assertEquals("Unexpected results: ", expectedList, actualList);
+
+        }
+
+
+    }*/
+}