Hibernate Community • View topic - Exception doing a bulk delete, is this a bug?

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Exception doing a bulk delete, is this a bug?

Page 1 of 1

[ 12 posts ]

Previous topic | Next topic

Author

Message

rlecheta

Post subject: Exception doing a bulk delete, is this a bug?

Posted: Sat Dec 08, 2007 9:29 am

Newbie

Joined: Thu Oct 27, 2005 8:31 am
Posts: 3

Hi all,

I´m using Hibernate 3.2.5

In my code I tried to delete all the Messagens sent by some user.

getSession().createQuery("delete Mensagem c where c.usuarioRecebeu.login = ?").setParameter(0, usuario).executeUpdate();

"usuarioRecebeu" is the User association, and represents the user that received that message.

The hibernate showSql=true printed:

Code:

Hibernate: delete from mensagem, usuario usuario1_ where login=?

It´s strange because it did not do the join between Message and User.

So in the logs, I got the following exception:

Code:

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:396)
   at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:259)
   at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1141)
   at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94)
   at net.wasys.wafv.chat.server.dao.MensagemDAO.deletaMensagemsRecebidasByUsuario(MensagemDAO.java:26)
   at net.wasys.mlife.actions.DeletaMensagensAction.execute(DeletaMensagensAction.java:27)
   at net.wasys.mlife.actions.framework.DecoratorTransactionAction.execute(DecoratorTransactionAction.java:33)
   at net.wasys.wafv.chat.server.moblife.ActionHelper.execute(ActionHelper.java:31)
   at net.wasys.wafv.chat.server.moblife.MobiLifeSocketServerThread.run(MobiLifeSocketServerThread.java:56)
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 login='ricardo'' at line 1
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2941)
   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1623)
   at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1715)
   at com.mysql.jdbc.Connection.execSQL(Connection.java:3249)
   at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1268)
   at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1541)
   at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1455)
   at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1440)

I tried the same HQL to select the data and it works very well:

Code:

      List list = getSession().createQuery("from Mensagem c where c.usuarioRecebeu.login = ?").setParameter(0, usuario).list();
      for (Iterator iterator = list.iterator(); iterator.hasNext();) {
         Mensagem msg = (Mensagem) iterator.next();
         System.out.println(msg.getMsg());
      }

The hibernate showSQL=true now prints the SQL and the join:

Quote:

Hibernate: select mensagem0_.id as id7_, mensagem0_.usuario_id as usuario2_7_, mensagem0_.usuario_contato_id as usuario3_7_, mensagem0_.msg as msg7_, mensagem0_.service as service7_, mensagem0_.data as data7_, mensagem0_.status as status7_, mensagem0_.rsId as rsId7_, mensagem0_.rsId_contato as rsId9_7_, mensagem0_.chat as chat7_ from mensagem mensagem0_, usuario usuario1_ where mensagem0_.usuario_contato_id=usuario1_.id and usuario1_.login=?

My Mensagem.hbm.xml mapping:

Code:

<class
    name="net.wasys.wafv.chat.server.model.Mensagem"
    proxy="net.wasys.wafv.chat.server.model.Mensagem"
    table="mensagem"
>
    <id
        name="id"
        type="java.lang.Long"
        column="id"
    >
        <generator class="native" />
    </id>

    <many-to-one
        name="usuarioEnviou"
        class="net.wasys.wafv.chat.server.model.Usuario"
        column="usuario_id"
        not-null="true"
        cascade="none"
     />

    <many-to-one
        name="usuarioRecebeu"
        class="net.wasys.wafv.chat.server.model.Usuario"
        column="usuario_contato_id"
        not-null="true"
        cascade="none"
     />

....

Since the same HQL works to retrieve the information, why I can´t delete the data with the same HQL, but using the executeUpdate ?

thank you,

Ricardo

Top

AlmGhandi

Post subject:

Posted: Sat Dec 08, 2007 7:27 pm

Beginner

Joined: Tue Nov 27, 2007 9:44 am
Posts: 46

Hi,

Quote:

It´s strange because it did not do the join between Message and User.

You do. It's an implicit join.

Have you already tried this?

Code:

getSession().createQuery("delete Mensagem c where c.usuarioRecebeu = ?").setParameter(0, usuario).executeUpdate(); 

with usuario being an instance of Usuario (or whatever that class is called).

Good Luck,
Frank

Top

rlecheta

Post subject:

Posted: Mon Dec 10, 2007 7:18 am

Newbie

Joined: Thu Oct 27, 2005 8:31 am
Posts: 3

Hi AlmGhandi,

thanks, but in my example "usuario" is a String which is the User "login"..

anyway I´ll try your example..

regards

Top

themop

Post subject:

Posted: Mon Dec 10, 2007 7:42 am

Newbie

Joined: Thu Nov 29, 2007 4:59 am
Posts: 3
Location: Milano - Italy

I think that you cannot use inner join in bulk-delete (bulk delete translates in SQL statement and I'm not sure you can use inner join in sql).
I'm referring to this document http://twasink.net/blog/archives/2005/04/differences_in.html, in particular to this excerpt:

Quote:

# The HQL syntax has restrictions. These are:
1. You can not use aliases. So "delete from Foo foo where foo.bar = :bar" isn't valid, while "delete from Foo where bar = :bar" is.1
2. No inner joins in the query (you can use subselects in the where clause, for similar behaviour)
3. And, of course, you need to have "delete" at the front.

I think you can use its suggestion and transform your query to something like this:
getSession().createQuery("delete Mensagem c where c.Id in (select m.id from Mensagem m where usuarioRecebeu.login = ?)").setParameter(0, usuario).executeUpdate();

Bye,
Marco
[/quote]

Top

AlmGhandi

Post subject:

Posted: Mon Dec 10, 2007 7:55 am

Beginner

Joined: Tue Nov 27, 2007 9:44 am
Posts: 46

Ricardo,

I know that your "usuario" is a String. That is the reason why HQL needs to join the entity Mensagem with the entity Usuario. If you use an instance of Usuario as your criteria for the where clause, it won't need that join.

But if it is not feasible in your case to use an instance of Usuario then you can of course use themop's example or try alternatively:

Code:

getSession().createQuery("delete Mensagem c where c.usuarioRecebeu = (select u from Usuario u where u.login = ?)").setParameter(0, usuario).executeUpdate(); 

where usuario would be a string in that case.

Regards,
Frank

Top

tamalmandal

Post subject:

Posted: Mon Sep 08, 2008 4:13 am

Newbie

Joined: Tue Sep 02, 2008 10:46 am
Posts: 3
Location: Bangalore

Hi Everyone,

I'm reopening this post as it's exactly the same problem I faced with query.executeUpdate() described by rlecheta.

Though I'm able to bypass the issue with inner-join by adopting sub-select queries as suggested in subsequent replies (agreed, it's really the most effective work around to inner-join problem in bulk update/delete context), but, I'm stuck at a critical point where I coundn't find any work around and seeking your help.

I'm working with an inheritance hierarchy of entities in which there are two entity classes ATP and ConnPoint. ConnPoint extends the base entity class ATP. They have separate tables named ni_eqatp and ni_eqport respectively for ATP and ConnPoint. This means, an instance of ConnPoint gets persisted to both these tables where as an instance of ATP gets persisted to only ni_eqatp table.

Now, a bulk delete query works fine with ATP, however, it fails with ConnPoint, in spite of of having the same where clause.

Here's my hql query which bounces for ConnPoint :

delete ConnPoint where (card in (from Card where shelf.node.nodename in ('GUWASSAECNOD'))) and objectid > 0

Corresponding sql generated by hibernate -

Hibernate: insert into HT_ni_eqatp select niconnpoin0_.objectid as objectid from ni_eqatp niconnpoin0_, ni_eqport niconnpoin0_1_ where niconnpoin0_.objectid=niconnpoin0_1_.objectid and srclass in ('NI5r', 'NI5j', 'NI5b') and (card in (select nieqcard1_.objectid from ni_eqcard nieqcard1_, ni_eqshlf nieqshelf2_, ni_eqnode nieqnode3_ where nieqcard1_.shelf=nieqshelf2_.objectid and nieqshelf2_.node=nieqnode3_.objectid and (nieqnode3_.nodename in ('GUWASSAECNOD')))) and objectid>0

Exception thrown:

ORA-00918: column ambiguously defined

Here's the hql which works fine with ATP:

delete ATP where (card in (from Card where shelf.node.nodename in ('GUWASSAECNOD'))) and objectid > 0

Corresponding sql generated by hibernate -

Hibernate: delete from ni_eqatp where (card in (select nieqcard1_.objectid from ni_eqcard nieqcard1_, ni_eqshlf nieqshelf2_, ni_eqnode nieqnode3_ where nieqcard1_.shelf=nieqshelf2_.objectid and nieqshelf2_.node=nieqnode3_.objectid and (nieqnode3_.nodename in ('GUWASSAECNOD')))) and objectid>0

Problem lies in the fact that both ni_atp and ni_eqport table has primary key column named objectid. Unlike select queries, hibernate doesn't generates and pre-pends a table alias to the column name for the generated delete query.

The delete query works fine with ATP as it deal with single table ni_atp.
On the other hand, ConnPoint class makes hibernate to deal with two tables, hence, generated sql query becomes ambiguous as "objectid>0" condition leads to ambiguity.

However, with select queries, hibernate properly generated table aliases and pre-pend them to appropriate columns, as you can see here in the sub select queries.

Can any one suggest how to cater the problem?? If I'm not detailed enough to describe the problem, plz do ask for any clarification.

Thanks 4 your patience to reach this line [:)]

Tamal

Top

AlmGhandi

Post subject:

Posted: Mon Sep 08, 2008 5:45 am

Beginner

Joined: Tue Nov 27, 2007 9:44 am
Posts: 46

try this:

delete ConnPoint conn where (card in (from Card where shelf.node.nodename in ('GUWASSAECNOD'))) and conn.objectid > 0

Regards,
Frank

Top

tamalmandal

Post subject:

Posted: Mon Sep 08, 2008 6:44 am

Newbie

Joined: Tue Sep 02, 2008 10:46 am
Posts: 3
Location: Bangalore

Thanks Frank for the reply...

Quote:

delete ConnPoint conn where (card in (from Card where shelf.node.nodename in ('GUWASSAECNOD'))) and conn.objectid > 0

This I had already tried. Hibernate removes any such aliases supplied externally. Essentially, the hibernate generated sql query remains same.

Top

AlmGhandi

Post subject:

Posted: Mon Sep 08, 2008 7:21 am

Beginner

Joined: Tue Nov 27, 2007 9:44 am
Posts: 46

Weird,

can you post the source of your Entity classes please?

Top

tamalmandal

Post subject:

Posted: Mon Sep 08, 2008 11:15 am

Newbie

Joined: Tue Sep 02, 2008 10:46 am
Posts: 3
Location: Bangalore

Here's mapping def for ConnPoint :

Code:

<hibernate-mapping>
    <subclass name="ConnPoint" extends="ATP"    
   entity-name="ConnPoint"    discriminator-value="NI5r">
    <join table="ni_eqport" fetch="select">
    <key column="objectid"/>

    ....

</join>    
    </subclass>
</hibernate-mapping>

Mapping def for ATP:

Code:

<hibernate-mapping>
    <class name="ATP" entity-name="ATP" table="ni_eqatp" discriminator-value="NI59" >
        <id name="objectid" type="long">
            <column name="objectid" precision="10" scale="0" />
       <generator class="sequence">
      <param name="sequence">SEQ_ATP_ID</param>
       </generator>
        </id>
   <discriminator column="srclass" type="string"/>
       
       ...
       
       <many-to-one name="card" lazy="false"  index="eqatp_card_id"       class="Card" entity-name="Card">
          <column name="card"/>
        </many-to-one>
</hibernate-mapping>

Soure code for entities:

Code:

public abstract class AbstractATP  implements java.io.Serializable {

    // Fields    

     private long objectid;
     private Card card;
     // other fields

    // Constructors

   //Getter n Setter
}

public class ATP extends AbstractATP implements java.io.Serializable { 
      // Constructors
}

public class ConnPoint extends ATP {

   // Fields    

     // additional fields specific to ConnPoint

    // Constructors

   //Getter n Setter

}

Plz let me know whether the information is sufficient.

Tamal

Top

AlmGhandi

Post subject:

Posted: Tue Sep 09, 2008 6:39 am

Beginner

Joined: Tue Nov 27, 2007 9:44 am
Posts: 46

Can't find anything wrong here.

How is Card defined?

Top

jroop

Post subject:

Posted: Wed Sep 24, 2008 8:21 pm

Newbie

Joined: Fri Jun 01, 2007 7:03 pm
Posts: 3

The bulk update cannot have a join so there is really no advantage to using an alias in that portion of the delete HQL. You can use subqueries in bulk update/deletes and you are allowed to use joins in those subqueries. Oh, and you can go nuts with aliases in the subquery but in this case there is no need.

So, rearrange the statement so that the join is isolated in the subquery. Working from the suggestion posted earlier I came up with:

Code:

delete ConnPoint
where objectid > 0
and card in (
  from Card where shelf.node.nodename in ('GUWASSAECNOD')
)

Top

Page 1 of 1

[ 12 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