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

Cegal Tech Advisors Fighting fuzz by striving to make your coding life more straight forward.

11/27/2018 |

Although SoapUI has a Test Step called “JDBC Request,” this test step requires hard-coded credentials to run properly. Otherwise, we will 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 a 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 the Groovy Test Step using Groovy code and appropriate Java classes.

In this article, we will demonstrate how to use Groovy Test Step within SoapUI to load database credentials from disk, and then initialize database connection using Groovy. Finally, a practical example of using this database connection will be illustrated.

Setting Up and Initializing Database Connection

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

Create Initialization Test Case with disabled Groovy Test Step
Load credentials from the disk
Set up and load the database driver
Save the database connection as a property to use

1. 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 the database initialization Test Step in a separate Test Case. This Test Case can be used to store initialization code for shared libraries as well.

An example of such a structure is visible in the following picture:

Structure of the project that is using shared library and database initialization

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

2. Load credentials from the 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. A 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 the command line or from e.g. Jenkins server, then we have to use relative paths.

3. Set up and load the database driver

In order to load the database driver from Groovy Test Step we first have to place the database driver in the correct location. When running this test step from SoapUI GUI than we have to find the 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 machines the path can be a little bit different, but it will still be in the bin/ext folder.

An example with an updated Oracle JDBC (ojdbc8.jar) driver location can be seen in the following picture:

Fig. 2. Driver location

When we have a driver that we want to use in place, we are now able to load and initialize the 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 the 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)

4. Save the database connection as a property to use

To actually use the database connection we have first to execute the disabled Groovy Test Step that contains the previously described initialization code, and then we can pass the captured reference to the database connection sql object to another function that is going to use it. Here is an example of 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 a few tricks, we can very easily write reusable Groovy code inside SoapUI Groovy Test Step where we are going to initialize and then reuse the database connection in the form of a Groovy object. This approach allows us to effectively store database credentials on disk in possibly encrypted form, thus avoiding hardcoding these credentials in specific JDBC Request test steps. This also adds to more flexibility and code reusability within SoapUI.