Checking unordered result sets from JDBC query
Hi all, I was looking for some advice on how to handle unsorted JDBC results. The below is an example of some query results, simplified a little to keep it short. We have rows that are grouped by the...
I ended up writing a script for this. Here it is current watchers and future googlers. Note that this solution ignored the Row element in both the results and the expected values since the rowNumber value isn't relevant. I hope this helps!
def allslurped = new XmlSlurper().parseText("""<Results>
<ResultSet fetchSize="0">
<Row rowNumber="1">
<UNITS>4000</UNITS>
<AMOUNT>12800</AMOUNT>
<DOCID>3131459</DOCID>
</Row>
<Row rowNumber="2">
<UNITS>6000</UNITS>
<AMOUNT>1160800</AMOUNT>
<DOCID>3131459</DOCID>
</Row>
<Row rowNumber="3">
<UNITS>-3100</UNITS>
<AMOUNT>899000</AMOUNT>
<DOCID>3131460</DOCID>
</Row>
<Row rowNumber="4">
<UNITS>300</UNITS>
<AMOUNT>-22500</AMOUNT>
<DOCID>3131460</DOCID>
</Row>
</ResultSet>
</Results>""")
def expected_row01 = """<Row rowNumber="1">
<UNITS>4000</UNITS>
<AMOUNT>12800</AMOUNT>
<DOCID>3131459</DOCID>
</Row>"""
def expected_row02 = """<Row rowNumber="2">
<UNITS>-3100</UNITS>
<AMOUNT>899000</AMOUNT>
<DOCID>3131460</DOCID>
</Row>"""
def expected_row03 = """<Row rowNumber="3">
<UNITS>6000</UNITS>
<AMOUNT>1160800</AMOUNT>
<DOCID>3131459</DOCID>
</Row>"""
def expected_row04 = """<Row rowNumber="4">
<UNITS>300</UNITS>
<AMOUNT>-22500</AMOUNT>
<DOCID>3131460</DOCID>
</Row>"""
def expected_rows = [new XmlSlurper().parseText(expected_row01),
new XmlSlurper().parseText(expected_row02),
new XmlSlurper().parseText(expected_row03),
new XmlSlurper().parseText(expected_row04)]
for(item in allslurped.ResultSet.Row) {
assert expected_rows.contains(item) : "Database has unexpected data: " + item
expected_rows.remove(item)
}
assert expected_rows.size() == 0 : "Expected row(s) not found: " + expected_rows
assert allslurped.ResultSet.Row.size() == 4 : "Database results are too long, expected 4"
