Delete Query problem: Failure to Commit HQL Delete Transaction

[Samanthi perera]

this is my class for delete

import org.hibernate.Query;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.classic.Session;

public class DeleteQuary {

    public static void main(String[] args) {

        try {
            SessionFactory sessionfac = new Configuration().configure().buildSessionFactory();
            Session session = sessionfac.openSession();

            String hql = "delete from Person where id = 2";
            Query query = session.createQuery(hql);
            int row = query.executeUpdate();

            if (row == 0) {
                System.out.println("Doesn't deleted any row!");
            } else {
                System.out.println("Deleted Row: " + row);
            }
            session.close();

        } catch (Exception e) {
            e.printStackTrace();
            
        }

    }
}

after run this class it shows out as

INFO: building session factory
Jan 27, 2010 11:29:35 AM org.hibernate.impl.SessionFactoryObjectFactory addInstance
INFO: Not binding factory to JNDI, no JNDI name configured
Hibernate: delete from person where id=2
Deleted Row: 1
BUILD SUCCESSFUL (total time: 1 second)

it says one row is deleted
but when i run this query it shows both rows

import java.util.Iterator;
import org.hibernate.Query;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.classic.Session;

public class ViewQuery {

    public static void main(String[] args) {

        try {
            SessionFactory sessionfac = new Configuration().configure().buildSessionFactory();
            Session session = sessionfac.openSession();

            Person p1 = new Person();

           // String hql = "select person.id from Person person";
            String hql = "from Person person";
            Query query = session.createQuery(hql);
            //int row = query.executeUpdate();

            for (Iterator itor = query.iterate(); itor.hasNext();) {

                Person p=(Person)itor.next();
                Integer row = p.getId();
             // Integer row = (Integer) itor.next();
                System.out.println("ID: " + row);

            }

            session.close();

        } catch (Exception e) {
            e.printStackTrace();

        }

    }
}

Out put is here

INFO: Not binding factory to JNDI, no JNDI name configured
Hibernate: select person0_.id as col_0_0_ from person person0_
ID: 1
ID: 2
BUILD SUCCESSFUL (total time: 1 second)

where is the error?

[Christian Dillinger]

You didn't commit so your transaction did a rollback when you closed the session.

[Samanthi perera]

where is the Transaction. ?
there is no any transaction.
can't we create a session without transaction?

[Christian Dillinger]

session.getTransaction() gives you the transaction. No, you cannot do any database operation outside a transaction (apart from some nontransactional "databases"). Even in autocommit-mode there is a transaction inside the database.

[Samanthi perera]

oho.
then you mean this is wrong
http://www.roseindia.net/hibernate/firstexample.shtml

check is it wrong or not?

[Christian Dillinger]

It's wrong. If data isn't commited it must be wrong. I don't know MySQL that much but wasn't there a storage engine that didn't support transactions and another one that did? If you used the one that supported transactions the sample from roseindia cannot work. (Despite using autocommit-mode I think.)

[Sridhar Santhanakrishnan]

Agreed you need to have a transaction to update the database, but it does not have to explicitly coded. You can configure to have Application Server manage the transaction on its own in some cases. (CMT i think.)

Dont know if it helps but how about trying a session.flush() before you close the session?

[Samanthi perera]

but this view query is working.

import java.util.Iterator;
import org.hibernate.Query;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.hibernate.classic.Session;

public class ViewQuery {

    public static void main(String[] args) {

        try {
            SessionFactory sessionfac = new Configuration().configure().buildSessionFactory();
            Session session = sessionfac.openSession();

            Person p1 = new Person();

           // String hql = "select person.id from Person person";
            String hql = "from Person person";
            Query query = session.createQuery(hql);
            //int row = query.executeUpdate();

            for (Iterator itor = query.iterate(); itor.hasNext();) {

                Person p=(Person)itor.next();
                Integer row = p.getId();
             // Integer row = (Integer) itor.next();
                System.out.println("ID: " + row);

            }

            session.close();

        } catch (Exception e) {
            e.printStackTrace();

        }

    }
}

there is no any transaction in this code.

[Tomasz Szymanski]

It depends on what you mean by "it's working"...
Not using a transaction will not get you an exception - in that sense it's working. The problem is that whatever you do, it's cancelled as soon as you close the session (unless you have a non-transactional DB, as Christian mentioned, or you're working in the autocommit mode). Although, to be precise, according to this article, it's not 100% sure.
It seems that's exactly what's happening in your DeleteQuery,

[Prabhat Jha]

Your view query is working because it is just selecting something from a table....

and your delete is not working because you have changed the table and you didnt say commit.

One more question how are you building your session factory is it being managed by an App Server, if yes then call session.flush() as mentioned above...

If no check whether you db is transaction or non transactional, i feel its transactional so get the Transaction from session and do a commit.

Hope this helps.

[Samanthi perera]

I am using NEtbeans 6.8 IDE
Server is GlassFish which comes with Built in with Netbeans
DB is Java DB wich comes with Netbeans

[Christian Dillinger]

The code you showed above is not inside Glassfish! Thats plain old java without any application server, transaction manager or DI voodoo. No commit no database change. If you call flush you make it even worse because Hibernate prints the query and you think everything is okay, but then you don't see the rollback.