I have a problem in my manyToMany relationship...


I try to run this EJB-QL:

"DELETE from Grupo grp where grp.ugUsuario.usId = 1"


But the server throw an exception:


[#|2006-07-07T17:33:33.652-0300|INFO|sun-appserver-pe9.1|javax.enterprise.system.container.ejb|_ThreadID=15;_ThreadName=p: thread-pool-1; w: 3;|
javax.ejb.EJBException
at com.sun.ejb.containers.BaseContainer.processSystemException(BaseContainer.java:3730)
at com.sun.ejb.containers.BaseContainer.completeNewTx(BaseContainer.java:3630)
at com.sun.ejb.containers.BaseContainer.postInvokeTx(BaseContainer.java:3431)
at com.sun.ejb.containers.BaseContainer.postInvoke(BaseContainer.java:1247)
at com.sun.ejb.containers.EJBObjectInvocationHandler.invoke(EJBObjectInvocationHandler.java:197)
at com.sun.ejb.containers.EJBObjectInvocationHandlerDelegate.invoke(EJBObjectInvocationHandlerDelegate.java:67)
at $Proxy58.removeGruposDoUsuario(Unknown Source)
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 com.sun.corba.ee.impl.presentation.rmi.ReflectiveTie._invoke(ReflectiveTie.java:121)
at com.sun.corba.ee.impl.protocol.CorbaServerRequestDispatcherImpl.dispatchToServant(CorbaServerRequestDispatcherImpl.java:650)
at com.sun.corba.ee.impl.protocol.CorbaServerRequestDispatcherImpl.dispatch(CorbaServerRequestDispatcherImpl.java:193)
at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleRequestRequest(CorbaMessageMediatorImpl.java:1705)
at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleRequest(CorbaMessageMediatorImpl.java:1565)
at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleInput(CorbaMessageMediatorImpl.java:947)
at com.sun.corba.ee.impl.protocol.giopmsgheaders.RequestMessage_1_2.callback(RequestMessage_1_2.java:178)
at com.sun.corba.ee.impl.protocol.CorbaMessageMediatorImpl.handleRequest(CorbaMessageMediatorImpl.java:717)
at com.sun.corba.ee.impl.transport.SocketOrChannelConnectionImpl.dispatch(SocketOrChannelConnectionImpl.java:473)
at com.sun.corba.ee.impl.transport.SocketOrChannelConnectionImpl.doWork(SocketOrChannelConnectionImpl.java:1270)
at com.sun.corba.ee.impl.orbutil.threadpool.ThreadPoolImpl$WorkerThread.run(ThreadPoolImpl.java:479)
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute update query
at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:567)
at org.hibernate.ejb.QueryImpl.executeUpdate(QueryImpl.java:46)
at br.com.escriba.compartilhado.ejbx.AbstractEscribaSessionBean.executeCommand(AbstractEscribaSessionBean.java:152)
at br.com.escriba.escribauser.session.GrupoSessionBean.removeGruposDoUsuario(GrupoSessionBean.java:221)
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 com.sun.enterprise.security.application.EJBSecurityManager.runMethod(EJBSecurityManager.java:1050)
at com.sun.enterprise.security.SecurityUtil.invoke(SecurityUtil.java:165)
at com.sun.ejb.containers.BaseContainer.invokeTargetBeanMethod(BaseContainer.java:2766)
at com.sun.ejb.containers.BaseContainer.intercept(BaseContainer.java:3847)
at com.sun.ejb.containers.EJBObjectInvocationHandler.invoke(EJBObjectInvocationHandler.java:190)
... 17 more
Caused by: org.hibernate.exception.SQLGrammarException: could not execute update query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
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:391)
at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:259)
at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1134)
at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94)
at org.hibernate.ejb.QueryImpl.executeUpdate(QueryImpl.java:43)
... 28 more
Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where us_id=2' at line 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:930)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2816)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1530)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1620)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3025)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:937)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1178)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1095)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1080)
at com.mysql.jdbc.jdbc2.optional.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:834)
at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:75)
... 33 more
|#]






Hibernate version: 3.1.2 and 3.2.0CR2

Mapping documents:
@Entity
@Table(name = "compartilhado.grupo")
public class Grupo implements Serializable {

@Id
@Column(name = "gr_id", nullable = false)
private Integer grId;
@Column(name = "gr_nome", nullable = false)
private String grNome;
@JoinTable(name = "compartilhado.usuario_grupo", joinColumns = {@JoinColumn(name = "ug_grupo", referencedColumnName = "gr_id")}, inverseJoinColumns = {@JoinColumn(name = "ug_usuario", referencedColumnName = "us_id")})
@ManyToMany(fetch=FetchType.EAGER)
private java.util.Set <Usuario> ugUsuario = new HashSet<Usuario>();

gets... sets...


@Entity
@Table(name = "compartilhado.usuario")
public class Usuario implements Serializable {

@Id
@GeneratedValue(strategy=GenerationType.AUTO)
@Column(name = "us_id", nullable = false)
private Integer usId;
@Column(name = "us_nome", nullable = false)
private String usNome;
@ManyToMany(fetch=FetchType.EAGER,mappedBy = "ugUsuario")
private java.util.Set <Grupo> ugGrupo = new HashSet<Grupo>();

gets... sets...




Code between sessionFactory.openSession() and session.close():

em.createQuery("DELETE from Grupo grp where grp.ugUsuario.usId =1").execute();


Name and version of the database you are using:

MySQL 5.0.20 with Connector/J 5.0.0 Beta


The generated SQL (show_sql=true):

none, only print error

With oracle TOPLINK works fine.

Where did this 2 come from?


See the actual sql query - turn on sql log.

This query is definitively not valid. If this works on Toplink, please report a bug to the Toplink team. you must not access a single property of a collection through a dotted operation (ugUsuario.usId), this simply does not make sense.
Use a subselect and a the join keyword.

delete is with ID =1 not id =2... sorry....

Ok... but if i want to delete the "usuario_grupo" links of "usuario->grupo" what SQL i will have ?

i'm not undestang the left join for delete clause...