querydsl - QueryDSL 上未声明的路径

Question

我的 QueryDSL 给了我一个异常(exception):

2014-10-26 02:12:00,013 DEBUG  [ExceptionsHandler] org.springframework.dao.InvalidDataAccessApiUsageException: Undeclared path 'rolloutAdmin'. Add this path as a source to the query to be able to reference it.; nested exception is java.lang.IllegalArgumentException: Undeclared path 'rolloutAdmin'. Add this path as a source to the query to be able to reference it.

我正在 QueryDSL 中尝试以下无法在 JPQL 中完成的查询的代码:

@Query("SELECT new com.nsn.nitro.project.data.jpa.domain.RolloutMeta(r, count(b.id) as btsNbAll, ifnull(sum(b.status = com.nsn.nitro.project.data.utils.BTSStatus.PLANNED), 0) as btsNbPlanned, ifnull(sum(b.status = com.nsn.nitro.project.data.utils.BTSStatus.COMPLETED), 0) as btsNbCompleted, ifnull(sum(b.status = com.nsn.nitro.project.data.utils.BTSStatus.COMPLETED), 0) * 100.0 / count(b.id) as btsNbPercentage) FROM Rollout r, RolloutAdmin ra, BTS b WHERE b.rollout.id = r.id AND r.id = ra.rollout.id AND ra.admin = :admin GROUP BY r.id")
public Page<RolloutMeta> findMetaByAdmin(@Param("admin") Admin admin, Pageable page);

这是完整的查询:

@Override
@Transactional(readOnly = true)
public Page<RolloutMeta> findMetaByAdmin(Admin admin, Pageable page) {
    JPAQuery query = new JPAQuery(rolloutRepository.getEntityManager());
    QRollout qRollout = QRollout.rollout;
    QRolloutAdmin qRolloutAdmin = QRolloutAdmin.rolloutAdmin;
    QAdmin qAdmin = QAdmin.admin;
    QBTS qBTS = QBTS.bTS;
    query.from(qRollout).innerJoin(qRolloutAdmin.rollout, qRollout).innerJoin(qRolloutAdmin.admin, qAdmin).innerJoin(qBTS.rollout, qRollout);
    BooleanBuilder builder = new BooleanBuilder();
    builder.and(qAdmin.eq(admin));
    query.where(builder)
    NumberExpression<Integer> statusPlanned = qBTS.status.when(com.nsn.nitro.project.data.utils.BTSStatus.PLANNED).then(new Integer(1)).otherwise(new Integer(0));
    NumberExpression<Integer> statusCompleted = qBTS.status.when(com.nsn.nitro.project.data.utils.BTSStatus.COMPLETED).then(new Integer(1)).otherwise(new Integer(0));
    NumberExpression<Integer> btsNbPlanned = statusPlanned.sum();
    NumberExpression<Integer> btsNbCompleted = statusCompleted.sum();
    NumberExpression<Integer> btsPercentage = statusCompleted.sum().divide(new Integer(100)).multiply(qBTS.count());
    query.orderBy(btsPercentage.desc());
    QRolloutMeta qRolloutMeta = new QRolloutMeta(qRollout, qBTS.count(), btsNbPlanned, btsNbCompleted, btsPercentage);
    List<RolloutMeta> resultList = query.list(qRolloutMeta);
    long total = resultList.size();        
    query.offset(page.getOffset());
    query.limit(page.getPageSize());
    resultList = query.list(qRolloutMeta);
    Page<RolloutMeta> rolloutMetas = new PageImpl<RolloutMeta>(resultList, page, total);
    return rolloutMetas;
}

然后我尝试将 qRolloutAdmin 放在 query.from(qRolloutAdmin) 中:

query.from(qRolloutAdmin).innerJoin(qRolloutAdmin.rollout, qRollout).innerJoin(qRolloutAdmin.admin, qAdmin).innerJoin(qBTS.rollout, qRollout);

它似乎有所改善，这次异常几乎相同，但在 bts 上:

2014-10-26 08:51:18,489 DEBUG  [ExceptionsHandler] org.springframework.dao.InvalidDataAccessApiUsageException: Undeclared path 'bTS'. Add this path as a source to the query to be able to reference it.; nested exception is java.lang.IllegalArgumentException: Undeclared path 'bTS'. Add this path as a source to the query to be able to reference it.

因此，我删除了 bts 上的内部连接以将其包含在构建器中:

query.from(qRolloutAdmin).innerJoin(qRolloutAdmin.rollout, qRollout).innerJoin(qRolloutAdmin.admin, qAdmin);
BooleanBuilder builder = new BooleanBuilder();
builder.and(qBTS.rollout.eq(qRollout)).and(qAdmin.eq(admin));
query.where(builder);

但它仍然给出与之前完全相同的异常:

2014-10-26 09:08:00,397 DEBUG  [ExceptionsHandler] org.springframework.dao.InvalidDataAccessApiUsageException: Undeclared path 'bTS'. Add this path as a source to the query to be able to reference it.; nested exception is java.lang.IllegalArgumentException: Undeclared path 'bTS'. Add this path as a source to the query to be able to reference it.

除了试图解决这个问题外，我还有一些问题:

1- Does the entity sitting in the from method have to be a child one ?
2- Is there any difference between doing an innerJoin and an equal in the builder ?

我正在运行 QueryDSL 3.5.0

编辑:然后我怀疑innerJoin上的参数顺序可能有意义，所以我尝试了这个，以从左到右的方式描绘:

query.from(qRollout);
query.innerJoin(qRollout, qRolloutAdmin.rollout);
query.innerJoin(qRolloutAdmin.admin, qAdmin);
query.innerJoin(qRollout, qBTS.rollout);

这次给出了一个不同的异常(exception):

2014-10-26 10:02:04,354 DEBUG  [ExceptionsHandler] org.springframework.dao.InvalidDataAccessApiUsageException: rolloutAdmin.rollout is not a root path; nested exception is java.lang.IllegalArgumentException: rolloutAdmin.rollout is not a root path

然后我在 from() 方法中尝试了多个实体:

query.from(qRollout, qRolloutAdmin, qBTS);

但异常(exception)情况保持不变。

EDIT2:我尝试使用 on() 方法指定 innerJoin，如 2.1.8 中所述。引用文档的一般用法部分:

query.from(qRollout);
query.innerJoin(qRolloutAdmin).on(qRolloutAdmin.rollout.eq(qRollout));
query.innerJoin(qRolloutAdmin).on(qRolloutAdmin.admin.eq(qAdmin));
query.innerJoin(qRollout).on(qBTS.rollout.eq(qRollout));

并得到了异常(exception):

2014-10-26 10:24:11,098 DEBUG  [ExceptionsHandler] org.springframework.dao.InvalidDataAccessApiUsageException: rolloutAdmin is already used; nested exception is java.lang.IllegalStateException: rolloutAdmin is already used

EDIT3:我在每个 innerJoin 方法上添加了一个 on() 方法:

QRolloutAdmin qRolloutAdmin = QRolloutAdmin.rolloutAdmin;
QRollout qRollout = QRollout.rollout;
QAdmin qAdmin = QAdmin.admin;
QBTS qBTS = QBTS.bTS;
query.from(qRollout);
query.innerJoin(qRolloutAdmin.rollout).on(qRolloutAdmin.rollout.eq(qRollout));
query.innerJoin(qRolloutAdmin.admin).on(qRolloutAdmin.admin.eq(qAdmin));
query.innerJoin(qBTS.rollout).on(qBTS.rollout.eq(qRollout));

但它仍然提示 rolloutAdmin 的未声明路径:

Caused by: java.lang.IllegalArgumentException: Undeclared path 'rolloutAdmin'. Add this path as a source to the query to be able to reference it.

EDIT4:我尝试了以下操作:

query.from(qRollout, qRolloutAdmin, qAdmin, qBTS);
query.innerJoin(qRolloutAdmin.rollout).on(qRolloutAdmin.rollout.eq(qRollout));
query.innerJoin(qRolloutAdmin.admin).on(qRolloutAdmin.admin.eq(qAdmin));
query.innerJoin(qBTS.rollout).on(qBTS.rollout.eq(qRollout));

尽管如此，它仍然给了我一个异常(exception):

Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.InvalidWithClauseException: with clause can only reference columns in the driving table [select rollout, count(bTS), sum(case when bTS.status = ?1 then ?2 else ?3 end), sum(case when bTS.status = ?4 then ?2 else ?3 end), (sum(case when bTS.status = ?4 then ?2 else ?3 end) / ?5) * count(bTS)
from com.nsn.nitro.project.data.jpa.domain.Rollout rollout, com.nsn.nitro.project.data.jpa.domain.RolloutAdmin rolloutAdmin, com.nsn.nitro.project.data.jpa.domain.Admin admin, com.nsn.nitro.project.data.jpa.domain.BTS bTS

解决连接问题的方法是删除内部连接语句并将它们替换为 and 子句，如下所示:

builder.and(qRolloutAdmin.rollout.id.eq(qRollout.id)).and(qRolloutAdmin.admin.id.eq(qAdmin.id)).and(qBTS.rollout.id.eq(qRollout.id));

编辑:问题已在以下位置解决:

QueryDSL Could not determine data type for searched case statement

Answer 1

JPA 查询中的连接更多是关于属性遍历而不是 SQL 连接。因此，您需要重写查询以确保所有路径都通过属性连接到根变量。

如果你想从 RolloutAdmin 开始，那么

query.from(qRolloutAdmin)
     .innerJoin(qRolloutAdmin.rollout, qRollout)
     .innerJoin(qRolloutAdmin.admin, qAdmin);

现在您可以使用 qRolloutAdmin , qRollout和 qAdmin在您的查询中。 qBTS尚未连接到属性树。

Does the entity sitting in the from method have to be a child one ?

它需要是一个根变量，没有子变量。

Is there any difference between doing an innerJoin and an equal in the builder ?

生成的 SQL 是不同的，并且可能会进行不同的优化。使用连接是首选方式。