java - 使用 LEFT JOIN 和 GROUP BY 的 JPQL 查询

Question

我正在 Oracle 11g XE 上使用简单的 Hibernate 5.0.4、Spring 4.2.3、基于 Maven 3.3.3 的项目来学习 JPQL。完整的源代码可以在我的GitHub branch上找到。

我有 2 个模型:

import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.EnumType;
import javax.persistence.Enumerated;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

import lombok.Data;
import lombok.NoArgsConstructor;

@Entity
@Table(name = "T_OWNER")
@NoArgsConstructor
public @Data class OwnerModel {

    public OwnerModel(String firstName, String lastName, Integer age, OwnerType type) {
        super();
        this.firstName = firstName;
        this.lastName = lastName;
        this.type = type;
        this.since = new Date(System.currentTimeMillis());
        this.age = age;
    }

    @Id
    @GeneratedValue(generator = "owner-sequence-generator", strategy = GenerationType.SEQUENCE)
    @SequenceGenerator(name = "owner-sequence-generator", sequenceName = "OWNER_SEQ", initialValue = 1, allocationSize = 20)
    private Long id;

    @Column(name = "FIRST_NAME")
    private String firstName;

    @Column(name = "LAST_NAME")
    private String lastName;

    @Column(name = "TYPE")
    @Enumerated(EnumType.STRING)
    private OwnerType type;

    @Column(name = "SINCE")
    @Temporal(TemporalType.TIME)
    private Date since;

    @Column(name = "AGE")
    private Integer age;

    @OneToMany(mappedBy = "owner", cascade = CascadeType.ALL, fetch = FetchType.EAGER, targetEntity = CarModel.class)
    private List<CarModel> cars = new LinkedList<>();

    public void addCar(CarModel car) {
        cars.add(car);
        car.setOwner(this);
    }
}

import java.sql.Blob;
import java.sql.Clob;
import java.util.Date;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.Lob;
import javax.persistence.ManyToOne;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@Entity
@Table(name = "T_CAR")
@NoArgsConstructor
@AllArgsConstructor
@ToString(exclude = "owner")
public @Data class CarModel {

    public CarModel(String name, Integer wheelsNumber, Clob spec, Blob image) {
        super();
        this.name = name;
        this.wheelsNumber = wheelsNumber;
        this.spec = spec;
        this.image = image;
        this.createdIn = new Date(System.currentTimeMillis());
    }

    @Id
    @GeneratedValue(generator = "car-sequence-generator", strategy = GenerationType.SEQUENCE)
    @SequenceGenerator(name = "car-sequence-generator", sequenceName = "CAR_SEQ", initialValue = 1, allocationSize = 20)
    private Long id;

    @Column(name = "NAME")
    private String name;

    @Column(name = "CREATED_IN")
    @Temporal(TemporalType.TIMESTAMP)
    private Date createdIn;

    @Column(name = "WHEELS_NUMBER")
    private Integer wheelsNumber;

    @Lob
    @Column(name = "SPEC")
    private Clob spec;

    @Lob
    @Column(name = "IMAGE")
    private Blob image;

    @ManyToOne(cascade = CascadeType.ALL, fetch = FetchType.EAGER, targetEntity = OwnerModel.class)
    @JoinColumn(name = "ID_OWNER")
    private OwnerModel owner;
}

它们用于在我的数据库中准备一些数据。当我执行此类位于 DAO 的 JPQL 查询时:

    @Override
public List<?> executeSelectWithGroupBy() {
    return (List<?>) getSession().createQuery("select o, COUNT(c) from OwnerModel o LEFT JOIN o.cars c GROUP BY o").list();
}

我遇到以下错误:

Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2116)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1899)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1875)
    at org.hibernate.loader.Loader.doQuery(Loader.java:919)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336)
    at org.hibernate.loader.Loader.doList(Loader.java:2611)
    at org.hibernate.loader.Loader.doList(Loader.java:2594)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2423)
    at org.hibernate.loader.Loader.list(Loader.java:2418)
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:501)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:371)
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:226)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1268)
    at org.hibernate.internal.QueryImpl.list(QueryImpl.java:87)
    at com.pduleba.spring.dao.OwnerDaoImpl.executeSelectWithGroupBy(OwnerDaoImpl.java:57)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:497)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:302)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208)
    at com.sun.proxy.$Proxy39.executeSelectWithGroupBy(Unknown Source)
    at com.pduleba.spring.services.OwnerServiceImpl.executeSelectWithGroupBy(OwnerServiceImpl.java:40)
    at com.pduleba.spring.controller.QueryControllerImpl.executeQueries(QueryControllerImpl.java:39)
    at com.pduleba.hibernate.Main.execute(Main.java:50)
    at com.pduleba.hibernate.Main.main(Main.java:27)
Caused by: java.sql.SQLSyntaxErrorException: ORA-00979: not a GROUP BY expression

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:886)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3657)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1495)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
    at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:83)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70)
    ... 32 more

据我了解，这是由 Hibernate 生成的 SQL 错误引起的:

select
    ownermodel0_.id as col_0_0_,
    count(cars1_.id) as col_1_0_,
    ownermodel0_.id as id1_1_,
    ownermodel0_.AGE as AGE2_1_,
    ownermodel0_.FIRST_NAME as FIRST_NAME3_1_,
    ownermodel0_.LAST_NAME as LAST_NAME4_1_,
    ownermodel0_.SINCE as SINCE5_1_,
    ownermodel0_.TYPE as TYPE6_1_ 
from
    hibernate.T_OWNER ownermodel0_ 
left outer join
    hibernate.T_CAR cars1_ 
        on ownermodel0_.id=cars1_.ID_OWNER 
group by
    ownermodel0_.id

where GROUP BY 子句应包含所有列(而不仅仅是 id)。在我看来，hibernate 应该生成这样的 SQL:

select
    ownermodel0_.id as col_0_0_,
    count(cars1_.id) as col_1_0_,
    ownermodel0_.id as id1_1_,
    ownermodel0_.AGE as AGE2_1_,
    ownermodel0_.FIRST_NAME as FIRST_NAME3_1_,
    ownermodel0_.LAST_NAME as LAST_NAME4_1_,
    ownermodel0_.SINCE as SINCE5_1_,
    ownermodel0_.TYPE as TYPE6_1_ 
from
    hibernate.T_OWNER ownermodel0_ 
left outer join
    hibernate.T_CAR cars1_ 
        on ownermodel0_.id=cars1_.ID_OWNER 
group by
    ownermodel0_.id,
    ownermodel0_.AGE,
    ownermodel0_.FIRST_NAME,
    ownermodel0_.LAST_NAME,
    ownermodel0_.SINCE,
    ownermodel0_.TYPE; 

但是(据我了解)here和 here与我的相比，显示的 JPQL 查询完全相同。

这是 Hibernate 中的错误还是我代码中的隐藏错误？

感谢您的帮助和建议。

Answer 1

它不适用于 hibernate 。正如您所看到的，Jira 中存在与此相关的问题 - https://hibernate.atlassian.net/browse/HHH-2436并且处于未解决状态。

您提供的第一个链接是 JPA 规范，第二个链接没有按模型查询分组，而只是按简单的数字字段分组。

正如@GingerHead 的回答，修改您的查询会更容易