DoytoQuery中的关联查询方案示例详解

DoytoQuery关联查询方案 DoytoQuery 查询 2022-12-27 18:12:07 209人浏览独家记忆

Python 官方文档：入门教程 => 点击学习

摘要

目录1. 背景2. sql层的解决方案3. ORM应用层面的解决方案4. 小结 1. 背景 Java Persistence with Hibernate 在12.2.1小节使用

1. 背景

Java Persistence with Hibernate 在12.2.1小节使用如下例子描述 n+1查询问题:

List<Item> items = em.createQuery("select i from Item i").getResultList();
// select * from ITEM
for (Item item : items) {
    assertTrue(item.getBids().size() > 0);
    // select * from BID where ITEM_ID = ?
}

在这个例子中，每个bids集合的加载都需要执行一条额外的查询语句，当item有N条记录，一共就会执行N+1条查询语句：

SELECT * FROM item;
SELECT * FROM bid WHERE item_id = ?;
SELECT * FROM bid WHERE item_id = ?;
SELECT * FROM bid WHERE item_id = ?;
SELECT * FROM bid WHERE item_id = ?;

2. SQL层的解决方案

在本方案中，首先通过两个步骤对SQL语句加以改造，从SQL层面上解决这个问题。

使用关键字UNION ALL将N条查询语句合为一条语句，便将N+1次查询转化为了1+1次查询。

由于第二次查询的所有记录被一次性返回，而我们需要将Bid实体关联到相关的Item实体上，因此我们需要添加一个额外的item_id列以便进行实体关联。

以下是改造后的两条查询语句。

SELECT * FROM item;
SELECT ? AS item_id, b.* FROM bid b WHERE item_id = ? UNioN ALL
SELECT ? AS item_id, b.* FROM bid b WHERE item_id = ? UNION ALL
SELECT ? AS item_id, b.* FROM bid b WHERE item_id = ? UNION ALL
SELECT ? AS item_id, b.* FROM bid b WHERE item_id = ?;

When we want to query a bid list and every bid entity to carry its item, we can execute two query statements as follows:

SELECT * FROM bid;
SELECT ? AS bid_id, i.* FROM item i WHERE id IN (SELECT item_id FROM bid WHERE id = ?) UNION ALL
SELECT ? AS bid_id, i.* FROM item i WHERE id IN (SELECT item_id FROM bid WHERE id = ?) UNION ALL
SELECT ? AS bid_id, i.* FROM item i WHERE id IN (SELECT item_id FROM bid WHERE id = ?) UNION ALL
SELECT ? AS bid_id, i.* FROM item i WHERE id IN (SELECT item_id FROM bid WHERE id = ?);

Item和Bid之间的关系是典型的一对多/多对一关系。以上这一解决方案也可用于多对多关系。

3. ORM应用层面的解决方案

对于ORM层，我们需要想办法从表结构的信息中映射到第二条查询语句，Java中常用注解的方式。

上面的SQL语句中只有四个要素，两个表名item和bid，表bid中的外键列item_id和表item中的引用列id。其中，查询实体的表名是已知的，于是便只剩下三个要素。 DoytoQuery定义了一个注解@DomainPath来配置这三个要素，用以映射第二条查询语句。

@Target(FIELD)
@Retention(RUNTIME)
public @interface DomainPath {
    String[] value();
    String localField() default "id";
    String foreignField() default "id";
}

由于第二条查询语句中附加的id列仅用于实体赋值，因此我们将附加列的别名统一命名为 MAIN_ENTITY_ID。Item和Bid的类定义如下：

@Getter
@Setter
public class ItemView extends AbstractPersistable<Integer> {
    // other fields in Item
    // one-to-many
    // SELECT ? AS MAIN_ENTITY_ID, b.*
    //              FROM bid b           WHERE item_id = ? [UNION ALL ...]
    @DomainPath(value = "bid", foreignField = "item_id")
    private List<BidView> bids;
}
@Getter
@Setter
public class BidView extends AbstractPersistable<Integer> {
    // other fields in Bid
    // many-to-one
    // SELECT ? AS MAIN_ENTITY_ID, i.*
    //              FROM item i           WHERE id      IN (SELECT item_id FROM bid WHERE id = ?)  [UNION ALL ...]
    @DomainPath(value = "item", foreignField = "id", localField = "item_id")
    private ItemView item;
}

假设Item和CateGory之间的多对多关系存放于中间表CATEGORY_ITEM中，我们可以使用@DomainPath来定义如下实体，用以映射第二条查询语句：

@Getter
@Setter
public class ItemView extends AbstractPersistable<Integer> {
    // other fields in Item
    // many-to-many
    @DomainPath({"item", "~", "category"})
    private List<ItemView> items;
}
@Getter
@Setter
public class CategoryView extends AbstractPersistable<Integer> {
    // other fields in Category
    // many-to-many
    @DomainPath({"category", "item"})
    private List<ItemView> items;
}