Issue
Can someone please help me? I am trying to use the spring data JPA with a soft delete using the where clause in an inherited class. However, it seems that is not considering the inheritance when Pageable class generates its query to count the number of rows.
ClassA class (Base class): <- the deleted column is here
@Entity
@Table(name = "SYS_ELEMENT")
@Inheritance(strategy = InheritanceType.JOINED)
@DiscriminatorColumn(name = "DTYPE", discriminatorType = DiscriminatorType.STRING, length = 63) @DiscriminatorValue("Element")
@SQLDelete(sql = "UPDATE SYS_ELEMENT SET DELETED = 1, DELETED_ID = ID, DELETED_VERSION = VERSION WHERE ID = ? AND VERSION = ? ", check = ResultCheckStyle.COUNT)
@Where(clause="DELETED = 0" )
public class Element extends EntityBase {
}
ClassB class (Inherited class):
@Entity
@Table(name = "SYS_FORM")
@DiscriminatorValue("Form")
public class Form extends Element {
}
I load the following code:
Pageable pageable = PageRequest.of(0, 10);
Page page = service.findByAll(pageable);
And that is my exception:
Hibernate:
/* select
count(generatedAlias0)
from
Form as generatedAlias0 */ select
count(form0_.id) as col_0_0_
from
sys_form form0_
where
(
form0_1_.DELETED = 0
)
2020-07-09 21:23:10.123 WARN 22464 --- [nio-8080-exec-4] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 4104, SQLState: S0001
2020-07-09 21:23:10.123 ERROR 22464 --- [nio-8080-exec-4] o.h.engine.jdbc.spi.SqlExceptionHelper : The multi-part identifier "form0_1_.DELETED" could not be bound.
could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
org.hibernate.exception.SQLGrammarException: could not extract ResultSet
[Ljava.lang.StackTraceElement;@748df19a
If I remove the where clause, it works. I am not sure it is a spring data JPA or JPA issue.
As requested, here is a simple example:
- https://github.com/caiquebispoferreira/springdatajpa
- https://github.com/gtiwari333/hibernate-inheritance-JOINED-with-where-bug (simpler example)
Solution
This is an issue with Hibernate.
You can try an old hibernate version 5.4.4.Final now or wait until 5.4.19 is released. Watch for the following items for the release date.
Answered By - gtiwari333
