我正在尝试用JDBI编写一个对象查询来处理一对多关系。正如在文档中所看到的,reducerows
就是这样做的方法。但是我把这些错误归咎于构造函数;
更新:为每个实体保留一个构造函数。
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)
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 + '\'' +
'}';
}
}
}
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'
首先,JDBI总是需要一个空构造函数
,所以对于每个实体,您应该有两个构造函数一个是空的,另一个是带有您的属性的,另外,您还应该使用注释@RegisterBeanMapper(YourEntity.class)
在DAO中注册不同的映射器。
下面是一个工作示例:
主要方法
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);
}
}
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);
}
}
}
}
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
}
乐高实体
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
}
我有一个关于Hibernate ManyToMany映射的问题。我有两个类 A 和 B,它们之间的映射是由 Hibernate 解析的 ManyToMany 映射: 用户和组的外键是“A_id”和“B_id”。联接表称为A_B。 现在,我想加上C。我想A_B与C有关系,与C创建多对多关系,A_B我可以称之为A_B_C。 编辑:所以我会创建一个_B实体,A和B和A_B的关系为2 @OneToMany
我试图用JDBI编写一个对象查询,它将从左表中抓取一个完整的行,并将右表中所有匹配的行作为List(一个作者可以有很多本书)连接起来。 作者 图书编号、标题、作者、类别、创建日期、更新日期 我要创建的最终对象的结构如下所示: 书在哪里: 这是我正在尝试的查询(不按原样抓取图书列表) 如果有人能为我指出正确的方向,我将不胜感激! 谢谢
非常感谢所有的帮助, · ·提尔曼
运行CRON JOB时,得到一个异常,说连接已经关闭。应用程序正在dropwizard上运行,以下是数据库配置 请帮助解决问题。
我尝试通过应用服务器Glassfish和JPA连接到mysql数据库。 我的persistence.xml如下所示: 有什么问题?
你能帮助我如何使用Kafka流实现这一点吗? 场景:将订单数据的所有发票分组。在实时流媒体中,可能会延迟接收发票。因此,我们希望在加入之前等待20分钟将所有发票分组。 示例:订单“x”有3张发票,预计将在20分钟内收到。 预期输出:订单和3张发票应作为输出主题中的单个数据提供。 我们有下面的拓扑结构来实现这一点。 > 我们正在根据订单密钥对发票进行分组。我们设置了20分钟的翻滚窗口 将订单数据与生