Reusing a database connection in soapUI

Creation of a database connection is a rather resource heavy task. Depending on the way your soapUI test cases are implemented there might potentially be a large number of different calls to an underlying database. When running test cases or test suites the connection shall preferably only be created once and then be reused whenever needed. Though on the other hand when debugging test cases a single test step might need to be executed in its own context without an existing database connection. Below I’ll highlight a short example that will combine the two and which will make it rather smooth to reuse and create a database connection when needed.

Solution

A reusable database connection in;

  • test cases
  • test suites
  • test steps

The example below assumes that the code for setting up a database connection is already in place. If not setting up the database connection using SQL server with jtds can be found here.

How

The trick is really to use the soapUI context as a placeholder for the database connection object. Whenever a connection is established the connection shall be stored in the context so that it does not matter if a full test suite or a single test case is executed, the context variable will hold the DB connection. For execution of single test steps the connection needs to be created every time, sorry no workaround here.

Modularization (sort of)

Since this example references to the free version of soapUI, modularization is simply re-execution of a test step and a way of avoiding duplicate code lines. Assume a test case like the one below.

SoapUITestCase
  \- SetupDatabaseConnection
  \- TestRequest (SOAP)
  \- RunADatabaseQueryTestStep

The test step SetupDatabaseConnection is must preferably be present in all test cases (it is possible to include test cases in test cases) where a database connection shall be shared, it can if wanted even be disabled. The modularization in effect simply invokes the SetupDatabaseConnection test step from the query test step when needed i.e. when no connection exists.

Setup database connection test step

/* Groovy */
if (context.sqlconn == null) {
  // Create the database connection. This wont be needed in the following cases
  // 1) The running test suite context alread has a sql connection created and stored
  // 2) The running test case context alread has a sql connection created and stored
  ...
  def sql = Sql.newInstance("jdbc:jtds:sqlserver://"+dbIp+":"+dbPort"+"/"
  +dbName,dbUserName,dbUserPassword, "net.sourceforge.jtds.jdbc.Driver")
  context.setProperty("sqlconn", sql)
}

Running a database query test step

/* Groovy */
if (context.sqlconn == null) {
  // Recreate a database connection here since the
  // context is only alive for a test case execution
  // not for executing single test steps.
  testRunner.runTestStepByName("SetupDatabaseConnection")
}
if (context.sqlconn != null) {
  def res = sql.eachRow("select * from Users", {
    userId = it.UserId
  })
}

So by storing away the database connection in the current context and by using a simple switch the same connection can be resused all over and at the same time be created only a needed basis.

Advertisements

soapUI inline request parameterization made simple using static Java methods

Maintainability and reuse when using a lot of Groovy scripting or in-house Java libs with the free version of soapUI can be really cumbersome. The same groovy code snippets are all over the place, it does not matter if a lot of core functionality is mapped into the java libraries there are still some Groovy code to be written to get this to work in the long run.

I recently had to struggle creating maintainable test suites covering more than 200 test cases and in total easily more than a thousand different requests. There was a clear need for making the test suites easy to maintain and extend. Removing all the duplicated Groovy code was the top priority. Blow is the usual scenario we had for the majority of our tests in the beginning.

A) Groovy script

  • importing any bin/ext java libraries
  • invoking a given method the e.g. creates a timestamp value
  • put the generated timestamp into a test case property

B) Test request

  • inline expansion of the test case property

This was really an overkill approach when dealing with hundreds and hundreds of different test requests. So how to get this in to small one-liners inside the requests directly without even having to deal with test case properties asf..

Using static Java methods

Just implement a set of static Java methods and inline these in your test requests. No need for library imports or instance creation just call the method needed with arguments that in turn can be dynamically created.

Below is an example class that can be added to a jar file put in the soapUIinstallation/bin/ext folder.


public class TimeStampCreator {
  public static String getToday() { ... }
  public static String getTodayPlusDays(int days) { ... }
  public static String getTodayMinusDays(int days) { ... }
}

The methods above can be called accordingly as described in the TestRequest (SOAP) example below, note the nested test case parameter expansion.


<args xsi:type="api:ArrayOf_xsd_string" soapenc:arrayType="xsd:string[]">
  <item>${=package.name.TimeStampCreator.getToday()}</item>
  <item>${=package.name.TimeStampCreator.getTodayPlusDays(2)}</item>
  <item>${=package.name.TimeStampCreator.getTodayMinusDays(${#TestCase#days})}</item>
</args>

Works like a charm…

Data-driven soapUI JUnit integration with readable test reports

I have never really been satisfied with the way soapUI and JUnit integration had to be done. There where too many things that had to be maintained at two ends. This posting will describe an approach for runing soapUI test cases through JUnit using the data driven parts of JUnit with a small extension that makes the reporting really useful (traceable). In short, soapUI test case names are not needed any more, JUnit will pick up all non disabled test cases in a soapUI test suite and will execute and use the retrieved soapUI test case name in the report.

  • Minimal JUnit wrapping test classes
  • Superb reporting

Using

  • soapUI APIs – for running tests and retrieveing test cases
  • JUnit 4.8.x+ Parameterized

There are may examples on the web that explains how to wrap JUnit test cases around soapUI test cases. The basic approach for performing this is to simply map all test cases (test case names as strings) into single JUnit test cases. This is a very fragile approach since any change in a test case name needs to be reflected in the JUnit test suite.

  • soapUI test cases can be renamed at will
  • soapUI test cases can be enabled/disabled at will

Both of the above will break any JUnit test suites of not maintained properly.

JUnit 4 data-driven

JUnit 4.x comes with a feature where tests can be Parameterized or in other words data-driven. By using the Parameterized functionality it is rather trivial to extract all test cases to be executed and feed the test cases names as arguments to the test classes. This approach makes the JUnit test classes minimal and easy to maintain since all test case are read from the soapUI project files.

Sounds superb out of the box but things are seldom as fancy as the first appear. JUnit lacks a proper mechanism for the test reporting, all executed tests simply get names in the following format.


package.testclass
  \-<JUnit test case name>[0]
  \-<JUnit test case name>[1]
  \-<JUnit test case name>[2]
  \- ...

Not very useful since this makes the tracing back to the actual soapUI test case rather annoying.

So how to get what we want in place, starting from the end?

JUnit Parameterized extensions

I struggled with this bit for a while before I got it to work. After trying a few different approaches for extending the native JUnit functionality the easiest approach turned out to be to clone and extend the Parameterized class from the JUnit jar. Download any source jars from https://github.com/KentBeck/junit/downloads and fetch the org/junit/runners/Parameterized.java.

With the entire class code available the extension was trivial by simply edit the returned string from the getName and testName methods. I choose to use the exact name as given in the soapUI test suite, replacing all whitespace characters with an underscore ‘_’ sign. The soapUI test case name is fed through the JUnit class as described further down.


// My class is named ParameterizedExtended and is an exact clone of Parameterized apart from
// the changes below.

// Interface renaming to avoid any possible conflicts with the original classes in the JUnit diet
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.METHOD)
public static @interface ParametersExtended {
}

@Override
protected String getName() {
  Object[] tcName = fParameterList.get(fParameterSetNumber);
  return String.format("[%s]-%s", fParameterSetNumber,((String)tcName[0]).replaceAll(" ", "_"));
}


@Override
protected String testName(final FrameworkMethod method) {
  Object[] tcName = fParameterList.get(fParameterSetNumber);
  return String.format("%s", ((String)tcName[0]).replaceAll(" ", "_"));
}

The JUnit test class

For enabling the data-driven parts the test class fist of all needs to use the @RunWith annotation pointing at your extended Parameterized class. Apart from this there is a minimum of methods to be implmented the CTOR, the method for getting all test cases, the actual test case and the soapUI runner.

Implementing the test class

Annotations used, @RunWith, “@ParametersExtended” (from the interface in the extended Parameterized class), @Test. Every test case gets an instance where a unique test case name is set based on the collection returned in the getTestCases method. The collection is really a set of String arrays but in this example only a single String is needed, the name of a soapUI test case.


@RunWith(ParameterizedExtended.class)
public class DemoTestSuite {

  private String testCaseName;
  public DemoTestSuite(String testCaseName) {
    super();
    this.testCaseName = testCaseName;
  }

  private boolean runSoapUITestCase(String testCase) {
    ...
  }

  @ParametersExtended
  public static Collection getTestCases() {
    ...
  }

  @Test
  public void TC_SOAP() {
    assertTrue(runSoapUITestCase(this.testCaseName));
  }
}

Fetching all the soapUI test cases

The getTestCases method looks for test cases in the soapUI project and returns all test cases present in a given test suite ignoring all disabled ones.


public static Collection<String[]> getTestCases() {
  final ArrayList<String[]> testCases = new ArrayList<String[]>();
  WsdlProject soapuiProject = new WsdlProject("PROJECT_FILE_NAME");
  WsdlTestSuite wsdlTestSuite = soapuiProject.getTestSuiteByName("TEST_SUITE_NAME");
  List<TestCase> testCaseStrings = wsdlTestSuite.getTestCaseList();

  for (TestCase ts : testCaseStrings) {
    if (!ts.isDisabled()) {
      testCases.add(new String[] {ts.getName()});
    }
  }
  return testCases;
}

Running the soapUI test case

A basic soapUI runner as described in any soapUI forums or tutorials, add it to your framework and kick off some tests.


public static boolean runSoapUITestCase(WsdlProject soapuiProject, String testSuite, String testCase) {
  TestRunner.Status exitValue = TestRunner.Status.INITIALIZED;
  WsdlProject soapuiProject = new WsdlProject("PROJECT_FILE_NAME");
  WsdlTestSuite wsdlTestSuite = soapuiProject.getTestSuiteByName(testSuite);
  if (wsdlTestSuite == null) {
    System.err.println("soapUI runner, test suite is null: "+testSuite);
    return false;
  }
  WsdlTestCase soapuiTestCase = wsdlTestSuite.getTestCaseByName(testCase);
  if (soapuiTestCase == null) {
    System.err.println("soapUI runner, test case is null: " + testCase);
    return false;
  }
  soapuiTestCase.setDiscardOkResults(true);
  WsdlTestCaseRunner runner = soapuiTestCase.run(new PropertiesMap(), false);
  exitValue = runner.getStatus();

  System.out.println("soapUI test case ended ('" + testSuite + "':'"+ testCase + "'): " + exitValue);
  if (exitValue == TestRunner.Status.FINISHED) {
    return true;
  } else {
    return false;
  }
}

The final output

With a minimal test class implementation, only the soapUI project file and test suite needs to be defined to get an easy to maintain JUnit/soapUI framework that has the kind of reporting one would expect to be in place.

package.testclass
  \-<soapUI test case name A>[0]
  \-<soapUI test case name B>[1]
  \-<soapUI test case name C>[2]
  \- ...

Getting soapUI SQL server connections to work using jTDS and Groovy

So many suggestions, a few halfworking approaches and finally I found the line of Groovy code I was missing in a totally different context. I was struggling to get the Microsoft provided jdbc drivers to work getting “ClassNotFound”, incompatible JRE versions and could not find driver for error messages. There where an number of suggestions on the web, using jTDS was one of them that seemed simple enough and it worked.

Anyway this is how I got it to work in the end, setting up an SQL connection using jTDS, calling the DB and interating the response.

  • soapUI 3.6.1
  • jTDS 1.2.5
  • SQL server 2008

Setup

  • download the jTDS JDBC driver (jTDS), unzip and put the jarfile in the
    <soap UI installation folder>/bin/ext folder.
  • Initially I also put the ntlmauth.dll (matching the system) in the <soap UI installation folder>/bin folder. It showed later that this was not really needed.

Before I added the most important line off all, where the actual driver is registered with soapUI there was a lot of fiddling with the conncetion string. Do not do any mistakes there, the format is important and must absolutely follow the format defined for jTDS.

jdbc:jtds:<server_type>://<server>[:<port>][/<database>][;<property>=<value>[;...]]

The database is stated as optional but if not defining it there was an SQL exception thrown stating that the looked for table was not found and probably indicating that the default database was not the one I wanted to use.

My approach in Groovy below:

//
// * Register driver
// * Set up the connection
// * Run a query
//
import groovy.sql.Sql
com.eviware.soapui.support.GroovyUtils.registerJdbcDriver( "net.sourceforge.jtds.jdbc.Driver" )

def sql = Sql.newInstance('jdbc:jtds:sqlserver://<IP/HOSTNAME>:1433/<DATABASENAME>', "<USERNAME>", "<PASSWORD>", "net.sourceforge.jtds.jdbc.Driver")

def res = sql.eachRow("select * from <TABLE> where <COND>, {
    // Do the stuff
  }
)