Prepared statements for DG services

Convert sql queries to prepared statements in other
parts of appc.

Change-Id: If29225394de2ab286e4f2f7bdd17f6af6b286576
Signed-off-by: Patrick Brady <patrick.brady@att.com>
Issue-ID: OJSI-25

6 files changed, 584 insertions, 105 deletions

diff --git a/appc-config/appc-data-services/provider/src/main/java/org/onap/appc/data/services/db/CtxParameterizedResolver.java b/appc-config/appc-data-services/provider/src/main/java/org/onap/appc/data/services/db/CtxParameterizedResolver.java
new file mode 100644
index 000000000..bc4728a6b
--- /dev/null
+++ b/appc-config/appc-data-services/provider/src/main/java/org/onap/appc/data/services/db/CtxParameterizedResolver.java
@@ -0,0 +1,230 @@
+/*-
+ * ============LICENSE_START=======================================================
+ * ONAP : APPC
+ * ================================================================================
+ * Copyright (C) 2019 AT&T Intellectual Property. All rights reserved.
+ * ================================================================================
+ * 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=========================================================
+ */
+
+
+package org.onap.appc.data.services.db;
+
+import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+import java.util.ArrayList;
+
+import javax.sql.rowset.CachedRowSet;
+
+import org.apache.commons.lang3.StringUtils;
+import org.onap.ccsdk.sli.core.dblib.DbLibService;
+import org.onap.ccsdk.sli.core.sli.SvcLogicContext;
+
+import com.att.eelf.configuration.EELFLogger;
+import com.att.eelf.configuration.EELFManager;
+
+public class CtxParameterizedResolver {
+
+    private static String CRYPT_KEY = "QtfJMKggVk";
+    private static final EELFLogger log = EELFManager.getInstance().getLogger(CtxParameterizedResolver.class);
+    
+    protected static String resolveCtxVars(String key, SvcLogicContext ctx, ArrayList<String> arguments) {
+        if (key == null) {
+            return (null);
+        }
+
+        if (key.startsWith("'") && key.endsWith("'")) {
+            key = key.substring(1, key.length() - 1);
+            log.debug("Stripped outer single quotes - key is now [" + key + "]");
+        }
+
+        String[] keyTerms = key.split("\\s+");
+
+        StringBuffer sqlBuffer = new StringBuffer();
+
+        for (int i = 0; i < keyTerms.length; i++) {
+            sqlBuffer.append(resolveTerm(keyTerms[i], ctx, arguments));
+            sqlBuffer.append(" ");
+        }
+
+        return (sqlBuffer.toString());
+    }
+
+    private static String resolveTerm(String term, SvcLogicContext ctx, ArrayList<String> arguments) {
+        if (term == null) {
+            return (null);
+        }
+
+        log.trace("resolveTerm: term is " + term);
+
+        if (term.startsWith("$") && (ctx != null)) {
+            // Resolve any index variables.
+            term = resolveCtxVariable(term.substring(1), ctx);
+            // Escape single quote
+            if (term != null) {
+                term = term.replaceAll("'", "''");
+            }
+            //valueOf will store null values as a String "null"
+            arguments.add(String.valueOf(term));
+            return "?";
+        } else {
+            return (term);
+        }
+
+    }
+
+    private static String resolveCtxVariable(String ctxVarName, SvcLogicContext ctx) {
+
+        if (ctxVarName.indexOf('[') == -1) {
+            // Ctx variable contains no arrays
+            if ("CRYPT_KEY".equals(ctxVarName)) {
+                // Handle crypt key as special case. If it's set as a context
+                // variable, use it. Otherwise, use
+                // configured crypt key.
+                String cryptKey = ctx.getAttribute(ctxVarName);
+                if ((cryptKey != null) && (cryptKey.length() > 0)) {
+                    return (cryptKey);
+                } else {
+                    return (CRYPT_KEY);
+                }
+            }
+            return (ctx.getAttribute(ctxVarName));
+        }
+
+        // Resolve any array references
+        StringBuffer sbuff = new StringBuffer();
+        String[] ctxVarParts = ctxVarName.split("\\[");
+        sbuff.append(ctxVarParts[0]);
+        for (int i = 1; i < ctxVarParts.length; i++) {
+            if (ctxVarParts[i].startsWith("$")) {
+                int endBracketLoc = ctxVarParts[i].indexOf("]");
+                if (endBracketLoc == -1) {
+                    // Missing end bracket ... give up parsing
+                    log.warn("Variable reference " + ctxVarName + " seems to be missing a ']'");
+                    return (ctx.getAttribute(ctxVarName));
+                }
+
+                String idxVarName = ctxVarParts[i].substring(1, endBracketLoc);
+                String remainder = ctxVarParts[i].substring(endBracketLoc);
+
+                sbuff.append("[");
+                sbuff.append(ctx.getAttribute(idxVarName));
+                sbuff.append(remainder);
+
+            } else {
+                // Index is not a variable reference
+                sbuff.append("[");
+                sbuff.append(ctxVarParts[i]);
+            }
+        }
+
+        return (ctx.getAttribute(sbuff.toString()));
+    }
+        
+        protected static void saveCachedRowSetToCtx(CachedRowSet results, SvcLogicContext ctx, String prefix, DbLibService dblibSvc)
+                throws SQLException {
+            if (ctx != null) {
+                if ((prefix != null) && prefix.endsWith("[]")) {
+                    // Return an array.
+                    String pfx = prefix.substring(0, prefix.length() - 2);
+                    int idx = 0;
+                    do {
+                        ResultSetMetaData rsMeta = results.getMetaData();
+                        int numCols = rsMeta.getColumnCount();
+
+                        for (int i = 0; i < numCols; i++) {
+                            String colValue = null;
+                            String tableName = rsMeta.getTableName(i + 1);
+                            if (rsMeta.getColumnType(i + 1) == java.sql.Types.VARBINARY) {
+                                colValue = decryptColumn(tableName, rsMeta.getColumnName(i + 1), results.getBytes(i + 1),
+                                        dblibSvc);
+                            } else {
+                                colValue = results.getString(i + 1);
+                            }
+                            log.debug("Setting " + pfx + "[" + idx + "]."
+                                    + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-") + " = " + colValue);
+                            ctx.setAttribute(pfx + "[" + idx + "]." + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-"),
+                                    colValue);
+                        }
+                        idx++;
+                    } while (results.next());
+                    log.debug("Setting " + pfx + "_length = " + idx);
+                    ctx.setAttribute(pfx + "_length", "" + idx);
+                } else {
+                    ResultSetMetaData rsMeta = results.getMetaData();
+                    int numCols = rsMeta.getColumnCount();
+
+                    for (int i = 0; i < numCols; i++) {
+                        String colValue = null;
+                        String tableName = rsMeta.getTableName(i + 1);
+                        if ("VARBINARY".equalsIgnoreCase(rsMeta.getColumnTypeName(i + 1))) {
+                            colValue = decryptColumn(tableName, rsMeta.getColumnName(i + 1), results.getBytes(i + 1),
+                                    dblibSvc);
+                        } else {
+                            colValue = results.getString(i + 1);
+                        }
+                        if (prefix != null) {
+                            log.debug("Setting " + prefix + "." + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-") + " = "
+                                    + colValue);
+                            ctx.setAttribute(prefix + "." + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-"), colValue);
+                        } else {
+                            log.debug("Setting " + rsMeta.getColumnLabel(i + 1).replaceAll("_", "-") + " = " + colValue);
+                            ctx.setAttribute(rsMeta.getColumnLabel(i + 1).replaceAll("_", "-"), colValue);
+                        }
+                    }
+                }
+            }
+        }
+        
+        private static String decryptColumn(String tableName, String colName, byte[] colValue, DbLibService dblibSvc) {
+            String strValue = new String(colValue);
+
+            if (StringUtils.isAsciiPrintable(strValue)) {
+
+                // If printable, not encrypted
+                return (strValue);
+            } else {
+                ResultSet results = null;
+                try (Connection conn =  dblibSvc.getConnection();
+                   PreparedStatement stmt = conn.prepareStatement("SELECT CAST(AES_DECRYPT(?, ?) AS CHAR(50)) FROM DUAL")) {
+
+                    stmt.setBytes(1, colValue);
+                    stmt.setString(2, CRYPT_KEY);
+                    results = stmt.executeQuery();
+
+                    if ((results != null) && results.next()) {
+                        strValue = results.getString(1);
+                        log.debug("Decrypted value is " + strValue);
+                    } else {
+                        log.warn("Cannot decrypt " + tableName + "." + colName);
+                    }
+                } catch (Exception e) {
+                    log.error("Caught exception trying to decrypt " + tableName + "." + colName, e);
+                }finally {
+                    if (results != null) {
+                        try {
+                            results.close();
+                        } catch (SQLException se) {
+                            log.error("Caught exception trying to close ResultSet",se);
+                        }
+                    }
+                }
+            }
+            return (strValue);
+        }
+}
diff --git a/appc-config/appc-data-services/provider/src/main/java/org/onap/appc/data/services/db/DGGeneralDBService.java b/appc-config/appc-data-services/provider/src/main/java/org/onap/appc/data/services/db/DGGeneralDBService.java
index 83d47ba30..65c0b3015 100644
--- a/appc-config/appc-data-services/provider/src/main/java/org/onap/appc/data/services/db/DGGeneralDBService.java
+++ b/appc-config/appc-data-services/provider/src/main/java/org/onap/appc/data/services/db/DGGeneralDBService.java
@@ -27,7 +27,7 @@ package org.onap.appc.data.services.db;
 import java.util.Set;
 
 import org.apache.commons.lang3.StringUtils;
-
+import org.onap.ccsdk.sli.core.dblib.DbLibService;
 import org.onap.ccsdk.sli.core.sli.SvcLogicContext;
 import org.onap.ccsdk.sli.core.sli.SvcLogicException;
 import org.onap.ccsdk.sli.core.sli.SvcLogicResource;
@@ -39,7 +39,8 @@ import com.att.eelf.configuration.EELFManager;
 public class DGGeneralDBService {
 
     private static final EELFLogger log = EELFManager.getInstance().getLogger(DGGeneralDBService.class);
-    private SvcLogicResource serviceLogic;
+    //private SvcLogicResource serviceLogic;
+    private DbLibServiceQueries dblib;
     private static DGGeneralDBService dgGeneralDBService = null;
 
     public static DGGeneralDBService initialise() {
@@ -51,31 +52,37 @@ public class DGGeneralDBService {
     }
 
     private DGGeneralDBService() {
-        if (serviceLogic == null) {
-            serviceLogic = new SqlResource();
+        if (dblib == null) {
+            dblib = new DbLibServiceQueries();
         }
     }
 
-    protected DGGeneralDBService(SqlResource svcLogic) {
-        if (serviceLogic == null) {
-            serviceLogic = svcLogic;
+    protected DGGeneralDBService(DbLibService dbLibService) {
+        if (dblib == null) {
+            dblib = new DbLibServiceQueries(dbLibService);
+        }
+    }
+    
+    protected DGGeneralDBService(DbLibServiceQueries dbLibServiceQueries) {
+        if (dblib == null) {
+            dblib = dbLibServiceQueries;
         }
     }
 
     public QueryStatus getDeviceProtocolByVnfType(SvcLogicContext ctx, String prefix) throws SvcLogicException {
         QueryStatus status = null;
-        if (serviceLogic != null && ctx != null) {
+        if (dblib != null && ctx != null) {
             String key = "SELECT * FROM DEVICE_INTERFACE_PROTOCOL WHERE vnf_type = $vnf-type ;";
-            status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx);
+            status = dblib.query(key, prefix, ctx);
         }
         return status;
     }
 
     public QueryStatus getDeviceAuthenticationByVnfType(SvcLogicContext ctx, String prefix) throws SvcLogicException {
         QueryStatus status = null;
-        if (serviceLogic != null && ctx != null) {
+        if (dblib != null && ctx != null) {
             String key = "SELECT * FROM DEVICE_AUTHENTICATION WHERE vnf_type = $vnf-type ;";
-            status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx);
+            status = dblib.query(key, prefix, ctx);
 
         }
         return status;
@@ -83,9 +90,9 @@ public class DGGeneralDBService {
 
     public QueryStatus getConfigFileReferenceByVnfType(SvcLogicContext ctx, String prefix) throws SvcLogicException {
         QueryStatus status = null;
-        if (serviceLogic != null && ctx != null) {
+        if (dblib != null && ctx != null) {
             String key = "SELECT * FROM CONFIG_FILE_REFERENCE WHERE vnf_type = $vnf-type ;";
-            status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx);
+            status = dblib.query(key, prefix, ctx);
         }
         return status;
     }
@@ -93,24 +100,26 @@ public class DGGeneralDBService {
     public QueryStatus getConfigFileReferenceByFileTypeNVnfType(SvcLogicContext ctx, String prefix, String fileType)
             throws SvcLogicException {
         QueryStatus status = null;
-        if (serviceLogic != null && ctx != null) {
-            String key = "SELECT * FROM CONFIG_FILE_REFERENCE  WHERE file_type = '" + fileType
-                    + "' and vnf_type = $vnf-type ;";
-            status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx);
+        if (dblib != null && ctx != null) {
+            ctx.setAttribute("file-type", fileType);
+            String key = "SELECT * FROM CONFIG_FILE_REFERENCE  WHERE file_type = $file-type"
+                    + " and vnf_type = $vnf-type ;";
+            status = dblib.query(key, prefix, ctx);
         }
         return status;
     }
 
     public QueryStatus getTemplate(SvcLogicContext ctx, String prefix, String fileCategory) throws SvcLogicException {
         QueryStatus status = null;
-        if (serviceLogic != null && ctx != null) {
+        if (dblib != null && ctx != null) {
+            ctx.setAttribute("file-category", fileCategory);
             String key = "SELECT artifact_content file_content , asdc_artifacts_id config_file_id "
                     + " FROM ASDC_ARTIFACTS "
                     + " WHERE asdc_artifacts_id = ( SELECT MAX(a.asdc_artifacts_id) configfileid  "
                     + " FROM ASDC_ARTIFACTS a, ASDC_REFERENCE b " + " WHERE a.artifact_name = b.artifact_name "
-                    + " AND file_category =  '" + fileCategory + "'" + " AND action =  $request-action "
+                    + " AND file_category =  $file-category AND action =  $request-action "
                     + " AND vnf_type =  $vnf-type  " + " AND vnfc_type =   $vnfc-type ) ; ";
-            status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx);
+            status = dblib.query(key, prefix, ctx);
         }
         return status;
     }
@@ -118,14 +127,15 @@ public class DGGeneralDBService {
     public QueryStatus getTemplateByVnfTypeNAction(SvcLogicContext ctx, String prefix, String fileCategory)
             throws SvcLogicException {
         QueryStatus status = null;
-        if (serviceLogic != null && ctx != null) {
+        if (dblib != null && ctx != null) {
+            ctx.setAttribute("file-category", fileCategory);
             String key = "SELECT artifact_content file_content , asdc_artifacts_id config_file_id "
                     + " FROM ASDC_ARTIFACTS "
                     + " WHERE asdc_artifacts_id = (SELECT MAX(a.asdc_artifacts_id) configfileid  "
                     + " FROM ASDC_ARTIFACTS a, ASDC_REFERENCE b " + " WHERE a.artifact_name = b.artifact_name "
-                    + " AND file_category =  '" + fileCategory + "'" + " AND action =  $request-action "
+                    + " AND file_category =  $file-category AND action =  $request-action "
                     + " AND vnf_type =  $vnf-type ) ; ";
-            status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx);
+            status = dblib.query(key, prefix, ctx);
 
         }
         return status;
@@ -134,14 +144,15 @@ public class DGGeneralDBService {
     public QueryStatus getTemplateByVnfType(SvcLogicContext ctx, String prefix, String fileCategory)
             throws SvcLogicException {
         QueryStatus status = null;
-        if (serviceLogic != null && ctx != null) {
+        if (dblib != null && ctx != null) {
+            ctx.setAttribute("file-category", fileCategory);
             String key = "SELECT artifact_content file_content , asdc_artifacts_id config_file_id "
                     + " FROM ASDC_ARTIFACTS "
                     + " WHERE asdc_artifacts_id = (SELECT MAX(a.asdc_artifacts_id) configfileid  "
                     + " FROM ASDC_ARTIFACTS a, ASDC_REFERENCE b " + " WHERE a.artifact_name = b.artifact_name "
-                    + " AND file_category =  '" + fileCategory + "'" + " AND vnf_type =  $vnf-type ) ; ";
+                    + " AND file_category =  $file-category AND vnf_type =  $vnf-type ) ; ";
 
-            status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx);
+            status = dblib.query(key, prefix, ctx);
         }
         return status;
     }
@@ -149,13 +160,14 @@ public class DGGeneralDBService {
     public QueryStatus getTemplateByTemplateName(SvcLogicContext ctx, String prefix, String templateName)
             throws SvcLogicException {
         QueryStatus status = null;
-        if (serviceLogic != null && ctx != null) {
+        if (dblib != null && ctx != null) {
+            ctx.setAttribute("template-name", templateName);
             String key = "SELECT artifact_content file_content , asdc_artifacts_id config_file_id "
                     + " FROM ASDC_ARTIFACTS "
                     + " WHERE asdc_artifacts_id = (SELECT MAX(asdc_artifacts_id) configfileid  "
-                    + " FROM ASDC_ARTIFACTS  " + " WHERE artifact_name = '" + templateName + "' ) ; ";
+                    + " FROM ASDC_ARTIFACTS  " + " WHERE artifact_name = $template-name ) ; ";
 
-            status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx);
+            status = dblib.query(key, prefix, ctx);
         }
         return status;
     }
@@ -163,22 +175,22 @@ public class DGGeneralDBService {
     public QueryStatus getConfigureActionDGByVnfTypeNAction(SvcLogicContext ctx, String prefix)
             throws SvcLogicException {
         QueryStatus status = null;
-        if (serviceLogic != null && ctx != null) {
+        if (dblib != null && ctx != null) {
             String key = "SELECT * " + " FROM CONFIGURE_ACTION_DG "
                     + " where vnf_type = $vnf-type and action = $request-action ; ";
 
-            status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx);
+            status = dblib.query(key, prefix, ctx);
         }
         return status;
     }
 
     public QueryStatus getConfigureActionDGByVnfType(SvcLogicContext ctx, String prefix) throws SvcLogicException {
         QueryStatus status = null;
-        if (serviceLogic != null && ctx != null) {
+        if (dblib != null && ctx != null) {
             String key = "SELECT * " + " FROM CONFIGURE_ACTION_DG "
                     + " where vnf_type = $vnf-type and action IS NULL ; ";
 
-            status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx);
+            status = dblib.query(key, prefix, ctx);
         }
         return status;
     }
@@ -186,11 +198,12 @@ public class DGGeneralDBService {
     public QueryStatus getMaxConfigFileId(SvcLogicContext ctx, String prefix, String fileCategory)
             throws SvcLogicException {
         QueryStatus status = null;
-        if (serviceLogic != null && ctx != null) {
-            String key = "SELECT MAX(config_file_id) configfileid " + " FROM CONFIGFILES " + " WHERE file_category = '"
-                    + fileCategory + "'" + " AND vnf_id =  $vnf-id  AND vm_name = $vm-name ; ";
+        if (dblib != null && ctx != null) {
+            ctx.setAttribute("file-category", fileCategory);
+            String key = "SELECT MAX(config_file_id) configfileid " + " FROM CONFIGFILES " + " WHERE file_category = "
+                    + "$file-category AND vnf_id =  $vnf-id  AND vm_name = $vm-name ; ";
 
-            status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx);
+            status = dblib.query(key, prefix, ctx);
         }
         return status;
     }
@@ -199,7 +212,7 @@ public class DGGeneralDBService {
 
         QueryStatus status = null;
 
-        if (serviceLogic != null && ctx != null) {
+        if (dblib != null && ctx != null) {
             String key = "INSERT INTO CONFIGFILES " + " SET data_source        = $data-source , "
                     + " service_instance_id =  $service-instance-id ," + " action              =   $request-action ,"
                     + " vnf_type            =     $vnf-type ," + " vnfc_type           =     $vnfc-type ,"
@@ -207,7 +220,7 @@ public class DGGeneralDBService {
                     + " vm_name            =   $vm-name ," + " file_category         =  $file-category ,"
                     + " file_content        =  $file-content ; ";
 
-            status = serviceLogic.save("SQL", false, false, key, null, prefix, ctx);
+            status = dblib.save(key, ctx);
 
         }
         return status;
@@ -220,17 +233,17 @@ public class DGGeneralDBService {
         QueryStatus status = null;
         String key = null;
 
-        if (serviceLogic != null && ctx != null) {
-
+        if (dblib != null && ctx != null) {
+            ctx.setAttribute("file-id", fileId);
             if ("Y".equals(sdcInd))
 
                 key = "INSERT INTO PREPARE_FILE_RELATIONSHIP " + " SET service_instance_id =  $service-instance-id , "
-                        + "   request_id         = $request-id , " + "  asdc_artifacts_id        =  " + fileId + " ;";
+                        + "   request_id         = $request-id , " + "  asdc_artifacts_id        =  $file-id ;";
             else
                 key = "INSERT INTO PREPARE_FILE_RELATIONSHIP " + " SET service_instance_id =  $service-instance-id , "
-                        + "   request_id         = $request-id , " + "  config_file_id        =  " + fileId + " ;";
+                        + "   request_id         = $request-id , " + "  config_file_id        =  $file-id ;";
 
-            status = serviceLogic.save("SQL", false, false, key, null, prefix, ctx);
+            status = dblib.save(key, ctx);
 
             log.info("DGGeneralDBService.savePrepareRelationship()" + ctx.getAttributeKeySet());
         }
@@ -255,7 +268,7 @@ public class DGGeneralDBService {
 
         QueryStatus status = null;
 
-        if (serviceLogic != null && ctx != null) {
+        if (dblib != null && ctx != null) {
             String key = "INSERT INTO UPLOAD_CONFIG " + " SET request_id = $request-id , "
                     + " action = $request-action , " + " originator_id = $originator-id , " + " vnf_id =  $vnf-id , "
                     + " vnf_name = $vnf-name ,  " + " vm_name =  $vm-name ,  "
@@ -263,7 +276,7 @@ public class DGGeneralDBService {
                     + " vnfc_type           =     $vnfc-type , " + " config_indicator         =  'Current' , "
                     + " content        =  $tmp.escaped.devicerunningconfig ; ";
 
-            status = serviceLogic.save("SQL", false, false, key, null, prefix, ctx);
+            status = dblib.save(key, ctx);
 
             log.info("DGGeneralDBService.saveUploadConfig()" + ctx.getAttributeKeySet());
 
@@ -286,12 +299,12 @@ public class DGGeneralDBService {
 
     public QueryStatus updateUploadConfig(SvcLogicContext ctx, String prefix, int maxId) throws SvcLogicException {
         QueryStatus status = null;
-        if (serviceLogic != null && ctx != null) {
+        if (dblib != null && ctx != null) {
             String key = "UPDATE UPLOAD_CONFIG " + " SET  config_indicator         =  null "
                     + " WHERE upload_config_id != " + maxId + " AND config_indicator         =  'Current' "
                     + " AND vnf_id = $vnf-id " + " AND vnfc_type =  $vnfc-type ; ";
 
-            status = serviceLogic.save("SQL", false, false, key, null, prefix, ctx);
+            status = dblib.save(key, ctx);
 
             log.info("DGGeneralDBService.updateUploadConfig()" + ctx.getAttributeKeySet());
 
@@ -304,15 +317,17 @@ public class DGGeneralDBService {
     public QueryStatus getTemplateByArtifactType(SvcLogicContext ctx, String prefix, String fileCategory, String artifactType)
             throws SvcLogicException {
         QueryStatus status = null;
-        if (serviceLogic != null && ctx != null) {
+        if (dblib != null && ctx != null) {
+            ctx.setAttribute("file-category", fileCategory);
+            ctx.setAttribute("artifact-type", artifactType);
             String key = "SELECT artifact_content file_content , asdc_artifacts_id config_file_id "
                     + " FROM ASDC_ARTIFACTS "
                     + " WHERE asdc_artifacts_id = (SELECT MAX(a.asdc_artifacts_id) configfileid  "
                     + " FROM ASDC_ARTIFACTS a, ASDC_REFERENCE b " + " WHERE a.artifact_name = b.artifact_name "
-                    + " AND file_category =  '" + fileCategory + "'" + " AND action =  $request-action "
-                    + " AND artifactType =  '" + artifactType + "'"    + " AND vnf_type =  $vnf-type ) ; ";
+                    + " AND file_category =  $file-category  AND action =  $request-action "
+                    + " AND artifactType =  $artifact-type  AND vnf_type =  $vnf-type ) ; ";
 
-            status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx);
+            status = dblib.query(key, prefix, ctx);
         }
         return status;
     }
@@ -321,17 +336,19 @@ public class DGGeneralDBService {
     public QueryStatus getConfigFilesByVnfVmNCategory(SvcLogicContext ctx, String prefix, String fileCategory, String vnfId, String vmName)
             throws SvcLogicException {
         QueryStatus status = null;
-        if (serviceLogic != null && ctx != null) {
-
+        if (dblib != null && ctx != null) {
+            ctx.setAttribute("file-category", fileCategory);
+            ctx.setAttribute("vnf-id", vnfId);
+            ctx.setAttribute("vm-name", vmName);
             String key = "SELECT  file_content ,  config_file_id "
                     + " FROM CONFIGFILES "
                     + " WHERE config_file_id = ( SELECT MAX(config_file_id) configfileid " + " FROM CONFIGFILES "
-                    + " WHERE file_category = '"    + fileCategory + "'"
-                    + " AND vnf_id =  '" + vnfId + "'"
-                    + " AND vm_name = '" + vmName + "' ) ; ";
+                    + " WHERE file_category = $file-category"
+                    + " AND vnf_id =  $vnf-id"
+                    + " AND vm_name = $vm-name ) ; ";
 
 
-            status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx);
+            status = dblib.query(key, prefix, ctx);
         }
         return status;
     }
@@ -340,9 +357,9 @@ public class DGGeneralDBService {
     public QueryStatus getDownloadConfigTemplateByVnf(SvcLogicContext ctx, String prefix)
             throws SvcLogicException {
         QueryStatus status = null;
-        if (serviceLogic != null && ctx != null) {
+        if (dblib != null && ctx != null) {
             String key = "SELECT * FROM DOWNLOAD_CONFIG_TEMPLATE  WHERE vnf_type = $vnf-type ; ";
-            status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx);
+            status = dblib.query(key, prefix, ctx);
         }
         return status;
     }
@@ -353,16 +370,14 @@ public class DGGeneralDBService {
 
         QueryStatus status = null;
 
-        if (serviceLogic != null && ctx != null) {
-
+        if (dblib != null && ctx != null) {
 
                 String key = "INSERT INTO CONFIG_TRANSACTION_LOG " + " SET request_id = $request-id , "
                 + " message_type = $log-message-type , "
                 + " message = $log-message ;";
 
 
-                status = serviceLogic.save("SQL", false, false, key, null, prefix, ctx);
-
+                status = dblib.save(key, ctx);
 
 
         }
@@ -374,7 +389,7 @@ public class DGGeneralDBService {
     public QueryStatus getVnfcReferenceByVnfcTypeNAction(SvcLogicContext ctx, String prefix)
             throws SvcLogicException {
         QueryStatus status = null;
-        if (serviceLogic != null && ctx != null) {
+        if (dblib != null && ctx != null) {
 
             String key = "SELECT  * "
                     + " FROM VNFC_REFERENCE "
@@ -384,7 +399,7 @@ public class DGGeneralDBService {
                     + " ORDER BY vm_instance, vnfc_instance ; ";
 
 
-            status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx);
+            status = dblib.query(key, prefix, ctx);
         }
         return status;
     }
@@ -393,7 +408,7 @@ public class DGGeneralDBService {
     public QueryStatus getVnfcReferenceByVnfTypeNAction(SvcLogicContext ctx, String prefix)
             throws SvcLogicException {
         QueryStatus status = null;
-        if (serviceLogic != null && ctx != null) {
+        if (dblib != null && ctx != null) {
 
             String key = "SELECT  * "
                     + " FROM VNFC_REFERENCE "
@@ -401,7 +416,7 @@ public class DGGeneralDBService {
                     + " AND action =  $request-action   "
                     + " ORDER BY vm_instance, vnfc_instance ; ";
 
-            status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx);
+            status = dblib.query(key, prefix, ctx);
         }
         return status;
     }
@@ -410,7 +425,7 @@ public class DGGeneralDBService {
     public QueryStatus getUploadConfigInfo(SvcLogicContext ctx, String prefix)
             throws SvcLogicException {
         QueryStatus status = null;
-        if (serviceLogic != null && ctx != null) {
+        if (dblib != null && ctx != null) {
 
             String key = "SELECT  * , UNIX_TIMESTAMP(UPLOAD_DATE) UPLOAD_TIMESTAMP "
                     + " FROM UPLOAD_CONFIG "
@@ -418,7 +433,7 @@ public class DGGeneralDBService {
                     "( SELECT MAX(upload_config_id) uploadconfigid " + " FROM UPLOAD_CONFIG "
                     + " WHERE vnf_id =  $vnf-id  AND vm_name = $vm-name ) ; ";
 
-            status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx);
+            status = dblib.query(key, prefix, ctx);
         }
         return status;
     }
@@ -429,13 +444,13 @@ public class DGGeneralDBService {
          QueryStatus status = null;
          SvcLogicContext localContext = new SvcLogicContext();
          localContext.setAttribute("vnf-type", vnf_type);
-         if (serviceLogic != null) {
+         if (dblib != null) {
                  String queryString = "select max(internal_version) as maxInternalVersion, artifact_name as artifactName from ASDC_ARTIFACTS " +
                                   " where artifact_name in (select artifact_name from ASDC_REFERENCE  where vnf_type= $vnf-type "  +
                              " and file_category = 'capability' )" ;
 
                  log.info(fn + "Query String : " + queryString);
-                 status = serviceLogic.query("SQL", false, null, queryString, null, null, localContext);
+                 status = dblib.query(queryString, localContext);
 
                  if(status.toString().equals("FAILURE"))
                          throw new SvcLogicException("Error - while getting capabilitiesData ");
@@ -444,7 +459,7 @@ public class DGGeneralDBService {
                                  " where artifact_name = $artifactName  and internal_version = $maxInternalVersion ";
 
                  log.debug(fn + "Query String : " + queryString1);
-                 status = serviceLogic.query("SQL", false, null, queryString1, null, null, localContext);
+                 status = dblib.query(queryString1, localContext);
                  if (status.toString().equals("NOT_FOUND"))
                      return null;
                  if(status.toString().equals("FAILURE"))
@@ -457,18 +472,20 @@ public class DGGeneralDBService {
     public QueryStatus getTemplateWithTemplateModelId(SvcLogicContext ctx, String prefix, String fileCategory,
             String templateModelId) throws SvcLogicException {
         QueryStatus status = null;
-        String templatePattern = "'%_"+ templateModelId +"%'";
-        if (serviceLogic != null && ctx != null) {
+        String templatePattern = "%_"+ templateModelId +"%";
+        if (dblib != null && ctx != null) {
+            ctx.setAttribute("file-category", fileCategory);
+            ctx.setAttribute("template-pattern", templatePattern);
             String key = "SELECT artifact_content file_content , asdc_artifacts_id config_file_id "
                     + " FROM ASDC_ARTIFACTS "
                     + " WHERE asdc_artifacts_id = ( SELECT MAX(a.asdc_artifacts_id) configfileid  "
                     + " FROM ASDC_ARTIFACTS a, ASDC_REFERENCE b " + " WHERE a.artifact_name = b.artifact_name "
-                    + " AND file_category =  '" + fileCategory + "'" + " AND action =  $request-action "
+                    + " AND file_category =  $file-category AND action =  $request-action "
                     + " AND vnf_type =  $vnf-type  " + " AND vnfc_type =   $vnfc-type ) and ASDC_ARTIFACTS.artifact_name like "
-                    + templatePattern + "; ";
+                    + "$template-pattern ; ";
             log.info("getTemplateWithTemplateModelId()::: with template:::"+ key);
 
-            status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx);
+            status = dblib.query(key, prefix, ctx);
         }
         return status;
     }
@@ -476,18 +493,20 @@ public class DGGeneralDBService {
     public QueryStatus getTemplateByVnfTypeNActionWithTemplateModelId(SvcLogicContext ctx, String prefix,
             String fileCategory, String templateModelId) throws SvcLogicException {
         QueryStatus status = null;
-        String templatePattern = "'%_"+ templateModelId +"%'";
-        if (serviceLogic != null && ctx != null) {
+        String templatePattern = "%_"+ templateModelId +"%";
+        if (dblib != null && ctx != null) {
+            ctx.setAttribute("file-category", fileCategory);
+            ctx.setAttribute("template-pattern", templatePattern);
             String key = "SELECT artifact_content file_content , asdc_artifacts_id config_file_id "
                     + " FROM ASDC_ARTIFACTS "
                     + " WHERE asdc_artifacts_id = (SELECT MAX(a.asdc_artifacts_id) configfileid  "
                     + " FROM ASDC_ARTIFACTS a, ASDC_REFERENCE b " + " WHERE a.artifact_name = b.artifact_name "
-                    + " AND file_category =  '" + fileCategory + "'" + " AND action =  $request-action "
+                    + " AND file_category =  $file-category AND action =  $request-action "
                     + " AND vnf_type =  $vnf-type )  and ASDC_ARTIFACTS.artifact_name like "
-                    + templatePattern + "; ";
+                    + "$template-pattern ; ";
             log.info("getTemplateByVnfTypeNActionWithTemplateModelId()::: with template:::"+ key);
 
-            status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx);
+            status = dblib.query(key, prefix, ctx);
         }
         return status;
 
@@ -496,18 +515,18 @@ public class DGGeneralDBService {
     public QueryStatus getVnfcReferenceByVnfTypeNActionWithTemplateModelId(SvcLogicContext ctx, String prefix,
             String templateModelId) throws SvcLogicException {
         QueryStatus status = null;
-        if (serviceLogic != null && ctx != null) {
-
+        if (dblib != null && ctx != null) {
+            ctx.setAttribute("template-model-id", templateModelId);
             String key = "SELECT  * "
                     + " FROM VNFC_REFERENCE "
                     + " WHERE vnf_type =  $vnf-type "
                     + " AND action =  $request-action   "
-                    + " AND template_id = '"
-                    + templateModelId + "'"
+                    + " AND template_id = "
+                    + "$template-model-id"
                     + " ORDER BY vm_instance, vnfc_instance ; ";
 
             log.info("getVnfcReferenceByVnfTypeNActionWithTemplateModelId()::: with template:::"+ key);
-            status = serviceLogic.query("SQL", false, null, key, prefix, null, ctx);
+            status = dblib.query(key, prefix, ctx);
         }
         return status;
     }
diff --git a/appc-config/appc-data-services/provider/src/main/java/org/onap/appc/data/services/db/DbLibServiceQueries.java b/appc-config/appc-data-services/provider/src/main/java/org/onap/appc/data/services/db/DbLibServiceQueries.java
new file mode 100644
index 000000000..827d32824
--- /dev/null
+++ b/appc-config/appc-data-services/provider/src/main/java/org/onap/appc/data/services/db/DbLibServiceQueries.java
@@ -0,0 +1,182 @@
+/*-
+ * ============LICENSE_START=======================================================
+ * ONAP : APPC
+ * ================================================================================
+ * Copyright (C) 2019 AT&T Intellectual Property. All rights reserved.
+ * ================================================================================
+ * 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=========================================================
+ */
+
+package org.onap.appc.data.services.db;
+
+import java.sql.SQLException;
+import java.util.ArrayList;
+
+import javax.sql.rowset.CachedRowSet;
+
+import org.onap.ccsdk.sli.core.dblib.DBResourceManager;
+import org.onap.ccsdk.sli.core.dblib.DbLibService;
+import org.onap.ccsdk.sli.core.sli.SvcLogicContext;
+import org.onap.ccsdk.sli.core.sli.SvcLogicResource.QueryStatus;
+import org.onap.ccsdk.sli.core.sli.provider.SvcLogicService;
+import org.osgi.framework.Bundle;
+import org.osgi.framework.BundleContext;
+import org.osgi.framework.FrameworkUtil;
+import org.osgi.framework.ServiceReference;
+
+import com.att.eelf.configuration.EELFLogger;
+import com.att.eelf.configuration.EELFManager;
+
+public class DbLibServiceQueries {
+    
+    private static final String DBLIB_SERVICE = "org.onap.ccsdk.sli.core.dblib.DbLibService";
+    private static final EELFLogger log = EELFManager.getInstance().getLogger(DbLibServiceQueries.class);
+    
+    DbLibService dbLibService;
+    
+    public DbLibServiceQueries() {
+        this.dbLibService = getDbLibService();
+        if(this.dbLibService == null) {
+            throw new NullPointerException("DbLibService reference not found");
+        }
+    }
+    
+    public DbLibServiceQueries(DbLibService dbLibService) {
+        this.dbLibService = dbLibService;
+        if(this.dbLibService == null) {
+            throw new NullPointerException("Provided DbLibService is null");
+        }
+    }
+    
+    public DbLibServiceQueries(DbLibService dbLibService, boolean allowNull) {
+        this.dbLibService = dbLibService;
+        if(this.dbLibService == null && !allowNull) {
+            throw new NullPointerException("Provided DbLibService is null");
+        }
+    }
+    
+    public QueryStatus query(String query, SvcLogicContext ctx) {
+        ArrayList<String> arguments = new ArrayList<>();
+        query = CtxParameterizedResolver.resolveCtxVars(query, ctx, arguments);
+        return performQuery(query, ctx, null, arguments);
+    }
+    
+    public QueryStatus query(String query, String prefix, SvcLogicContext ctx) {
+        ArrayList<String> arguments = new ArrayList<>();
+        query = CtxParameterizedResolver.resolveCtxVars(query, ctx, arguments);
+        return performQuery(query, ctx, prefix, arguments);
+    }
+    
+    public QueryStatus query(String query, SvcLogicContext ctx, ArrayList<String> arguments) {
+        return performQuery(query, ctx, null, valueOfArrayList(arguments));
+    }
+    public QueryStatus query(String query, SvcLogicContext ctx, String prefix, ArrayList<String> arguments) {
+        return performQuery(query, ctx, prefix, valueOfArrayList(arguments));
+    }
+    
+    private QueryStatus performQuery(String query, SvcLogicContext ctx, String prefix, ArrayList<String> arguments) {
+        
+        CachedRowSet result = null;
+        try {
+            result = dbLibService.getData(query, arguments, null);
+            if (!result.next()) {
+                log.debug("No data found");
+                return QueryStatus.NOT_FOUND;
+            } else {
+                CtxParameterizedResolver.saveCachedRowSetToCtx(result, ctx, prefix, dbLibService);
+            }
+        } catch (SQLException e) {
+            log.error("Exception in query()",e);
+            return QueryStatus.FAILURE;
+        }
+        return QueryStatus.SUCCESS;
+        
+    }
+    public QueryStatus save(String query, SvcLogicContext ctx) {
+        ArrayList<String> arguments = new ArrayList<>();
+        query = CtxParameterizedResolver.resolveCtxVars(query, ctx, arguments);
+        return performSave(query, arguments);
+    }
+    
+    public QueryStatus save(String query, SvcLogicContext ctx, ArrayList<String> arguments) {
+        return performSave(query, valueOfArrayList(arguments));
+    }
+    
+    private QueryStatus performSave(String query, ArrayList<String> arguments) {
+        boolean success = false;
+        try {
+            success = dbLibService.writeData(query, arguments, null);
+        } catch (SQLException e) {
+            log.error("Exception in save()",e);
+            success = false;
+        }
+        if(success) {
+            return QueryStatus.SUCCESS;
+        }
+        return QueryStatus.FAILURE;
+    }
+    
+    private static DbLibService getDbLibService() {
+        
+        DbLibService dbLibService = null;
+        BundleContext bundleContext = null;
+        ServiceReference serviceRef = null;
+
+        Bundle bundle =  FrameworkUtil.getBundle(SvcLogicService.class);
+
+        if (bundle != null) {
+            bundleContext = bundle.getBundleContext();
+        }
+
+        if (bundleContext != null) {
+            log.debug("Getting bundle Context");
+            serviceRef = bundleContext.getServiceReference(DBLIB_SERVICE);
+        }
+
+        if (serviceRef == null) {
+            log.warn("Could not find service reference for DBLib service");
+                    
+        } else {
+            dbLibService = (DbLibService)bundleContext.getService(serviceRef);
+            if (dbLibService == null) {
+                log.warn("DBLIB_SERVICE is null");
+            }
+        }
+        if (dbLibService == null) {
+            try {
+                dbLibService = new DBResourceManager(System.getProperties());
+            } catch (Exception e) {
+                log.error("Caught exception trying to create db service", e);
+            }
+
+            if (dbLibService == null) {
+                log.warn("Could not create new DBResourceManager");
+            }
+        }
+        return dbLibService;
+    }
+    
+    //By using String.valueOf on the array list items, we can store any null values as
+    //Strings with the value "null". This mirrors the way queries worked prior to the
+    //prepared statements.
+    private ArrayList<String> valueOfArrayList(ArrayList<String> original) {
+        ArrayList<String> valueOfList = new ArrayList<>();
+        for(String s : original) {
+            valueOfList.add(String.valueOf(s));
+        }
+        return valueOfList;
+    }
+
+}
diff --git a/appc-config/appc-data-services/provider/src/test/java/org/onap/appc/data/services/db/MockDGGeneralDBService.java b/appc-config/appc-data-services/provider/src/test/java/org/onap/appc/data/services/db/MockDGGeneralDBService.java
index 5aed72a25..adc080581 100644
--- a/appc-config/appc-data-services/provider/src/test/java/org/onap/appc/data/services/db/MockDGGeneralDBService.java
+++ b/appc-config/appc-data-services/provider/src/test/java/org/onap/appc/data/services/db/MockDGGeneralDBService.java
@@ -21,23 +21,24 @@
 
 package org.onap.appc.data.services.db;
 
+
 public class MockDGGeneralDBService extends DGGeneralDBService {
 
-    private static MockSvcLogicResource serviceLogic = new MockSvcLogicResource();
+    private static MockDbLibServiceQueries dbLibServiceQueries = new MockDbLibServiceQueries();
 
     public MockDGGeneralDBService() {
 
-        super(serviceLogic);
-        serviceLogic = new MockSvcLogicResource();
+        super(dbLibServiceQueries);
+        dbLibServiceQueries = new MockDbLibServiceQueries();
     }
 
-    public MockDGGeneralDBService(MockSvcLogicResource serviceLogic) {
-        super(serviceLogic);
-        this.serviceLogic = serviceLogic;
+    public MockDGGeneralDBService(MockDbLibServiceQueries dbLibServiceQueries) {
+        super(dbLibServiceQueries);
+        this.dbLibServiceQueries = dbLibServiceQueries;
     }
 
     public static MockDGGeneralDBService initialise() {
-        MockDGGeneralDBService mockDGGeneralDBService = new MockDGGeneralDBService(serviceLogic);
+        MockDGGeneralDBService mockDGGeneralDBService = new MockDGGeneralDBService(dbLibServiceQueries);
         return mockDGGeneralDBService;
     }
 }
diff --git a/appc-config/appc-data-services/provider/src/test/java/org/onap/appc/data/services/db/MockSvcLogicResource.java b/appc-config/appc-data-services/provider/src/test/java/org/onap/appc/data/services/db/MockDbLibServiceQueries.java
index e4dbbc15d..91dd1c11f 100644
--- a/appc-config/appc-data-services/provider/src/test/java/org/onap/appc/data/services/db/MockSvcLogicResource.java
+++ b/appc-config/appc-data-services/provider/src/test/java/org/onap/appc/data/services/db/MockDbLibServiceQueries.java
@@ -21,6 +21,7 @@
 
 package org.onap.appc.data.services.db;
 
+import java.util.ArrayList;
 import java.util.Map;
 
 import org.onap.ccsdk.sli.core.sli.SvcLogicContext;
@@ -28,20 +29,40 @@ import org.onap.ccsdk.sli.core.sli.SvcLogicException;
 import org.onap.ccsdk.sli.core.sli.SvcLogicResource.QueryStatus;
 import org.onap.ccsdk.sli.adaptors.resource.sql.SqlResource;
 
-public class MockSvcLogicResource extends SqlResource {
+public class MockDbLibServiceQueries extends DbLibServiceQueries {
 
+    public MockDbLibServiceQueries() {
+        super(null,true);
+    }
+
+    @Override
+    public QueryStatus query(String query, SvcLogicContext ctx, String prefix, ArrayList<String> arguments) {
+
+        return QueryStatus.SUCCESS;
+    }
+    @Override
+    public QueryStatus query(String query, SvcLogicContext ctx, ArrayList<String> arguments) {
 
+        return QueryStatus.SUCCESS;
+    }
+    @Override
+    public QueryStatus query(String query, String prefix, SvcLogicContext ctx) {
 
+        return QueryStatus.SUCCESS;
+    }
     @Override
-    public QueryStatus query(String resource, boolean localOnly, String select, String key, String prefix,
-            String orderBy, SvcLogicContext ctx) throws SvcLogicException {
+    public QueryStatus query(String query, SvcLogicContext ctx) {
 
         return QueryStatus.SUCCESS;
     }
 
     @Override
-    public QueryStatus save(String resource, boolean force, boolean localOnly, String key, Map<String, String> parms,
-            String prefix, SvcLogicContext ctx) throws SvcLogicException {
+    public QueryStatus save(String query, SvcLogicContext ctx, ArrayList<String> arguments) {
+
+        return QueryStatus.SUCCESS;
+    }
+    @Override
+    public QueryStatus save(String query, SvcLogicContext ctx) {
 
         return QueryStatus.SUCCESS;
     }
diff --git a/appc-config/appc-data-services/provider/src/test/java/org/onap/appc/data/services/db/TestDGGeneralDBService.java b/appc-config/appc-data-services/provider/src/test/java/org/onap/appc/data/services/db/TestDGGeneralDBService.java
index fbb8cf8b9..ae5518dc9 100644
--- a/appc-config/appc-data-services/provider/src/test/java/org/onap/appc/data/services/db/TestDGGeneralDBService.java
+++ b/appc-config/appc-data-services/provider/src/test/java/org/onap/appc/data/services/db/TestDGGeneralDBService.java
@@ -26,11 +26,21 @@ package org.onap.appc.data.services.db;
 
 import static org.junit.Assert.assertEquals;
 import static org.junit.Assert.assertNull;
+import static org.mockito.Matchers.any;
+import static org.mockito.Mockito.mock;
+import static org.mockito.Mockito.times;
+import static org.mockito.Mockito.verify;
+import static org.mockito.Mockito.when;
 
 import java.io.IOException;
+import java.util.ArrayList;
+
+import javax.sql.rowset.CachedRowSet;
+
 import org.apache.commons.io.IOUtils;
 import org.apache.commons.lang.StringEscapeUtils;
 import org.junit.Test;
+import org.onap.ccsdk.sli.core.dblib.DbLibService;
 import org.onap.ccsdk.sli.core.sli.SvcLogicContext;
 import org.onap.ccsdk.sli.core.sli.SvcLogicException;
 import org.onap.ccsdk.sli.core.sli.SvcLogicResource.QueryStatus;
@@ -268,12 +278,28 @@ public class TestDGGeneralDBService {
 
     @Test
     public void testGetTemplateWithTemplateModelId() throws Exception {
-        MockDGGeneralDBService dbService =     MockDGGeneralDBService.initialise();
+        DbLibService mockDbLibService = mock(DbLibService.class);
+        CachedRowSet mockCachedRowSet = mock(CachedRowSet.class);
+        when(mockCachedRowSet.next()).thenReturn(false);
+        DGGeneralDBService dbService = new DGGeneralDBService(mockDbLibService);
         SvcLogicContext ctx = new SvcLogicContext();
-        String prefix="test";
-        String templateModelId = "template001";
-        String fileCategory="testCategory";
-        dbService.getTemplateWithTemplateModelId(ctx, prefix, fileCategory, templateModelId);
+        ctx.setAttribute("request-action", "testRequestAction");
+        ctx.setAttribute("vnf-type", "testVnfType");
+        ctx.setAttribute("vnfc-type", "testVnfcType");
+        String expectedStatement = "SELECT artifact_content file_content , asdc_artifacts_id config_file_id FROM ASDC_ARTIFACTS WHERE"
+                + " asdc_artifacts_id = ( SELECT MAX(a.asdc_artifacts_id) configfileid FROM ASDC_ARTIFACTS a, ASDC_REFERENCE b"
+                + " WHERE a.artifact_name = b.artifact_name AND file_category = ? AND action = ? AND vnf_type = ? AND vnfc_type = ? )"
+                + " and ASDC_ARTIFACTS.artifact_name like ? ; ";
+        ArrayList<String> expectedArguments = new ArrayList<>();
+        expectedArguments.add("testFileCategory");
+        expectedArguments.add("testRequestAction");
+        expectedArguments.add("testVnfType");
+        expectedArguments.add("testVnfcType");
+        String templateModelId = "testTemplateModelId";
+        expectedArguments.add("%_"+ templateModelId +"%");
+        when(mockDbLibService.getData(any(), any(), any())).thenReturn(mockCachedRowSet);
+        dbService.getTemplateWithTemplateModelId(ctx, "testPrefix", "testFileCategory", templateModelId);
+        verify(mockDbLibService,times(1)).getData(expectedStatement, expectedArguments, null);
     }
 
     @Test