在指数()函数给出“ORA-01722:无效号码”错误
问题描述:
我创建由以下hibernate documentation在指数()函数给出“ORA-01722:无效号码”错误
该文件有以下HQL示例使用in indices
功能处于休眠HQL一个例子:
from Show show where 'fizard' in indices(show.acts)
所以我创建了一个实体Show
与acts
在像这样的列表:
@Entity
public class Show {
@Id
@GeneratedValue
private int id;
private String name;
@ElementCollection
private List<String> acts;
// Getters & Settters
}
当我尝试执行文件中提到那么HQL我得到了异常,因为:
Exception in thread "main" org.hibernate.QueryException: unindexed collection before indices() [from Show show where 'fizard' in indices(show.acts)]
因此,为了使作为索引我更新了我的Show
实体属性acts
这样的集合:
@ElementCollection
@OrderColumn
private List<String> acts;
这是我的计划启动后抛出错误为:
Hibernate: select show0_.id as id1_2_, show0_.name as name2_2_ from Show show0_ where 'fizard' in (select acts1_.acts_ORDER from Show_acts acts1_ where show0_.id=acts1_.Show_id)
ERROR: ORA-01722: invalid number
Oct 23, 2014 10:12:31 AM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl stop
INFO: HHH000030: Cleaning up connection pool [jdbc:oracle:thin:@localhost:1521:xe]
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 AppTest.showData(AppTest3.java:44)
at AppTest.main(AppTest3.java:29)
Caused by: java.sql.SQLSyntaxErrorException: ORA-01722: invalid number
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:207)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:884)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1289)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3584)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3628)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1493)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82)
... 16 more
这里是我的工作程序:
要保存数据到数据库我有以下逻辑:
private static void saveData() {
Session session = getSession();
session.getTransaction().begin();
List<String> acts = new ArrayList<String>();
acts.add("fizard");
acts.add("Lord of Rings");
Show s = new Show();
s.setName("One");
s.setActs(acts);
List<String> acts1 = new ArrayList<String>();
acts1.add("Tales");
acts1.add("Stories");
Show s1 = new Show();
s1.setName("Two");
s1.setActs(acts1);
session.save(s);
session.save(s1);
session.getTransaction().commit();
session.close();
}
然后运行HQL我有如下的逻辑:
private static void showData() {
Session session = getSession();
session.getTransaction().begin();
Query q = session
.createQuery("from Show show where 'fizard' in indices(show.acts)");
List<Show> result = q.list();
System.out.println("result=" + result);
for (Show p : result) {
System.out.println(p.getName());
}
session.getTransaction().commit();
session.close();
}
基于错误的,我明白,我试图比较一个字符串'fizard',索引是一个数字。但是,因为我只是遵循官方的休眠文档,有人可以告诉我如何使用Hibernate的in indices
函数编写简单的程序。
答
你质疑这一点: 休眠: 选择show0_.id为id1_2_,show0_.name从展会name2_2_ show0_ 在 其中 'fizard'(从Show_acts选择acts1_.acts_ORDER acts1_其中show0_.id = acts1_.Show_id );
意义是可以打包的,但acts1_.acts_ORDER可能是一个数字(例如数字)列来获得像这样的结果。