Avoiding Multiple Streaming When Using Collection

Question

I have a combination of querying a database with jooq and then processing the result with threads. However, I feel that my code is not very readable and not concise enough. How can I improve my code to better express my intentions.

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()))); 

At first I have problems with multiple threads. First I pass the result from jooq and then I pass the assembled map. Also the comparator seems capable of being noticeable. Of course, I could make a class out of this, but maybe there is another solution.

Answer 1

I can’t say about the JOOQ part, but the Stream API part looks great. You must collect intermediate information to know the quantity before sorting. Please note that such a comparator is already implemented in the JDK: it is Map.Entry.comparingByValue() . You can use it (add the 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

If this is not a simplified version of a more complex query, I would move all the logic to SQL. Equivalent SQL query (using Oracle dialect):

 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();