案例:我有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();
}
对于此样本数据,它应该仅选择记录
1
和2
: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!