Nested object support for bean mapping?

[Patrick Lightbody]

Hi there!

Suppose I have a query like so:

SELECT b.id as b_id, b.title as b_title, a.id as a_id, a.name as a_name

FROM book b, author a

WHERE b.author_id = a.id

The class structure is:

class Book {

 - long id

 - String title

 - Author author

}

class Author {

 - long id

 - String name

}

What I want is a List<Book> where the references inside the Book objects point to a smaller number of Author objects. And I'm actually able to get this by writing my own RowReducer, so no problems there. But it makes me wonder: does Jdbi support this natively without the need to maintain my own RowReducer? Either by being smart about the join key (a.id in this case) or by being dumb and creating new Author objects even for the same record? That is, is there a convention or utility I can use to just get the BeanMapper for Book to also create the necessary Author objects based on the result set?

Thanks.

Patrick

[Matthew Hall]

Assuming you're using Jdbi 3.x, you want the @Nested annotation.

Cheers,

-Matt

--
You received this message because you are subscribed to the Google Groups "jDBI" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+uns...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jdbi/ffcd1479-16c6-42a6-a5a8-82e0aa949756%40googlegroups.com.

[Steven Schlansker]

> On Aug 5, 2019, at 11:23 AM, Matthew Hall <quali...@gmail.com> wrote:
>
> Assuming you're using Jdbi 3.x, you want the @Nested annotation.
>

Note that this won't actually "point to a smaller number of Author objects" -- you'll need to intern the aggregated Authors as well, otherwise you'd get a different Author (object) per row.

A simple approach would be to hold a Map of author ID to author as you collect, and then use "computeIfAbsent" to fill it in with each Author object only once.

> To view this discussion on the web visit https://groups.google.com/d/msgid/jdbi/CACwOQD2U7aAHps3Hwb_79MCp3zqsPkWC9JFfyZ_FNyY8yQ%3D5OQ%40mail.gmail.com.

[Patrick Lightbody]

A simple approach would be to hold a Map of author ID to author as you collect, and then use "computeIfAbsent" to fill it in with each Author object only once.

Yes, that's what I'm doing currently and it's working great. But it gets complicated when I'm joining against multiple tables, although even that I built a generic RowMapper that can do that. I just figured it was such a common use case there had to be something easier, and @Nested was it. I'm a dope for not seeing that earlier :) Now I just need to figure out how to get the prefix stuff working nicely with my existing code (see below) and with @Nested. It's turning out to be a little weird, but I probably am doing something wrong.

If anyone is curious, here is my monstrosity. You use it like so:

List<Book> books = query.reduceRows(new FlexJoinRowMapper<>(Book.class,
    new FlexJoinRowMapper.Joint<>("a_id", Long.class, Author.class, Book::setAuthor),
    new FlexJoinRowMapper.Joint<>("c_id", Long.class, Category.class, Book::setCategory)))
    .collect(Collectors.toList());

...

public class FlexJoinRowMapper<Source> implements RowReducer<FlexJoinRowMapper.Container<Source>, Source> {
  private Class<Source> sourceClass;
  private Joint<Source, ?, ?>[] joints;

  @SafeVarargs
  public FlexJoinRowMapper(Class<Source> sourceClass, Joint<Source, ?, ?>... joints) {
    this.sourceClass = sourceClass;
    this.joints = joints;

  }

  @Override
  public Container<Source> container() {
    Container<Source> container = new Container<>();
    for (Joint<Source, ?, ?> joint : joints) {
      container.linkMap.put(joint.keyColumn, new HashMap());
    }

    return container;
  }

  @Override
  public void accumulate(Container<Source> container, RowView rowView) {
    Source source = rowView.getRow(sourceClass);
    container.results.add(source);

    for (Joint<Source, ?, ?> joint : joints) {
      Map links = container.linkMap.get(joint.keyColumn);
      Object key = rowView.getColumn(joint.keyColumn, joint.key);
      if (key != null) {
        Object ref = links.computeIfAbsent(key, item -> rowView.getRow(joint.ref));
        joint.linker.link(source, ref);
      }
    }

  }

  @Override
  public Stream<Source> stream(Container<Source> container) {
    return container.results.stream();
  }

  static class Container<Source> {
    Collection<Source> results = new ArrayList<>();
    Map<String, Map> linkMap = new HashMap<>();
  }

  public static class Joint<Source, Ref, Key> {
    private Class<Ref> ref;
    private Class<Key> key;
    private ObjectLinker linker;
    private String keyColumn;

    public Joint(String keyColumn, Class<Key> key, Class<Ref> ref, ObjectLinker<Source, Ref> linker) {
      this.keyColumn = keyColumn;
      this.ref = ref;
      this.key = key;
      this.linker = linker;
    }
  }
}

public interface ObjectLinker<Source, Ref> {
  void link(Source source, Ref ref);
}

To view this discussion on the web visit https://groups.google.com/d/msgid/jdbi/23691DDB-6985-4D9E-9AF6-862F4EC2D081%40gmail.com.

[Steven Schlansker]

Yup, makes sense.
It is somewhat sucky that each project has to re-implement this themselves.

If you think you have a generalizable solution (and the code before looks like a reasonable start) I'd encourage you to start a discussion or PR on the GitHub page; it'd be nice to have a "out of the box" story for this.

> To view this discussion on the web visit https://groups.google.com/d/msgid/jdbi/CALzbjpauxfH%3DcOM2Axz_0Q26uNF74w3JOVJQeviqs-2G5Q3LPg%40mail.gmail.com.

[Patrick Lightbody]

Cool, I'll put something up there soon. One more question: if using the @Nested approach (with the disclaimer that I end up with duplicate objects that effectively represent the same data), how do Jdbi users typically handle column naming? Up until now, I had been naming the columns with prefixes like "a_", "b_", "c_" and then registering a BeanMapper with "a", "b", and "c" prefixes. That worked fine with my custom RowReducer. 

But for @Nested, I'm finding that I either need to remove the prefix from the "root" bean (Book in this case) or add a "b_a_" prefix to the Author fields in order for it all to work. The problem with that is that it makes it hard to reuse static Strings that contain reusable column names in BookDao, AuthorDao, CategoryDao, etc. 

Hopefully that makes sense, but let me know if you need some samples that demonstrate what I'm talking about. Mostly I'm trying to understand how people embrace DRY / avoid having the same columns captured multiple times with slightly different prefixes.

Patrick

To view this discussion on the web visit https://groups.google.com/d/msgid/jdbi/CF640FE1-AF90-4FC1-847E-B4697B8D50A6%40gmail.com.