Can't use createQuery to delete rows

kenkw · **Joined:** Thu Jul 21, 2005 4:50 am **Posts:** 5

I am having problem with the following (very simple) delete statement:

Session s...
Transaction t = s.beginTransaction();
try {
Query query = s.createQuery("delete from TABLE1 where Node_No=?");
query.setInteger(0, 2);
query.executeUpdate();
t.commit();
}
catch(Exception e) {
e.printStackTrace();
t.rollback();
}
s.close();

The executeUpdate() throws this exception:

java.lang.IndexOutOfBoundsException: Index: 0, Size: 0
at java.util.ArrayList.RangeCheck(Unknown Source)
at java.util.ArrayList.get(Unknown Source)
at org.hibernate.hql.ast.HqlSqlWalker.postProcessDelete(HqlSqlWalker.java:418)
at org.hibernate.hql.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:175)
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:427)
at org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:884)
at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:865)
at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:89)

Please advice on what can be wrong with this simple statement.

gavin · **Posted:** Mon Aug 08, 2005 9:34 pm

I'm quite certain you dont have a class named TABLE1.

kenkw · **Joined:** Thu Jul 21, 2005 4:50 am **Posts:** 5

Yes, you are correct.

I tried to use the createSQLQuery() method but it doesn't seem to support update queries - java.lang.UnsupportedOperationException: Update queries only supported through HQL.

How should I write the query? i.e. "delete from MyTable t where t.nodeNo=?" didn't work. The MyTable class has been mapped to TABLE1.

Thanks.

sven · **Posted:** Tue Aug 09, 2005 7:35 am

Code:

Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();

int deletedEntities = s.createQuery("delete from MyTable where nodeNo = :nodeNo")
                      .setInteger("nodeNo", nodeNo)
                      .executeUpdate();
tx.commit();
session.close();

Note that MyTable has to be your class and nodeNo ist one of its properties mapped. You cannot use aliases here. I assumed that nodeNo is an integer.
The word "from" can be left out.

Read 14.3. Bulk update/delete of the documentation:
http://www.hibernate.org/hib_docs/v3/re ... tch-direct

Best regards
Sven

katukas · **Joined:** Tue Aug 09, 2005 9:09 am **Posts:** 3

I think it's possible to get PreparedStatement or jdbc connection from Hibernate session so it should work then.

kenkw · **Joined:** Thu Jul 21, 2005 4:50 am **Posts:** 5

Thanks, I was able to locate the same document after the posting.

Here is another question regarding batching:

Session session = sessionFactory.openSession();
Transaction tx = session.beginTransaction();
int[] ids = <array of ids to delete>

Query q = s.createQuery("delete from MyTable where nodeNo = :nodeNo");

for (int i = 0; i < ids.length; i++ ) {
q.setInteger("nodeNo", nodeNo);
q.executeUpdate();
}

tx.commit();
session.close();

Is this how one would write for batch delete? The ids must be deleted in the same order (due to some constraints).

Thanks.

Daniel · **Posted:** Sat Aug 13, 2005 7:00 pm

kenkw wrote:

How should I write the query? i.e. "delete from MyTable t where t.nodeNo=?" didn't work. The MyTable class has been mapped to TABLE1.

Thanks.

Try this:

delete from TheNameOfTheMappedClass t where t.nodeNO=?

good luck