-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 3 posts ] 
  Previous topic | Next topic 
Author Message
josep.mena
 Post subject: Criteria with many to many and setMaxResults
PostPosted: Fri Jun 27, 2008 2:37 am 
Newbie

Joined: Fri Jun 27, 2008 2:14 am
Posts: 2
Location: Barcelona
Hello,
I'm having problems when trying to use Criteria with a many to many relation. I'm using it with setFirstResult and setMaxResults.

Hibernate version: 3.0.5

Mapping documents:
<class name="es.abertis.neo.seguridad.model.Usuario" table="N_SEG_USUARIOS">
<meta attribute="extra-import">es.abertis.neo.base.hibernate.model.PersistentObject</meta>
<meta attribute="extends">PersistentObject</meta>
<id name="id" type="java.lang.Long">
<column name="ID" precision="8" scale="0" />
<generator class="native"></generator>
</id>
<property name="codigo" type="string">
<column name="CODIGO" length="50" />
</property>
<property name="nombre" type="string">
<column name="NOMBRE" length="100" not-null="true" />
</property>
<property name="EMail" type="string">
<column name="E_MAIL" length="100" />
</property>
<property name="fhAlta" type="java.util.Date">
<column name="FH_ALTA" length="7" />
</property>
<property name="fhModificacion" type="java.util.Date">
<column name="FH_MODIFICACION" length="7" />
</property>
<property name="fhBaja" type="java.util.Date">
<column name="FH_BAJA" length="7" />
</property>
<set name="rolesNeos" inverse="false" table="N_SEG_ROLES_USER">
<key>
<column name="ID_USUARIO" precision="8" scale="0" />
</key>
<many-to-many entity-name="es.abertis.neo.seguridad.model.RolesNeo">
<column name="ID_ROL" precision="10" scale="0" />
</many-to-many>
</set>

</class>
<class name="es.abertis.neo.seguridad.model.RolesNeo" table="N_SEG_ROLES_NEO">
<meta attribute="extra-import">es.abertis.neo.base.hibernate.model.PersistentObject</meta>
<meta attribute="extends">PersistentObject</meta>
<id name="id" type="java.lang.Long">
<column name="ID" precision="10" scale="0" />
<generator class="sequence">
<param name="sequence">SEQ_N_SEG_ROLES_NEO</param>
</generator>
</id>
<property name="nombreRol" type="string">
<column name="NOMBRE_ROL" length="100" not-null="true" />
</property>
<property name="descripcion" type="string">
<column name="DESCRIPCION" not-null="true" />
</property>
<property name="fhAlta" type="java.util.Date">
<column name="FH_ALTA" length="7" not-null="true" />
</property>
<property name="usrAlta" type="string">
<column name="USR_ALTA" length="50" not-null="true" />
</property>
<property name="fhHabilitado" type="java.util.Date">
<column name="FH_HABILITADO" length="7" />
</property>
<property name="fhDeshab" type="java.util.Date">
<column name="FH_DESHAB" length="7" />
</property>
<property name="habilitado" type="es.abertis.neo.base.hibernate.model.type.SiNoType">
<column name="HABILITADO" length="1" not-null="true" />
</property>
<property name="usrUltModif" type="string">
<column name="USR_ULT_MODIF" length="50" />
</property>
<property name="fhUltModif" type="java.util.Date">
<column name="FH_ULT_MODIF" length="7" />
</property>
<set name="usuarios" inverse="true" table="N_SEG_ROLES_USER">
<key>
<column name="ID_ROL" precision="10" scale="0" not-null="true" />
</key>
<many-to-many entity-name="es.abertis.neo.seguridad.model.Usuario">
<column name="ID_USUARIO" precision="8" scale="0" not-null="true" />
</many-to-many>
</set>
</class>

Code between sessionFactory.openSession() and session.close():
Criteria crit = comunDao.createCriteria(Usuario.class);

crit = crit.createCriteria("rolesNeos").add( Expression.eq( "nombreRol", "GR") );
if ((Integer.valueOf(-1) != pagina)
|| (Integer.valueOf(-1) != numRegistros)) {
crit.setFirstResult(HelperBO.getFirstResult(pagina.intValue(),
numRegistros.intValue()));
crit.setMaxResults(numRegistros.intValue());
}
lResponsables = crit.list();

Full stack trace of any exception that occurs:
2008-06-27 08:22:36,609 ERROR [main] impl.GestorSeguridadBOImpl - findBuscarResponsable
org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.ErrorCodeConverter.handledNonSpecificException(ErrorCodeConverter.java:92)
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:80)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:1596)
at org.hibernate.loader.Loader.list(Loader.java:1577)
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:111)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1322)
at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:300)
at org.hibernate.impl.CriteriaImpl$Subcriteria.list(CriteriaImpl.java:142)
at es.abertis.riags.base.bo.impl.GestorSeguridadBOImpl.findBuscarUsuarios(GestorSeguridadBOImpl.java:143)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:292)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:155)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:122)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:144)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:174)
at $Proxy5.findBuscarUsuarios(Unknown Source)
at es.abertis.riags.base.bci.GestorSeguridadBOTest.testFindBuscarUsuarios(GestorSeguridadBOTest.java:403)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at junit.framework.TestCase.runTest(TestCase.java:164)
at junit.framework.TestCase.runBare(TestCase.java:130)
at junit.framework.TestResult$1.protect(TestResult.java:110)
at junit.framework.TestResult.runProtected(TestResult.java:128)
at junit.framework.TestResult.run(TestResult.java:113)
at junit.framework.TestCase.run(TestCase.java:120)
at junit.framework.TestSuite.runTest(TestSuite.java:228)
at junit.framework.TestSuite.run(TestSuite.java:223)
at org.junit.internal.runners.OldTestClassRunner.run(OldTestClassRunner.java:35)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:38)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
Caused by: java.sql.SQLException: ORA-00918: columna definida de forma ambigua

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:743)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:911)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1122)
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:962)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1242)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3415)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3459)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1272)
at org.hibernate.loader.Loader.doQuery(Loader.java:391)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
at org.hibernate.loader.Loader.doList(Loader.java:1593)
... 37 more

Name and version of the database you are using:
Oracle 10

The generated SQL (show_sql=true):

select * from ( select row_.*, rownum rownum_ from ( select this_.ID as ID1_, this_.CODIGO as CODIGO57_1_, this_.NOMBRE as NOMBRE57_1_, this_.E_MAIL as E4_57_1_, this_.FH_ALTA as FH5_57_1_, this_.FH_MODIFICACION as FH6_57_1_, this_.FH_BAJA as FH7_57_1_, rolesneos3_.ID_USUARIO as ID2_, rolesneo1_.ID as ID1_, rolesneo1_.ID as ID0_, rolesneo1_.NOMBRE_ROL as NOMBRE2_45_0_, rolesneo1_.DESCRIPCION as DESCRIPC3_45_0_, rolesneo1_.FH_ALTA as FH4_45_0_, rolesneo1_.USR_ALTA as USR5_45_0_, rolesneo1_.FH_HABILITADO as FH6_45_0_, rolesneo1_.FH_DESHAB as FH7_45_0_, rolesneo1_.HABILITADO as HABILITADO45_0_, rolesneo1_.USR_ULT_MODIF as USR9_45_0_, rolesneo1_.FH_ULT_MODIF as FH10_45_0_ from N_SEG_USUARIOS this_ inner join N_SEG_ROLES_USER rolesneos3_ on this_.ID=rolesneos3_.ID_USUARIO inner join N_SEG_ROLES_NEO rolesneo1_ on rolesneos3_.ID_ROL=rolesneo1_.ID where rolesneo1_.NOMBRE_ROL=? ) row_ where rownum <= ?) where rownum_ > ?

As you can see the problem comes when it adds in the projection some repeated aliases. Then, when it makes the select with rownum it detects that there are repeated columns and fails.
I don't know why it doesn't take in accoutn the aliases suffix when it introduces the projection of the many-to-many relation's Id.

What am i doing wrong?
Is there any way to avoid it to put in the projections these IDs?

Thanks a lot.
Top
 Profile  
 
josep.mena
 Post subject: i've found it
PostPosted: Mon Jun 30, 2008 10:40 am 
Newbie

Joined: Fri Jun 27, 2008 2:14 am
Posts: 2
Location: Barcelona
I've fixed the problem. I was using antlr 2.7.5. I've changed it to 2.7.6 and it works perfectly.
Thanks!
Top
 Profile  
 
Cameron McKenzie
 Post subject:
PostPosted: Fri Jul 04, 2008 10:54 am 
Expert
Expert

Joined: Tue May 13, 2008 3:42 pm
Posts: 919
Location: Toronto & Ajax Ontario www.hibernatemadeeasy.com
Fantastic! Thanks for the update.

For some extra tutorials on mapping associations with Hibernate, you can look here:

http://www.hiberbook.com/HiberBookWeb/learn.jsp?tutorial=19mappingmanytomanyrelationships

But it looks like you might not need the extra help. Great work!

_________________
Cameron McKenzie - Author of "Hibernate Made Easy" and "What is WebSphere?"
http://www.TheBookOnHibernate.com Check out my 'easy to follow' Hibernate & JPA Tutorials

Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 3 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.