How to compare two datasets to check for missing rows which don't exist in the other dataset?

[M Richardson]

I have built a method which takes two datasets: dataset1 and retirementSimpleData. It matches the two datasets based on a primary key/number, defined as cnum, in the code below. I wanted to return the value of the difference between the getAssets value and the getSums value, and this is working, except for one little problem.

Some of the cnums that exist in dataset1 don't exist in retirementSimpleData. Similarly, some cnums which may exist in retirementSimpleData may not exist in dataset1. This is resulting in no data being returned for that cnum

I would like to implement two passes at the end which check in one direction to see if I missed anything. The second pass would check in the opposite direction. However, not sure how I would go about implementing this.

 public void getReportData(int index) {

        String date1 = Util.dateTimeToShortString(reportDate);
        String date2 = reportDao.getPreviousRetirementDate(date1);

        List<SurveyCompareAssetsCheckData> dataset1 = reportDao.getSurveyCheckCompareAssetsData(date1);

        List<RetSurveyAssets> retirementSimpleData = reportDao.findRetSurveyByDate(date1);

        for (SurveyCompareAssetsCheckData surveyCompareAssetsCheckData : dataset1) {
            for (RetSurveyAssets surveyCompareAssetsCheckData2 : retirementSimpleData) {
                if (surveyCompareAssetsCheckData.getCnum() == surveyCompareAssetsCheckData2.getCnum()) {
                    surveyCompareAssetsCheckData.setRetirementsimple(surveyCompareAssetsCheckData2.getSums());
                    surveyCompareAssetsCheckData.setDifference(surveyCompareAssetsCheckData.getAssets() - surveyCompareAssetsCheckData2.getSums());

Caveat: dataset1 and retirementSimpledata both use existing SQL pulls which I am not allowed to touch, otherwise I would have simply defined new SQL for these methods in my "DAOImpl." Therefore, I have to work with the data I am getting, and programmatically check for this.

[Joe Areeda]

The operation you need are available in the Set interface.  Classes TreeSet or HashSet have addAll and removeAll methods.

One approach would be to build 2 sets od cnums from the lists  something like

HashSet<Long> assets = getCnumSet(dataset1);
HashSet<Long> retirementAssets = getCnumSet(retirementSimpleData);

HashSet<Long> assetNoRetirement = new HashSet<Long>(assets);
assetNoRetirement,removeAll(retirementAssets);

HashSet<Long> retirementNoAsset = new HashSet<Long>(retirementAssets);
assetNoRetirement,removeAll(assets);

Note I didn't compile or test it, so consider it pseudo-code

Joe

[Junilu Lacar]

Another approach would be to use the appropriate join query and let the database do the work for you. Then your Java code can just process the results that came back from the DB Query.

[M Richardson]

Junilu Lacar wrote:Another approach would be to use the appropriate join query and let the database do the work for you. Then your Java code can just process the results that came back from the DB Query.

Yep! That would make more sense, but in this example, I wasn't allowed to touch any of the queries and was told to simply re-use what is there... which made it trickier.