java - Jpa Specification IN Clause onjoined property 未按预期工作

Question

我们在使用 JPA 规范时遇到了奇怪的行为。所以我们想在一个连接的属性上做一个 where 子句，这样我们就只获取一个连接项的属性与过滤器匹配的项。通过 SQL 在数据库上运行生成的查询按预期工作，但是在 Spring JPA 中似乎没有执行过滤。

描述

情况：有两个实体，每个实体都有 1 个子实体保存在数据库中。两者都有一个具有不同值的属性。我们希望通过 JPA 规范过滤子实体之一的值。

预期结果：数据库仅返回与子实体属性的查询匹配的实体。

实际结果：数据库返回两个实体，即使是属性值不匹配的实体。

这是此的代码：

实体

@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();
    }
}

规格

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

过滤对象

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

}

保存到数据库后的实体

筛选

Jpa Sql 控制台查询

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 (
                ?
            )
        )

绑定变量

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]

实际结果

在暂存数据库上运行 sql 时的结果

分期数据：

在那里运行查询时的结果：

如您所见，如果您在没有 JPA/Hibernate 的情况下简单地运行查询，则该查询可以暂存。那么这里发生了什么？我们如何解决这个问题？

需要更多信息或问题，请告诉我。我们被困在这一点上。

提前致谢！

标签： javaspringhibernatejpa