[JIRA] (HHH-16005) @BatchSize with @ManyToMany does not work as expected when migrating 5 -> 6
by 규호 (JIRA)
규호 ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=624fda4... ) *updated* an issue
Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiOGVlOGYyMmM4... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16005?atlOrigin=eyJpIjoiOGVlOG... ) HHH-16005 ( https://hibernate.atlassian.net/browse/HHH-16005?atlOrigin=eyJpIjoiOGVlOG... ) @BatchSize with @ManyToMany does not work as expected when migrating 5 -> 6 ( https://hibernate.atlassian.net/browse/HHH-16005?atlOrigin=eyJpIjoiOGVlOG... )
Change By: 규호 ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=624fda4... )
from [https://discourse.hibernate.org/t/batchsize-with-manytomany-does-not-work...]
I have the following entities.
Entity Models:
{code:java}@Entity
@Getter
@Setter
public class Article {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;
private String categoryId;
@BatchSize(size = 20)
@ManyToMany
private List<Tag> tags = new ArrayList<>();
}{code}
{code:java}@Entity
@Getter
@NoArgsConstructor
public class Tag {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(unique = true)
private String name;
public Tag (String name) {
this.name = name;
}
}
{code}
And DTO:
{noformat}@Getter
public class ArticleResponseDto {
private final int id;
private final List<TagDto> tags;
public ArticleResponseDto(Article article) {
this.id = article.getId();
this.tags = article.getTags().stream()
.map(TagDto::new)
.toList();
}
}
@Getter
class TagDto {
private final String name;
public TagDto(Tag tag) {
this.name = tag.getName();
}
}{noformat}
In Hibernate 5.6.14, when querying articles, it generated queries :
{noformat}Hibernate: select article0_.id as id1_0_, article0_.category_id as category2_0_ from article article0_ order by article0_.id DESC limit ?
Hibernate: select tags0_.article_id as article_1_1_1_, tags0_.tags_id as tags_id2_1_1_, tag1_.id as id1_2_0_, tag1_.name as name2_2_0_ from article_tags tags0_ inner join tag tag1_ on tags0_.tags_id=tag1_.id where tags0_.article_id in (?, ?, ?, ?, ?){noformat}
However In Hibernate 6.1.6:
{noformat}Hibernate: select t1_0.article_id,t1_1.id,t1_1.name from article_tags t1_0 join tag t1_1 on t1_1.id=t1_0.tags_id where t1_0.article_id in(?,?,?,?,?)
Hibernate: select t1_0.article_id,t1_1.id,t1_1.name from article_tags t1_0 join tag t1_1 on t1_1.id=t1_0.tags_id where t1_0.article_id in(?,?)
Hibernate: select t1_0.article_id,t1_1.id,t1_1.name from article_tags t1_0 join tag t1_1 on t1_1.id=t1_0.tags_id where t1_0.article_id in(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?){noformat}
Hibernate 6.1.6 generated queries 2 more and binded data in each query were article-id that had empty tag list. And the values in IN clause at the last query was always generated as much as the size of {{@BatchSize}}.
Test Code:
{noformat}@SpringBootTest
@Transactional
class TestServiceTest {
@Autowired
private EntityManager entityManager;
@BeforeEach
void fixture() {
List<Tag> tags = List.of(new Tag("t1"), new Tag("t2"), new Tag("t3"));
List<Tag> tags2 = List.of(new Tag("t4"), new Tag("t5"));
Article article = new Article();
Article article2 = new Article();
Article article3 = new Article();
Article article4 = new Article();
Article article5 = new Article();
article.setTags(tags);
article3.setTags(tags2);
tags.forEach(entityManager::persist);
tags2.forEach(entityManager::persist);
entityManager.persist(article);
entityManager.persist(article2);
entityManager.persist(article3);
entityManager.persist(article4);
entityManager.persist(article5);
entityManager.flush();
entityManager.clear();
}
@Test
void test() {
Query query = entityManager.createQuery("select a from Article a");
List<Article> tech = query.setMaxResults(20).getResultList();
tech.stream()
.map(ArticleResponseDto::new)
.toList();
}
}{noformat}
I expected executing queries same in both version, it wasn't.
h3. Reproducer
reproducer: [https://github.com/chlrbgh89/hibernate-batch-size-bug|https://github.com/...]
reproducer Hibernate version is {{6.1.6.Final}}
If you want to downgrade version to {{5.6.14.Final}}, uncomment this code in {{build.gradle}}
{code:groovy}//configurations.configureEach {
// resolutionStrategy.eachDependency {details ->
// if(details.requested.name == 'hibernate-core') {
// details.useTarget group: 'org.hibernate', name:'hibernate-core-jakarta', version: '5.6.14.Final'
// }
// }
//}{code}
( https://hibernate.atlassian.net/browse/HHH-16005#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16005#add-comment?atlOrigin=ey... )
Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100213- sha1:1fa7b87 )
1 year, 11 months
[JIRA] (HHH-16004) Downcasting with CriteriaBuilder.treat causes ClassCastException
by Roberts Z (JIRA)
Roberts Z ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=63bc113... ) *updated* an issue
Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiNjE0ZjI2ZTQ4... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16004?atlOrigin=eyJpIjoiNjE0Zj... ) HHH-16004 ( https://hibernate.atlassian.net/browse/HHH-16004?atlOrigin=eyJpIjoiNjE0Zj... ) Downcasting with CriteriaBuilder.treat causes ClassCastException ( https://hibernate.atlassian.net/browse/HHH-16004?atlOrigin=eyJpIjoiNjE0Zj... )
Change By: Roberts Z ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=63bc113... )
When building the a query with Criteria API, downcasting to access a class specific attribute, leads to ClassCastException for the internal Hibernate classes. Please see the attachment for a minimal Spring project to reproduce this issue.
{code:java}@Entity
@Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)
@Getter
@Setter
public abstract class Term extends BaseEntity {
@Column(nullable = false)
private String title;
@OneToMany(mappedBy = "term")
private Collection<Linkage> linkagesToPublications = new ArrayList<>();
}
////////
@Table
@Entity(name = "LocalTermTitle")
@Getter
@Setter
@NoArgsConstructor
public class LocalTerm extends Term {
@ManyToOne(cascade = CascadeType.MERGE)
@JoinColumn(name = "IdLanguage", nullable = false)
private Language language;
}
////////
@Table
@Entity(name = "PublicationLinkageToTerm")
@Getter
@Setter
@NoArgsConstructor
public class Linkage extends BaseEntity {
@ManyToOne
@JoinColumn(name = "IdTermTitleBase", nullable = false)
private Term term;
}
////////
public static Specification<Linkage> byTermLanguage(final Language language) {
return (root, query, cb) -> {
final var asLocalTerm = cb.treat(root.get("term"), LocalTerm.class);
return cb.equal(asLocalTerm.get("language"), language);
};
}
{code}
_Caused by: java.lang.ClassCastException: class org.hibernate.metamodel.model.domain.internal.EntitySqmPathSource cannot be cast to class org.hibernate.metamodel.model.domain.EntityDomainType (org.hibernate.metamodel.model.domain.internal.EntitySqmPathSource and org.hibernate.metamodel.model.domain.EntityDomainType are in unnamed module of loader 'app')_
_at org.hibernate.query.sqm.tree.domain.SqmTreatedSimplePath.<init>(SqmTreatedSimplePath.java:51) ~[hibernate-core-6.1.6.Final.jar:6.1.6.Final]_
_at org.hibernate.query.sqm.tree.domain.SqmEntityValuedSimplePath.treatAs(SqmEntityValuedSimplePath.java:78) ~[hibernate-core-6.1.6.Final.jar:6.1.6.Final]_
_at org.hibernate.query.sqm.tree.domain.SqmEntityValuedSimplePath.treatAs(SqmEntityValuedSimplePath.java:73) ~[hibernate-core-6.1.6.Final.jar:6.1.6.Final]_
_at org.hibernate.query.sqm.tree.domain.SqmEntityValuedSimplePath.treatAs(SqmEntityValuedSimplePath.java:21) ~[hibernate-core-6.1.6.Final.jar:6.1.6.Final]_
_at org.hibernate.query.sqm.internal.SqmCriteriaNodeBuilder.treat(SqmCriteriaNodeBuilder.java:378) ~[hibernate-core-6.1.6.Final.jar:6.1.6.Final]_
_at org.hibernate.query.sqm.internal.SqmCriteriaNodeBuilder.treat(SqmCriteriaNodeBuilder.java:153) ~[hibernate-core-6.1.6.Final.jar:6.1.6.Final]_
_at com.example.demo.repo.LinkageRepo$Specifications.lambda$byTermLanguage$67bfe078$1(LinkageRepo.java:19) ~[classes/:na]_
_at org.springframework.data.jpa.repository.support.SimpleJpaRepository.applySpecificationToCriteria(SimpleJpaRepository.java:807) ~[spring-data-jpa-3.0.0.jar:3.0.0]_
_at org.springframework.data.jpa.repository.support.SimpleJpaRepository.getQuery(SimpleJpaRepository.java:741) ~[spring-data-jpa-3.0.0.jar:3.0.0]_
_at org.springframework.data.jpa.repository.support.SimpleJpaRepository.getQuery(SimpleJpaRepository.java:726) ~[spring-data-jpa-3.0.0.jar:3.0.0]_
_at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:463) ~[spring-data-jpa-3.0.0.jar:3.0.0]_
_at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]_
_at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) ~[na:na]_
_at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[na:na]_
_at java.base/java.lang.reflect.Method.invoke(Unknown Source) ~[na:na]_
_at org.springframework.data.repository.core.support.RepositoryMethodInvoker$RepositoryFragmentMethodInvoker.lambda$new$0(RepositoryMethodInvoker.java:288) ~[spring-data-commons-3.0.0.jar:3.0.0]_
_at org.springframework.data.repository.core.support.RepositoryMethodInvoker.doInvoke(RepositoryMethodInvoker.java:136) ~[spring-data-commons-3.0.0.jar:3.0.0]_
_at org.springframework.data.repository.core.support.RepositoryMethodInvoker.invoke(RepositoryMethodInvoker.java:120) ~[spring-data-commons-3.0.0.jar:3.0.0]_
_at org.springframework.data.repository.core.support.RepositoryComposition$RepositoryFragments.invoke(RepositoryComposition.java:516) ~[spring-data-commons-3.0.0.jar:3.0.0]_
_…_
----
( https://hibernate.atlassian.net/browse/HHH-16004#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16004#add-comment?atlOrigin=ey... )
Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100213- sha1:1fa7b87 )
1 year, 11 months
[JIRA] (HHH-16005) @BatchSize with @ManyToMany does not work as expected when migrating 5 -> 6
by 규호 (JIRA)
규호 ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=624fda4... ) *updated* an issue
Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiMmMwOTIxOWIy... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16005?atlOrigin=eyJpIjoiMmMwOT... ) HHH-16005 ( https://hibernate.atlassian.net/browse/HHH-16005?atlOrigin=eyJpIjoiMmMwOT... ) @BatchSize with @ManyToMany does not work as expected when migrating 5 -> 6 ( https://hibernate.atlassian.net/browse/HHH-16005?atlOrigin=eyJpIjoiMmMwOT... )
Change By: 규호 ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=624fda4... )
I have the following entities.
Entity Models:
{code:java}@Entity
@Getter
@Setter
public class Article {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;
private String categoryId;
@BatchSize(size = 20)
@ManyToMany
private List<Tag> tags = new ArrayList<>();
}{code}
{code:java}@Entity
@Getter
@NoArgsConstructor
public class Tag {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(unique = true)
private String name;
public Tag (String name) {
this.name = name;
}
}
{code}
And DTO:
{noformat}@Getter
public class ArticleResponseDto {
private final int id;
private final List<TagDto> tags;
public ArticleResponseDto(Article article) {
this.id = article.getId();
this.tags = article.getTags().stream()
.map(TagDto::new)
.toList();
}
}
@Getter
class TagDto {
private final String name;
public TagDto(Tag tag) {
this.name = tag.getName();
}
}{noformat}
In Hibernate 5.6.14, when querying articles, it generated queries :
{noformat}Hibernate: select article0_.id as id1_0_, article0_.category_id as category2_0_ from article article0_ order by article0_.id DESC limit ?
Hibernate: select tags0_.article_id as article_1_1_1_, tags0_.tags_id as tags_id2_1_1_, tag1_.id as id1_2_0_, tag1_.name as name2_2_0_ from article_tags tags0_ inner join tag tag1_ on tags0_.tags_id=tag1_.id where tags0_.article_id in (?, ?, ?, ?, ?){noformat}
However In Hibernate 6.1.6:
{noformat}Hibernate: select t1_0.article_id,t1_1.id,t1_1.name from article_tags t1_0 join tag t1_1 on t1_1.id=t1_0.tags_id where t1_0.article_id in(?,?,?,?,?)
Hibernate: select t1_0.article_id,t1_1.id,t1_1.name from article_tags t1_0 join tag t1_1 on t1_1.id=t1_0.tags_id where t1_0.article_id in(?,?)
Hibernate: select t1_0.article_id,t1_1.id,t1_1.name from article_tags t1_0 join tag t1_1 on t1_1.id=t1_0.tags_id where t1_0.article_id in(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?){noformat}
Hibernate 6.1.6 generated queries 2 more and binded data in each query were article-id that had empty tag list. And the values in IN clause at the last query was always generated as much as the size of {{@BatchSize}}.
Test Code:
{noformat}@SpringBootTest
@Transactional
class TestServiceTest {
@Autowired
private EntityManager entityManager;
@BeforeEach
void fixture() {
List<Tag> tags = List.of(new Tag("t1"), new Tag("t2"), new Tag("t3"));
List<Tag> tags2 = List.of(new Tag("t4"), new Tag("t5"));
Article article = new Article();
Article article2 = new Article();
Article article3 = new Article();
Article article4 = new Article();
Article article5 = new Article();
article.setTags(tags);
article3.setTags(tags2);
tags.forEach(entityManager::persist);
tags2.forEach(entityManager::persist);
entityManager.persist(article);
entityManager.persist(article2);
entityManager.persist(article3);
entityManager.persist(article4);
entityManager.persist(article5);
entityManager.flush();
entityManager.clear();
}
@Test
void test() {
Query query = entityManager.createQuery("select a from Article a");
List<Article> tech = query.setMaxResults(20).getResultList();
tech.stream()
.map(ArticleResponseDto::new)
.toList();
}
}{noformat}
I expected executing queries same in both version, it wasn't.
h3. Reproducer
reproducer: [https://github.com/chlrbgh89/hibernate-batch-size-bug|https://github.com/...]
reproducer Hibernate version is {{6.1.6.Final}}
If you want to downgrade version to {{5.6.14.Final}}, uncomment this code in {{build.gradle}}
{code:groovy}//configurations.configureEach {
// resolutionStrategy.eachDependency {details ->
// if(details.requested.name == 'hibernate-core') {
// details.useTarget group: 'org.hibernate', name:'hibernate-core-jakarta', version: '5.6.14.Final'
// }
// }
//}{code}
Envrionment
* Spring Boot 3.0.1
* Hibernate 6.1.6.Final
( https://hibernate.atlassian.net/browse/HHH-16005#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16005#add-comment?atlOrigin=ey... )
Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100213- sha1:1fa7b87 )
1 year, 11 months
[JIRA] (HHH-16005) @BatchSize with @ManyToMany does not work as expected when migrating 5 -> 6
by 규호 (JIRA)
규호 ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=624fda4... ) *updated* an issue
Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiYmVmMTE4Njdm... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16005?atlOrigin=eyJpIjoiYmVmMT... ) HHH-16005 ( https://hibernate.atlassian.net/browse/HHH-16005?atlOrigin=eyJpIjoiYmVmMT... ) @BatchSize with @ManyToMany does not work as expected when migrating 5 -> 6 ( https://hibernate.atlassian.net/browse/HHH-16005?atlOrigin=eyJpIjoiYmVmMT... )
Change By: 규호 ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=624fda4... )
I have the following entities.
Entity Models:
{code:java}@Entity
@Getter
@Setter
public class Article {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;
private String categoryId;
@BatchSize(size = 20)
@ManyToMany
private List<Tag> tags = new ArrayList<>();
}{code}
{code:java}@Entity
@Getter
@NoArgsConstructor
public class Tag {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(unique = true)
private String name;
public Tag (String name) {
this.name = name;
}
}
{code}
And DTO:
{noformat}@Getter
public class ArticleResponseDto {
private final int id;
private final List<TagDto> tags;
public ArticleResponseDto(Article article) {
this.id = article.getId();
this.tags = article.getTags().stream()
.map(TagDto::new)
.toList();
}
}
@Getter
class TagDto {
private final String name;
public TagDto(Tag tag) {
this.name = tag.getName();
}
}{noformat}
In Hibernate 5.6.14, when querying articles, it generated queries :
{noformat}Hibernate: select article0_.id as id1_0_, article0_.category_id as category2_0_ from article article0_ order by article0_.id DESC limit ?
Hibernate: select tags0_.article_id as article_1_1_1_, tags0_.tags_id as tags_id2_1_1_, tag1_.id as id1_2_0_, tag1_.name as name2_2_0_ from article_tags tags0_ inner join tag tag1_ on tags0_.tags_id=tag1_.id where tags0_.article_id in (?, ?, ?, ?, ?){noformat}
However In Hibernate 6.1.6:
{noformat}Hibernate: select t1_0.article_id,t1_1.id,t1_1.name from article_tags t1_0 join tag t1_1 on t1_1.id=t1_0.tags_id where t1_0.article_id in(?,?,?,?,?)
Hibernate: select t1_0.article_id,t1_1.id,t1_1.name from article_tags t1_0 join tag t1_1 on t1_1.id=t1_0.tags_id where t1_0.article_id in(?,?)
Hibernate: select t1_0.article_id,t1_1.id,t1_1.name from article_tags t1_0 join tag t1_1 on t1_1.id=t1_0.tags_id where t1_0.article_id in(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?){noformat}
Hibernate 6.1.6 generated queries 2 more and binded data in each query were article-id that had empty tag list. And the values in IN clause at the last query was always generated as much as the size of {{@BatchSize}}.
Test Code:
{noformat}@SpringBootTest
@Transactional
class TestServiceTest {
@Autowired
private EntityManager entityManager;
@BeforeEach
void fixture() {
List<Tag> tags = List.of(new Tag("t1"), new Tag("t2"), new Tag("t3"));
List<Tag> tags2 = List.of(new Tag("t4"), new Tag("t5"));
Article article = new Article();
Article article2 = new Article();
Article article3 = new Article();
Article article4 = new Article();
Article article5 = new Article();
article.setTags(tags);
article3.setTags(tags2);
tags.forEach(entityManager::persist);
tags2.forEach(entityManager::persist);
entityManager.persist(article);
entityManager.persist(article2);
entityManager.persist(article3);
entityManager.persist(article4);
entityManager.persist(article5);
entityManager.flush();
entityManager.clear();
}
@Test
void test() {
Query query = entityManager.createQuery("select a from Article a");
List<Article> tech = query.setMaxResults(20).getResultList();
tech.stream()
.map(ArticleResponseDto::new)
.toList();
}
}{noformat}
I expected executing queries same in both version, it wasn't.
h3. Reproducer
reproducer: [https://github.com/chlrbgh89/hibernate-batch-size-bug|https://github.com/...]
reproducer Hibernate version is {{6.1.6.Final}}
If you want to downgrade version to {{5.6.14.Final}}, uncomment this code in {{build.gradle}}
{code:groovy}//configurations.configureEach {
// resolutionStrategy.eachDependency {details ->
// if(details.requested.name == 'hibernate-core') {
// details.useTarget group: 'org.hibernate', name:'hibernate-core-jakarta', version: '5.6.14.Final'
// }
// }
//}{code}
Envrionment
* Spring Boot 3.0.1
* Hibernate 6.1.6.Final
( https://hibernate.atlassian.net/browse/HHH-16005#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16005#add-comment?atlOrigin=ey... )
Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100213- sha1:1fa7b87 )
1 year, 11 months
[JIRA] (HHH-15991) Hibernate fails when grouping by a related many-to-one entity
by Mike Keller (JIRA)
Mike Keller ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *updated* an issue
Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiNmI5N2M3NzM4... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-15991?atlOrigin=eyJpIjoiNmI5N2... ) HHH-15991 ( https://hibernate.atlassian.net/browse/HHH-15991?atlOrigin=eyJpIjoiNmI5N2... ) Hibernate fails when grouping by a related many-to-one entity ( https://hibernate.atlassian.net/browse/HHH-15991?atlOrigin=eyJpIjoiNmI5N2... )
Change By: Mike Keller ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... )
*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|...]
Here are is the classes repository class 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}
( https://hibernate.atlassian.net/browse/HHH-15991#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-15991#add-comment?atlOrigin=ey... )
Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100213- sha1:1fa7b87 )
1 year, 11 months
[JIRA] (HHH-16005) @BatchSize with @ManyToMany does not work as expected when migrating 5 -> 6
by 규호 (JIRA)
규호 ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=624fda4... ) *created* an issue
Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiZmQyMWU4YWQ2... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-16005?atlOrigin=eyJpIjoiZmQyMW... ) HHH-16005 ( https://hibernate.atlassian.net/browse/HHH-16005?atlOrigin=eyJpIjoiZmQyMW... ) @BatchSize with @ManyToMany does not work as expected when migrating 5 -> 6 ( https://hibernate.atlassian.net/browse/HHH-16005?atlOrigin=eyJpIjoiZmQyMW... )
Issue Type: Bug Affects Versions: 6.1.6 Assignee: Unassigned Components: hibernate-core Created: 09/Jan/2023 05:55 AM Environment: * Spring Boot 3.0.1
* Hibernate 6.1.6.Final Priority: Major Reporter: 규호 ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=624fda4... )
I have the following entities.
Entity Models:
@Entity
@Getter
@Setter
public class Article {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int id;
private String categoryId;
@BatchSize(size = 20)
@ManyToMany
private List<Tag> tags = new ArrayList<>();
}
@Entity
@Getter
@NoArgsConstructor
public class Tag {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(unique = true )
private String name;
public Tag ( String name) {
this.name = name;
}
}
And DTO:
@Getter
public class ArticleResponseDto {
private final int id;
private final List<TagDto> tags;
public ArticleResponseDto(Article article) {
this.id = article.getId();
this.tags = article.getTags().stream()
.map(TagDto::new)
.toList();
}
}
@Getter
class TagDto {
private final String name;
public TagDto(Tag tag) {
this.name = tag.getName();
}
}
In Hibernate 5.6.14, when querying articles, it generated queries :
Hibernate: select article0_.id as id1_0_, article0_.category_id as category2_0_ from article article0_ order by article0_.id DESC limit ?
Hibernate: select tags0_.article_id as article_1_1_1_, tags0_.tags_id as tags_id2_1_1_, tag1_.id as id1_2_0_, tag1_.name as name2_2_0_ from article_tags tags0_ inner join tag tag1_ on tags0_.tags_id=tag1_.id where tags0_.article_id in (?, ?, ?, ?, ?)
However In Hibernate 6.1.6:
Hibernate: select t1_0.article_id,t1_1.id,t1_1.name from article_tags t1_0 join tag t1_1 on t1_1.id=t1_0.tags_id where t1_0.article_id in(?,?,?,?,?)
Hibernate: select t1_0.article_id,t1_1.id,t1_1.name from article_tags t1_0 join tag t1_1 on t1_1.id=t1_0.tags_id where t1_0.article_id in(?,?)
Hibernate: select t1_0.article_id,t1_1.id,t1_1.name from article_tags t1_0 join tag t1_1 on t1_1.id=t1_0.tags_id where t1_0.article_id in(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
Hibernate 6.1.6 generated queries 2 more and binded data in each query were article-id that had empty tag list. And the values in IN clause at the last query was always generated as much as the size of @BatchSize.
Test Code:
@SpringBootTest
@Transactional
class TestServiceTest {
@Autowired
private EntityManager entityManager;
@BeforeEach
void fixture() {
List<Tag> tags = List.of(new Tag("t1"), new Tag("t2"), new Tag("t3"));
List<Tag> tags2 = List.of(new Tag("t4"), new Tag("t5"));
Article article = new Article();
Article article2 = new Article();
Article article3 = new Article();
Article article4 = new Article();
Article article5 = new Article();
article.setTags(tags);
article3.setTags(tags2);
tags.forEach(entityManager::persist);
tags2.forEach(entityManager::persist);
entityManager.persist(article);
entityManager.persist(article2);
entityManager.persist(article3);
entityManager.persist(article4);
entityManager.persist(article5);
entityManager.flush();
entityManager.clear();
}
@Test
void test() {
Query query = entityManager.createQuery("select a from Article a");
List<Article> tech = query.setMaxResults(20).getResultList();
tech.stream()
.map(ArticleResponseDto::new)
.toList();
}
}
I expected executing queries same in both version, it wasn't.
reproducer: https://github.com/chlrbgh89/hibernate-batch-size-bug ( https://github.com/chlrbgh89/hibernate-batch-size-bug )
reproducer Hibernate version is 6.1.6.Final
If you want to downgrade version to 5.6.14.Final , uncomment this code in build.gradle
//configurations.configureEach {
// resolutionStrategy.eachDependency {details ->
// if (details.requested.name == 'hibernate-core' ) {
// details.useTarget group: 'org.hibernate' , name: 'hibernate-core-jakarta' , version: '5.6.14.Final'
// }
// }
//}
Envrionment
* Spring Boot 3.0.1
* Hibernate 6.1.6.Final
( https://hibernate.atlassian.net/browse/HHH-16005#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-16005#add-comment?atlOrigin=ey... )
Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100213- sha1:1fa7b87 )
1 year, 11 months
[JIRA] (HHH-15991) Hibernate fails when grouping by a related many-to-one entity
by Mike Keller (JIRA)
Mike Keller ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... ) *updated* an issue
Hibernate ORM ( https://hibernate.atlassian.net/browse/HHH?atlOrigin=eyJpIjoiNjk4NTg4ZTgw... ) / Bug ( https://hibernate.atlassian.net/browse/HHH-15991?atlOrigin=eyJpIjoiNjk4NT... ) HHH-15991 ( https://hibernate.atlassian.net/browse/HHH-15991?atlOrigin=eyJpIjoiNjk4NT... ) Hibernate fails when grouping by a related many-to-one entity ( https://hibernate.atlassian.net/browse/HHH-15991?atlOrigin=eyJpIjoiNjk4NT... )
Change By: Mike Keller ( https://hibernate.atlassian.net/secure/ViewProfile.jspa?accountId=557058%... )
*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|...]
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}
( https://hibernate.atlassian.net/browse/HHH-15991#add-comment?atlOrigin=ey... ) Add Comment ( https://hibernate.atlassian.net/browse/HHH-15991#add-comment?atlOrigin=ey... )
Get Jira notifications on your phone! Download the Jira Cloud app for Android ( https://play.google.com/store/apps/details?id=com.atlassian.android.jira.... ) or iOS ( https://itunes.apple.com/app/apple-store/id1006972087?pt=696495&ct=EmailN... ) This message was sent by Atlassian Jira (v1001.0.0-SNAPSHOT#100213- sha1:1fa7b87 )
1 year, 11 months