Nested objects jdbi 3 with bean mapper is null

Question

I have a child class with a member variable nested as defined below. However, when I run

val child = childRepo[1]

child.parent is null. How can I automatically fetch the nested member variable?

Models.kt

import org.jdbi.v3.core.mapper.Nested
data class Child(
        var id: Int = -1,

        @Nested
        var parent: Parent? = null,

        var created: Instant? = null
)

data class Parent(
        var id: Int = -1,
)

ChildRepo.kt

import org.jdbi.v3.sqlobject.config.RegisterBeanMapper
import org.jdbi.v3.sqlobject.statement.SqlQuery
@SqlQuery("""
        SELECT 
            c.id as c_id, c.created as c_created,
            p.id as p_id 
        FROM child as c
            INNER JOIN parent p on p.id = c.parent_id
        WHERE c.id = :id
        """)
@RegisterBeanMapper(value = Child::class, prefix = "c")
@RegisterBeanMapper(value = Parent::class, prefix = "p")
operator fun get(id: Int): Child?

Answer 1

You'll get the closest thing to your repository snippet if you also register a JoinRowMapper (e.g. with @RegisterJoinRowMapper) for the two classes. The return type of your get method will then be JoinRow, from which you can retrieve the two values.

There's more info and some examples here: https://jdbi.org/#_joins

If you're comfortable setting the prefix in the class definition, something that might be easier is putting @Nested("p") on the field parent of Child and just the following annotations on the repository method:

@SqlQuery("""
    SELECT 
        c.id as id, c.created as created,
        p.id as p_id 
    FROM child as c
        INNER JOIN parent p on p.id = c.parent_id
    WHERE c.id = :id
    """)
@RegisterBeanMapper(value = Child::class)

Answer 2

The prefixes in the registered mappers apply to top level beans. So your Child bean is correctly mapped using the "c" prefix. However, the "@Nested" annotation does not provide any additional information what columns to use for the nested bean. So JDBI tries to map columns with the "c" prefix onto the "Parent" bean, which does not work. Replace the @Nested annotation with @Nested("p") to signal JDBI that this nested bean uses a different prefix. However, as this is a nested Bean, JDBI uses a nested prefix (which is c_p). So select the columns for the parent as p_id as c_p_id.

This code works with JDBI 3.33.0 (where there are some changes to the bean mapping logic):

    @Test
    void testParentChild() {
        handle.execute("create table child (id integer not null, parent_id integer, created timestamp)");
        handle.execute("create table parent(id integer, created timestamp)");
        handle.execute("insert into parent (id, created) values (1, now())");
        handle.execute("insert into parent (id, created) values (2, now())");
        handle.execute("insert into parent (id, created) values (3, now())");

        handle.execute("insert into child(id, parent_id, created) values(1, 1, now())");
        handle.execute("insert into child(id, parent_id, created) values(2, 1, now())");
        handle.execute("insert into child(id, parent_id, created) values(3, 1, now())");
        handle.execute("insert into child(id, parent_id, created) values(4, 2, now())");
        handle.execute("insert into child(id, parent_id, created) values(5, 2, now())");
        handle.execute("insert into child(id, parent_id, created) values(6, 2, now())");
        handle.execute("insert into child(id, parent_id, created) values(7, 3, now())");
        handle.execute("insert into child(id, parent_id, created) values(8, 3, now())");
        handle.execute("insert into child(id, parent_id, created) values(9, 3, now())");

        ParentChildDao dao = handle.attach(ParentChildDao.class);

        Child c = dao.getChild(4);

        assertThat(c).isNotNull();
        assertThat(c.getParent()).isNotNull();
        assertThat(c.getParent().getId()).isEqualTo(2);

    }

    public static class Child {
        private int id;
        private Parent parent;
        private Instant created;

        public int getId() {
            return id;
        }

        public void setId(int id) {
            this.id = id;
        }

        public Parent getParent() {
            return parent;
        }

        @Nested("p")
        public void setParent(Parent parent) {
            this.parent = parent;
        }

        public Instant getCreated() {
            return created;
        }

        public void setCreated(Instant created) {
            this.created = created;
        }
    }

    public static class Parent {
        private int id;
        private Instant created;

        public int getId() {
            return id;
        }

        public void setId(int id) {
            this.id = id;
        }

        public Instant getCreated() {
            return created;
        }

        public void setCreated(Instant created) {
            this.created = created;
        }
    }

    public interface ParentChildDao {
        @SqlQuery("SELECT  c.id as c_id, c.created as c_created,p.id as c_p_id  FROM child as c INNER JOIN parent p on p.id = c.parent_id WHERE c.id = :id")
        @RegisterBeanMapper(value=Child.class, prefix="c")
        @RegisterBeanMapper(value=Parent.class)
        Child getChild(int id);
    }

Answer 3

I tried the suggestions from the other people here, but couldn't get it to work. What I finally ended up doing which made it work, was to remove all the @RegisterBeanMappers, select the child columns without a prefix (c.id as id etc), while still selecting the parent colums with p prefix (p.id as p_id). Finally, added a @Nested("p") on the parent.