How to map foreign key attribute to record in Java with JDBI

Question

Say my DB looks like this, presenting using POJO:

class A {
   long id; // auto-increment primary key
   String aAttribute;
}

class B {
   long id; // auto-increment primary key
   long aId; // foreign key of class A
   String bAttribute;     
}

How could I naturally map the DB records to class B using JDBI so the class B could contain the actual object of A instead of a foreign key to A:

class B {
   long id; // auto-increment primary key
   A a; // actual object of class A
   String bAttribute;    
}

Answer 1

One approach (there are others, also) is to use the JDBI @Nested annotation with a bean mapper. The annotation:

"...creates a mapper for the nested bean."

Place the annotation on the relevant setter (or getter). So, in your case that would be like this:

import org.jdbi.v3.core.mapper.Nested;
import org.jdbi.v3.core.mapper.reflect.ColumnName;

public class B {

    private long id; // auto-increment primary key
    private A a; // actual object of class A
    private String bAttribute;

    @ColumnName("b_id")
    public long getId() {
        return id;
    }

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

    public A getA() {
        return a;
    }

    @Nested
    public void setA(A a) {
        this.a = a;
    }

    @ColumnName("b_attribute")
    public String getBAttribute() {
        return bAttribute;
    }

    public void setBAttribute(String bAttribute) {
        this.bAttribute = bAttribute;
    }

}

I have also added @ColumnName annotations to disambiguate the otherwise identical column names between the two objects (and, presumably, the tables).

Here is class A:

package com.northcoder.jdbidemo;

import org.jdbi.v3.core.mapper.reflect.ColumnName;

public class A {

    private long id; // auto-increment primary key
    private String aAttribute;

    @ColumnName("a_id")
    public long getId() {
        return id;
    }

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

    @ColumnName("a_attribute")
    public String getAAttribute() {
        return aAttribute;
    }

    public void setAAttribute(String aAttribute) {
        this.aAttribute = aAttribute;
    }

}

A query therefore needs to use column aliases to match these annotations:

String sql = """
             select b.id as b_id, b.bAttribute as b_attribute, a.id as a_id, a.aAttribute as a_attribute
             from your_db.a as a
             inner join your_db.b as b
             on a.id = b.a_id;
             """;

jdbi.useHandle(handle -> {
    List<B> bees = handle
            .select(sql)
            .mapToBean(B.class)
            .list();
});

Each instance of class B in the resulting list will contain an instance of A (assuming the data exists in the database).