Scripting in ReadyAPI Virtualization for Dynamic Mocks

Here's an example on mapping values from a data source into a response message. This example reads in an Excel file, then uses a request parameter as a key value to find the values of a row of data in the Excel spreadsheet. 

There's another example on the SoapUI documentation that discusses how to do this with a database connection.  You can navigate here for that example.

This script is broken down into three sections. 
The first section reads in the request parameter that we'll use as our key. Request parameters can be accessed with the mockRequest.getRequest().getQueryString() methods. I take the output of this method and tie it to a string. You'll want to do some parsing on the string to isolate the exact value you want to use. 

The second section then opens a workbook in Excel and looks for the row of data based on our key (the "q" property). The POI libraries make it fairly easy to work with Excel workbooks, and there's lots of resources online that can explain how to use the libraries better than I can. All we're doing here is opening the workbook and scanning each row to see if we find a row of data that matches our key. 

The third section triggers if we find a row of data that matches our key. I use a switch statement here to map the values of the row to a property. The main thing to note is that we use the  requestContext.value format to assign the values to a virtual service property. 

Here's the script I use: 

//Read in excel libraries
import org.apache.poi.ss.usermodel.*
import org.apache.poi.xssf.usermodel.XSSFWorkbook

//================================================================//
//Part 1. Read in request parameters. 
//================================================================//

// Get the query string of the request, isolate the "q" parameter
// In this example, the request string looks like: "q=CityName&apikey=abc1234"
def inputString = mockRequest.getRequest().getQueryString()
def inputString2 = mockRequest.getRequest()
log.info inputString2
def q = null
//split query string
def keyValuePairs = inputString.split("&")
keyValuePairs.each { pair ->
    def key = pair.split("=")[0]
    def value = pair.split("=")[1]
    if (key == "q") {
        q = value
    }
}

//================================================================//
//Part 2. Open Workbook. Scan for row of data 
//================================================================//

// Specify the path to your Excel file
def filePath = "<path to your file here>"
// Specify the value to search in the first column
def searchValue = q

// Load the Excel file
def file = new File(filePath)
def workbook = new XSSFWorkbook(file)

// Assuming the data is in the first sheet, you can change the sheet index if needed
def sheet = workbook.getSheetAt(0)

// Find the row with the matching search value
def matchedRow = null

for (Row row : sheet) {
    def cellValue = row.getCell(0)?.toString()
    if (cellValue == searchValue) {
        matchedRow = row
        break
    }
}

//================================================================//
//Part 3. Map cells of the row to response variables. Close workbook.
//================================================================//
if (matchedRow != null) {
    for (Cell cell : matchedRow) {
        int columnIndex = cell.getColumnIndex()
        switch (columnIndex) {
            case 0:
                requestContext.cityCell = cell.toString()
                break
            case 1:
                requestContext.countryCell = cell.toString()
                break
            case 2:
               requestContext.abbrCell = cell.toString()
                break
            default:
                break
        }
}
}else {
    //Do Something 
}

// Close the workbook
workbook.close()

The response then looks like this: 

{
  "Response": "This is a dynamic response",
  "Location":"${cityCell}",
  "Country" : "${countryCell}",
  "Country Code" : "${abbrCell}"
}

My example uses a spreadsheet which provides responses for the 50 most populated cities. As long as we hit a city on this list we'll return a response such as this:

The dataset that I'm using in this example looks like this: