Compare JSON & JDBC response using groovy
SOLVED- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Compare JSON & JDBC response using groovy
I am trying to compare json response with jdbc response. Please find screenshot for the responses. I am using below groovy to compare value by value, but its only picking the latest attribute from json response instead of comparing all.
As of now I am doing it only for 2 attributes - Transaction Id & Date.
import com.eviware.soapui.support.XmlHolder
import groovy.xml.XmlUtil
import groovy.util.XmlSlurper
import groovy.json.JsonSlurper
def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context)
/**
* Model object for comparing
*/
@groovy.transform.Canonical
class Model {
def transactionId
def transactionDate
//def storeId
//def marketingName
//def storeVisitToCity
//def transactionChanel
//def transactionValue
//def totalDiscount
/**
* this will acception jdbc row
* @Param row
* @return
*/
def buildJdbcData(row) {
row.with {
transactionId = TRANSACTION_ID
transactionDate = TRANSACTION_DATE_KEY
//storeId = STORE_ID
//marketingName = MARKETING_NAME
//storeVisitToCity = STORE_VISIT_TO_CITY
//transactionChanel = TRANSACTION_CHANNEL
//transactionValue = TRANSACTION_VALUE
//totalDiscount = TOTAL_DISCOUNT
// log.info transactionId
}
}
/**
* this will accept the json TagInfo
* @Param tagInfo
* @return
*/
def buildJsonData(tagInfo){
transactionId = tagInfo.transactionId
//log.info transactionId
transactionDate = tagInfo.transactionDate
// storeId = tagInfo.@storeId
// marketingName = tagInfo.@marketingName
// storeVisitToCity = tagInfo.@storeVisitToCity
// transactionChanel = tagInfo.@transactionChanel
// transactionValue = tagInfo.@transactionValue
// totalDiscount = tagInfo.@totalDiscount
}
}
/**
* Creating the jdbcResponse from the response received, using fixed value for testing
* If you want, you can assign the response received directly using below instead of current and make sure you replace the step name correctly
* def jdbcResponse = context.expand('${JdbcStepName#Response}')
*/
def jdbcResponse = context.expand( '${JDBC#ResponseAsXML}' )
//log.info jdbcResponse
//Parsing the jdbc and build the jdbc model object list
def results = new XmlSlurper().parseText(jdbcResponse)
//log.info results.ResultSet.Row[0]
def jdbcObjects = []
def jdbcDataObjects = []
results.ResultSet.Row.each { Row ->
jdbcDataObjects.add(new Model().buildJdbcData(Row))
}
log.info "JDBC Response -> "+ results
log.info "JDBC "+ jdbcDataObjects
/**
* Creating the jsonResponse from the response received, using fixed value for testing
* If you want, you can assign the response received directly using below instead of current and make sure you replace the step name correctly
* def jsonResponse = context.expand('${JsonStepName#Response}')
*/
def restResponse = context.expand('${Request#Response}')
//log.info restResponse
//Parsing the json and build the json model object list
def arrayOfTagInfo = new JsonSlurper().parseText(restResponse)
//log.info arrayOfTagInfo
def jsonDataObjects = []
log.info "JSON Response -> "+arrayOfTagInfo
arrayOfTagInfo.transactions.each{ tagInfo ->
jsonDataObjects.add(new Model().buildJsonData(tagInfo))
}
log.info "JSON "+jsonDataObjects
//sorting the Data before checking for equality
//jdbcDataObjects.sort()
//jsonDataObjects.sort()
if (jdbcDataObjects.size() != jsonDataObjects.size()) {
System.err.println("Jdbc resultset size is : ${jdbcDataObjects.size()} and Json result size is : ${jsonDataObjects.size()}")
}
assert jdbcDataObjects == jsonDataObjects, "Comparison of Jdbc and Json data is failed"
On running the step, it gives me the result , please see attached image.
it only compares the transaction_date and not transaction id.
Solved! Go to Solution.
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You say this code is only comparing the date but not the id, but why do you say that? From your screenshot it looks like the end result is an assertion like this:
assert ['20170308','20170330'] == ['20170308','20170330']
And then it is not throwing an assertion error... so isn't it already correct?
Otherwise, can you explain differently what the problem is?
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Ok, I saw your problem now. I simplify it to this to show you the solution:
xml = ''' <Results> <ResultSet> <Row> <TRANSACTION_ID>123</TRANSACTION_ID> <TRANSACTION_DATE_KEY>20182103</TRANSACTION_DATE_KEY> </Row> <Row> <TRANSACTION_ID>456</TRANSACTION_ID> <TRANSACTION_DATE_KEY>20182003</TRANSACTION_DATE_KEY> </Row> </ResultSet> </Results>'''
class Model { def transactionId def transactionDate def buildJdbcData(row) { transactionId = row.TRANSACTION_ID transactionDate = row.TRANSACTION_DATE_KEY } } new XmlSlurper().parseText(xml).ResultSet.Row.collect { new Model().buildJdbcData(it) }
The problem is that your "constructor" method isn't really a constructor. It's basically doing this:
groovy.slurpersupport.NodeChildren buildJdbcData(row) { transactionId = row.TRANSACTION_ID transactionDate = row.TRANSACTION_DATE_KEY
return transactionDate }
That's because a method with no return statement will always return the last thing it evaluated. What you probably meant was:
Model buildJdbcData(row) { transactionId = row.TRANSACTION_ID transactionDate = row.TRANSACTION_DATE_KEY
return this }
That gets us closer to the solution, but it's still not quite going to solve your problem.
a = new XmlSlurper().parseText(xml).ResultSet.Row.collect { new Model().buildJdbcData(it) } b = new XmlSlurper().parseText(xml).ResultSet.Row.collect { new Model().buildJdbcData(it) } assert a != b
But they should be the same right? Well since you haven't defined what Model.equals(Model) means, it's just coming back with "a is b", i.e. whether they are the same object, in this case they are not. You can fix it like this:
...
boolean equals(Model other) { (this.transactionId == other.transactionId) && (this.transactionDate == other.transactionDate) }
}
and now
assert a==b
But that's tedious for a lot of properties... instead I would just recommend that you do away with the whole Model class, and use a Map. A Map comes with Map.equals(Map) already defined.
import groovy.json.JsonSlurper xml = ''' <Results> <Row> <transactionId>123</transactionId> <transactionDate>20182103</transactionDate> </Row> <Row> <transactionId>456</transactionId> <transactionDate>20182003</transactionDate> </Row> </Results>''' json = ''' { "Row": [{ "transactionId":"123", "transactionDate":"20182103" }, { "transactionId":"456", "transactionDate":"20182003" }] }''' fromXml = new XmlSlurper().parseText(xml) .Row.collect { [transactionId: it.transactionId, transactionDate: it.transactionDate] } fromJson = new groovy.json.JsonSlurper().parseText(json) .Row.collect { [transactionId: it.transactionId, transactionDate: it.transactionDate] } assert fromXml == fromJson
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi thanks for your response
You are correct, it gives results :
assert ['20170308','20170330'] == ['20170308','20170330']
but it does not display the assertion it has performed for other attribute that is - transaction date.
so does it mean it is internally validating all the attributes and displaying only the latest in the result ?
I checked it by manually changing the value of ID in JDBC XML , it captures it correctly but still at the time of comparing it only compared date and not ID and passed the step.
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Regards,
Rao.
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi, I edited my response above after I saw the problem. Please check it above.
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Jhunt,
Thanks again.
I need to understand the information you have shared, i am just a beginner in groovy / scripting / coding.
Will get the understanding and get back to you.
Thanks much for your time.
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi nmrao,
Thanks for your response. Please find below
JSON Response
{
"customerId": "1234",
"yearMonth": "201703",
"transactions": [
{
"transactionId": "C111.158",
"transactionDate": 20170308,
"storeId": 1123,
"marketingName": "ABC Store",
"storeVisitToCity": "StoreCity",
"transactionChanel": "In-Store",
"transactionValue": 566,
"totalDiscount": -33
},
{
"transactionId": "C123.876",
"transactionDate": 20170330,
"storeId": 1111
"marketingName": "StoreName2",
"storeVisitToCity": "cityStore",
"transactionChanel": "In-Store",
"transactionValue": 117,
"totalDiscount": 0
}
]
}
--------------------------------------------------------
XML JDBC response
<Results>
<ResultSet fetchSize="10">
<Row rowNumber="1">
<TRANSACTION_ID>C111.158</TRANSACTION_ID>
<TRANSACTION_DATE_KEY>20170309</TRANSACTION_DATE_KEY>
<TRANSACTION_VALUE>566</TRANSACTION_VALUE>
<TOTAL_DISCOUNT>-33</TOTAL_DISCOUNT>
<STORE_ID>1123</STORE_ID>
<MARKETING_NAME>ABC Store</MARKETING_NAME>
<VISIT_TO_CITY>StoreCity</VISIT_TO_CITY>
<TRANSACTION_CHANNEL_DESC>In-Store</TRANSACTION_CHANNEL_DESC>
</Row>
<Row rowNumber="2">
<TRANSACTION_ID>C123.876</TRANSACTION_ID>
<TRANSACTION_DATE_KEY>20170330</TRANSACTION_DATE_KEY>
<TRANSACTION_VALUE>117</TRANSACTION_VALUE>
<TOTAL_DISCOUNT/>
<STORE_ID>1111</STORE_ID>
<MARKETING_NAME>StoreName2</MARKETING_NAME>
<VISIT_TO_CITY>cityStore</VISIT_TO_CITY>
<TRANSACTION_CHANNEL_DESC>In-Store</TRANSACTION_CHANNEL_DESC>
</Row>
</ResultSet>
</Results>
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
I tried to use below:
def jdbcResponse = context.expand('${JDBC#ResponseAsXML#(//Results)}')
def results = new XmlSlurper().parseText(jdbcResponse)
.ResultSet.Row.collect{[transactionId: it.TRANSACTION_ID,
transactionDate: it.TRANSACTION_DATE_KEY,
transactionValue: it.TRANSACTION_VALUE,
totalDiscount: (it.TOTAL_DISCOUNT).toString(),
storeId: it.STORE_ID,
] }
log.info "JDBC -> " + results
def restResponse = context.expand('${Request#Response}')
def arrayOfTagInfo = new JsonSlurper().parseText(restResponse)
.transactions.collect{ [transactionId: it.transactionId,
transactionDate: it.transactionDate,
transactionValue: it.transactionValue,
totalDiscount:(it.totalDiscount).toString(),
storeId: it.storeId,
] }
log.info "JSON -> " + arrayOfTagInfo
assert results == arrayOfTagInfo
Now this is not able to compare values of - totalDiscount because in the values are :
JSON
"totalDiscount": -32.4
"totalDiscount": 0
JDBC XML
<TOTAL_DISCOUNT>-32.4</TOTAL_DISCOUNT>
<TOTAL_DISCOUNT/> ---------------------------------------> because from database, value of totaldiscount is null for this particular tag.
Please help!
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try it like this:
def results = new XmlSlurper().parseText(jdbcResponse)
.ResultSet.Row
.collect{
[transactionId: it.TRANSACTION_ID,
transactionDate: it.TRANSACTION_DATE_KEY,
transactionValue: it.TRANSACTION_VALUE,
totalDiscount: it.TOTAL_DISCOUNT ?: 0,
storeId: it.STORE_ID]
}
This ?: means that if its null or empty string etc, it will be set to zero instead.
