aspose file tools*
The moose likes Object Relational Mapping and the fly likes Batch Delete Problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Batch Delete Problem" Watch "Batch Delete Problem" New topic
Author

Batch Delete Problem

subhashchandra medhiassam
Ranch Hand

Joined: Sep 10, 2008
Posts: 72
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.



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
Ranch Hand

Joined: Sep 10, 2008
Posts: 72
When i am using printStackTrace() method of Exception class in the catch block I am getting the following stack trace:



Kindly help me out.

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

Joined: Apr 14, 2004
Posts: 10336

Do you have a mapped object called a1?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
subhashchandra medhiassam
Ranch Hand

Joined: Sep 10, 2008
Posts: 72
Yes, I am having a class a1.
Here's the code for a1:



The mapping file is:



Kindly help.
Thanks,
Subhash
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

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
Ranch Hand

Joined: Sep 10, 2008
Posts: 72
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
Ranch Hand

Joined: Sep 10, 2008
Posts: 72
Hi,
Could anybody help me out?

Thanks,
Subhash
subhashchandra medhiassam
Ranch Hand

Joined: Sep 10, 2008
Posts: 72
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
Greenhorn

Joined: Sep 10, 2008
Posts: 5
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
Ranch Hand

Joined: Sep 10, 2008
Posts: 72
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
Ranch Hand

Joined: Sep 10, 2008
Posts: 72
When i use the second approach, i am getting the following message:



The program looks something like this:



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

Thanks,
Subhash
subhashchandra medhiassam
Ranch Hand

Joined: Sep 10, 2008
Posts: 72
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:



And this program is working.

Thanks,
Subhash
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

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
Greenhorn

Joined: Sep 10, 2008
Posts: 5
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.
 
wood burning stoves
 
subject: Batch Delete Problem