Database connection in SOAP UI

Database connection:

In order to show on how to operate database, here creating a sample to present through groovy script.

 

Structure:

1.set run properties script

2.load properties from external file

3.operating db

 

The First, we need to create a property file, for example: i create properties file named DB_TEST_Connectivity.properties, in this file,you can put some configuration information of db in it,as following:

 

url=jdbc:mysql://localhost:3306/test
driver=com.mysql.jdbc.Driver
username=root
password=*****

the Second, we will read data from properties fiel, so firstly need to find the path that properties file stored.

 //get test suite def ts=context.getProperty("#TestSuiteRunner#").currentTestCase.testSuite //set up path,environment,type ts.setPropertyValue("TYPE","DB") ts.setPropertyValue("ENVIRONMENT","TEST") ts.setPropertyValue("PATH","D:/Soap/DB_TEST/")

 

And then,we will load data from properties file and put them in TestSuite level so that can be shared by all of test case under test suite project, the script is following:

//get test suite def ts=context.getProperty("#TestSuiteRunner#").currentTestCase.testSuite //def ts=testRunner.testCase.testSuite def filename=ts.getPropertyValue("PATH")+ts.getPropertyValue("TYPE")+"_"+ts.getPropertyValue("ENVIRONMENT")+"_"+"Connectivity.properties" def props = new java.util.Properties() props.load( new java.io.FileInputStream(filename)) Enumeration e = props.keys() while (e.hasMoreElements()) { key = e.nextElement() val = props.get(key) ts.setPropertyValue(key, val) }

 

Lastly, we can create our sql to access database, the script is as follows:

//import groovy import com.eviware.soapui.support.SoapUIException import groovy.sql.Sql def ts=context.getProperty("#TestSuiteRunner#").currentTestCase.testSuite //def DBProperties=testRunner.testCase.getTestStepByName("Load DB Properties Script") //def ts=testRunner.testCase def sql=Sql.newInstance(ts.getPropertyValue("url"),ts.getPropertyValue("username"),ts.getPropertyValue("password"),ts.getPropertyValue("driver")) //select sql.eachRow("select * from mytest") { log.info("${it.id} "+"${it.username}") } //delete table try{ sql.execute("drop table person") }catch(Exception e){} //create new table sql.execute('''create table PERSON ( id integer not null primary key, firstname varchar(20), lastname varchar(20), location_id integer, location_name varchar(30) )''') //there are 2 ways to insert data //insert record to table sql.execute("insert into PERSON (id,firstname,lastname,location_id,location_name) values (1,'Aaron','Liu',1,'WH') ") //insert record to table using another way def people = sql.dataSet("PERSON") people.add( firstname:"Kobe", lastname:"Bryant", id:2, location_id:2, location_name:'Los Angeleas' ) //search multi-record def allPerson = sql.rows(" select * from PERSON") log.info(allPerson) log.info(allPerson[0]) sql.eachRow("select * from PERSON"){ row -> log.info(row.lastname) } //using eachRow method to search each row sql.eachRow("select * from person"){ log.info(it.id) }