Java QueryDSL 使用本机 SQL 连接和子查询

QueryDSL joins and subqueries with native SQL

提问by M. Lindenmann

I use Spring Data and QueryDSL 3.2.4 and I want to implement the following SQL query with QueryDSLs native SQL API:

我使用 Spring Data 和 QueryDSL 3.2.4，我想用 QueryDSLs 原生 SQL API 实现以下 SQL 查询：

WITH ORDER_GROUP AS (   -- TODO have to merge this subquery into the main query
    SELECT 
        ordergroup
       ,count(ID) AS nofOrdersPerGroup 
       ,MIN(priority) as minPriority
       ,MIN(requesteddeliverytime) as minRequestedDeliveryTime
       ,MIN(creationtime) as minCreationTime
      FROM ORDERHEADER hh
     group by orderGroup      
),
ALL_ORDERS AS (   -- TODO have to merge this subquery into the main query
    SELECT h.ordercode
      , h.ordergroup
      , h.priority
      , h.requesteddeliverytime
      , h.creationtime
      , h.statecode
      , (SELECT COUNT(ID)
           FROM orderposition p
          WHERE p.orderheaderid = h.ID
        ) AS nof_positions_per_order
      , CASE
            WHEN h.ordergroup IS NOT NULL
            THEN g.nofOrdersPerGroup
            ELSE 1
        END AS nof_orders_per_group
      , CASE
            WHEN h.ordergroup IS NOT NULL
            THEN g.minPriority
            ELSE h.priority
        END AS most_important_prio
      , CASE
            WHEN h.ordergroup IS NOT NULL
            THEN g.minRequestedDeliveryTime
            ELSE h.requesteddeliverytime
        END AS earliest_del_time
      , CASE
            WHEN h.ordergroup IS NOT NULL
            THEN g.minCreationTime
            ELSE h.creationtime
        END AS earliest_cre_time
       FROM ORDERHEADER h left outer join ORDER_GROUP g on h.ordergroup = g.ordergroup
      WHERE 1=1  -- TODO have to add filter clauses here
)
 SELECT ordercode
  , ordergroup
  , priority
  , requesteddeliverytime
  , creationtime
  , statecode
  , nof_positions_per_order
  , nof_orders_per_group
  , most_important_prio
  , earliest_del_time
  , earliest_cre_time
   FROM ALL_ORDERS
ORDER BY most_important_prio
  , earliest_del_time
  , earliest_cre_time
  , priority
  , requesteddeliverytime
  , creationtime;

The join is not on a FK column but on some arbitrary attribute of the subquery ORDER_GROUP. This subquery aggregates some min / max valuse on on OrderHeader for later use in sorting.

连接不在 FK 列上，而是在子查询 ORDER_GROUP 的某个任意属性上。此子查询在 OrderHeader 上聚合了一些最小/最大值，以便以后用于排序。

The query types are:

查询类型为：

package com.stoecklin.wms.entity;

import static com.mysema.query.types.PathMetadataFactory.*;

import com.mysema.query.types.path.*;

import com.mysema.query.types.PathMetadata;
import javax.annotation.Generated;
import com.mysema.query.types.Path;
import com.mysema.query.types.path.PathInits;


/**
 * QOrderHeader is a Querydsl query type for OrderHeader
 */
@Generated("com.mysema.query.codegen.EntitySerializer")
public class QOrderHeader extends EntityPathBase<OrderHeader> {

    private static final long serialVersionUID = 2006939562;
    public static final QOrderHeader orderHeader = new QOrderHeader("orderHeader");
    public final com.stoecklin.utility.database.QBaseEntity _super = new com.stoecklin.utility.database.QBaseEntity(this);
    public final DateTimePath<java.util.Date> actualDeliveryTime = createDateTime("actualDeliveryTime", java.util.Date.class);
    public final StringPath creationMode = createString("creationMode");
    //inherited
    public final DateTimePath<java.util.Date> creationTime = _super.creationTime;
    public final StringPath customerCode = createString("customerCode");
    public final StringPath customerOrderCode = createString("customerOrderCode");
    public final StringPath deliveryCode = createString("deliveryCode");
    public final StringPath deliveryNote = createString("deliveryNote");
    public final StringPath headerText = createString("headerText");
    public final NumberPath<Integer> hostId = createNumber("hostId", Integer.class);
    public final NumberPath<Long> id = createNumber("id", Long.class);
    //inherited
    public final DateTimePath<java.util.Date> lastUpdateTime = _super.lastUpdateTime;
    public final StringPath orderCode = createString("orderCode");
    public final StringPath orderGroup = createString("orderGroup");
    public final ListPath<OrderPosition, QOrderPosition> orderPositions = this.<OrderPosition, QOrderPosition>createList("orderPositions", OrderPosition.class, QOrderPosition.class, PathInits.DIRECT2);
    public final StringPath orderTypeCode = createString("orderTypeCode");
    public final StringPath priority = createString("priority");
    public final DateTimePath<java.util.Date> requestedDeliveryTime = createDateTime("requestedDeliveryTime", java.util.Date.class);
    public final StringPath setupType = createString("setupType");
    public final StringPath shippingMode = createString("shippingMode");
    public final StringPath stagingArea = createString("stagingArea");
    public final EnumPath<com.stoecklin.wms.enums.OrderHeaderState> stateCode = createEnum("stateCode", com.stoecklin.wms.enums.OrderHeaderState.class);
    public final StringPath stateReason = createString("stateReason");
    public final DateTimePath<java.util.Date> stateTime = createDateTime("stateTime", java.util.Date.class);
    //inherited
    public final NumberPath<Long> version = _super.version;

    public QOrderHeader(String variable) {
        super(OrderHeader.class, forVariable(variable));
    }

    public QOrderHeader(Path<? extends OrderHeader> path) {
        super(path.getType(), path.getMetadata());
    }

    public QOrderHeader(PathMetadata<?> metadata) {
        super(OrderHeader.class, metadata);
    }

}

and

和

package com.stoecklin.wms.entity;

import static com.mysema.query.types.PathMetadataFactory.*;

import com.mysema.query.types.path.*;

import com.mysema.query.types.PathMetadata;
import javax.annotation.Generated;
import com.mysema.query.types.Path;
import com.mysema.query.types.path.PathInits;


/**
 * QOrderPosition is a Querydsl query type for OrderPosition
 */
@Generated("com.mysema.query.codegen.EntitySerializer")
public class QOrderPosition extends EntityPathBase<OrderPosition> {

    private static final long serialVersionUID = 2091670278;
    private static final PathInits INITS = PathInits.DIRECT2;
    public static final QOrderPosition orderPosition = new QOrderPosition("orderPosition");
    public final com.stoecklin.utility.database.QBaseEntity _super = new com.stoecklin.utility.database.QBaseEntity(this);
    public final StringPath articleCode = createString("articleCode");
    //inherited
    public final DateTimePath<java.util.Date> creationTime = _super.creationTime;
    public final NumberPath<Integer> customerOrderPos = createNumber("customerOrderPos", Integer.class);
    public final NumberPath<Float> deliveredQuantity = createNumber("deliveredQuantity", Float.class);
    public final StringPath fromWarehouseCode = createString("fromWarehouseCode");
    public final StringPath hostData = createString("hostData");
    public final StringPath hostRef = createString("hostRef");
    public final NumberPath<Long> id = createNumber("id", Long.class);
    //inherited
    public final DateTimePath<java.util.Date> lastUpdateTime = _super.lastUpdateTime;
    public final StringPath lotCode = createString("lotCode");
    public final NumberPath<Float> missingQuantity = createNumber("missingQuantity", Float.class);
    public final QOrderHeader orderHeader;
    public final NumberPath<Integer> orderPos = createNumber("orderPos", Integer.class);
    public final StringPath ownerCode = createString("ownerCode");
    public final StringPath posText = createString("posText");
    public final NumberPath<Float> requestedQuantity = createNumber("requestedQuantity", Float.class);
    public final EnumPath<com.stoecklin.wms.enums.OrderPositionState> stateCode = createEnum("stateCode", com.stoecklin.wms.enums.OrderPositionState.class);
    public final StringPath stateReason = createString("stateReason");
    public final DateTimePath<java.util.Date> stateTime = createDateTime("stateTime", java.util.Date.class);
    public final StringPath toBePicked = createString("toBePicked");
    public final StringPath toLocation = createString("toLocation");
    //inherited
    public final NumberPath<Long> version = _super.version;

    public QOrderPosition(String variable) {
        this(OrderPosition.class, forVariable(variable), INITS);
    }

    public QOrderPosition(Path<? extends OrderPosition> path) {
        this(path.getType(), path.getMetadata(), path.getMetadata().isRoot() ? INITS : PathInits.DEFAULT);
    }

    public QOrderPosition(PathMetadata<?> metadata) {
        this(metadata, metadata.isRoot() ? INITS : PathInits.DEFAULT);
    }

    public QOrderPosition(PathMetadata<?> metadata, PathInits inits) {
        this(OrderPosition.class, metadata, inits);
    }

    public QOrderPosition(Class<? extends OrderPosition> type, PathMetadata<?> metadata, PathInits inits) {
        super(type, metadata, inits);
        this.orderHeader = inits.isInitialized("orderHeader") ? new QOrderHeader(forProperty("orderHeader")) : null;
    }

}

Now the questions:

现在的问题：

1. How do I have to do the join at the bottom of the ALL_ORDERS subquery? I already tried the following:

   QOrderHeader orderHeader = QOrderHeader.orderHeader;
   QOrderHeader orderHeaderGroup = new QOrderHeader("orderHeaderGroup");
   QOrderPosition orderPosition = QOrderPosition.orderPosition;
   
   List<Tuple> tuples = query.from(orderHeader)
           .leftJoin(orderHeader, orderHeaderGroup).on(orderHeader.orderGroup.eq(orderHeaderGroup.orderGroup))
           .list(
                   orderHeader.orderGroup,
                   orderHeader.id
                 );
   

1. 我必须如何在 ALL_ORDERS 子查询的底部进行连接？我已经尝试了以下方法：

   QOrderHeader orderHeader = QOrderHeader.orderHeader;
   QOrderHeader orderHeaderGroup = new QOrderHeader("orderHeaderGroup");
   QOrderPosition orderPosition = QOrderPosition.orderPosition;
   
   List<Tuple> tuples = query.from(orderHeader)
           .leftJoin(orderHeader, orderHeaderGroup).on(orderHeader.orderGroup.eq(orderHeaderGroup.orderGroup))
           .list(
                   orderHeader.orderGroup,
                   orderHeader.id
                 );
   

but this even won't compile because there is no matching method leftJoin available. The join is not on a FK column but on some arbitrary attribute. The subquery ORDER_GROUP

但这甚至无法编译，因为没有可用的匹配方法 leftJoin。连接不在 FK 列上，而是在某个任意属性上。子查询 ORDER_GROUP

1. How is the correlated subquery implemented which computes the nof_orders_per_group (7th item in SELECT list)? (I have no clue how tho do this :-))

1. 如何实现计算 nof_orders_per_group（SELECT 列表中的第 7 项）的相关子查询？（我不知道如何做到这一点:-)）

The SQL shown on the top is somewhat optimized for efficiency. For that reason I decided to use native SQL because we have subqueries all around.

顶部显示的 SQL 为效率做了一些优化。出于这个原因，我决定使用原生 SQL，因为我们到处都有子查询。

Any help is appreciated.

任何帮助表示赞赏。

回答by Timo Westk?mper

If you want to use Querydsl with SQL then you need to create the metamodel in a different way, which is described here http://www.querydsl.com/static/querydsl/3.2.4/reference/html/ch02s03.html

如果您想将 Querydsl 与 SQL 一起使用，那么您需要以不同的方式创建元模型，这里描述了http://www.querydsl.com/static/querydsl/3.2.4/reference/html/ch02s03.html

And to your more specific questions:

以及您更具体的问题：

1) FROM ORDERHEADER h left outer join ORDER_GROUP g on h.ordergroup = g.ordergroup

1) FROM ORDERHEADER h 左外连接 ORDER_GROUP g on h.ordergroup = g.ordergroup

from(h).leftJoin(g).on(h.ordergroup.eq(g.ordergroup))

2) CASE WHEN h.ordergroup IS NOT NULL THEN g.nofOrdersPerGroup ELSE 1 END AS nof_orders_per_group

2) 情况 WHEN h.ordergroup 不为 NULL THEN g.nofOrdersPerGroup ELSE 1 END AS nof_orders_per_group

new CaseBuilder()
    .when(h.ordergroup.isNotNull()).then(g.nofOrderPerGroup)
    .otherwise(1)

Concerning query construction, joins work different from JPA

关于查询构造，连接工作与 JPA 不同

Querydsl JPA

查询dsl JPA

query.join(entity.property, reference)

Querydsl SQL

查询dsl SQL

query.join(table).on(condition)

or alternatively

或者

query.join(table.fk, otherTable)