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
LegoDao
Piece entity
public class Piece {
Lego entity