英文:
How to use Querydsl to construct complex predicate that involves multiple tables?
问题
我正在尝试使用Querydsl从表中获取一些结果。到目前为止，这是我尝试过的内容 -
假设有5个名为T1..T5的实体。我正在尝试在Querydsl中执行这个SQL查询 -
SELECT T1.*
FROM T1, T2, T3, T4, T5
WHERE T1.A = T2.A
AND T2.B = T5.B
AND T4.C = T2.C
AND T1.B = 1234;
我尝试了以下方法，但Hibernate查询一直在运行，似乎没有结束。
booleanBuilder.and(JPAExpressions.select(qT1).from(qT1, qT2, qT3, qT4, qT5)
.where(
qT1.a.eq(qT2.a)
.and(qT2.b.eq(qT5.b))
.and(qT4.c.eq(qT2.c))
.and(qT1.b.eq(1234)))
.exists());
我正在使用扩展了QuerydslPredicateExecutor的Repository，并使用findAll来执行此操作。问题是查询需要很长时间才能运行。而我只对可能出现的第一个结果感兴趣。那么，是哪里出了问题，导致查询一直运行？
编辑：
我选择使用了JPAQuery。当然，生成的Hibernate查询是相同的。这是我的JPAQuery。
JPQLQuery jpqlQuery = new JPAQuery(entityManager);
jpqlQuery.select(qT1).from(qT1, qT2, qT3, qT4, qT5).where(booleanBuilder);
return jpqlQuery.fetch();
如何在上述JPAQuery中添加limit，以仅获取第一个结果？
英文:
I am trying to utilize Querydsl to fetch some results from a table. So far, this is what I have tried -
Assume there are 5 entities named T1..T5. And I am trying to do this SQL query in Querydsl -
SELECT T1.*
FROM T1,T2,T3,T4,T5
WHERE T1.A=T2.A
AND T2.B=T5.B
AND T4.C=T2.C
AND T1.B=1234;
I tried the following, but the Hibernate query keeps running, and does not seem to end.
booleanBuilder.and(JPAExpressions.select(qT1).from(qT1,qT2,qT3,qT4,qT5)
.where(
qT1.a.eq(qT2.a)
.and(qT1.a.eq(qT2.a))
... // and so on
.exists());
I am using the Repository that extends QuerydslPredicateExecutor and using findAll to execute this. The problem is that the query takes forever to run. And I am interested only in the first result that may appear.
So, where am I going wrong that is making the query execute forever?
Edit:
I opted to use the JPAQuery instead. And of course, the Hibernate query generated is the same. Here is my JPAQuery.
JPQLQuery jpqlQuery = new JPAQuery(entityManager);
jpqlQuery.select(qT1).from(qT1, qT2, qT3, qT4, qT5).where(booleanBuilder);
return jpqlQuery.fetch();
How do I incorporate the limit in the above JPAQuery so that only the first result is fetched?
答案1
得分: 1
复杂性不在于谓词或QueryDSL，而在于你要在结果的每一行中执行子查询。根据总结果集的大小，这可能变得越来越难计算。但在QueryDSL、Hibernate的HQL、JPA的JPQL或你的数据库SQL中，它都同样复杂。所以你试图生成的SQL将同样很慢。
你可以尝试使用limit子句来优化查询。在QueryDSL中向查询添加limit子句非常简单：.limit(1)。那么你的查询就变成了：
JPQLQuery jpqlQuery = new JPAQuery(entityManager);
jpqlQuery.select(qT1).from(qT1, qT2, qT3, qT4, qT5).where(booleanBuilder);
jpqlQuery.limit(1);
return jpqlQuery.fetch();
注意：上述代码部分只是提供了翻译，不包含完整的上下文和功能。
英文:
The complexity is not in the predicate or in QueryDSL, but in the fact that you're executing it in a subquery that has to be executed for every row in the result. Depending on the total result set size, this may become increasingly difficult to compute. It is however equally complex among QueryDSL, Hibernates HQL, JPA's JPQL or your databases SQL. So the SQL you're trying to generate, will be just as slow.
You might succeed at optimising the query using a limit clause. Adding a limit clause to query in QueryDSL is quite trivial: .limit(1). So then your query becomes:
JPQLQuery jpqlQuery = new JPAQuery(entityManager);
jpqlQuery.select(qT1).from(qT1, qT2, qT3, qT4, qT5).where(booleanBuilder);
jpqlQuery.limit(1);
return jpqlQuery.fetch();
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论