How to avoid multiple streaming when using collect

Question

I have a combination of querying a database with jooq and post processing the result with the streams. However I feel that my code is not very readable and not concise enough. How can I improve my code in ways of better expressing my intent.

sql
    .select(field("USER_NAME", String.class))
    .from(table("CWD_USER"))
    .fetch()
    .stream()
    .map(f -> f.getValue(field("USER_NAME", String.class)))
    .collect(Collectors.groupingBy(s -> StringUtils.split(s, "-")[0], Collectors.counting()))
    .entrySet().stream()
    .sorted(new java.util.Comparator<Entry<String, Long>>() {
        @Override
        public int compare(Entry<String, Long> o1,
                Entry<String, Long> o2) {
            return o2.getValue().compareTo(o1.getValue());
        }
    })
    .forEach(e -> System.out.println(String.format("%13s: %3d", e.getKey(), e.getValue())));

First I have problems with the multiple streaming. I first stream the result from jooq then I stream the collected map. Also the comparator seems way to prominent. Sure I could make a class out of it, but maybe there is another solution.

Answer 1

I cannot say about JOOQ part, but the Stream API part looks fine. You have to collect intermediately to know the counts prior to sorting. Note that such comparator is already implemented in JDK: it's Map.Entry.comparingByValue(). You can use it (add Comparator.reverseOrder() parameter to sort in reverse order):

sql
    .select(field("USER_NAME", String.class))
    .from(table("CWD_USER"))
    .fetch()
    .stream()
    .map(f -> f.getValue(field("USER_NAME", String.class)))
    .collect(Collectors.groupingBy(s -> StringUtils.split(s, "-")[0], Collectors.counting()))
    .entrySet().stream()
    .sorted(Map.Entry.comparingByValue(Comparator.reverseOrder()))
    .forEach(e -> System.out.println(String.format("%13s: %3d", e.getKey(), e.getValue())));

Answer 2

Unless this is a drastically simplified version of a more complex query, I'd move all logic to SQL. The equivalent SQL query (using the Oracle dialect) is:

SELECT PREFIX, COUNT(*)
FROM (
  SELECT SUBSTR(USER_NAME, 1, INSTR(USER_NAME, '-') - 1) AS PREFIX
  FROM CWD_USER
) T
GROUP BY PREFIX
ORDER BY COUNT(*)

Or, with jOOQ:

sql.select(field("PREFIX", String.class), count())
   .from(
     select(substring(
       field("USER_NAME", String.class), 
       inline(1), 
       position(field("USER_NAME", String.class), inline("-")).sub(inline(1))
     ).as("PREFIX"))
     .from(table("CWD_USER"))
   )
   .groupBy(field("PREFIX", String.class))
   .orderBy(count())
   .fetch();