[Karate API Automation Framework] [Oracle DB] Database Scenario Validation with Karate Framework

Recently, when we need to validate our APIs along with oracle DB, We could not do it just by adding maven dependency. We followed the below approach:

We first downloaded the oracle JDBC jar and later added it to a folder External Jar in our project.

Then we gave the link to this jar in our pom file like this:

   <dependency>

            <groupId>com.oracle.jdbc</groupId>

            <artifactId>JDBC</artifactId>

            <scope>system</scope>

            <version>1.0</version>

            <systemPath>${basedir}\ExternalJars\ojdbc8.jar</systemPath>

        </dependency>

And later we created a DbUtil java class in our project.

import javax.swing.*;

import java.sql.*;

import java.util.Arrays;

import java.util.Enumeration;

import java.util.List;

import java.util.Map;

public class dbUtil {

public static Connection dbConn = null;

public static String ANSI_RED = "\u001B[31m";

private dbUtil() throws SQLException {

}

public static ResultSet getDataFromDB(String dbQuery) throws Exception {

dbConn = DriverManager.getConnection("jdbc:oracle:thin:@HostName:Port:Service Name", "username", "password");

if (dbConn != null) {

System.out.println("Checking Database.");

}

Statement stmt = dbConn.createStatement();

ResultSet rs = stmt.executeQuery(dbQuery);

return rs;

}

public static List<String> getListFromDB(String dbQuery,String Column) throws Exception

{

List<String> arrayList = new ArrayList<>();

dbConn = DriverManager.getConnection("jdbc:oracle:thin:@HostName:Port:Service Name", "username", "password");

if (dbConn != null) {

System.out.println("Checking Database.");

}

Statement stmt = dbConn.createStatement();

ResultSet rs = stmt.executeQuery(dbQuery);

while (rs.next())

{

arrayList.add(rs.getString(Column));

}

return arrayList;

}

public static String getCountFromDB(String dbQuery) throws Exception

{

ResultSet rs = getDataFromDB(dbQuery);

while (rs.next())

{

return rs.getString(1);

}

return "";

}

}

This code we called from our karate framework feature file:

@DBTest
Feature: Database Connectivity with karate framework
  Background:
    Given url  baseURL
    * def helper = Java.type("helper")
    * def DbUtils = Java.type("dbUtil")

  Scenario: Validate API record count from DB
    * def oracleDbCount = DbUtils.getCountFromDB("select count(*) from tableName where condition
    And print oracleDbCount
    And path "apiPath"
    When method get
    Then status 200
    * def countFromAPI= response.metadata.count
    And print countFromAPI
    And assert oracleDbCount == countFromAPI

* def dbData = DbUtils.getListFromDB(<Query>,'<Field>')
And def dbDataList = new ArrayList()
* eval for(var i = 0; i < dbData.length; i++) dbDataList.add(dbData[i])
And print fieldsToCheck
And print dbDataList
And assert dbDataList.length == fieldsToCheck.length
And assert helper.fnCompareTwoList(fieldsToCheck,dbDataList)