oracle 如何在 Hibernate 中使用 @Subselect
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25226741/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use @Subselect in Hibernate
提问by Chaitanya
I am trying to work on an example for @Subselectusing Hibernate documentaion.
我正在尝试@Subselect使用Hibernate documentaion的示例。
I have created entities for Bid and Item as follows:
我为 Bid 和 Item 创建了实体，如下所示：
@Entity
public class Bid {
@Id
private int id;
@Column(name="item_id")
private int itemId;
@Column
private int amount;
//getters & setters
}
@Entity
public class Item {
@Id
private int id;
@Column
private String name;
//getters and setters
}
I have inserted some records in database tables for Bid and Item. Now I have created another entity to test the @Subselect as:
我在 Bid 和 Item 的数据库表中插入了一些记录。现在我创建了另一个实体来测试@Subselect：
@Entity
@Subselect("select item.name name, max(bid.amount) amount, count(*) count " + "from item "
+ "join bid on bid.item_id = item.id " + "group by item.name")
@Synchronize({ "item", "bid" })
// tables impacted
public class Summary {
@Id @GeneratedValue
private int id;
@Column
private String name;
@Column
private String amount;
@Column
private String count;
//getters & setters
}
I am new to Hibernate so trying to create a sample program to test the feature of @Subselect.
我是 Hibernate 的新手，因此尝试创建一个示例程序来测试 @Subselect 的功能。
public class AppTest {
public static void main(String[] args) {
Session session = HibernateUtil.getSessionFactory().getCurrentSession();
session.beginTransaction();
//createItemsAndBids(session);
Summary summary = new Summary();
session.save(summary);
session.getTransaction().commit();
HibernateUtil.getSessionFactory().close();
}
When I run this program I am getting below errors:
当我运行这个程序时，我收到以下错误：
Hibernate: select hibernate_sequence.nextval from dual Hibernate: insert into ( select item.name name, max(bid.amount) amount, count(*) count from item join bid on bid.item_id = item.id group by item.name ) (amount, count, name, id) values (?, ?, ?, ?) Aug 10, 2014 1:24:31 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions WARN: SQL Error: 904, SQLState: 42000 Aug 10, 2014 1:24:31 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions ERROR: ORA-00904: "ID": invalid identifier
Caused by: java.sql.SQLSyntaxErrorException: ORA-00904: "ID": invalid identifier
Hibernate: select hibernate_sequence.nextval from dual Hibernate: insert into ( select item.name name, max(bid.amount) amount, count(*) count from item join bid on bid.item_id = item.id group by item.name ) (amount, count, name, id) values (?, ?, ?, ?) 2014 年 8 月 10 日下午 1:24:31 org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions 警告：SQL 错误：904，SQLState： 42000 2014 年 8 月 10 日下午 1:24:31 org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions 错误：ORA-00904：“ID”：无效标识符
引起：java.sql.SQLSyntaxErrorException：ORA-00904：“ID”：标识符无效
Please help me how to test the feature of @Subselect of hibernate
请帮助我如何测试hibernate的@Subselect的功能
Also I tried using HQL, even then I am getting same error:
我也尝试使用 HQL，即使这样我也遇到了同样的错误：
public class AppTest {
public static void main(String[] args) {
Session session = HibernateUtil.getSessionFactory().getCurrentSession();
session.beginTransaction();
//createItemsAndBids(session);
Query query = session.createQuery("from Summary");
List result = query.list();
System.out.println(result);
session.getTransaction().commit();
HibernateUtil.getSessionFactory().close();
}
Update: The error I am getting with this HQL query is :
更新：我在这个 HQL 查询中遇到的错误是：
Aug 11, 2014 12:35:07 AM org.hibernate.tool.hbm2ddl.SchemaUpdate execute
INFO: HHH000232: Schema update complete
Hibernate: select summary0_.id as id1_2_, summary0_.amount as amount2_2_, summary0_.count as count3_2_, summary0_.name as name4_2_ from ( select item.name name, max(bid.amount) amount, count(*) count from item join bid on bid.item_id = item.id group by item.name ) summary0_
Aug 11, 2014 12:35:07 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 904, SQLState: 42000
Aug 11, 2014 12:35:07 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: ORA-00904: "SUMMARY0_"."ID": invalid identifier
Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:80)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:91)
at org.hibernate.loader.Loader.getResultSet(Loader.java:2065)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1862)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1838)
at org.hibernate.loader.Loader.doQuery(Loader.java:909)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:354)
at org.hibernate.loader.Loader.doList(Loader.java:2553)
at org.hibernate.loader.Loader.doList(Loader.java:2539)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2369)
at org.hibernate.loader.Loader.list(Loader.java:2364)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:496)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:387)
at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:231)
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1264)
at org.hibernate.internal.QueryImpl.list(QueryImpl.java:103)
at org.hibernate.tutorials.hibernate5.one.AppTest.main(AppTest.java:19)
回答by JB Nizet
Summary is supposed to be an immutable, read-only entity. It makes no sense to create a Summary. What you can do is create Items, create Bids, then query Summary instances.
摘要应该是一个不可变的只读实体。创建摘要是没有意义的。您可以做的是创建项目，创建投标，然后查询摘要实例。
EDIT: the error is pretty clear. Look at the generated SQL:
编辑：错误很明显。查看生成的SQL：
select summary0_.id as id1_2_, summary0_.amount as amount2_2_, summary0_.count as count3_2_, summary0_.name as name4_2_ from ( select item.name name, max(bid.amount) amount, count(*) count from item join bid on bid.item_id = item.id group by item.name ) summary0_
and at the error:
并在错误处：
ORA-00904: "SUMMARY0_"."ID": invalid identifier
Your entity defines an id property:
您的实体定义了一个 id 属性：
@Id @GeneratedValue
private int id;
but the query of the Subselect annotation doesn't select any property named id:
但是 Subselect 注释的查询没有选择任何名为 id 的属性：
select item.name name, max(bid.amount) amount, count(*) count from item
join bid on bid.item_id = item.id
group by item.name
You probably want your query to be
您可能希望您的查询是
select item.id id, item.name name, max(bid.amount) amount, count(*) count from item
join bid on bid.item_id = item.id
group by item.id, item.name
Also note that the @GeneratedValueannotation doesn't make sense, since you can't persist instances of Summary, and Hibernate will thus never have to generate an ID for this entity.
另请注意，@GeneratedValue注释没有意义，因为您无法保留摘要的实例，因此 Hibernate 将永远不必为该实体生成 ID。
