Search code examples
javaspringhibernatejpajpql

JPQL query with LEFT JOIN and GROUP BY

I'm learning JPQL using simple Hibernate 5.0.4, Spring 4.2.3, Maven 3.3.3 based project on Oracle 11g XE. Full source code can be found on my GitHub branch.

I've got 2 models:

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;
}

They were used to prepare some data in my DB. When I execute such DAO located JPQL query:

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

I've got error as below :

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

As far as I understand it is caused by error in Hibernate generated 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 clause should include all columns (not just id). In my opinion hibernate should generate such 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;

However (as far as I understand) here and here are shown exactly the same JPQL Queries comparing to mine.

Is it a bug in Hibernate or just a hidden bug in my code?

I appreciate your help and advice.

Solution

  • It would not work for hibernate. As you can see there is issue connected with this in Jira - https://hibernate.atlassian.net/browse/HHH-2436 and it's in unresolved state.

    Links that you provided first is JPA spec and second one there is not group by model query but just by simple numeric field.

    As @GingerHead answered it would be easier to modfiy your query