案例:我有2个具有以下OneToMany关系的表:TagAbstract 1..* TagConf,其中:


tag_abstract_ID是一个字段,用于标识许多TagConf记录,这些记录是单个标签对象的历史配置,
“活动配置”被识别为valid_until = NULL
活动标签是具有最新配置活动的标签。


数据库模式为:

CREATE TABLE TagAbstract (
  tag_abstract_ID INT UNSIGNED NOT NULL AUTO_INCREMENT,

  PRIMARY KEY (tag_abstract_ID)
);

CREATE TABLE TagConf (
  tag_conf_ID          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  tag_abstract_ID      INT UNSIGNED NOT NULL,
  valid_until          DATETIME,

  PRIMARY KEY (tag_conf_ID),
  FOREIGN KEY (tag_abstract_ID) REFERENCES TagAbstract (tag_abstract_ID)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);


问题:是否可以使用这些TagAbstract记录的条件来定义Hibernate / JPA查询(也许是HQL),其中引用的TagConf的最后一个“处于活动状态”(如上所述)?

我被困在这里:

public List<TagAbstract> fetchTagAbstractActive() {
    //noinspection unchecked

    CriteriaBuilder builder = entityManager.getCriteriaBuilder();

    CriteriaQuery<TagAbstract> criteria = builder.createQuery(TagAbstract.class);
    Root<TagAbstract> tagAbstractRoot = criteria.from(TagAbstract.class);
    criteria.select(tagAbstractRoot);

    // TODO: how to correctly define following criterion in Hibernate/JPA query?
    // ---------------------------------------------------------------------
    Predicate predicate = ?; // for tagConfigs[last].validUntil == NULL
    // ---------------------------------------------------------------------

    criteria.where(predicate);
    return entityManager.createQuery(criteria).getResultList();
}


对于此样本数据,它应该仅选择记录12

record 0
    tagConfigs
        ref 0
            tagConfId      1
            tagAbstractId  1
            validUntil     "11-08-2017 08:11:45"

record 1
    tagConfigs
        ref 0
            tagConfId      2
            tagAbstractId  2
            validUntil     "11-08-2017 08:19:19"
        ref 1
            tagConfId      4
            tagAbstractId  2
            validUntil     NULL

record 2
    tagConfigs
        ref 0
            tagConfId      3
            tagAbstractId  3
            validUntil     NULL


数据映射定义如下:

@Entity
@Table(name = "TagAbstract")
public class TagAbstract {
    private static final long serialVersionUID = 1L;

    @Id()
    @GeneratedValue(generator = "sequence", strategy = GenerationType.SEQUENCE)
    @SequenceGenerator(name = "sequence", allocationSize = 10)
    @Column(name = "tag_abstract_ID")
    private long tagAbstractId;

    @OneToMany(mappedBy = "tagAbstractId", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private List<TagConf> tagConfigs = new ArrayList<>();

    public List<TagConf> getTagConfigs() {
        return tagConfigs;
    }
}

@Entity
@Table(name = "TagConf")
public class TagConf {
    private static final long serialVersionUID = 1L;

    @Id()
    @GeneratedValue(generator = "sequence", strategy = GenerationType.SEQUENCE)
    @SequenceGenerator(name = "sequence", allocationSize = 10)
    @Column(name = "tag_conf_ID")
    private long tagConfId;

    @Column(name = "tag_abstract_ID")
    private long tagAbstractId;

    @JsonFormat(shape = JsonFormat.Shape.STRING, pattern = Const.DATE_TIME_PATTERN)
    @Column(name = "valid_until")
    private Date validUntil;

    public long getTagConfId() {
        return tagConfId;
    }

    public long getTagAbstractId() {
        return tagAbstractId;
    }

    public Date getValidUntil() {
        return validUntil;
    }
}

最佳答案

所以我已经根据上面评论中JB Nizet的建议纠正了映射-在@ManyToOne中添加了TagConf映射:

@Entity
@Table(name = "TagAbstract")
public class TagAbstract {
    private static final long serialVersionUID = 1L;

    @Id()
    @GeneratedValue(generator = "sequence", strategy = GenerationType.SEQUENCE)
    @SequenceGenerator(name = "sequence", allocationSize = 10)
    @Column(name = "tag_abstract_ID")
    private long tagAbstractId;

    @OneToMany(mappedBy = "tagAbstract", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private List<TagConf> tagConfigs = new ArrayList<>();

    public List<TagConf> getTagConfigs() {
        return tagConfigs;
    }
}

@Entity
@Table(name = "TagConf")
public class TagConf {
    private static final long serialVersionUID = 1L;

    @Id()
    @GeneratedValue(generator = "sequence", strategy = GenerationType.SEQUENCE)
    @SequenceGenerator(name = "sequence", allocationSize = 10)
    @Column(name = "tag_conf_ID")
    private long tagConfId;

    @ManyToOne
    @JoinColumn(name = "tag_abstract_ID", foreignKey = @ForeignKey(name = "tag_abstract_ID"))
    private TagAbstract tagAbstract;

    @JsonFormat(shape = JsonFormat.Shape.STRING, pattern = Const.DATE_TIME_PATTERN)
    @Column(name = "valid_until")
    private Date validUntil;

    public long getTagConfId() {
        return tagConfId;
    }

    public long getTagAbstractId() {
        return tagAbstract.getTagAbstractId();
    }

    public Date getValidUntil() {
        return validUntil;
    }
}


而且有效...

    String hql = "select t from TagAbstract as t join t.tagConfigs tc where tc.validUntil is null";
    return (List<TagAbstract>) entityManager.createQuery(hql).getResultList();


感谢JB Nizet!

10-06 06:47