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 DOCID column but they can come in any order other than that. Is there a way to check that a row exists in the result set easily?
For example I need to be able to check that there is a row that has UNITS of 4000, AMOUNT of 12800 and DOCID of 3131459 but that row can appear in any order in the result set so I'm drawing a blank on a way to do this. Any advice is appreciated.
<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>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"
