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;    
}

score 1 · Accepted Answer · answered Feb 07 '23 at 21:02

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).

Awesome! Per doc, "There is a limitation that only one type of mapper can be nested at a time". Say beside entry A,the class B has another foreign key called c_id, and I would like to get the actual entry of table C using the c_id. What would be a good option here? A separate sql command to get C entry and explicitly call B's setC()? — EzyHoo, Feb 07 '23 at 21:36
Yes, the docs do say that - but I am not sure what the context of that sentence is. I think it refers to the mapper created by JDBI behind the scenes _for that field_. You can certainly add a new class `C` - and then in class `B` add a field `private C c;` - and then add a second usage of `@Nested` in `B` for that new field. I have an example of that, and it works as expected. You should not need a second SQL query to populate `C` into `B`. — andrewJames, Feb 07 '23 at 21:55
Any suggestion for writing the class B into the database then? Since the actual object is stored instead of foreign key, which is different with db schema. I cannot do BindBean(). What would be the best way to do store the POJO into DB? — EzyHoo, Feb 08 '23 at 17:16
That is worth asking as a brand new question, I think. You will get more viewers/eyeballs and better answers that way! — andrewJames, Feb 08 '23 at 17:23

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

1 Answers1