JMeter: Connect to PostGresSQL in JSR using groovy and then compare values from multiple tables in DB with API response

Question

Sorry for the long post, but I really need some guidance here. I need to compare values from an API response with the values from multiple tables in the DB.

Currently, I am doing it as follows:

1. Use a JDBC Connect Configuration to connect to Postgres DB and then use the JDBC Sampler to execute queries. I use it three times to query 3 different tables. I store this data in variables (lets call them DBVariables). Please see this image for current Jmeter setup. https://i.stack.imgur.com/GZJyF.png
2. In JSR Assertion, I have written code that takes data from various DBVariables and compares it against the API response.

However, my issue is the API response can have an array of records and then nested arrays inside each (please see API response sample below). And these array elements can be sorted in any order. This is where I have issues.

I was wondering what would be the most efficient way of writing this JSR Assertion to validate all data elements returned by the API are the same as what is in the DB.

I am very new to groovy, but I think if I can query the DB inside the JSR assertion (instead of using the JDBC sampler), then the comparison can be done by storing API response in a map and then the DBResponse in another map and sorting both and comparing the items. My questions are:

1. How can I connect to postgressql using groovy and then execute query statements in it? I have not done that before and was hoping if someone can provide a sample code.

2. How can I store API response and DB responses in Map and sort them and compare them in groovy?

The API response is of the following type:

    {
  "data":{
    "response":{
      "employeeList":[
        {
          "employeeNumber":"11102",
          "addressList":[
            {
              "addrType":"Home",
              "street_1":"123 Any street"
            },
            {
              "addrType":"Alternate",
              "street_1":"123 Any street"
            }
          ],
          "departmentList":[
            {
              "deptName":"IT"
            },
            {
              "deptName":"Finance"
            },
            {
              "deptName":"IT"
            }
          ]
        },
        {
          "employeeNumber":"11103",
          "addressList":[
            {
              "addrType":"Home",
              "street_1":"123 Any street"
            },
            {
              "addrType":"Alternate",
              "street_1":"123 Any street"
            }
          ],
          "departmentList":[
            {
              "deptName":"IT"
            },
            {
              "deptName":"Finance"
            },
            {
              "deptName":"IT"
            }
          ]
        }
      ]
    }
  }
}

Answer 1

1. Have you seen Working with a relational database chapter of Groovy documentation? Alternatively you can obtain a Connection instance from the JDBC Configuration Element like

   def connection = org.apache.jmeter.protocol.jdbc.config.getConnection('your-pool-name')
   

2. With regards to "sort" There is DefaultGroovyMethods class which provides sort() function for any "sortable" entity. With regards to "compare" - we don't know how the object from the database looks like hence cannot provide a comprehensive solution.

   Maybe an easier option would be converting the response from the JDBC Sampler to JSON using JsonBuilder and once you have 2 JSON structures use the library like JSONassert which doesn't care about order and depth

3. You haven't asked, but if you're "very new to groovy" maybe it worth extracting individual values from API using JSON Extractor, do the same for the database with the JDBC elements and compare individual JMeter Variables using Response Assertion?