Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Bonjour,
J'ai un probleme dans un sub-select dans un in. Mes classes Foo et Bar ont le meme nom d'id et hibernate n'alias pas l'id dans le subselect alors que je fais un jointure entre Foo et Bar.
Que puis je faire, à part faire le sub-select avant ?
Merci d'avance

Hibernate version:
hibernate-3.1
Mapping documents:
<class name="com.test.Foo" table="FOO" mutable="true">
<id name="id" type="string" length="4">
<generator class="assigned" />
</id>
<property name="name" length="25" type="string" />
<many-to-one name="bar" class="com.test.Bar" column="bar" not-null="true" />
</class>

<class name="com.test.Bar" table="BAR" mutable="true">
<id name="id" type="string" length="4">
<generator class="assigned" />
</id>
<property name="name" length="25" type="string" />
</class>

Code between sessionFactory.openSession() and session.close():
sessionHibernate.createQuery("update Foo foo set foo.name = 'test' "
+ "where foo.id in (select foo2.id from Foo as foo2 join foo2.bar as bar "
+ " where bar.name = 'toto' ) ")
.executeUpdate();

Full stack trace of any exception that occurs:
Hibernate: update FOO set name='test' where id in (select id from FOO foo1_ inner join BAR bar2_ on bar=bar2_.id where name='toto')
11:21:56,542 6660 WARN JDBCExceptionReporter (logExceptions, 71 ) - SQL Error: 918, SQLState: 42000
11:21:56,542 6660 ERROR JDBCExceptionReporter (logExceptions, 72 ) - ORA-00918: column ambiguously defined

11:21:56,542 6660 FATAL TestLauncher (main, 110 ) - Erreur lors de l'execution des tests
11:21:56,542 6660 FATAL TestLauncher (main, 111 ) - org.hibernate.exception.SQLGrammarException: could not execute update query
org.hibernate.exception.SQLGrammarException: could not execute update query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:65)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:84)
at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:326)
at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:202)
at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1111)
at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94)
at com.bozo.integrationlayer.TestLauncher.testUpdateFoo(TestLauncher.java:244)
at com.bozo.integrationlayer.TestLauncher.execute(TestLauncher.java:178)
at com.bozo.integrationlayer.TestLauncher.main(TestLauncher.java:106)
Caused by: java.sql.SQLException: ORA-00918: column ambiguously defined

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:579)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1894)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1094)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2132)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2015)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2877)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608)
at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:75)
... 7 more

Name and version of the database you are using:
Oracle9i Enterprise Edition Release 9.2.0.8.0

<property name="connection.provider_class">
org.hibernate.connection.DriverManagerConnectionProvider
</property>
<property name="connection.driver_class">oracle.jdbc.driver.OracleDriver
</property>
<property name="dialect">org.hibernate.dialect.Oracle9Dialect</property>

The generated SQL (show_sql=true):
Hibernate: update FOO set name='test' where id in (select id from FOO foo1_ inner join BAR bar2_ on bar=bar2_.id where name='toto')

Debug level Hibernate log excerpt:


Problems with Session and transaction handling?

Read this: http://hibernate.org/42.html

Bonjour,

sorry, my french is too bad for writing, so I have to use english.

I had the same problem and found out, that hibernate skips your aliases (foo, bar) when converting the query. As your classes foo and bar both define a field 'id', the query created by hibernate is not able to determine which id column should be used from the subselect statement.

I simply renamed one id column and then the query worked.

Thanks for your response.
I finded an other solution to avoid this problem whith an other select.

List list = sessionHibernate.createQuery("select foo2 from Foo as foo2 join foo2.bar as bar where bar.name = 'toto' ").list();
sessionHibernate.createQuery("update Foo foo set foo.name = 'test' where foo.id in (:list)).setParameterList("list",list).executeUpdate();

And it work.