如果我在父子表之间建立一个关系,并且父子表都有额外的约束条件,然后使用 @JoinFetch,那么子表的额外约束条件会被忽略。
例如:
TableA.java:
例如:
TableA.java:
@javax.persistence.Entity
@Table(name = "TABLE_A")
@AdditionalCriteria("this.tableAfield2=:propA")
public class TableA {
@Id
@Column(name = "TABLEAFIELD1")
private String tableAfield1;
@Column(name = "TABLEAFIELD2")
private String tableAfield2;
@JoinColumn(name = "TABLEAFIELD2", referencedColumnName = "TABLEBFIELD1", insertable = false, updatable = false)
@OneToOne(fetch = FetchType.EAGER)
// @JoinFetch(JoinFetchType.OUTER)
private TableB tableAtableB;
}
TableB.java:
@javax.persistence.Entity
@Table(name = "TABLE_B")
@AdditionalCriteria("this.tableBfield2=:propB")
public class TableB {
@Id
@Column(name = "TABLEBFIELD1")
private String tableBfield1;
@Column(name = "TABLEBFIELD2")
private String tableBfield2;
public String getTableBfield1() {
return tableBfield1;
}
public String getTableBfield2() {
return tableBfield2;
}
}
主要内容:
em.setProperty("propA", "propertyAValue");
em.setProperty("propB", "propertyBValue");
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<TableA> criteriaQuery = cb.createQuery(TableA.class);
Root<TableA> tableA = criteriaQuery.from(TableA.class);
Predicate pred = cb.equal(tableA.get("tableAfield1"), "keyA1");
criteriaQuery.where(pred);
List<TableA> results = em.createQuery(criteriaQuery).getResultList();
将表格A设置如示例(注释掉JoinFetch),应用程序会创建2个SQL语句。
SELECT TABLEAFIELD1, TABLEAFIELD2 FROM TABLE_A WHERE ((TABLEAFIELD1 = ?) AND (TABLEAFIELD2 = ?))
bind => [keyA1, propertyAValue]
SELECT TABLEBFIELD1, TABLEBFIELD2 FROM TABLE_B WHERE ((TABLEBFIELD1 = ?) AND (TABLEBFIELD2 = ?))
bind => [propertyAValue, propertyBValue]
这是可以接受的,因为eclipselink会按需加载table_b。
但是对于我们的应用程序,我们需要一个单一的SQL语句,因为可能有成千上万行数据需要进行单一的连接查询。
因此,如果我重新加入@JoinFetch,则生成的SQL语句将如下:
SELECT t1.TABLEAFIELD1, t1.TABLEAFIELD2, t0.TABLEBFIELD1, t0.TABLEBFIELD2 FROM TABLE_A t1 LEFT OUTER JOIN TABLE_B t0 ON (t0.TABLEBFIELD1 = t1.TABLEAFIELD2) WHERE ((t1.TABLEAFIELD1 = ?) AND (t1.TABLEAFIELD2 = ?))
bind => [keyA1, propertyAValue]
TableB的additionalCriteria未被添加(没有t0.tableBField1=?(propertyBValue))
有什么建议吗?这让我疯了。
非常感谢。
为了完整起见,这里是表格:
create table TABLE_A (
TABLEAFIELD1 varchar2(20),
TABLEAFIELD2 varchar2(30),
CONSTRAINT tableApk PRIMARY KEY (TABLEAFIELD1)
) ;
create table TABLE_B (
TABLEBFIELD1 varchar2(20),
TABLEBFIELD2 varchar2(30),
CONSTRAINT tableBpk PRIMARY KEY (TABLEBFIELD1)
) ;
insert into TABLE_A (TABLEAFIELD1,TABLEAFIELD2) values ('keyA1','propertyAValue');
insert into TABLE_A (TABLEAFIELD1,TABLEAFIELD2) values ('keyA2','propertyAValue');
insert into TABLE_A (TABLEAFIELD1,TABLEAFIELD2) values ('keyA3','random');
insert into TABLE_B (TABLEBFIELD1,TABLEBFIELD2) values ('propertyAValue','propertyBValue');