Getting ready

Before using the H2 database, we need to download its JAR and add it to SoapUI's classpath. You can get the latest H2 JAR from http://mvnrepository.com/artifact/com.h2database/h2/ (I took version 1.4.181). Then, add it to <SoapUI Installation Directory>/java/app/bin/ext/.

You'll also need some headed CSV data. Amazingly, the script might be able to handle any valid CSV structure (see http://www.h2database.com/html/functions.html#csvread). We'll use a simple invoice example invoices_with_headers.csv that can be found in the chapter 2 samples.

I have provided a completed SoapUI project GroovyInMemoryDB-soapui-project.xml in the Chapter2 samples.

How to do it...

Assuming you have a project, TestSuite and TestCase, we'll add a Groovy TestStep to register the H2 JDBC driver, load the CSV test data into a new table, select the data from the table, and log the results. Perform the following steps:

That's it!

How it works...

One of the key requirements for working with JDBC drivers in SoapUI Groovy TestStep scripts is to register the driver using the GroovyUtils.registerJdbcDriver method. If you don't do this, you get a no suitable driver found error when trying to get a new database connection on the next line.

The groovy.sql.Sql class provides a very convenient wrapper to hide all the usual Java JDBC connectivity code and connection management.

Apart from specifying the driver's class name as org.h2.Driver, the connection string jdbc:h2:mem:test specifies that we want our H2 database to be called test and created in memory (mem).

Next, we have a pretty compact and dynamic SQL statement:

create table if not exists invoices as select * from csvread('$fileName')

This not only creates the table if it doesn't already exist, but also defines its structure based on the CSV file and then loads it with the data—Pow!

The last statement is fairly standard Groovy just to select all the invoice records, then iterate over them, and print each one to the log.

There's more...

The preceding example is very compact and can prove to be useful when setting up test data. See Chapter 3, Developing and Deploying Dynamic REST and SOAP Mocks for an example. If you need to tear down the data, you can either delete the records or drop the table:

db.execute("delete from invoices")
db.execute("drop table invoices")

def fileName = testRunner.testCase.getPropertyValue("invoiceFileName")

That's assuming the invoiceFileName property was set on TestCase.

See also