Menu
I want to remove all rows from a specific table using JPA.
What I did:
public class EmptyBomTables extends HttpServlet {
private static final long serialVersionUID = 1L;
@PersistenceContext(unitName = "xxx")
public EntityManager em;
@Resource
UserTransaction utx;
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
Query q1 = em.createQuery("DELETE FROM BomModule");
Query q2 = em.createQuery("DELETE FROM BomItem");
Query q3 = em.createQuery("DELETE FROM ItemModuleConnection");
Query q4 = em.createQuery("DELETE FROM ModuleConnection");
try {
utx.begin();
} catch (NotSupportedException | SystemException e) {
e.printStackTrace();
}
q1.executeUpdate();
q2.executeUpdate();
q3.executeUpdate();
q4.executeUpdate();
try {
utx.commit();
} catch (SecurityException | IllegalStateException | RollbackException | HeuristicMixedException | HeuristicRollbackException | SystemException e) {
e.printStackTrace();
}
}
}
Error:
09:30:30,197 ERROR [org.apache.catalina.core.ContainerBase.[jboss.web].[default-host].[/SSIS2].[EmptyBomTables]] (http-localhost-127.0.0.1-8080-1) Servlet.service() for servlet EmptyBomTables threw exception: javax.persistence.TransactionRequiredException: Executing an update/delete query
at org.hibernate.ejb.AbstractQueryImpl.executeUpdate(AbstractQueryImpl.java:96) [hibernate-entitymanager-4.0.1.Final.jar:4.0.1.Final]
at org.jboss.as.jpa.container.QueryNonTxInvocationDetacher.executeUpdate(QueryNonTxInvocationDetacher.java:80) [jboss-as-jpa-7.1.1.Final.jar:7.1.1.Final]
at com.sicap.ssis2.bom.EmptyBomTables.doGet(EmptyBomTables.java:50) [classes:]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:734) [jboss-servlet-api_3.0_spec-1.0.0.Final.jar:1.0.0.Final]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:847) [jboss-servlet-api_3.0_spec-1.0.0.Final.jar:1.0.0.Final]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:329) [jbossweb-7.0.13.Final.jar:]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:248) [jbossweb-7.0.13.Final.jar:]
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:275) [jbossweb-7.0.13.Final.jar:]
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:161) [jbossweb-7.0.13.Final.jar:]
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:397) [jbossweb-7.0.13.Final.jar:]
at org.jboss.as.jpa.interceptor.WebNonTxEmCloserValve.invoke(WebNonTxEmCloserValve.java:50) [jboss-as-jpa-7.1.1.Final.jar:7.1.1.Final]
at org.jboss.as.web.security.SecurityContextAssociationValve.invoke(SecurityContextAssociationValve.java:153) [jboss-as-web-7.1.1.Final.jar:7.1.1.Final]
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:155) [jbossweb-7.0.13.Final.jar:]
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) [jbossweb-7.0.13.Final.jar:]
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) [jbossweb-7.0.13.Final.jar:]
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:368) [jbossweb-7.0.13.Final.jar:]
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:877) [jbossweb-7.0.13.Final.jar:]
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:671) [jbossweb-7.0.13.Final.jar:]
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:930) [jbossweb-7.0.13.Final.jar:]
at java.lang.Thread.run(Thread.java:722) [rt.jar:1.7.0_09]
09:30:30,218 ERROR [org.jboss.as.txn] (http-localhost-127.0.0.1-8080-1) JBAS010152: APPLICATION ERROR: transaction still active in request with status 1
What am I doing wrong?
517
The deleteAll() internally use delete() method only. The above one will delete all records that belong to that repository. The deleteAll() internally uses findAll() and delete() method as below. Let's see in below code how to use the Spring Data JPA CrudRepository delete() and deleteAll() method for delete operation.
To delete a record from database, EntityManager interface provides remove() method. The remove() method uses primary key to delete the particular record.
Some confusing explanation: flush(); Flushing is the process of synchronizing the underlying persistent store with persistable state held in memory.it will update or insert into your tables in the running transaction, but it may not commit those changes.
In JPA, to delete an entity, the entity itself must be managed, meaning that it is present in the persistence context. This means that the calling application should have already loaded or accessed the entity and is now issuing a command to remove it.
That was the problem, everything has to be in the same try/catch:
try {
utx.begin();
Query q3 = em.createQuery("DELETE FROM ItemModuleConnection");
Query q4 = em.createQuery("DELETE FROM ModuleConnection");
Query q1 = em.createQuery("DELETE FROM BomModule");
Query q2 = em.createQuery("DELETE FROM BomItem");
q1.executeUpdate();
q2.executeUpdate();
q3.executeUpdate();
q4.executeUpdate();
utx.commit();
} catch (NotSupportedException | SystemException | SecurityException | IllegalStateException | RollbackException | HeuristicMixedException | HeuristicRollbackException e) {
e.printStackTrace();
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With