Dalibor Blazevic ,
· 6 min read

Setting Up and Initializing Database Connection from Groovy Test Step in SoapUI

Introduction

Although SoapUI has a Test Step called “JDBC Request” this test step requires hard coded credentials to run properly. Otherwise we are going to get “java.sqlSQLException” because of missing credentials. However very often we don’t want to supply hard coded credentials. Especially if the tests are running on test automation server (e.g. from Jenkins). Sometimes we want to apply Java cryptography facilities for added security. This can only be applied from within Groovy Test Step by using Groovy code and appropriate Java classes.

In this article we are going to demonstrate how to use Groovy Test Step within SoapUI to load database credentials from disk, and than initialize database connection by using Groovy. At the and we will see practical example on using this database connection.

Setting Up and Initializing Database Connection

To set up database connection from within Groovy Test Step we are going to do following steps:

  1. Create Initialization Test Case with disabled Groovy Test Step,
  2. Load credentials from disk,
  3. Set up and load database driver,
  4. Save database connection as a property.

Create Initialization Test Case with disabled Groovy Test Step

First we have to prepare our test environment. Since many Test Cases within our project will share the same database connection it is best to store database initialization Test Step in a separate Test Case. This Test Case can be used to store initialization code for shared libraries as well.

Example of such structure is visible on the following picture:

Structure of the project that is using shared library and database initialization Fig. 1. Structure of the SoapUI project that is using shared library and database initialization Groovy Test Step

Load credentials from disk

We don’t want hardcoded credentials in our code or in JDBC Request test step. That’s why we are going to load them from disk. Code example to do so can be seen below:

class Credentials {
    String userName
    String password
}


//When running in SoapUI
String fileName = "/Users/<username>/Documents/elemif/src/test/resources/database.json"

//When running on Jenkins (DISABLE when needed)
fileName = "src/test/resources/database.json"


def inputFile = new File(fileName)
Credentials credentials = new JsonSlurper().parseText(inputFile.text)

When we are running our tests from within SoapUI GUI environment we have to supply absolute paths but when we are running our tests from command line or from e.g. Jenkins server than we have to use relative paths.

Setting up and loading database driver

In order to load database driver from Groovy Test Step we first have to place database driver in a correct location. When running this test step from SoapUI GUI than we have to find installation folder of SoapUI (On my mac this is: /Applications/Development/SoapUI-5.4.0.app) and put the driver in: Contents/java/app/bin/ext. On Windows or Linux machine the path can be a little bit different but it will still be bin/ext folder.

Example with updated Oracle JDBC (ojdbc8.jar) dirver location can be seen on the following picture:

Driver location Fig. 2. Driver location

When we have driver that we want to use in place we are now able to load and initialize driver from Groovy code with this script:

com.eviware.soapui.support.GroovyUtils.registerJdbcDriver("oracle.jdbc.driver.OracleDriver")
Sql sql = Sql.newInstance(
    'jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx.xxx.xxx.xx)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=<service_name>)))',
    credentials.userName, credentials.password, 'oracle.jdbc.driver.OracleDriver')

Finally in order to share the object we are going to register it as a property of context object, as can be seen from the whole script seen here:

import groovy.sql.Sql
import groovy.json.JsonSlurper

/* DATABASE INTIALIZATION BLOCK */
class Credentials {
    String userName
    String password
}


//When running in SoapUI
String fileName = "/Users/<username>/Documents/elemif/src/test/resources/database.json"

//When running on Jenkins (DISABLE when needed)
fileName = "src/test/resources/database.json"


def inputFile = new File(fileName)
Credentials credentials = new JsonSlurper().parseText(inputFile.text)
//log.info "DEBUG: credentials=${credentials.userName}"

com.eviware.soapui.support.GroovyUtils.registerJdbcDriver("oracle.jdbc.driver.OracleDriver")
Sql sql = Sql.newInstance(
    'jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xx.xx.xx.xx)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=custom_qa)))',
    credentials.userName, credentials.password, 'oracle.jdbc.driver.OracleDriver')

context.setProperty("sql", sql)

Using database connection

To actually use database connection we have first to execute disabled Groovy Test Step that contains previously described initialization code, and than we can pass captured reference to database connection sql object to another function that is going to use it. Here is en example how to do it from Groovy Test Step:

import com.eviware.soapui.impl.wsdl.teststeps.WsdlTestRequestStepResult;
import groovy.sql.Sql
import java.time.LocalDateTime
import java.util.Date

/**
 * Class to runn test steps (has to be class to runn in passed context)
 */
class TestSteps {


  //Global objects
  def log
  def context
  def sql

  def TestSteps(log, context, testRunner) {
      this.log = log
      this.context = context
      this.testRunner = testRunner

    testRunner.testCase.testSuite.getTestCaseByName("Initialization").getTestStepByName("InitDB").run(testRunner, context)
    this.sql = context.getProperty("sql")
  }

  /**
   * Test steps execution function
   */
  def executeSteps(def stepNames, def expectedResults) {

    HashMap<String, WsdlTestRequestStepResult> results = new HashMap<String, WsdlTestRequestStepResult>()

    log.info "DB ARCHIVE TESTING STARTET AT: ${LocalDateTime.now().toString()}"

    stepNames.each { stepName ->
        WsdlTestRequestStepResult result = testFunctions.runTestStep(stepName);
        results.put(stepName, result);
    }

    stepNames.each { stepName ->
        String result = validateTestStep(stepName, sql, results.get(stepName), dbTestUtils.verify, expectedResults);
        log.info result
    }

    sql.close()

      log.info "DB ARCHIVE TESTING ENDED AT: ${LocalDateTime.now().toString()}"
  }

  /**
   * Run test step
   * @param stepName - Name of the test step to execute
   * @return Result of test step execution
   */
  WsdlTestRequestStepResult runTestStep(String stepName) {
    //TODO additional preconditions here

    WsdlTestRequestStepResult response = testRunner.runTestStepByName(stepName);
    return response
  }

  /**
   * Validates test step
   * @param stepName - name of Test Step e.g. "RequestStartOfSupply"
   * @param sql - reference to groovy.sql.Sql object
   * @param response - reference to the Test Result response
   */
  def validateTestStep(String stepName, Sql sql, WsdlTestRequestStepResult response) {
    //TODO implement result validation by using results from database
  }

}

Conclusion

With the little bit of tricks we can very easily write reusable Groovy code inside SoapUI Groovy Test Step where we are going to initialize and than reuse database connection in a form of Groovy object. This approach allow us to effectively store database credentials on disk in possibly encrypted form, thus avoiding hardcoding these credentials in specific JDBC Request test step. This also adds to more flexibility and code reusability within SoapUI.