When you work with queries that involve a LEFT JOIN on a 1:n relation, you usually want to map the parent to a collection of its child elements. Imagine a simple example, a result set with two columns, daddy and kiddo (order by daddy). Try solving this problem with some pseudo-code before you read any further.

Quite commonly, result sets are processed in a simple while(rs.next()) {...} cycle. However, in this scenario, you would need some state variables (e.g. lastDaddy) as well as a post-cycle operation. Even if such contraption is error-free, it’s rather unreadable for your coworkers. I tried a different approach. In java, it should work with any valid ResultSet implementation. For the simple example, it looks like:

Map<String, Collection<String>> result = new HashMap<>();
boolean hadNext = rs.next();

while (hadNext) {
    String group = rs.getString("daddy");
    Collection<String>; elements = new ArrayList<>();

    do {
        elements.add(rs.getString("kiddo"));
    } while (hadNext = rs.next() && 
             group.equals(rs.getString("daddy")));

    result.put(group, elements);
}

The main advantage of this approach is readability. Result set is only iterated in the inner cycle, so you need to initialize first: hadNext = rs.next(). The outer cycle contains the whole life cycle of daddy (a great win for readability). The inner cycle quite interesting: I hardly ever use the do-while cycle, but here it shines. The first child must be read before iterating the result set any further. The condition causes the inner cycle to stop both when the parent changes and when the result set ends.

In most scenarios, two levels of hierarchy are still one too many (separate queries are used instead). However, this approach can handle even multiple levels of hierarchy while staying readable. In this case, all the inner cycles use a do-while cycle, matching the whole parental line of an element. Only the innermost cycle iterates the result set.

No votes yet.
Please wait...
  • Vlasec

    On a side note, the pattern might find its use outside databases. You can use it to process 1:n data from any source, e.g. CSV, provided that it is sorted.

    No votes yet.
    Please wait...
  • Tomáš Záluský

    Nice craftmanship trick, though I think the time to understand it is comparable to time to understand lastDaddy version. For similar situations, I use a sequence of classes based on builder pattern: group(rows).by(new DaddyGroupBuilder()), which concentrates on “what” rather than “how”. Also JDK8 stream collector could perhaps be utilized.

    Also, there exists alternative yet IMHO very elegant solution for Oracle:

    select t.daddy, cast(collect(t.kiddo order by t.kiddo) as sys.ku$_vcnt) as kiddos
    from t
    group by t.daddy
    order by t.daddy

    while (rs.next()) {
    String daddy = rs.getString(1);
    Array kiddosSqlArray = rs.getArray(2);
    Collection kiddos = new ArrayList(Arrays.asList((String[])kiddosSqlArray.getArray()));
    result.put(daddy,kiddos);
    }

    No votes yet.
    Please wait...
    • Vlasec

      Thanks for reading and commenting my article! Nice trick as well, but I guess other DBs aren’t capable of that (or they use different name for it). I came up with this solution while reading some legacy code and I only wanted to make it more readable.

      Of course, using new technologies would be nice – Stream API is a nice thing and whenever I work in Java 8, I use it extensively. Now imagine we have an immutable Daddy object that has unmodifiable collection of Kiddos inside. You could just map it all to Daddy objects and then collect all the fathers using Collectors.toList(), because the DB did the projection for you. In my case, it would work much worse.

      I think my approach thrives well on the fact the results are ordered. Thus, I can create Daddy in the body of the cycle, no builder needs to be passed elsewhere. (I can also create the Kiddo separately in a different method or class.) This all is made possible by the fact I use cycles driven by result data structure rather than the result set.

      No votes yet.
      Please wait...

Post Navigation