如何使用Hibernate作为持久化提供者在JPA中添加"outer join conditions ON子句“？

Question

此方法在使用hibernate时失败：

SELECT e, COUNT(p) FROM Employee e JOIN e.projects p ON e.timeEmployed <=  p.timeStarted GROUP BY e

以下测试：

package provider.hibernate;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.transaction.Transactional;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.test.context.ActiveProfiles;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import context.ContextConfig;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = { ContextConfig.class })
@Transactional
// @ActiveProfiles("eclipseLink")
@ActiveProfiles("hibernate")
public class JoinFilterTest {

    @PersistenceContext
    private EntityManager entityManager;

    @Test
    public void testJoinFilter() {
        entityManager.createQuery(
                "SELECT e, COUNT(p) FROM Employee e JOIN e.projects p"
                        + " ON e.timeEmployed <= p.timeStarted GROUP BY e")
                .getResultList();
    }

}

失败，并出现异常：

java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: with-clause referenced two different from-clause elements [Select e, COUNT(p) FROM entity.Employee e JOIN e.projects p ON e.timeEmployed <= p.timeStarted GROUP BY e]
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1750)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1677)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1683)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:331)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:289)
    at com.sun.proxy.$Proxy34.createQuery(Unknown Source)
    at provider.hibernate.JoinFilterTest.testJoinFilter(JoinFilterTest.java:27)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:72)
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:81)
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:72)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:216)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:82)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:60)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:67)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:162)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459) 
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:675)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192) 
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: with-clause referenced two different from-clause elements [Select e, COUNT(p) FROM entity.Employee e JOIN e.projects p ON e.timeEmployed <= p.timeStarted GROUP BY e]
    at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:91)
    at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:109)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:284)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:206)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:158)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:131)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:93)
    at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:167)
    at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:301)
    at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:236)
    at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1800)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:328)
        ... 36 more

employee实体类如下：

package entity;

import java.time.Instant;
import java.util.Collection;

import javax.persistence.Basic;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.ManyToMany;
import javax.persistence.PrePersist;

@Entity
public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToMany(mappedBy = "employees")
    private Collection<Project> projects;

    @Basic
    private Instant timeEmployed;

    @PrePersist
    public void prePersist() {
        timeEmployed = Instant.now();
    }

}

项目实体类如下：

package entity;

import java.time.Instant;
import java.util.Collection;

import javax.persistence.Basic;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.ManyToMany;

@Entity
public class Project {

    @Id
    private Long id;

    @ManyToMany
    private Collection<Employee> employees;

    @Basic
    private Instant timeStarted;

}

测试中的jpql查询在eclipse link中工作得很好。

Answer 1

您不能使用某些聚合函数对整个实体进行分组。

你可以这样做：

SELECT 
    e.id, 
    COUNT(p) 
FROM Employee e 
JOIN e.projects p 
WHERE e.timeEmployed <= p.timeStarted 
GROUP BY e.id

使用返回的ids，您可以通过一个辅助查询来获取实体：

select e FROM Employee e where id in :ids

Answer 2

我确定的解决方案与您的建议非常相似，但有些相反。将查询条件移到ON子句而不是在WHERE子句上指定它的原因是为了确保所有Employee实体都包含在结果中。

作为第一步，我选择了下面的员工

SELECT e FROM Employee e

然后，我发出了一个二级查询来计算每个员工受雇后启动的项目：

SELECT 
    e.id, 
    COUNT(p) 
FROM Employee e 
JOIN e.projects p 
WHERE e.timeEmployed <= p.timeStarted AND e in :employees 
GROUP BY e

第一个查询的雇员列表作为参数(: employees )传递给第二个查询。

在第二个查询中选择了员工id (e.id)，以便可以将项目计数映射到适当的员工。这显然是更多的工作，但我现在不得不接受它。