I'm trying to write an object query with JDBI to deal with one-to-many relationship. As seen in the docs, reduceRows is the way to do that. But I got these errors blaming the constructor;
Update: keep one constructor for each entity.
I tested a code from the docs and I got these stack traces:
get1
Exception in thread "main" java.lang.IllegalArgumentException: Could not find column mapper for type 'join.AppJoin' of parameter 'c_id' for instance factory 'public join.AppJoin$Contact(join.AppJoin,int,java.lang.String)'
at org.jdbi.v3.core.mapper.reflect.ConstructorMapper.lambda$specialize0$3(ConstructorMapper.java:231)
at java.base/java.util.Optional.orElseThrow(Optional.java:408)
at org.jdbi.v3.core.mapper.reflect.ConstructorMapper.specialize0(ConstructorMapper.java:230)
at org.jdbi.v3.core.mapper.reflect.ConstructorMapper.specialize(ConstructorMapper.java:189)
at org.jdbi.v3.core.result.internal.RowViewImpl.rowMapperFor(RowViewImpl.java:63)
at org.jdbi.v3.core.result.internal.RowViewImpl.getRow(RowViewImpl.java:50)
at org.jdbi.v3.core.result.RowView.getRow(RowView.java:35)
at join.AppJoin.lambda$get1$0(AppJoin.java:70)
My code:
import org.jdbi.v3.core.Handle;
import org.jdbi.v3.core.Jdbi;
import org.jdbi.v3.core.mapper.reflect.BeanMapper;
import org.jdbi.v3.core.mapper.reflect.ColumnName;
import org.jdbi.v3.core.mapper.reflect.ConstructorMapper;
import org.jdbi.v3.core.result.RowView;
import org.jdbi.v3.sqlobject.SqlObjectPlugin;
import java.beans.ConstructorProperties;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import static java.util.stream.Collectors.toList;
public class AppJoin {
public static void main(final String[] args) {
Properties properties = new Properties();
properties.setProperty("username", "sa");
properties.setProperty("password", "");
Jdbi jdbi = Jdbi.create("jdbc:h2:mem:testDB", properties);
jdbi.installPlugin(new SqlObjectPlugin());
try (final var handle = jdbi.open()) {
String sqlc1 = "CREATE TABLE contacts ( \n" +
" id BIGSERIAL PRIMARY KEY, \n" +
" name VARCHAR(255)\n" +
")";
String sqlc2 = "CREATE TABLE phones ( \n" +
" id BIGSERIAL PRIMARY KEY, \n" +
" phone VARCHAR(255), \n" +
" contactId int, \n" +
" foreign key (contactId) references contacts(id) on delete cascade \n" +
")";
handle.createUpdate(sqlc1).execute();
handle.createUpdate(sqlc2).execute();
handle.createUpdate("insert into contacts (name) values (:name)")
.bind("name", "str")
.execute();
handle.createUpdate("insert into phones (phone, contactId) values (:phone, :contactId)")
.bind("phone", "1111111")
.bind("contactId", "1")
.execute();
// List<Contact> list = handle.select("select id, name from contacts")
// .mapToBean(Contact.class).list();
// System.out.println(list);
System.out.println(get1(handle));
}
}
private static Contact get1(Handle handle) {
return handle.createQuery("select contacts.id c_id, name c_name, "
+ "phones.id p_id, phones.phone p_phone "
+ "from contacts left join phones on contacts.id = phones.contactId "
+ "where contacts.id = :id")
.bind("id", 1)
.registerRowMapper(ConstructorMapper.factory(Contact.class, "c_"))
.registerRowMapper(ConstructorMapper.factory(Phone.class, "p_"))
.reduceRows(null, (contact, rowView) -> {
if (contact == null) {
contact = rowView.getRow(Contact.class);
}
if (rowView.getColumn("p_id", Integer.class) != null) {
contact.addPhone(rowView.getRow(Phone.class));
}
return contact;
});
}
public class Contact {
@ColumnName("id")
private final int id;
@ColumnName("name")
private final String name;
private List<Phone> phones;
public Contact(int id, String name) {
this.id = id;
this.name = name;
this.phones = new ArrayList<>();
}
public int getId() {
return id;
}
public String getName() {
return name;
}
public List<Phone> getPhones() {
return phones;
}
public void addPhone(Phone phone) {
phones.add(phone);
}
@Override
public String toString() {
return "Contact{" +
"id=" + id +
", name='" + name + '\'' +
", phones=" + phones +
'}';
}
}
public class Phone {
@ColumnName("id")
private final int id;
@ColumnName("phone")
private final String phone;
public Phone(int id, String phone) {
this.id = id;
this.phone = phone;
}
public int getId() {
return id;
}
public String getPhone() {
return phone;
}
@Override
public String toString() {
return "Phone{" +
"id=" + id +
", phone='" + phone + '\'' +
'}';
}
}
}
Gradle dependencies:
compile "com.h2database:h2:1.4.199"
compile group: 'org.jdbi', name: 'jdbi3-core', version: '3.12.2'
compile group: 'org.jdbi', name: 'jdbi3-sqlobject', version: '3.12.2'
Would appreciate any help/someone pointing me in the right direction! Is ti possible to do that in a Dao interface ?
First, JDBI requires always an empty constructor so for each entity you should have two constructors one empty and another one with your attributes, plus you should also register different mappers in your DAO using the annotation @RegisterBeanMapper(YourEntity.class).
Here's a working example:
main method
public static void main(String[] args) {
Jdbi jdbi = Jdbi.create("jdbc:h2:mem:test");
jdbi.installPlugin(new SqlObjectPlugin());
try (Handle handle = jdbi.open()) {
LegoDao dao = handle.attach(LegoDao.class);
dao.createTables();
dao.insertLegoSetWithTags(new Lego("75211", 2018));
dao.insertLegoSetWithTags(
new Lego("21034", 2017)
.addTag(new Piece("1", "vis", "21034"))
.addTag(new Piece("2", "wheel", "21034"))
);
dao.getLegoSetWithTags("21034").ifPresent(System.out::println);
System.out.println();
dao.listLegoSetsWithTags().forEach(System.out::println);
}
}
LegoDao
public interface LegoDao extends SqlObject {
String SELECT_ALL =
"SELECT l.number l_number, l.year l_year, " +
"lp.number lp_number, lp.tag lp_tag, lp.legoid lp_legoid \n" +
"FROM lego l LEFT OUTER JOIN lego_pieces lp ON l.number = lp.legoid ";
@SqlUpdate("CREATE TABLE lego (\n" +
" number VARCHAR PRIMARY KEY,\n" +
" year INTEGER NOT NULL\n" +
" )"
)
void createLegoSetTable();
@SqlUpdate(" CREATE TABLE lego_pieces(\n" +
" number VARCHAR,\n" +
" tag VARCHAR,\n" +
" legoid VARCHAR,\n" +
" PRIMARY KEY (number),\n" +
" FOREIGN KEY (legoid) REFERENCES lego ON DELETE CASCADE\n" +
" )"
)
void createTagsTable();
default void createTables() {
createLegoSetTable();
createTagsTable();
}
@SqlUpdate("INSERT INTO lego VALUES (:number, :year)")
void insertLegoSet(@BindBean Lego lego);
@SqlBatch("INSERT INTO lego_pieces(number, tag, legoid) VALUES (:tag.number, :tag.tag, :legoid)")
void insertLegoSetTags(@Bind("legoid") String legoid, @BindBean("tag") Set<Piece> pieces);
@SqlUpdate("INSERT INTO lego_pieces(number, tag, legoid) VALUES (:number, :tag, :legoid)")
void insertBean(@BindBean Piece piece);
default void insertLegoSetWithTags(Lego lego) {
insertLegoSet(lego);
insertLegoSetTags(lego.getNumber(), lego.getPieces());
}
@SqlQuery("SELECT * FROM lego WHERE number = :number")
Optional<Lego> getLegoSet(@Bind("number") String number);
@SqlQuery("SELECT * FROM lego ORDER BY number")
List<Lego> listLegoSets();
default Optional<Lego> getLegoSetWithTags(String number) {
return getHandle().createQuery(SELECT_ALL + " WHERE l.number = :number")
.bind("number", number)
.registerRowMapper(BeanMapper.factory(Lego.class, "l"))
.registerRowMapper(BeanMapper.factory(Piece.class, "lp"))
.reduceRows(new Reducer()).findFirst();
}
default List<Lego> listLegoSetsWithTags() {
return getHandle().createQuery(SELECT_ALL)
.registerRowMapper(BeanMapper.factory(Lego.class, "l"))
.registerRowMapper(BeanMapper.factory(Piece.class, "lp"))
.reduceRows(new Reducer()).collect(Collectors.toList());
}
class Reducer implements LinkedHashMapRowReducer<String, Lego> {
@Override
public void accumulate(Map<String, Lego> map, RowView rowView) {
Lego lego = map.computeIfAbsent(
rowView.getColumn("l_number", String.class),
id -> rowView.getRow(Lego.class));
if (rowView.getColumn("lp_tag", String.class) != null) {
Piece piece = rowView.getRow(Piece.class);
lego.addTag(piece);
}
}
}
}
Piece entity
public class Piece {
private String number;
private String tag;
private String legoId;
public Piece() {
}
public Piece(String number, String tag, String legoId) {
this.number = number;
this.tag = tag;
this.legoId = legoId;
}
// getter + setter
}
Lego entity
public class Lego {
private String number;
private int year;
private Set<Piece> pieces = new HashSet<>();
public Lego() {
}
public Lego(String number, int year) {
this.number = number;
this.year = year;
}
public Lego addPiece(Piece piece) {
pieces.add(piece);
return this;
}
// getter + setter for all attreibutes exept pieces
}