使用 @JoinTable 和 SQLQuery 获取连接

发布于 2024-08-08 22:30:57 字数 2263 浏览 9 评论 0原文

我有一个具有属性“latestHistory”的映射实体,该实体通过联接表进行映射,例如:

class Record {

  @OneToOne(cascade = { CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REMOVE }, fetch = FetchType.LAZY, optional = true)
  @JoinTable(name = "latest_history_join_view", joinColumns = { @JoinColumn(name = "record_id") }, inverseJoinColumns = { @JoinColumn(name = "history_id") })
  @AccessType("field")
  public History getLatestHistory() { ... }
}

当我调用 myRecord.getLatestHistory() 时,映射可以正常工作。

我有一个复杂的本机 SQL 查询,它返回一批记录,并使用连接表连接每个记录的历史记录。我想从查询中返回记录实体,并在结果中填充历史对象。我的尝试如下所示:

StringBuffer sb = new StringBuffer();
sb.append("select {r.*}, {latestHistory.*}");
sb.append(" from record r");
sb.append(" left join latest_history_join_view lh on lh.record_id = r.record_id");
sb.append(" left join history latestHistory on latestHistory.history_id = lh.history_id");
SQLQuery query = session.createSQLQuery(sb.toString());
query.addEntity("r", Record.class).addJoin("latestHistory", "r.latestHistory");

当我这样做时,它会生成一个查询,例如:

select 
   r.record_id, r.name..., 
   r_1.history_id,  --this part is wrong; there is no such alias r_1 
   latestHistory.history_id, latestHistory.update_date, ...
 from record r
 left join latest_history_join_view lh on lh.record_id = r.record_id
 left join history latestHistory on latestHistory.history_id = lh.history_id

如何让它正确加入并获取我的关联,而不弄乱选择列表?

[更新:我尝试过的一些方法:

select {r.*}, {latestHistory.*} -> SQL error, generates a wrong column name "r_1.history_id"
select {r.*}, {anyOtherEntityAssociatedToR.*} -> wrong column name (as above)
select {r.*}, {r.history_id}, {latestHistory.*} -> hibernate error, r has no history_id column
select r.*, lh.history_id as history_id -> this works (though hackish), but doesn't accomplish the join
select r.*, lh.history_id as history_id, latestHistory.* -> appears correct, but results in column name collisions
select r.*, {latestHistory.*} -> error when hibernate looks for a nonexistent column in the result set (this happens if there is any alias at all in the select list)

无论我使用 addEntity(...) 还是 addJoin(...) 似乎都没有太大区别,只要最左边(根)实体使用 addEntity 添加。

]

I have a mapped entity with a property "latestHistory", which is mapped through a join table, like:

class Record {

  @OneToOne(cascade = { CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REMOVE }, fetch = FetchType.LAZY, optional = true)
  @JoinTable(name = "latest_history_join_view", joinColumns = { @JoinColumn(name = "record_id") }, inverseJoinColumns = { @JoinColumn(name = "history_id") })
  @AccessType("field")
  public History getLatestHistory() { ... }
}

The mapping works correctly when I call myRecord.getLatestHistory().

I have a complex native SQL query, which returns a batch of Records, and joins on the History for each record using the join table. I want to return Record entites from the query, and have the History objects populated in the result. My attempt looks like this:

StringBuffer sb = new StringBuffer();
sb.append("select {r.*}, {latestHistory.*}");
sb.append(" from record r");
sb.append(" left join latest_history_join_view lh on lh.record_id = r.record_id");
sb.append(" left join history latestHistory on latestHistory.history_id = lh.history_id");
SQLQuery query = session.createSQLQuery(sb.toString());
query.addEntity("r", Record.class).addJoin("latestHistory", "r.latestHistory");

When I do this, it generates a query like:

select 
   r.record_id, r.name..., 
   r_1.history_id,  --this part is wrong; there is no such alias r_1 
   latestHistory.history_id, latestHistory.update_date, ...
 from record r
 left join latest_history_join_view lh on lh.record_id = r.record_id
 left join history latestHistory on latestHistory.history_id = lh.history_id

How can I get it to join correctly and fetch my association, without messing up the select list?

[Update: some of the approaches that I've tried:

select {r.*}, {latestHistory.*} -> SQL error, generates a wrong column name "r_1.history_id"
select {r.*}, {anyOtherEntityAssociatedToR.*} -> wrong column name (as above)
select {r.*}, {r.history_id}, {latestHistory.*} -> hibernate error, r has no history_id column
select r.*, lh.history_id as history_id -> this works (though hackish), but doesn't accomplish the join
select r.*, lh.history_id as history_id, latestHistory.* -> appears correct, but results in column name collisions
select r.*, {latestHistory.*} -> error when hibernate looks for a nonexistent column in the result set (this happens if there is any alias at all in the select list)

It doesn't seem to make a lot of difference whether I use addEntity(...) or addJoin(...), as long as the leftmost (root) entity is added using addEntity.

]

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

温折酒 2024-08-15 22:30:57

我认为您实际上需要在 select 中指定 latestHistory 的完整路径,否则

select {r.*}, {r.latestHistory.*}

Hibernate 会感到困惑并尝试将其视为单独的实体。另一个选项是根本不在 select 中指定注入的别名,其中 只要表中的列顺序与实体中的属性顺序匹配,就应该适用于单个“一对一”关系。

不过,我从未在关联表上的@OneToOne 上尝试过这个。

I'm thinking you actually need to specify full path for your latestHistory in select e.g.

select {r.*}, {r.latestHistory.*}

otherwise Hibernate gets confused and attempts to treat it as a separate entity. The other option is to not specify injected aliases in select at all which should work for a single "to-one" relationship so long as column order in your tables matches property order in your entities.

I've never tried this on @OneToOne over association table, though.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文