We have encountered weird behavior when using JPA Specification. So we wanted to do a where clause on a joined property, so that we only fetch items where a property of a joined item matches the filter. Running the generated query on a database by SQL works as expected, however in Spring JPA doesn't seem to execute the filtering.
Description
Situation: Having two entities with each 1 sub-entity saved in the database. Both have a property with different values. We want, through JPA specification, filter on the value of one of the sub-entities.
Expected Result: The database only returns the entity for which the query on the property of the sub-entity matches.
Actual Result: The database returns both entities, even the one where the value of the property doesn't match.
Here is the code for this:
Entities
@Entity
@Table(name="item")
@Builder
@AllArgsConstructor
@NoArgsConstructor
@Data
public class ItemEntity implements Serializable {
@Id
@GeneratedValue(strategy= GenerationType.IDENTITY)
private Long id;
@Column(name = "item_number")
private String itemNumber;
@Column(name = "edition")
private String edition;
@OneToMany(cascade = CascadeType.ALL)
@JoinColumn(name="item_number", referencedColumnName = "item_number")
private List<PlmItemInfoEntity> plmItemInfoEntity;
@Enumerated(EnumType.STRING)
private ItemStatus status;
private String fsi;
private String sqa;
@Column(name = "creation_date")
private Long creationDate;
@Column(name = "modified_date")
private Long modifiedDate;
@Column(name = "plm_refresh_date")
private Long plmRefreshDate;
public PlmItemInfoEntity getLatestPlmInfoEntity() {
// TODO Clarify what the "latest" item is -- QAP-63
return plmItemInfoEntity.get(0);
}
@PrePersist
void createdAt() {
this.creationDate = this.modifiedDate = ZonedDateTime.now().toEpochSecond();
}
@PreUpdate
void updatedAt() {
this.modifiedDate = ZonedDateTime.now().toEpochSecond();
}
}
@Entity
@Immutable
@Table(name="plm_item_info")
@Builder
@NoArgsConstructor
@AllArgsConstructor
@Data
public class PlmItemInfoEntity {
@Id
@GeneratedValue(strategy= GenerationType.IDENTITY)
private Long id;
@Column(name = "item_number")
private String itemNumber;
@Column(name = "item_name")
private String itemName;
private String edition;
@Enumerated(EnumType.STRING)
private PlmItemStatus status;
private Long date;
@Column(name = "creation_date")
private Long creationDate;
@PrePersist
void createdAt() {
this.creationDate = ZonedDateTime.now().toEpochSecond();
}
}
Specification
public class ItemSpecification extends AbstractSpecification<ItemEntity> {
private ItemFilter filter;
public ItemSpecification(ItemFilter filter) {
this.filter = filter;
}
@Override
public Predicate toPredicate(Root<ItemEntity> root, CriteriaQuery<?> query,
CriteriaBuilder cb) {
query.distinct(true);
Predicate base = cb.isTrue(cb.literal(true));
Join<Object, Object> plmItemInfoEntityJoin = root.join("plmItemInfoEntity", JoinType.INNER);
final Subquery<Number> subquery = query.subquery(Number.class);
final Root<PlmItemInfoEntity> creationDateConstraint =
subquery.from(PlmItemInfoEntity.class);
subquery.select(cb.max(creationDateConstraint.get("creationDate")));
subquery.groupBy(creationDateConstraint.get("creationDate"),
creationDateConstraint.get("itemNumber"));
base = cb.and(base, cb.in(root.get("plmRefreshDate")).value(subquery));
base = cb.and(base, plmItemInfoEntityJoin.get("itemName").in(filter.getItemName()));
return base;
}
}
Filter Object
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class ItemFilter implements Serializable {
private DatePair date;
private List<String> itemName = new ArrayList<>();
public Optional<DatePair> getDate() {
return Optional.ofNullable(date);
}
}
Entities after saved to database
Filter
Jpa Sql Console Query
2021-10-04 11:19:48.893 CID:DEBUG 15140 --- [ main] org.hibernate.SQL :
select
distinct itementity0_.id as id1_0_,
itementity0_.creation_date as creation2_0_,
itementity0_.edition as edition3_0_,
itementity0_.fsi as fsi4_0_,
itementity0_.item_number as item_num5_0_,
itementity0_.modified_date as modified6_0_,
itementity0_.plm_refresh_date as plm_refr7_0_,
itementity0_.sqa as sqa8_0_,
itementity0_.status as status9_0_
from
item itementity0_
inner join
plm_item_info plmiteminf1_
on itementity0_.item_number=plmiteminf1_.item_number
where
?=1
and (
itementity0_.plm_refresh_date in (
select
max(plmiteminf2_.creation_date)
from
plm_item_info plmiteminf2_
group by
plmiteminf2_.creation_date ,
plmiteminf2_.item_number
)
)
and (
plmiteminf1_.item_name in (
?
)
)
Hibernate:
select
distinct itementity0_.id as id1_0_,
itementity0_.creation_date as creation2_0_,
itementity0_.edition as edition3_0_,
itementity0_.fsi as fsi4_0_,
itementity0_.item_number as item_num5_0_,
itementity0_.modified_date as modified6_0_,
itementity0_.plm_refresh_date as plm_refr7_0_,
itementity0_.sqa as sqa8_0_,
itementity0_.status as status9_0_
from
item itementity0_
inner join
plm_item_info plmiteminf1_
on itementity0_.item_number=plmiteminf1_.item_number
where
?=1
and (
itementity0_.plm_refresh_date in (
select
max(plmiteminf2_.creation_date)
from
plm_item_info plmiteminf2_
group by
plmiteminf2_.creation_date ,
plmiteminf2_.item_number
)
)
and (
plmiteminf1_.item_name in (
?
)
)
Bind Variables
2021-10-04 11:45:23.923 CID:TRACE 14328 --- [ main] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [BOOLEAN] - [true]
2021-10-04 11:45:23.925 CID:TRACE 14328 --- [ main] o.h.type.descriptor.sql.BasicBinder : binding parameter [2] as [VARCHAR] - [TOFIND]
Actual Result
Result when running the sql on staging database
The data on staging:
The result when running the query there:
So as you can see, the query works on staging if you run it plainly, without JPA/Hibernate. So what is going on here? How do we fix this?
More info needed or questions, please let me know. We are stuck on this one.
Thanks in advance!