Update appc-dispatcher for >1 ansible servers

Change Transaction record update logic to handle null instance entry.

Issue-ID: APPC-1516

Change-Id: Ia87e08fbb61d4d0be38802e3e81b45773b1d9498
Signed-off-by: Dunietz, Irwin <irwin.dunietz@att.com>

6 files changed, 314 insertions, 82 deletions

diff --git a/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/pom.xml b/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/pom.xml
index 5d1861d06..60352e427 100644
--- a/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/pom.xml
+++ b/appc-dispatcher/appc-dispatcher-common/appc-data-access-lib/pom.xml
@@ -10,17 +10,17 @@
   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.
-  
+
   ============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">
     <modelVersion>4.0.0</modelVersion>
@@ -68,13 +68,22 @@
             <version>${apache.common.dbcp2.version}</version>
         </dependency>
         <dependency>
+            <groupId>org.mockito</groupId>
+            <artifactId>mockito-core</artifactId>
+            <scope>test</scope>
+        </dependency>
+        <dependency>
             <groupId>org.slf4j</groupId>
             <artifactId>slf4j-api</artifactId>
         </dependency>
-	<dependency>
-		<groupId>org.mariadb.jdbc</groupId>
-		<artifactId>mariadb-java-client</artifactId>
-	</dependency>
+        <dependency>
+            <groupId>com.att.eelf</groupId>
+            <artifactId>eelf-core</artifactId>
+        </dependency>
+        <dependency>
+            <groupId>org.mariadb.jdbc</groupId>
+            <artifactId>mariadb-java-client</artifactId>
+        </dependency>
         <dependency>
             <groupId>org.onap.appc</groupId>
             <artifactId>appc-test-dependencies</artifactId>
diff --git a/appc-dispatcher/appc-dispatcher-common/execution-queue-management-lib/pom.xml b/appc-dispatcher/appc-dispatcher-common/execution-queue-management-lib/pom.xml
index 93da4fe79..9b96a533a 100644
--- a/appc-dispatcher/appc-dispatcher-common/execution-queue-management-lib/pom.xml
+++ b/appc-dispatcher/appc-dispatcher-common/execution-queue-management-lib/pom.xml
@@ -50,6 +50,11 @@
             <groupId>com.att.eelf</groupId>
             <artifactId>eelf-core</artifactId>
         </dependency>
+        <dependency>
+            <groupId>org.mockito</groupId>
+            <artifactId>mockito-core</artifactId>
+            <scope>test</scope>
+        </dependency>
     </dependencies>
 
     <build>
diff --git a/appc-dispatcher/appc-dispatcher-common/transaction-recorder/pom.xml b/appc-dispatcher/appc-dispatcher-common/transaction-recorder/pom.xml
index ca1bfc678..6747c58f0 100644
--- a/appc-dispatcher/appc-dispatcher-common/transaction-recorder/pom.xml
+++ b/appc-dispatcher/appc-dispatcher-common/transaction-recorder/pom.xml
@@ -61,6 +61,11 @@
             <scope>test</scope>
             <type>pom</type>
         </dependency>
+        <dependency>
+            <groupId>org.mockito</groupId>
+            <artifactId>mockito-core</artifactId>
+            <scope>test</scope>
+        </dependency>
 
     </dependencies>
 
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 17e0e2575..40bd0a71d 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
@@ -2,7 +2,7 @@
  * ============LICENSE_START=======================================================
  * ONAP : APPC
  * ================================================================================
- * Copyright (C) 2017-2018 AT&T Intellectual Property. All rights reserved.
+ * Copyright (C) 2017-2019 AT&T Intellectual Property. All rights reserved.
  * ================================================================================
  * Copyright (C) 2017 Amdocs
  * =============================================================================
@@ -45,10 +45,12 @@ public interface TransactionRecorder {
     /**
      * 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 requestId
      * @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;
+    void update(String key, String requestId, 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
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 5c5d8fc24..12a3457d0 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
@@ -2,7 +2,7 @@
  * ============LICENSE_START=======================================================
  * ONAP : APPC
  * ================================================================================
- * Copyright (C) 2017-2018 AT&T Intellectual Property. All rights reserved.
+ * Copyright (C) 2017-2019 AT&T Intellectual Property. All rights reserved.
  * ================================================================================
  * Copyright (C) 2017 Amdocs
  * ================================================================================
@@ -109,14 +109,29 @@ public class TransactionRecorderImpl implements TransactionRecorder {
     }
 
     @Override
-    public void update(String key, Map<TransactionConstants.TRANSACTION_ATTRIBUTES, String> updateColumns) throws
-        APPCException {
+    public void update(String key,
+                       String requestId,
+                       Map<TransactionConstants.TRANSACTION_ATTRIBUTES, String> updateColumns) throws APPCException {
+        logger.debug("Inside update in TransactionRecorderImpl");
+
+        if (appcInstanceId != null && checkIfNullInstanceEntryExist(key, requestId)) {
+            updateNullInstanceEntry(key, requestId, updateColumns);
+        } else {
+            updateTransactionEntry(key, updateColumns);
+        }
+        logger.trace("End of Update Transaction table.");
+    }
+
+    private void updateTransactionEntry(String key, Map<TRANSACTION_ATTRIBUTES, String> updateColumns)
+        throws APPCException {
         ArrayList<String> values = new ArrayList<>();
+        logger.debug("Inside updateTransactionEntry");
 
         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());
+            logger.debug("Value for " + entry.getKey().getColumnName() + " in Transaction table: " + entry.getValue());
         }
         queryBuilder.deleteCharAt(queryBuilder.lastIndexOf(","));
         queryBuilder.append(WHERE + TRANSACTION_ID.getColumnName() + " = ?");
@@ -124,9 +139,16 @@ public class TransactionRecorderImpl implements TransactionRecorder {
 
         String query = queryBuilder.toString();
         try {
-            dbLibService.writeData(query, values, SCHEMA);
+            if (logger.isDebugEnabled()) {
+                logger.debug("Before updating Transaction table the Query is: " + query);
+                for (String value : values) {
+                    logger.debug("Value for Transaction table: " + value);
+                }
+            }
+            Boolean result = dbLibService.writeData(query, values, SCHEMA);
+            logger.debug("Transactions table data update, writeData() result: " + result);
         } catch (SQLException e) {
-            logger.error("Error in updating records " + e);
+            logger.error("Error in updating records in updateTransactionEntry " + e);
             throw new APPCException(ERROR_ACCESSING_DATABASE, e);
         }
         if (logger.isTraceEnabled()) {
@@ -135,6 +157,70 @@ public class TransactionRecorderImpl implements TransactionRecorder {
 
     }
 
+    private void updateNullInstanceEntry(String key,
+                                         String requestId,
+                                         Map<TRANSACTION_ATTRIBUTES, String> updateColumns) throws APPCException {
+        logger.debug("Inside updateNullInstanceEntry");
+
+        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(TRANSACTION_ID.getColumnName() + " = ?");
+        queryBuilder.append(WHERE + TRANSACTION_ID.getColumnName() + " = ? AND "
+                            + REQUEST_ID.getColumnName() + " = ? ");
+        values.add(appcInstanceId + "~" + key);
+        values.add(null + "~" + key);
+        values.add(requestId);
+        String query = queryBuilder.toString();
+        try {
+            if (logger.isDebugEnabled()) {
+                logger.debug("Before updating Transaction table the Query is: " + query);
+                for (String value : values) {
+                        logger.debug("Value for Transaction table: " + value);
+                }
+            }
+            Boolean result = dbLibService.writeData(query, values, SCHEMA);
+            logger.debug("Transactions table data update, writeData() result: " + result);
+
+        } catch (SQLException e) {
+            logger.error("Error in updating records in updateNullInstanceEntry " + e);
+            throw new APPCException(ERROR_ACCESSING_DATABASE, e);
+        }
+
+    }
+
+    private boolean checkIfNullInstanceEntryExist(String key, String requestId) throws APPCException {
+        logger.debug("Entered checkIfNullInstanceEntryExist");
+        String nullInstanceCheckQuery = new String("SELECT COUNT(*) as ROWS  FROM " +
+            TransactionConstants.TRANSACTIONS + WHERE +
+            TRANSACTION_ID.getColumnName() + " = ? AND " +
+            REQUEST_ID.getColumnName() + " = ? ");
+
+        ArrayList<String> nullInstanceCheckParams = new ArrayList<>();
+        nullInstanceCheckParams.add(null + "~" + key);
+        nullInstanceCheckParams.add(requestId);
+
+        try{
+            CachedRowSet rowSet = dbLibService.getData(nullInstanceCheckQuery, nullInstanceCheckParams, SCHEMA);
+            int noRows = 0;
+            if (rowSet != null && rowSet.first()) {
+                noRows = rowSet.getInt("ROWS");
+                logger.info("No of Rows in Transactions Table with TRANSACTION_ID: " +
+                            null + "~" + key + " and REQUEST_ID " + requestId + " is: " + noRows);
+            }
+            if(noRows > 0)
+                return true;
+        } catch (SQLException e) {
+            logger.error("Error in checkIfNullInstanceEntryExist in the transaction table", e);
+            throw new APPCException(ERROR_ACCESSING_DATABASE, e);
+        }
+        return false;
+    }
+
     @Override
     public void markTransactionsAborted(String appcInstanceId) {
         if (logger.isTraceEnabled()) {
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
index 8d30ef302..36cf26bb7 100644
--- 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
@@ -2,7 +2,7 @@
  * ============LICENSE_START=======================================================
  * ONAP : APPC
  * ================================================================================
- * Copyright (C) 2017-2018 AT&T Intellectual Property. All rights reserved.
+ * Copyright (C) 2017-2019 AT&T Intellectual Property. All rights reserved.
  * ================================================================================
  * Copyright (C) 2017 Amdocs
  * ================================================================================
@@ -145,7 +145,7 @@ public class TransactionRecorderImplTest {
     }
 
     /**
-     * Verify the transactionRecorderImpl.sore() store the TransactionRecord correctly in the database.
+     * Verify the transactionRecorderImpl.store() store the TransactionRecord correctly in the database.
      */
     @Test
     public void testStore() throws Exception {
@@ -274,9 +274,13 @@ public class TransactionRecorderImplTest {
         insertRecord(input);
         Map<TransactionConstants.TRANSACTION_ATTRIBUTES, String> updateColumns = new HashMap<>();
         updateColumns.put(TransactionConstants.TRANSACTION_ATTRIBUTES.TARGET_TYPE, "Firewall");
+        Mockito.when(dbLibService.getData(anyString(), anyObject(), anyString())).thenAnswer(invocation ->
+                returnResult(invocation.getArguments()));
         Mockito.when(dbLibService.writeData(anyString(), anyObject(), anyString())).thenAnswer(invocation ->
                 testUpdateInMemory(invocation.getArguments()));
-        transactionRecorderImpl.update(input.getTransactionId(), updateColumns);
+        transactionRecorderImpl.update(input.getTransactionId(), input.getRequestId(), updateColumns);
+        Mockito.verify(dbLibService).getData(anyString(), anyObject(), anyString());
+        Mockito.verify(dbLibService).writeData(anyString(), anyObject(), anyString());
     }
 
     @Test
@@ -289,7 +293,97 @@ public class TransactionRecorderImplTest {
         expectedEx.expect(APPCException.class);
         expectedEx.expectMessage(TransactionConstants.ERROR_ACCESSING_DATABASE);
         expectedEx.expectCause(isA(SQLException.class));
-        transactionRecorderImpl.update(input.getTransactionId(), updateColumns);
+        transactionRecorderImpl.update(input.getTransactionId(), input.getRequestId(), updateColumns);
+    }
+
+    @Test
+    public void testUpdatewithNullInstance() throws APPCException, SQLException {
+        TransactionRecord input = prepareTransactionsInput();
+        Map<TransactionConstants.TRANSACTION_ATTRIBUTES, String> updateColumns = new HashMap<>();
+        updateColumns.put(TransactionConstants.TRANSACTION_ATTRIBUTES.TARGET_TYPE, "Firewall");
+        Mockito.when(dbLibService.getData(anyString(), anyObject(), anyString())).thenAnswer(invocation ->
+                returnPositiveResult(invocation.getArguments()));
+        Mockito.when(dbLibService.writeData(anyString(), anyObject(), anyString())).thenAnswer(invocation ->
+                testUpdateInMemory(invocation.getArguments()));
+
+        transactionRecorderImpl.update(input.getTransactionId(), input.getRequestId(), updateColumns);
+        Mockito.verify(dbLibService).getData(anyString(), anyObject(), anyString());
+        Mockito.verify(dbLibService).writeData(anyString(), anyObject(), anyString());
+    }
+
+    private Object returnPositiveResult(Object[] obj) throws Exception {
+        String query = (String) obj[0];
+        ArrayList<String> args = (ArrayList<String>) obj[1];
+        System.out.println("returnPositiveResult: Query: " + query + "\nArgs: " + args);
+
+        insertNullInstanceData(args.get(0));
+
+        try (
+             Connection con = dbConnectionPool.getConnection();
+             PreparedStatement ps_second = con.prepareStatement(query)
+             ) {
+            for (int i = 1; i <= args.size(); i++) {
+                ps_second.setString(i, args.get(i - 1));
+            }
+            CachedRowSet rowSet = new CachedRowSetImpl();
+            rowSet.populate(ps_second.executeQuery());
+            return rowSet;
+        }
+    }
+
+    private void insertNullInstanceData(String transactionId) throws Exception {
+        final String nullInstanceQuery = (TransactionConstants.INSERT_INTO + TransactionConstants.TRANSACTIONS +
+                                          " values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
+        try (
+             Connection con = dbConnectionPool.getConnection();
+             PreparedStatement ps = con.prepareStatement(nullInstanceQuery)
+             ) {
+            ArrayList<String> input = new ArrayList<String>();
+            input.add(transactionId);
+            input.add(dateToStringConverterMillis(Instant.parse("2017-09-12T00:00:01.00Z")));
+            input.add("REQUEST_ID");
+            input.add("SUB_REQUEST_ID");
+            input.add("ORIGINATOR_ID");
+            input.add(dateToStringConverterMillis(Instant.parse("2018-09-12T00:00:02.00Z")));
+            input.add(dateToStringConverterMillis(Instant.parse("2018-09-12T20:00:02.00Z")));
+            input.add("TARGET_ID");
+            input.add("TARGET_TYPE");
+            input.add("Audit");
+            input.add(String.valueOf(200));
+            input.add("SERVICE_INSTANCE_ID");
+            input.add("ACCEPTED");
+            input.add("DESCRIPTION");
+            input.add("test");
+            input.add("test");
+            input.add("test");
+            input.add("EXCLUSIVE");
+
+            for (int i = 1; i <= input.size(); i++) {
+                ps.setString(i, input.get(i-1));
+            }
+            ps.execute();
+
+            if (checkIfRowIsPresent(input.get(0))) {
+                System.out.println("RECORD INSERTED " + input.get(0));
+            }
+        }
+    }
+
+    private ResultSet returnResult(Object[] obj) throws Exception {
+        String query = (String) obj[0];
+        ArrayList<String> args = (ArrayList<String>) obj[1];
+        System.out.println("Query: " + query + "\nArgs: " + args);
+        try (
+             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;
+        }
     }
 
     @Test
@@ -334,74 +428,94 @@ public class TransactionRecorderImplTest {
     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));
+        // System.out.println("Query: " + query + "\nArgs: " + args);
+        try (
+             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;
         }
-        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));
+        try (
+             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();
         }
-        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;
+        try (
+             Connection con = dbConnectionPool.getConnection();
+             PreparedStatement ps = con.prepareStatement(query)
+             ) {
+            ps.setString(1, RequestStatus.ABORTED.toString());
+            try (
+                 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");
             }
         }
-        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;
+        try (
+             Connection con = dbConnectionPool.getConnection();
+             PreparedStatement ps = con.prepareStatement(query)
+             ) {
+            for (int i = 1; i <= args.size(); i++) {
+                System.out.println("Value at " + i + ": " + args.get(i - 1));
+                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");
         }
-        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;
+        try (
+             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");
         }
-        throw new Exception("Failed to update");
     }
 
     private TransactionRecord prepareTransactionsInput() {
@@ -426,19 +540,21 @@ public class TransactionRecorderImplTest {
     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));
+        try (
+             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) {
@@ -498,15 +614,24 @@ public class TransactionRecorderImplTest {
     }
 
     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;
+        try (
+             Connection con = dbConnectionPool.getConnection();
+             PreparedStatement ps = con.prepareStatement("SELECT TARGET_TYPE,TRANSACTION_ID FROM TRANSACTIONS" +
+                                                         " WHERE TRANSACTION_ID = ?")
+             ) {
+            ps.setString(1, key);
+            try (
+                 ResultSet rs = ps.executeQuery()
+                 ) {
+               while (rs.next()) {
+                   String value = rs.getString("TARGET_TYPE");
+                   String transactionId = rs.getString("TRANSACTION_ID");
+                   System.out.println("Updated data: TRANSACTION_ID: " + transactionId + " TARGET_TYPE: " + value);
+                   return value;
+               }
+               throw new Exception("Value not found");
+            }
         }
-        throw new Exception("Value not found");
     }