Groovy脚本与SoapUI：存储单查询返回的列数据到测试用例属性

Got it, let's get your Groovy script sorted out to properly save your SQL query results into test case properties. Here's a complete, robust version that handles both single and multi-row results, plus error handling to make debugging easier:

Complete Script (Supports Multi-Row Results) #

// Fetch the pre-defined query from your test case property
def query = testRunner.testCase.getPropertyValue("query")

// Initialize SQL connection using project-level DB properties (adjust if your setup differs)
def sql = groovy.sql.Sql.newInstance(
    testRunner.testCase.testSuite.project.getPropertyValue("DB_URL"),
    testRunner.testCase.testSuite.project.getPropertyValue("DB_USER"),
    testRunner.testCase.testSuite.project.getPropertyValue("DB_PASSWORD"),
    testRunner.testCase.testSuite.project.getPropertyValue("DB_DRIVER")
)

try {
    def rowNum = 1
    // Iterate over each row returned by the query
    sql.eachRow(query) { row ->
        // Store the entire row as a test case property (e.g., row1, row2, etc.)
        testRunner.testCase.setPropertyValue("row${rowNum}", row.toString())
        
        // Extract and store individual column values
        def firstName = row.firstName
        def secondName = row.secondName
        
        // Handle null values to avoid NullPointerExceptions
        testRunner.testCase.setPropertyValue("FirstName", firstName?.toString() ?: "")
        testRunner.testCase.setPropertyValue("SecondName", secondName?.toString() ?: "")
        
        rowNum++
    }
} catch (Exception e) {
    // Log errors for debugging
    log.error("Failed to execute SQL query: ${e.message}", e)
} finally {
    // Ensure the SQL connection is closed even if an error occurs
    if (sql) {
        sql.close()
    }
}

Key Improvements & Explanations #

• Flexible DB Connection: Uses project-level properties for database credentials/URL, so you don't have to hardcode sensitive data. Adjust these property names if your project uses different labels.
• Null Safety: The ?.toString() ?: "" syntax ensures that if a column value is null, we store an empty string instead of throwing a null pointer exception.
• Multi-Row Handling: If your query returns multiple rows, the script will create numbered rowN properties for each full row. Note that the FirstName and SecondName properties will be overwritten with each row—if you need to keep all individual values, rename them to FirstName${rowNum} and SecondName${rowNum}.
• Error Handling: The try-catch-finally block ensures the SQL connection is always closed, even if something goes wrong, and logs detailed errors to help you troubleshoot.

Simplified Version for Single-Row Queries #

If you know your query will always return exactly one row, you can skip the loop and use firstRow() for cleaner code:

def query = testRunner.testCase.getPropertyValue("query")
def sql = groovy.sql.Sql.newInstance(
    testRunner.testCase.testSuite.project.getPropertyValue("DB_URL"),
    testRunner.testCase.testSuite.project.getPropertyValue("DB_USER"),
    testRunner.testCase.testSuite.project.getPropertyValue("DB_PASSWORD"),
    testRunner.testCase.testSuite.project.getPropertyValue("DB_DRIVER")
)

try {
    def row = sql.firstRow(query)
    if (row) {
        testRunner.testCase.setPropertyValue("FirstName", row.firstName?.toString() ?: "")
        testRunner.testCase.setPropertyValue("SecondName", row.secondName?.toString() ?: "")
        testRunner.testCase.setPropertyValue("row1", row.toString())
    } else {
        log.warn("Query returned no results—setting default empty values")
        testRunner.testCase.setPropertyValue("FirstName", "")
        testRunner.testCase.setPropertyValue("SecondName", "")
    }
} catch (Exception e) {
    log.error("Failed to execute SQL query: ${e.message}", e)
} finally {
    sql?.close()
}

内容的提问来源于stack exchange，提问作者Vittorio Chierici