官术网_书友最值得收藏!

首頁 >

SoapUI Cookbook

Importing CSV file data into an in-memory H2 database with Groovy

There are times when you just need a quick database loaded with test data and don't want to persist, set up, or install anything. Well, this is where the in-memory mode of the H2 database engine can come in handy (http://www.h2database.com/)!

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:

  1. Create a Groovy TestStep and add the following code: 
    import groovy.sql.Sql
import org.h2.Driver

com.eviware.soapui.support.GroovyUtils.registerJdbcDriver("org.h2.Driver")

def db = Sql.newInstance("jdbc:h2:mem:test", "org.h2.Driver")

//Change this to the location of your CSV file.
def fileName = "/temp/invoices_with_headers.csv"

db.execute("create table if not exists invoices as select * from csvread('$fileName')")
 
db.eachRow("select * from invoices"){invoice->
  log.info invoice.toString()
}

    Note

    Before running, make sure that the fileName variable is set to the correct path.

  2. Running the Groovy TestStep should show the CSV data output to the log: 
    Thu Aug 28 16:40:57 BST 2014:INFO:[ID:1, COMPANY:comp1, AMOUNT:100.0, DUE_DATE:2014-12-01 00:00:00]
Thu Aug 28 16:40:57 BST 2014:INFO:[ID:2, COMPANY:comp2, AMOUNT:200.0, DUE_DATE:2014-12-01 00:00:00]
Thu Aug 28 16:40:57 BST 2014:INFO:[ID:3, COMPANY:comp3, AMOUNT:300.0, DUE_DATE:2014-12-01 00:00:00]

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.

Tip

Groovy SQL

It's worth taking a better look at this if you want to do more Groovy scripting with JDBC data sources. Apart from the driver details and SQL, the code here would be applicable to other JDBC databases like MySQL. For more info, see http://groovy.codehaus.org/api/groovy/sql/Sql.html.

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).

Note

The in-memory mode

One thing to say about the convenience of in-memory mode is that the H2 database instance doesn't stop running after your Groovy script has finished, and remains available until SoapUI's JVM is closed down. This is why I put the if not exists clause in the create table statement. Otherwise, rerunning the script will cause a table already exists error.

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")

Tip

Parameterize file paths

To improve the example, rather than hardcoding the file path, it would be a better practice to use a property:

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

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

See also

主站蜘蛛池模板： 汉寿县| 得荣县| 宁强县| 新闻| 从江县| 南和县| 麟游县| 喀喇| 南康市| 辉南县| 吴堡县| 霞浦县| 获嘉县| 巢湖市| 修武县| 南投县| 广德县| 丹东市| 营口市| 两当县| 叶城县| 许昌县| 故城县| 石嘴山市| 沽源县| 伽师县| 衡东县| 莱州市| 增城市| 福建省| 曲松县| 武城县| 珲春市| 泸州市| 平陆县| 灵璧县| 达州市| 铁岭县| 陵川县| 桐梓县| 康马县|