Batch Delete Problem

[subhashchandra medhiassam]

Hi all,
I have a table named "a1" in MYSQL. It's columns are "identity"(type int) and "name"(type varchar). It has seven rows out of which one row's "name" column's value is the string jkl.
I am trying to delete this row using batch delete. Here's the code fragment.

 
public static void main(String[] args) {
Session session = null;
    try {
      // This step will read hibernate.cfg.xml and prepare hibernate for
      // use
      SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
      session = sessionFactory.openSession();
      Transaction tx = session.beginTransaction();
      Query query = session.createQuery("Delete from a1 a where a.name=?");
      query.setString(0,"jkl");
      query.executeUpate();
      tx.commit();
      
    } catch (Exception e) {
      System.out.println(e.getMessage());
    } finally {
session.flush();
session.close();
    }    
 
    }
 
   

when i run this program,however,I am getting the following message:

Hibernate: delete from a1 a1x0_ where (a1x0_.name=?)
could not execute update query

Why is it so? Could anybody kindly guide me as to how to solve this issue?

Thanks,
Subhash

[subhashchandra medhiassam]

When i am using printStackTrace() method of Exception class in the catch block I am getting the following stack trace:

 could not execute update query
org.hibernate.exception.SQLGrammarException: could not execute update query
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.hql.ast.UpdateStatementExecutor.execute(UpdateStatementExecutor.java:76)
at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:294)
at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:808)
at org.hibernate.impl.QueryImpl.executeUpate(QueryImpl.java:89)
at BatchDelete.main(BatchDelete.java:22)
Caused by: java.sql.SQLException: Syntax error or access violation message from server:
"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 (a1x0_.name='jkl')' at line 1"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1997)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2251)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1772)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1619)
at org.hibernate.hql.ast.UpdateStatementExecutor.execute(UpdateStatementExecutor.java:67)
... 4 more
   

Kindly help me out.

[ UD: added line breaks to preserve layout ]
[ September 17, 2008: Message edited by: Ulf Dittmer ]

[Paul Sturrock]

Do you have a mapped object called a1?

[subhashchandra medhiassam]

Yes, I am having a class a1.
Here's the code for a1:

  public class a1 {
 
   private int identity;
   private String name;
   public int getIdentity() {
       return identity;
   }
   public void setIdentity(int identity) {
       this.identity = identity;
   }
   public String getName() {
       return name;
   }
   public void setName(String name) {
       this.name = name;
   }
   
   
   
 
}
  

The mapping file is:

  <?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC 
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
 
<hibernate-mapping>
  <class name="b2" table="b2">
     <id name="id" column="id" type="int">
     </id>
     <property name="address" column="address" type="string" /> 
     <property name="name" column="name" type="string" /> 
 </class>  
 <class name="a1" table="a1">
     <id name="identity" column="identity" type="int">
     </id>
     <property name="name" column="name" type="string" />  
 </class>
 
</hibernate-mapping>  

Kindly help.
Thanks,
Subhash

[Paul Sturrock]

And that is definately the HQL you are running? I ask because you are using bind variables but your error shows a query that doesn't appear to use them?

[subhashchandra medhiassam]

Yes, I am surely running that HQL only. What could be the problem?
Could anybody identify where i have gone wrong?

Thanks,
Subhash

[subhashchandra medhiassam]

Hi,
Could anybody help me out?

Thanks,
Subhash

[subhashchandra medhiassam]

When i changed my query string to

"delete from a1 a where a.Name=?"(have changed name to Name.) I am getting the following message:


org.hibernate.QueryException: could not resolve property: Identity of: a1 [delete from a1 a where a.Identity=?]
at org.hibernate.persister.entity.AbstractPropertyMapping.throwPropertyException(AbstractPropertyMapping.java:43)
at org.hibernate.persister.entity.AbstractPropertyMapping.toType(AbstractPropertyMapping.java:37)
at org.hibernate.persister.entity.BasicEntityPersister.toType(BasicEntityPersister.java:1108)
at org.hibernate.hql.ast.FromElementType.getPropertyType(FromElementType.java:223)
at org.hibernate.hql.ast.FromElement.getPropertyType(FromElement.java:333)
at org.hibernate.hql.ast.DotNode.getDataType(DotNode.java:439)
at org.hibernate.hql.ast.DotNode.prepareLhs(DotNode.java:205)
at org.hibernate.hql.ast.DotNode.resolve(DotNode.java:165)
at org.hibernate.hql.ast.FromReferenceNode.resolve(FromReferenceNode.java:87)
at org.hibernate.hql.ast.FromReferenceNode.resolve(FromReferenceNode.java:83)
at org.hibernate.hql.ast.HqlSqlWalker.resolve(HqlSqlWalker.java:437)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.expr(HqlSqlBaseWalker.java:824)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.exprOrSubquery(HqlSqlBaseWalker.java:3209)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.comparisonExpr(HqlSqlBaseWalker.java:2690)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:1321)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.whereClause(HqlSqlBaseWalker.java:584)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.deleteStatement(HqlSqlBaseWalker.java:285)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:156)
at org.hibernate.hql.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:189)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:130)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:83)
at org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:414)
at org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:814)
at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:801)
at org.hibernate.impl.QueryImpl.executeUpate(QueryImpl.java:89)
at BatchDelete.main(BatchDelete.java:24)

What could be the problem? could anybody let me know, please?

Thanks,
Subhash

[ September 18, 2008: Message edited by: subhashchandra medhiassam ]
[ September 18, 2008: Message edited by: subhashchandra medhiassam ]

[k.siva prasadreddy]

Hi,
IF you want to delete a row you can follow one of the following ways:

#1:

txn=session.beginTransaction();
User user = session.get(User.class,1);
session.delete(user);
txn.commit();

#2:
txn=session.beginTransaction();
Query query = session.createQuery("delete from User where userId = ?");
query.setInteger(0, 1);
query.executeUpdate();
txn.commit();
txn.commit();

[subhashchandra medhiassam]

I must thank you. I took the first approach and it worked.The second approach is not working.

Thank you once again.

But i have a question. Whether the first approach can be used for "batch delete". suppose i want to delete one or more rows having the same values in one of the columns then can the first approach be used? In the first approach, are we able to delete a row using a column value other than that of the primary key?

Subhash

[ September 18, 2008: Message edited by: subhashchandra medhiassam ]
[ September 18, 2008: Message edited by: subhashchandra medhiassam ]

[subhashchandra medhiassam]

When i use the second approach, i am getting the following message:

 Hibernate: delete from a1 a1x0_ where (name=?)
could not execute update query
org.hibernate.exception.SQLGrammarException: could not execute update query
at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.hql.ast.UpdateStatementExecutor.execute(UpdateStatementExecutor.java:76)
at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:294)
at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:808)
at org.hibernate.impl.QueryImpl.executeUpate(QueryImpl.java:89)
at BatchDelete.main(BatchDelete.java:26)
Caused by: java.sql.SQLException: Syntax error or access violation message from server: "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 (name='jkl')' at line 1"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1997)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1167)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1278)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2251)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1772)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1619)
at org.hibernate.hql.ast.UpdateStatementExecutor.execute(UpdateStatementExecutor.java:67)
... 4 more
   

The program looks something like this:

  public static void main(String[] args) {
Session session = null;
    try {
      //This step will read hibernate.cfg.xml and prepare hibernate for
      //use
      SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
      session = sessionFactory.openSession();
      Transaction tx = session.beginTransaction();
      
      //Query query = session.createQuery("delete from a1 where name = ?");
      //query.setString(0,"jkl");
      //query.executeUpate();
 
      //a1 a = (a1)session.get(a1.class,1);
      //session.delete(a);
     
      tx.commit();
      
      
    } catch (Exception e) {
      System.out.println(e.getMessage());
      e.printStackTrace();
    } finally {
session.flush();
session.close();
    }    
 
    
    }
  

Could anybody kindly tell me where i have gone wrong and what should be done to remedy this?

Thanks,
Subhash

[subhashchandra medhiassam]

Hi all,
I found out a way to implement batch delete using hibernate. I am posting it here so that somebody who might be in a similar situation may benefit from it.

My program looks like this:

 public static void main(String[] args) {
Session session = null;
    try {
      //This step will read hibernate.cfg.xml and prepare hibernate for
      //use
      SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
      session = sessionFactory.openSession();
      Transaction tx = session.beginTransaction();
            
      Query query = session.createQuery("from a1 where name = ?");
      query.setString(0,"jkl");
      List list = query.list();
      
      for(int i = 0; i < list.size(); i++){
  session.delete(list.get(i));
      }
             
      tx.commit();
      
      
    } catch (Exception e) {
      System.out.println(e.getMessage());
      e.printStackTrace();
    } finally {
session.flush();
session.close();
    }    
 
    
    }   

And this program is working.

Thanks,
Subhash

[Paul Sturrock]

You don't need to do that. Hibernate supports batch deletes (see the documentation). Something about your implementation is causing the issue.

[k.siva prasadreddy]

Hi,
I test the above mentioned two ways on hibernate3 version and both methods are working fine for me.

And also we can use the first method to delete multiple rows which have the same name.

Thanks,
K. Siva Prasad Reddy.