*Problem*
After upgrading to Spring Boot 3, Hibernate generates invalid sql statements on a JPQL that still worked with Spring Boot 2.7.5.
See reproducer application here: [https://github.com/mkeller75/spring-boot-3-hibernate-group-by-reproducer|https://github.com/mkeller75/spring-boot-3-hibernate-group-by-reproducer|smart-link]
Here are the classes which still worked with Spring Boot 2.7.5 and did not work with Spring Boot 3.0:
{code:java}package com.test.action.entity;
import jakarta.persistence.Basic;
import jakarta.persistence.Column;
import jakarta.persistence.Id;
import jakarta.persistence.MappedSuperclass;
import jakarta.persistence.PrePersist;
import jakarta.persistence.PreUpdate;
import jakarta.persistence.Version;
import lombok.AccessLevel;
import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.ToString;
import lombok.experimental.SuperBuilder;
import org.hibernate.annotations.JdbcTypeCode;
import org.hibernate.type.SqlTypes;
import java.time.OffsetDateTime;
import java.util.Objects;
import java.util.UUID;
import static java.util.Objects.isNull;
@SuperBuilder
@Getter
@NoArgsConstructor(access = AccessLevel.PUBLIC)
@AllArgsConstructor(access = AccessLevel.PRIVATE)
@MappedSuperclass
@ToString(onlyExplicitlyIncluded = true)
public class BaseEntity {
@Id
@Column(name = "ID")
@Basic
@JdbcTypeCode(SqlTypes.CHAR)
@ToString.Include
private UUID id;
@Column(name = "SEQUENCE_NUMBER", nullable = false)
@Version
private Long sequenceNumber;
@Column(name = "MODIFICATION_TIMESTAMP", nullable = false)
private OffsetDateTime modificationTimestamp;
@Column(name = "CREATION_TIMESTAMP", nullable = false)
private OffsetDateTime creationTimestamp;
@PreUpdate
public void preUpdate() {
modificationTimestamp = OffsetDateTime.now();
}
@PrePersist
public void prePersist() {
if (isNull(this.id)) {
this.id = UUID.randomUUID();
}
if (sequenceNumber == null) {
sequenceNumber = 0L;
}
if (creationTimestamp == null) {
creationTimestamp = OffsetDateTime.now();
}
preUpdate();
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
BaseEntity that = (BaseEntity) o;
return id != null && Objects.equals(id, that.id);
}
@Override
public int hashCode() {
return id != null ? id.hashCode() : 0;
}
}{code}
{code:java}package com.test.action.entity;
import jakarta.persistence.CascadeType;
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.FetchType;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.ManyToOne;
import jakarta.persistence.Table;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.ToString;
import lombok.experimental.SuperBuilder;
import java.time.LocalDate;
@Getter
@ToString(callSuper = true, onlyExplicitlyIncluded = true)
@SuperBuilder
@Entity
@Table(name = "ACT_ACTION")
@NoArgsConstructor
public class Action extends BaseEntity {
@Column(name = "PARTNER_NUMBER", length = 50)
private String partnerNumber;
@Column(name = "TITLE", nullable = false)
private String title;
@Column(name = "FINDING", nullable = false)
private String finding;
@Column(name = "DEADLINE")
@Setter
private LocalDate deadline;
@ManyToOne(fetch = FetchType.LAZY, cascade = {CascadeType.MERGE})
@JoinColumn(name = "FK_USER_ID")
@Setter
private User user;
}{code}
{code:java}package com.test.action.entity;
import com.google.common.base.Joiner;
import jakarta.persistence.CascadeType;
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.OneToMany;
import jakarta.persistence.Table;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.ToString;
import lombok.experimental.SuperBuilder;
import java.util.ArrayList;
import java.util.List;
import static org.apache.commons.lang3.StringUtils.isNotBlank;
@Getter
@ToString(callSuper = true, onlyExplicitlyIncluded = true)
@SuperBuilder
@Entity
@Table(name = "ACT_USER")
@NoArgsConstructor
public class User extends BaseEntity {
@Column(name = "PARTNER_NUMBER", length = 50)
private String partnerNumber;
@Column(name = "FIRSTNAME", length = 40)
private String firstname;
@Column(name = "LASTNAME", length = 40)
private String lastname;
@Column(name = "EMAIL", length = 128)
private String email;
@Column(name = "MOBILE", length = 30)
private String mobile;
@OneToMany(mappedBy = "user", cascade = CascadeType.ALL, orphanRemoval = true)
private List<Action> actions = new ArrayList<>();
public boolean hasFirstnameLastname() {
return isNotBlank(resolveFirstnameLastname());
}
public String resolveFirstnameLastname() {
return Joiner.on(" ").skipNulls().join(firstname, lastname);
}
public String resolveChannel() {
if (mobile == null) {
return email;
} else {
return mobile;
}
}
}{code}
{code:java}package com.test.action.entity.statistic;
public interface Statistic {
String getName();
String getKey();
Integer getCount();
String getAdditionalInfo();
}{code}
{code:java}package com.test.action.entity.statistic;
import com.test.action.entity.User;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.ToString;
import java.util.UUID;
@ToString
@Builder
@AllArgsConstructor
public class UserStatistic implements Statistic {
private UUID userId;
private String name;
private String channel;
private Long count;
public UserStatistic(User user) {
this(user, 0L);
}
public UserStatistic(User user, Long count) {
this.userId = user != null ? user.getId() : null;
this.name = user != null ? user.resolveFirstnameLastname() : null;
this.channel = user != null && user.hasFirstnameLastname() ? user.resolveChannel() : null;
this.count = count;
}
@Override
public String getName() {
return name;
}
@Override
public String getKey() {
return userId != null ? userId.toString() : null;
}
@Override
public Integer getCount() {
return count.intValue();
}
@Override
public String getAdditionalInfo() {
return channel;
}
}
{code}
{code:java}package com.test.action.persistence;
import com.test.action.entity.Action;
import com.test.action.entity.statistic.UserStatistic;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.UUID;
@Repository
public interface ActionRepository extends JpaRepository<Action, UUID> {
@Query("SELECT new com.test.action.entity.statistic.UserStatistic(u, count(a))" +
" FROM Action a INNER JOIN a.user u"+
" WHERE a.partnerNumber = :partnerNumber" +
" GROUP BY u")
List<UserStatistic> getUserStatistic(@Param("partnerNumber") String partnerNumber);
}
{code}
With Spring Boot 3 we get the following error:
{noformat}Caused by: org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [select u1_0.id,u1_0.creation_timestamp,u1_0.email,u1_0.firstname,u1_0.lastname,u1_0.mobile,u1_0.modification_timestamp,u1_0.partner_number,u1_0.sequence_number,count(a1_0.id) from act_action a1_0 join act_user u1_0 on u1_0.id=a1_0.fk_user_id where a1_0.partner_number=? group by a1_0.fk_user_id]
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:64)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:56)
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.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:253)
at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.getResultSet(DeferredResultSetAccess.java:146)
at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.lambda$processNext$0(JdbcValuesResultSetImpl.java:89)
at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.advance(JdbcValuesResultSetImpl.java:274)
at org.hibernate.sql.results.jdbc.internal.JdbcValuesResultSetImpl.processNext(JdbcValuesResultSetImpl.java:85)
at org.hibernate.sql.results.jdbc.internal.AbstractJdbcValues.next(AbstractJdbcValues.java:29)
at org.hibernate.sql.results.internal.RowProcessingStateStandardImpl.next(RowProcessingStateStandardImpl.java:89)
at org.hibernate.sql.results.spi.ListResultsConsumer.consume(ListResultsConsumer.java:142)
at org.hibernate.sql.results.spi.ListResultsConsumer.consume(ListResultsConsumer.java:32)
at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.doExecuteQuery(JdbcSelectExecutorStandardImpl.java:443)
at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.executeQuery(JdbcSelectExecutorStandardImpl.java:166)
at org.hibernate.sql.exec.internal.JdbcSelectExecutorStandardImpl.list(JdbcSelectExecutorStandardImpl.java:91)
at org.hibernate.sql.exec.spi.JdbcSelectExecutor.list(JdbcSelectExecutor.java:31)
at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.lambda$new$0(ConcreteSqmSelectQueryPlan.java:102)
at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.withCacheableSqmInterpretation(ConcreteSqmSelectQueryPlan.java:305)
at org.hibernate.query.sqm.internal.ConcreteSqmSelectQueryPlan.performList(ConcreteSqmSelectQueryPlan.java:246)
at org.hibernate.query.sqm.internal.QuerySqmImpl.doList(QuerySqmImpl.java:546)
at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:363)
at org.hibernate.query.sqm.internal.QuerySqmImpl.list(QuerySqmImpl.java:1032)
at org.hibernate.query.Query.getResultList(Query.java:94)
at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:127)
at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:90)
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:148)
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:136)
at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:136)
at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:120)
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.doInvoke(QueryExecutorMethodInterceptor.java:164)
at org.springframework.data.repository.core.support.QueryExecutorMethodInterceptor.invoke(QueryExecutorMethodInterceptor.java:143)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:77)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
... 170 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: ORA-00979: not a GROUP BY expression
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:629)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:563)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1150)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:770)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:298)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:497)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:151)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:936)
at oracle.jdbc.driver.OracleStatement.prepareDefineBufferAndExecute(OracleStatement.java:1171)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1100)
at oracle.jdbc.driver.OracleStatement.executeSQLSelect(OracleStatement.java:1425)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1308)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3745)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3854)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1097)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
at org.hibernate.sql.results.jdbc.internal.DeferredResultSetAccess.executeQuery(DeferredResultSetAccess.java:217)
... 205 common frames omitted
Caused by: oracle.jdbc.OracleDatabaseException: ORA-00979: not a GROUP BY expression{noformat}
*Solution*
To run it successfully with Spring Boot 3, the JPQL had to be adapted as follows, which is IMHO quite nasty and annoying to define the JOINS via JOIN ... ON and to list all attributes in the GROUP BY declaration:
{code:java} @Query("SELECT new com.test.action.entity.statistic.UserStatistic(u, count(a))" +
" FROM Action a INNER JOIN User u ON a.user.id = u.id"+
" WHERE a.partnerNumber = :partnerNumber" +
" GROUP BY u.id, u.sequenceNumber, u.modificationTimestamp, u.creationTimestamp, u.partnerNumber, u.firstname, u.lastname, u.email, u.mobile")
List<UserStatistic> getUserStatistic(@Param("partnerNumber") String partnerNumber);
{code} |
|
