0

I am using hsqldb and would like to delete all records from a table.

Right now I am trying to do that with:

em.createQuery("DELETE FROM Datatable").executeUpdate();

However, I am getting the following exeption:

Exception in thread "AWT-EventQueue-0" javax.persistence.TransactionRequiredException: Executing an update/delete query
    at org.hibernate.ejb.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:96)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:311)
    at com.sun.proxy.$Proxy16.executeUpdate(Unknown Source)
    at com.limitCalculator.dao.PersonImporterDaoImpl.dropTable(PersonImporterDaoImpl.java:71)
    at com.limitCalculator.dao.PersonImporterDaoImpl$$FastClassByCGLIB$$ccda8cd0.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:627)
    at com.limitCalculator.dao.PersonImporterDaoImpl$$EnhancerByCGLIB$$20e91a8e.dropTable(<generated>)
    at com.limitCalculator.service.PersonImporterServiceImpl.dropTable(PersonImporterServiceImpl.java:99)
    at com.limitCalculator.gui.PersonTable.TabPanel$1.actionPerformed(TabPanel.java:96)
    at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
    at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
    at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
    at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
    at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
    at java.awt.AWTEventMulticaster.mouseReleased(Unknown Source)
    at java.awt.Component.processMouseEvent(Unknown Source)
    at javax.swing.JComponent.processMouseEvent(Unknown Source)
    at java.awt.Component.processEvent(Unknown Source)
    at java.awt.Container.processEvent(Unknown Source)
    at java.awt.Component.dispatchEventImpl(Unknown Source)
    at java.awt.Container.dispatchEventImpl(Unknown Source)
    at java.awt.Component.dispatchEvent(Unknown Source)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
    at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
    at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
    at java.awt.Container.dispatchEventImpl(Unknown Source)
    at java.awt.Window.dispatchEventImpl(Unknown Source)
    at java.awt.Component.dispatchEvent(Unknown Source)
    at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
    at java.awt.EventQueue.access$400(Unknown Source)
    at java.awt.EventQueue$2.run(Unknown Source)
    at java.awt.EventQueue$2.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown Source)
    at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown Source)
    at java.awt.EventQueue$3.run(Unknown Source)
    at java.awt.EventQueue$3.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown Source)
    at java.awt.EventQueue.dispatchEvent(Unknown Source)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.run(Unknown Source)

Any recommendations what I am doing wrong?

I appreciate your answer!

UPDATE

Using:

public void deleteTable() { try { em.getTransaction().begin();
em.createQuery("DELETE FROM Data").executeUpdate(); em.getTransaction().commit(); } catch(Exception ex) { em.getTransaction().rollback(); } }

I am getting the following exception:

Exception in thread "AWT-EventQueue-0" java.lang.IllegalStateException: Not allowed to create transaction on shared EntityManager - use Spring transactions or EJB CMT instead
    at org.springframework.orm.jpa.SharedEntityManagerCreator$SharedEntityManagerInvocationHandler.invoke(SharedEntityManagerCreator.java:199)
    at com.sun.proxy.$Proxy14.getTransaction(Unknown Source)
    at com.limitCalculator.dao.FileImporterDaoImpl.deleteTable(FileImporterDaoImpl.java:76)
    at com.limitCalculator.dao.FileImporterDaoImpl$$FastClassByCGLIB$$ccda8cd0.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:627)
    at com.limitCalculator.dao.FileImporterDaoImpl$$EnhancerByCGLIB$$20e91a8e.deleteTable(<generated>)
    at com.limitCalculator.service.FileImporterServiceImpl.dropTable(FileImporterServiceImpl.java:99)
    at com.limitCalculator.gui.Data.TabPanel$1.actionPerformed(TabPanel.java:96)
    at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
    at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
    at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
    at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
    at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
    at java.awt.AWTEventMulticaster.mouseReleased(Unknown Source)
    at java.awt.Component.processMouseEvent(Unknown Source)
    at javax.swing.JComponent.processMouseEvent(Unknown Source)
    at java.awt.Component.processEvent(Unknown Source)
    at java.awt.Container.processEvent(Unknown Source)
    at java.awt.Component.dispatchEventImpl(Unknown Source)
    at java.awt.Container.dispatchEventImpl(Unknown Source)
    at java.awt.Component.dispatchEvent(Unknown Source)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
    at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
    at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
    at java.awt.Container.dispatchEventImpl(Unknown Source)
    at java.awt.Window.dispatchEventImpl(Unknown Source)
    at java.awt.Component.dispatchEvent(Unknown Source)
    at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
    at java.awt.EventQueue.access$400(Unknown Source)
    at java.awt.EventQueue$2.run(Unknown Source)
    at java.awt.EventQueue$2.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown Source)
    at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown Source)
    at java.awt.EventQueue$3.run(Unknown Source)
    at java.awt.EventQueue$3.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.AccessControlContext$1.doIntersectionPrivilege(Unknown Source)
    at java.awt.EventQueue.dispatchEvent(Unknown Source)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.run(Unknown Source)

Any recommendations about this case?

Carol.Kar
  • 4,581
  • 36
  • 131
  • 264
  • 1
    wrap it in a transaction `em.beginTransaction ()` – Scary Wombat Jul 08 '14 at 08:58
  • @ScaryWombat Thx for your answer! I did it like that`em.beginTransaction(em.createQuery("DELETE FROM Datatable").executeUpdate());`, however, I am receiving `The method beginTransaction(int) is undefined for the type EntityManager`. Any recommendations for my problem? – Carol.Kar Jul 08 '14 at 09:01
  • beginTransaction is part of the UserTransaction and not EntityManager. – nano_nano Jul 08 '14 at 09:07
  • For your updated question, you need to show Spring configuration of datasource and transaction management. Basically, add annotation `@Transactional` to method or class level should solve it (but need to remove your transaction boundary codes). – Wundwin Born Jul 08 '14 at 09:21
  • @suninsky what do you mean by transaction boundary codes. Would appreciate your answer! – Carol.Kar Jul 08 '14 at 09:31
  • 1
    @Vivien I means to remove your transaction boundary code lines like `begin(); commit(); rollback()`, as they are not required. Bcuz Spring container will manage them for you. – Wundwin Born Jul 08 '14 at 09:38

2 Answers2

2

You need to control commit/rollback when finalising the changes made on database.

try {
  em.getTransaction().begin(); 
  ..do what you need to do..
  em.getTransaction().commit();
} catch(Exception ex) {
  em.getTransaction().rollback();
  throw ex; // let upper methods know this did not go well
}
Whome
  • 10,181
  • 6
  • 53
  • 65
  • Thx for your answer! Please have a look at my update! – Carol.Kar Jul 08 '14 at 09:05
  • 1
    Ok I see it now you have hit two separate transaction worlds. Manual TX handling by programming code (my example) and container managed transaction (Spring framework) conflict. See this answer, you need to put @Transactional annotation on your method so that it joins the container-managed transaction AND remove manual begin()+commit()+rollback calls. http://stackoverflow.com/questions/2427223/spring-transactional-not-creating-required-transaction – Whome Jul 08 '14 at 09:22
1

you should try it this way:

@Resource
UserTransaction mUserTransaction;

public void delete()
{
        mUserTransaction.begin();
        em.createQuery("DELETE FROM Datatable").executeUpdate();
        mUserTransaction.commit();
}
nano_nano
  • 12,351
  • 8
  • 55
  • 83