Java vs SQL when fetching data

Question

I have a table where I have saved some parameters which act as logic for deciding which "receiver" to choose. I have illustrated the content / data of this table below:

table content

For selecting the correct receiver I used the following SQL:

SELECT *
FROM tableOfReceivers
WHERE client = 'DU'
      AND (country = countryOfDestination OR '*' = countryOfDestination)
      AND (
        (packages >= minNumberOfPackages OR minNumberOfPackages IS NULL) AND
        (packages <= maxNumberOfPackages OR maxNumberOfPackages IS NULL)
      )
      AND (
        (weight >= minNetWeight OR minNetWeight IS NULL) AND
        (weight <= maxNetWeight OR maxNetWeight IS NULL)
      )
ORDER BY priority;

The variables for above SQL is:

country = BE
packages = 5
weight = 50

The SQL works fine and I get the result I want. But I need to translate this into Java and when doing so I feel that it's over-complicated and I'm thinking that there has to be an easier what to do this.

Here is the Java code that I'm currently using:

ArrayList<tableOfReceivers > listOfReceivers = getPersistenceManager().getQueryManager().queryAll(
            tableOfReceivers.class, "this.client = ?1", "this.client", new Object[] { client }, 0);

ArrayList<tableOfReceivers> result = new ArrayList<tableOfReceivers>();

for (tableOfReceivers  rec : listOfReceivers) {
    if ((StringUtils.equals(countryOfDestination, rec.getCountryOfDestination())
            || StringUtils.equals("*", rec.getCountryOfDestination()))) {

        if (((numberOfPackages >= nvl(rec.getMinNumberOfPackages(), 0))
                || rec.getMinNumberOfPackages() == null)
                && (numberOfPackages <= nvl(rec.getMaxNumberOfPackages(),0))
                || rec.getMaxNumberOfPackages() == null) {

            if (((totalNetWeight >= nvl(rec.getMinNetWeight(),0)) || rec.getMinNetWeight() == null)
                    && (totalNetWeight <= nvl(rec.getMaxNetWeight(),0)) || rec.getMaxNetWeight() == null) {
                result.add(rec);
            }
        }
    }
}

Collections.sort(result, new Comparator<tableOfReceivers >() {

    @Override
    public int compare(tableOfReceivers  o1, tableOfReceivers  o2) {
        return o1.getPriority().compareTo(o2.getPriority());
    }
});

System.out.println("Receiver selection result is:" + "\n");
if(!result.isEmpty()){
    System.out.println(result.get(0).getReceiver() + "\n");
}

}

public static Integer nvl(Integer value, Integer alternateValue) {
    if (value == null)
        return alternateValue;
    return value;
}

So, is there an easier and better way to fetch what I need using only Java here?

Thanks!

Why do you only want to use Java? SQL can use indexes and other information to more effectively query the database. — Colonel Thirty Two
– Colonel Thirty Two, Commented Oct 18, 2016 at 12:31

Peter Lawrey · Accepted Answer · 2016-10-18 12:44:36Z

1

I would use Java 8

List<tableOfReceivers> result = 
    listOfReceivers.stream()
                   .filter(rec -> countryOfDestination.equals(rec.getCountryOfDestination())
                                  || "*".equals(rec.getCountryOfDestination())
                   .filter(numberOfPackages >= nvl(rec.getMinNumberOfPackages(), numberOfPackages ))
                   .filter(numberOfPackages <= nvl(rec.getMaxNumberOfPackages(), numberOfPackages ))
                   .filter(totalNetWeight >= nvl(rec.getMinNetWeight(), totalNetWeight))
                   .filter(totalNetWeight <= nvl(rec.getMaxNetWeight(), totalNetWeight))
                   .sort(Comparator.comparing(rec -> rec.getPriority()))
                   .collect(Collectors.toList());

Or you could use a helper method like in

public static boolean in(int value, Integer min, Integer max) {
    return (min == null || min <= value) &&
           (max == null || max >= value);
}

public static boolean countryMatch(String a, String b) {
    return "*".equals(a) || "*".equals(b) || a.equals(b);
}

List<tableOfReceivers> result = 
    listOfReceivers.stream()
                   .filter(rec -> countryMatch(countryOfDestination, rec.getCountryOfDestination()))
                   .filter(rec -> in(numberOfPackages, rec.getMinNumberOfPackages(), rec.getMaxNumberOfPackages())))
                   .filter(rec -> in(totalNetWeight, rec.getMinNetWeight(), rec.getMaxNetWeight(), totalNetWeight))
                   .sort(Comparator.comparing(rec -> rec.getPriority()))
                   .collect(Collectors.toList());

edited Oct 18, 2016 at 12:44

answered Oct 18, 2016 at 12:38

Peter Lawrey

535k83 gold badges770 silver badges1.2k bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

AxelH Over a year ago

This is the power of the Stream ! But what about the perfs ?

Mattias Wärn Over a year ago

That's awesome and thanks for the help! I need to catch up on those streams.

Peter Lawrey Over a year ago

@AxelH in general perf is the same as loops for warmed up code. The sorting can be a bit slower but rarely enough to matter.

AxelH Over a year ago

@PeterLawrey, I will stop the spam here ;) I will check about it, this seems really well made. I hope they will think about upgrading Java one day at the office... Thanks for the info !

Peter Lawrey Over a year ago

@AxelH one of the cool features of the Stream API is you can use parallelStream() and it will use all the CPUs of your machine (possibly without changing any other code)

|

Collectives™ on Stack Overflow

Java vs SQL when fetching data

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related