JPA2标准查询,针对使用隐藏列的@OneToMany关系,无需字段映射。

huangapple 未分类评论43阅读模式
英文:

JPA2 criteria query for @OneToMany relation using a hidden column without a field mapping

问题

我有一个名为Instrument的实体,具有以下字段:

@OneToMany(fetch = FetchType.EAGER, cascade = { CascadeType.PERSIST, CascadeType.MERGE })
@JoinColumn(referencedColumnName = "instrumentId", nullable = false)
public Set<InstrumentSymbol> getSymbols() {
    return this.symbols;
}

@Column
@JsonProperty("statusCode")
public Integer getStatusCode() {
    return statusCode;
}

InstrumentSymbol现在在数据库中有一个名为symbols_instrumentId的列,Hibernate会自动创建,但没有与之关联的属性。如何找到所有statusCode1InstrumentSymbol

我尝试过:

CriteriaBuilder builder = session.getCriteriaBuilder();
CriteriaQuery<InstrumentSymbol> query = builder.createQuery(InstrumentSymbol.class);
Root<InstrumentSymbol> rootSymbol = query.from(InstrumentSymbol.class);

Subquery<Long> subquery = query.subquery(Long.class);
Root<Instrument> rootInstrument = subquery.from(Instrument.class);
subquery.select(rootInstrument.get(Instrument_.instrumentId));
subquery.where(builder.equal(rootInstrument.get(Instrument_.statusCode), 1));

query.where(builder.in(rootSymbol.get("symbols_instrumentId")).in(subquery)); // <--- 此处失败

但是这会报错:

java.lang.IllegalArgumentException: Unable to locate Attribute with the given name [symbols_instrumentId] on this ManagedType [...InstrumentSymbol]

然后我尝试向我的@StaticMetamodel中添加一个自定义字段:

public static volatile SingularAttribute<InstrumentSymbol, Long> symbols_instrumentId; // NOSONAR Hibernate 5 需要这个

但不幸的是,Hibernate忽略了它:

// Hibernate为我填充了这些字段
assertEquals("Instrument#instrumentId(BASIC)", Instrument_.instrumentId.toString());
assertEquals("InstrumentSymbol#instrumentSymbolId(BASIC)", InstrumentSymbol_.instrumentSymbolId.toString());
// 但是没有填充这个...
assertEquals(null, InstrumentSymbol_.symbols_instrumentId);

如何创建一个有效的查询?

供参考,这是InstrumentSymbol的静态元数据:

@StaticMetamodel(InstrumentSymbol.class)
public class InstrumentSymbol_ { // NOSONAR Hibernate 5 需要这个
    public static volatile SingularAttribute<InstrumentSymbol, String> externalInstrumentSymbolId; // NOSONAR Hibernate 5 需要这个
    public static volatile SingularAttribute<InstrumentSymbol, Long> instrumentSymbolId; // NOSONAR Hibernate 5 需要这个
    public static volatile SingularAttribute<InstrumentSymbol, String> symbol; // NOSONAR Hibernate 5 需要这个
    public static volatile SingularAttribute<InstrumentSymbol, String> symbolType; // NOSONAR Hibernate 5 需要这个
}

(没有指向Instrument的外键列)。这是自动生成的DDL:

create table InstrumentSymbol (
    instrumentSymbolId int8 not null,
    externalInstrumentSymbolId varchar(50) not null,
    symbol varchar(40) not null,
    symbolType varchar(20) not null,
    symbols_instrumentId int8 not null,  /* 额外的列,在Java中不可见 */
    primary key (instrumentSymbolId)
);

alter table InstrumentSymbol 
   add constraint FKcb138lkaiji2wh11275m77byk 
   foreign key (symbols_instrumentId) 
   references Instrument;
英文:

I have an entity Instrument with these fields:

@OneToMany(fetch = FetchType.EAGER, cascade = { CascadeType.PERSIST, CascadeType.MERGE })
@JoinColumn(referencedColumnName = &quot;instrumentId&quot;, nullable = false)
public Set&lt;InstrumentSymbol&gt; getSymbols() {
	return this.symbols;
}

@Column
@JsonProperty(&quot;statusCode&quot;)
public Integer getStatusCode() {
	return statusCode;
}

InstrumentSymbol has now a column in the database called symbold_instrumentId which Hibernate automatically creates but there is no property associated with it. How can I find all InstrumentSymbol where statusCode is 1?

I tried

    CriteriaBuilder builder = session.getCriteriaBuilder();
    CriteriaQuery&lt;InstrumentSymbol&gt; query = builder.createQuery(InstrumentSymbol.class);
    Root&lt;InstrumentSymbol&gt; rootSymbol = query.from(InstrumentSymbol.class);

    Subquery&lt;Long&gt; subquery = query.subquery(Long.class);
    Root&lt;Instrument&gt; rootInstrument = subquery.from(Instrument.class);
    subquery.select(rootInstrument.get(Instrument_.instrumentId));
    subquery.where(builder.equal(rootInstrument.get(Instrument_.statusCode), 1));

    query.where(builder.in(rootSymbol.get(&quot;symbols_instrumentId&quot;)).in(subquery)); // &lt;--- Fails here

but that fails with

java.lang.IllegalArgumentException: Unable to locate Attribute  with the the given name [symbols_instrumentId] on this ManagedType [....InstrumentSymbol]

Then I tried to add a custom field to my @StaticMetamodel:

public static volatile SingularAttribute&lt;InstrumentSymbol, Long&gt; symbols_instrumentId; // NOSONAR Hibernate 5 requires this

but unfortunately, Hibernate ignores it:

    // Hibernate fills these fields for me
    assertEquals(&quot;Instrument#instrumentId(BASIC)&quot;, Instrument_.instrumentId.toString());
    assertEquals(&quot;InstrumentSymbol#instrumentSymbolId(BASIC)&quot;, InstrumentSymbol_.instrumentSymbolId.toString());
    // But not this...
    assertEquals(null, InstrumentSymbol_.symbols_instrumentId);

How do I create a query which works?

For reference, this is the static meta data for InstrumentSymbol:

@StaticMetamodel(InstrumentSymbol.class)
public class InstrumentSymbol_ { // NOSONAR Hibernate 5 requires this
    public static volatile SingularAttribute&lt;InstrumentSymbol, String&gt; externalInstrumentSymbolId; // NOSONAR Hibernate 5 requires this
    public static volatile SingularAttribute&lt;InstrumentSymbol, Long&gt; instrumentSymbolId; // NOSONAR Hibernate 5 requires this
    public static volatile SingularAttribute&lt;InstrumentSymbol, String&gt; symbol; // NOSONAR Hibernate 5 requires this
    public static volatile SingularAttribute&lt;InstrumentSymbol, String&gt; symbolType; // NOSONAR Hibernate 5 requires this
}

(no column with the FK of Instrument). This is the auto generated DDL:

create table InstrumentSymbol (
    instrumentSymbolId int8 not null,
    externalInstrumentSymbolId varchar(50) not null,
    symbol varchar(40) not null,
    symbolType varchar(20) not null,
    symbols_instrumentId int8 not null,  /* Additional column, not visible from Java */
    primary key (instrumentSymbolId)
);

alter table InstrumentSymbol 
   add constraint FKcb138lkaiji2wh11275m77byk 
   foreign key (symbols_instrumentId) 
   references Instrument;

huangapple
  • 本文由 发表于 2020年4月10日 01:15:58
  • 转载请务必保留本文链接:https://java.coder-hub.com/61126625.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定